git.cworth.org Git - turbot/blob - turbot/sheets.py

   1 PUZZLE_TEMPLATE_ID = "1drSoyrE4gM3JaGweDkOybwXWdKPIDTfUmB1gQCYS3Uw"
   2 PUZZLE_TEMPLATE_SHEETS = ["Text", "Square grid", "Hex Grid",
   3                           "Formula reference: indexing"]
   4
   5 def sheets_create(turb, name):
   6     """Create a new sheet with the given name.
   7
   8     Returns the dict with 'id' and 'url'of the spreadsheet.
   9     """
  10
  11     # Create a new spreadsheet
  12     spreadsheet_body = {
  13         'properties': {
  14             'title': name
  15         }
  16     }
  17
  18     new_sheet = turb.sheets.create(body=spreadsheet_body).execute()
  19
  20     # Now that we've created a new sheet, we need to also allow anyone
  21     # with the link to the sheet to be able to edit it.
  22     turb.permissions.create(fileId=new_sheet["spreadsheetId"],
  23                             body={'type': 'anyone', 'role': 'writer'},
  24                             fields='id').execute()
  25
  26     return {
  27         'id': new_sheet["spreadsheetId"],
  28         'url': new_sheet["spreadsheetUrl"]
  29     }
  30
  31 def sheets_create_for_puzzle(turb, puzzle):
  32     """Creates a new sheet for a puzzle of the given name
  33
  34     Like sheets_create(), but also copies the puzzle template sheet.
  35
  36     Here, 'puzzle' is a dict that must have a 'name' key and may optionally
  37     have a 'channel_url' or 'url' key."""
  38
  39     # First create the new sheet
  40     new_sheet = sheets_create(turb, puzzle['name'])
  41
  42     # Insert some useful links into the sheet
  43     url_link=''
  44     if 'url' in puzzle:
  45         url_text = "Original puzzle is at: {}".format(puzzle['url'])
  46         url_link = puzzle['url']
  47     else:
  48         url_text = ''
  49
  50     channel_url_link = ''
  51     if 'channel_url' in puzzle:
  52         channel_url_text = "Discussion for this puzzle is at: {}".format(
  53             puzzle['channel_url'])
  54         channel_url_link = puzzle['channel_url']
  55     else:
  56         channel_url_text = ''
  57
  58     turb.sheets.values().append(
  59         spreadsheetId=new_sheet['id'],
  60         range='A1:A2',
  61         valueInputOption='USER_ENTERED',
  62         insertDataOption='INSERT_ROWS',
  63         body={
  64             'range': 'A1:A2',
  65             'values': [
  66                 ['=HYPERLINK("'+url_link+'","'+url_text+'")'],
  67                 ['=HYPERLINK("'+channel_url_link+'","'+channel_url_text+'")']
  68             ]
  69         }).execute()
  70
  71     # Copy some sheets from the Template spreadsheet
  72
  73     response = turb.sheets.get(spreadsheetId=PUZZLE_TEMPLATE_ID).execute()
  74
  75     for sheet in response["sheets"]:
  76         if sheet["properties"]["title"] in PUZZLE_TEMPLATE_SHEETS:
  77             res = turb.sheets.sheets().copyTo(
  78                 spreadsheetId=PUZZLE_TEMPLATE_ID,
  79                 sheetId=sheet["properties"]["sheetId"],
  80                 body={
  81                     "destinationSpreadsheetId": new_sheet['id']
  82                 }).execute()
  83             rename_sheet(turb, new_sheet['id'], res['sheetId'],
  84                          sheet["properties"]["title"])
  85
  86     return new_sheet
  87
  88 def rename_sheet(turb, spreadsheet_id, sheet_id, name):
  89
  90     body = {
  91         'requests': [{
  92             'updateSheetProperties': {
  93                 'properties': {
  94                     'sheetId': sheet_id,
  95                     'title': name
  96                 },
  97                 'fields': 'title'
  98             }
  99         }]
 100     }
 101
 102     turb.sheets.batchUpdate(spreadsheetId=spreadsheet_id,
 103                             body=body
 104                             ).execute()
 105
 106 def rename_spreadsheet(turb, url, newName):
 107     id = extractIdFromSheetUrl(url)
 108     requests = []
 109     requests.append({
 110         'updateSpreadsheetProperties': {
 111             'properties': {
 112                 'title': newName
 113             },
 114             'fields': 'title'
 115         }
 116     })
 117
 118     body = {
 119         'requests': requests
 120     }
 121
 122     turb.sheets.batchUpdate(spreadsheetId = id,
 123                             body=body
 124                             ).execute()
 125
 126 def extractIdFromSheetUrl(url):
 127     # Google sheet ids are between the /d/ and /edit in the url, like
 128     # https://docs.google.com/spreadsheets/d/1dxHBzjen...-LaXeVPrg/edit#gid=0
 129     startIndex = url.find('/d/') + 3
 130     endIndex = url.find('/edit')
 131     return url[startIndex : endIndex]