1 PUZZLE_TEMPLATE_ID = "1drSoyrE4gM3JaGweDkOybwXWdKPIDTfUmB1gQCYS3Uw"
2 PUZZLE_TEMPLATE_SHEETS = ["Text", "Square grid", "Hex Grid",
3 "Formula reference: indexing"]
5 def sheets_create_folder(turb, folder_name, parents = None):
6 """Create a new folder within Google Drive
8 Returns the id of the created folder."""
12 "mimeType": "application/vnd.google-apps.folder"
16 body["parents"] = parents
18 folder = turb.files.create(body=body, fields='id').execute()
20 return folder.get('id')
22 def sheets_create(turb, name, folder_id):
23 """Create a new spreadsheet with the given name.
25 Returns a dict with 'id' and 'url' of the spreadsheet
30 "parents": [folder_id],
31 "mimeType": "application/vnd.google-apps.spreadsheet"
34 spreadsheet = turb.files.create(body=body, fields='id').execute()
35 id = spreadsheet['id']
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']
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'},
54 def sheets_create_for_puzzle(turb, puzzle, folder_id):
55 """Creates a new sheet for a puzzle of the given name
57 Like sheets_create(), but also copies the puzzle template sheet.
59 Here, 'puzzle' is a dict that must have a 'name' key and may optionally
60 have a 'channel_url' or 'url' key.
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']
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']
73 # Then, copy some useful sheets over from the Template spreadsheet
74 response = turb.sheets.get(spreadsheetId=PUZZLE_TEMPLATE_ID).execute()
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"],
82 "destinationSpreadsheetId": spreadsheet_id
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)
91 # Next, delete the blank sheet made at spreadsheet creation time
92 delete_sheet(turb, spreadsheet_id, first_sheet_id)
94 # Insert some useful links into the sheet where expected
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))
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))
110 'id': spreadsheet_id,
111 'url': spreadsheet_url
114 def spreadsheet_update_data(turb, spreadsheet_id, range, text):
116 turb.sheets.values().update(
117 spreadsheetId=spreadsheet_id,
119 valueInputOption='USER_ENTERED',
128 def delete_sheet(turb, spreadsheet_id, sheet_id):
138 turb.sheets.batchUpdate(spreadsheetId=spreadsheet_id,
142 def rename_sheet(turb, spreadsheet_id, sheet_id, name):
146 'updateSheetProperties': {
156 turb.sheets.batchUpdate(spreadsheetId=spreadsheet_id,
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]
167 def rename_spreadsheet(turb, spreadsheet_url, name):
169 spreadsheet_id = spreadsheet_id_from_url(spreadsheet_url)
173 'updateSpreadsheetProperties': {
182 turb.sheets.batchUpdate(spreadsheetId=spreadsheet_id,