1 PUZZLE_TEMPLATE_ID = "1drSoyrE4gM3JaGweDkOybwXWdKPIDTfUmB1gQCYS3Uw"
2 PUZZLE_TEMPLATE_SHEETS = ["Text", "Square grid", "Hex Grid",
3 "Formula reference: indexing"]
5 def sheets_create(turb, name):
6 """Create a new spreadsheet with the given name.
8 Returns a dict with 'id' and 'url' of the spreadsheet
11 spreadsheet = create_spreadsheet(turb, name)
14 'id': spreadsheet['spreadsheetId'],
15 'url': spreadsheet['spreadsheetUrl']
18 def create_spreadsheet(turb, name):
20 Returns the request's dict which has at least the following keys:
22 ['spreadsheetId']: ID for this spreadsheet
23 ['spreadsheetUrl']: URL of this spreadsheet
24 ['sheets'][0]['properties']['sheetId']: ID of first sheet inside
27 # Create a new spreadsheet
34 spreadsheet = turb.sheets.create(body=spreadsheet_body).execute()
36 # Now that we've created a new spreadsheet, we need to also allow
37 # anyone with the link to the sheet to be able to edit it.
38 turb.permissions.create(fileId=spreadsheet["spreadsheetId"],
39 body={'type': 'anyone', 'role': 'writer'},
40 fields='id').execute()
44 def sheets_create_for_puzzle(turb, puzzle):
45 """Creates a new sheet for a puzzle of the given name
47 Like sheets_create(), but also copies the puzzle template sheet.
49 Here, 'puzzle' is a dict that must have a 'name' key and may optionally
50 have a 'channel_url' or 'url' key.
53 # First create the new spreadsheet
54 spreadsheet = create_spreadsheet(turb, puzzle['name'])
55 spreadsheet_id = spreadsheet['spreadsheetId']
57 # Then, copy some useful sheets over from the Template spreadsheet
59 response = turb.sheets.get(spreadsheetId=PUZZLE_TEMPLATE_ID).execute()
61 for sheet in response["sheets"]:
62 if sheet["properties"]["title"] in PUZZLE_TEMPLATE_SHEETS:
63 res = turb.sheets.sheets().copyTo(
64 spreadsheetId=PUZZLE_TEMPLATE_ID,
65 sheetId=sheet["properties"]["sheetId"],
67 "destinationSpreadsheetId": spreadsheet_id
69 # Rename each copied sheet to match original name
70 rename_sheet(turb, spreadsheet_id, res['sheetId'],
71 sheet["properties"]["title"])
73 # Next, delete the blank sheet that's was created before the template
74 sheet_id = spreadsheet['sheets'][0]['properties']['sheetId']
75 delete_sheet(turb, spreadsheet_id, sheet_id)
77 # Insert some useful links into the sheet where expected
79 url_link = puzzle['url']
80 url_text = "Original puzzle link"
81 spreadsheet_insert_data(
82 turb, spreadsheet_id, "B1:B1",
83 '=HYPERLINK("{}","{}")'.format(url_link, url_text))
85 if 'channel_url' in puzzle:
86 url_link = puzzle['channel_url']
87 url_text = "Slack channel link"
88 spreadsheet_insert_data(
89 turb, spreadsheet_id, "B2:B2",
90 '=HYPERLINK("{}","{}")'.format(url_link, url_text))
94 'url': spreadsheet['spreadsheetUrl']
97 def spreadsheet_insert_data(turb, spreadsheet_id, range, text):
99 turb.sheets.values().append(
100 spreadsheetId=spreadsheet_id,
102 valueInputOption='USER_ENTERED',
103 insertDataOption='INSERT_ROWS',
112 def delete_sheet(turb, spreadsheet_id, sheet_id):
122 turb.sheets.batchUpdate(spreadsheetId=spreadsheet_id,
126 def rename_sheet(turb, spreadsheet_id, sheet_id, name):
130 'updateSheetProperties': {
140 turb.sheets.batchUpdate(spreadsheetId=spreadsheet_id,
144 def spreadsheet_id_from_url(url):
145 # Google sheet ids are between the /d/ and /edit in the url, like
146 # https://docs.google.com/spreadsheets/d/1dxHBzjen...-LaXeVPrg/edit#gid=0
147 start = url.find('/d/') + 3
148 end = url.find('/edit')
149 return url[start:end]
151 def rename_spreadsheet(turb, spreadsheet_url, name):
153 spreadsheet_id = spreadsheet_id_from_url(spreadsheet_url)
157 'updateSpreadsheetProperties': {
166 turb.sheets.batchUpdate(spreadsheetId=spreadsheet_id,