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_insert_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_insert_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_insert_data(turb, spreadsheet_id, range, text):
 112
 113     turb.sheets.values().append(
 114         spreadsheetId=spreadsheet_id,
 115         range=range,
 116         valueInputOption='USER_ENTERED',
 117         insertDataOption='INSERT_ROWS',
 118         body={
 119             'range': range,
 120             'values': [
 121                 [text]
 122             ]
 123         }
 124     ).execute()
 125
 126 def delete_sheet(turb, spreadsheet_id, sheet_id):
 127
 128     body = {
 129         'requests': [{
 130             'deleteSheet': {
 131                 'sheetId': sheet_id,
 132             },
 133         }]
 134     }
 135
 136     turb.sheets.batchUpdate(spreadsheetId=spreadsheet_id,
 137                             body=body
 138                             ).execute()
 139
 140 def rename_sheet(turb, spreadsheet_id, sheet_id, name):
 141
 142     body = {
 143         'requests': [{
 144             'updateSheetProperties': {
 145                 'properties': {
 146                     'sheetId': sheet_id,
 147                     'title': name
 148                 },
 149                 'fields': 'title'
 150             }
 151         }]
 152     }
 153
 154     turb.sheets.batchUpdate(spreadsheetId=spreadsheet_id,
 155                             body=body
 156                             ).execute()
 157
 158 def spreadsheet_id_from_url(url):
 159     # Google sheet ids are between the /d/ and /edit in the url, like
 160     # https://docs.google.com/spreadsheets/d/1dxHBzjen...-LaXeVPrg/edit#gid=0
 161     start = url.find('/d/') + 3
 162     end = url.find('/edit')
 163     return url[start:end]
 164
 165 def rename_spreadsheet(turb, spreadsheet_url, name):
 166
 167     spreadsheet_id = spreadsheet_id_from_url(spreadsheet_url)
 168
 169     body = {
 170         'requests': [{
 171             'updateSpreadsheetProperties': {
 172                 'properties': {
 173                     'title': name
 174                 },
 175                 'fields': 'title'
 176             }
 177         }]
 178     }
 179
 180     turb.sheets.batchUpdate(spreadsheetId=spreadsheet_id,
 181                             body=body
 182                             ).execute()