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