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_folder(turb, folder_name):
   6     """Create a new folder within Google Drive
   7
   8     Returns the id of the created folder."""
   9
  10     body = {
  11         "name": folder_name,
  12         "mimeType": "application/vnd.google-apps.folder"
  13     }
  14
  15     folder = turb.files.create(body=body, fields='id').execute()
  16
  17     return folder.get('id')
  18
  19 def sheets_create(turb, name, folder_id):
  20     """Create a new spreadsheet with the given name.
  21
  22     Returns a dict with 'id' and 'url' of the spreadsheet
  23     """
  24
  25     body = {
  26         "name": name,
  27         "parents": [folder_id],
  28         "mimeType": "application/vnd.google-apps.spreadsheet"
  29     }
  30
  31     spreadsheet = turb.files.create(body=body, fields='id').execute()
  32     id = spreadsheet['id']
  33
  34     # The files.create call gives us the ID for our new sheet, but we
  35     # need the URL for it as well. So we get that with the sheets API.
  36     spreadsheet = turb.sheets.get(spreadsheetId=id,
  37                                   fields='spreadsheetUrl').execute()
  38     url = spreadsheet['spreadsheetUrl']
  39
  40     # Finally, we want to also allow anyone with the link to the sheet
  41     # to be able to edit it.
  42     turb.permissions.create(fileId=id,
  43                             body={'type': 'anyone', 'role': 'writer'},
  44                             fields='').execute()
  45
  46     return {
  47         'id': id,
  48         'url': url
  49     }
  50
  51 def sheets_create_for_puzzle(turb, puzzle, folder_id):
  52     """Creates a new sheet for a puzzle of the given name
  53
  54     Like sheets_create(), but also copies the puzzle template sheet.
  55
  56     Here, 'puzzle' is a dict that must have a 'name' key and may optionally
  57     have a 'channel_url' or 'url' key.
  58     """
  59
  60     # First create the new spreadsheet
  61     spreadsheet = sheets_create(turb, puzzle['name'], folder_id)
  62     spreadsheet_id = spreadsheet['id']
  63     spreadsheet_url = spreadsheet['url']
  64
  65     # And fetch the individual "sheets" from the spreadsheet
  66     spreadsheet = turb.sheets.get(spreadsheetId=spreadsheet_id,
  67                                   fields='sheets').execute()
  68     first_sheet_id = spreadsheet['sheets'][0]['properties']['sheetId']
  69
  70     # Then, copy some useful sheets over from the Template spreadsheet
  71     response = turb.sheets.get(spreadsheetId=PUZZLE_TEMPLATE_ID).execute()
  72
  73     for sheet in response["sheets"]:
  74         if sheet["properties"]["title"] in PUZZLE_TEMPLATE_SHEETS:
  75             res = turb.sheets.sheets().copyTo(
  76                 spreadsheetId=PUZZLE_TEMPLATE_ID,
  77                 sheetId=sheet["properties"]["sheetId"],
  78                 body={
  79                     "destinationSpreadsheetId": spreadsheet_id
  80                 }).execute()
  81             # Rename each copied sheet to match the name from the template
  82             sheet_name = sheet["properties"]["title"]
  83             # Except for "Text" which we rename to the puzzle name
  84             if sheet_name == "Text":
  85                 sheet_name = puzzle['name']
  86             rename_sheet(turb, spreadsheet_id, res['sheetId'], sheet_name)
  87
  88     # Next, delete the blank sheet made at spreadsheet creation time
  89     delete_sheet(turb, spreadsheet_id, first_sheet_id)
  90
  91     # Insert some useful links into the sheet where expected
  92     if 'url' in puzzle:
  93         url_link = puzzle['url']
  94         url_text = "Original puzzle link"
  95         spreadsheet_update_data(
  96             turb, spreadsheet_id, "B1:B1",
  97             '=HYPERLINK("{}","{}")'.format(url_link, url_text))
  98
  99     if 'channel_url' in puzzle:
 100         url_link = puzzle['channel_url']
 101         url_text = "Slack channel link"
 102         spreadsheet_update_data(
 103             turb, spreadsheet_id, "B2:B2",
 104             '=HYPERLINK("{}","{}")'.format(url_link, url_text))
 105
 106     return {
 107         'id': spreadsheet_id,
 108         'url': spreadsheet_url
 109     }
 110
 111 def spreadsheet_update_data(turb, spreadsheet_id, range, text):
 112
 113     turb.sheets.values().update(
 114         spreadsheetId=spreadsheet_id,
 115         range=range,
 116         valueInputOption='USER_ENTERED',
 117         body={
 118             'range': range,
 119             'values': [
 120                 [text]
 121             ]
 122         }
 123     ).execute()
 124
 125 def delete_sheet(turb, spreadsheet_id, sheet_id):
 126
 127     body = {
 128         'requests': [{
 129             'deleteSheet': {
 130                 'sheetId': sheet_id,
 131             },
 132         }]
 133     }
 134
 135     turb.sheets.batchUpdate(spreadsheetId=spreadsheet_id,
 136                             body=body
 137                             ).execute()
 138
 139 def rename_sheet(turb, spreadsheet_id, sheet_id, name):
 140
 141     body = {
 142         'requests': [{
 143             'updateSheetProperties': {
 144                 'properties': {
 145                     'sheetId': sheet_id,
 146                     'title': name
 147                 },
 148                 'fields': 'title'
 149             }
 150         }]
 151     }
 152
 153     turb.sheets.batchUpdate(spreadsheetId=spreadsheet_id,
 154                             body=body
 155                             ).execute()
 156
 157 def spreadsheet_id_from_url(url):
 158     # Google sheet ids are between the /d/ and /edit in the url, like
 159     # https://docs.google.com/spreadsheets/d/1dxHBzjen...-LaXeVPrg/edit#gid=0
 160     start = url.find('/d/') + 3
 161     end = url.find('/edit')
 162     return url[start:end]
 163
 164 def rename_spreadsheet(turb, spreadsheet_url, name):
 165
 166     spreadsheet_id = spreadsheet_id_from_url(spreadsheet_url)
 167
 168     body = {
 169         'requests': [{
 170             'updateSpreadsheetProperties': {
 171                 'properties': {
 172                     'title': name
 173                 },
 174                 'fields': 'title'
 175             }
 176         }]
 177     }
 178
 179     turb.sheets.batchUpdate(spreadsheetId=spreadsheet_id,
 180                             body=body
 181                             ).execute()