"Formula reference: indexing"]
def sheets_create(turb, name):
- """Create a new sheet with the given name.
+ """Create a new spreadsheet with the given name.
- Returns the dict with 'id' and 'url'of the spreadsheet.
+ Returns a dict with 'id' and 'url' of the spreadsheet
+ """
+
+ spreadsheet = create_spreadsheet(turb, name)
+
+ return {
+ 'id': spreadsheet['spreadsheetId'],
+ 'url': spreadsheet['spreadsheetUrl']
+ }
+
+def create_spreadsheet(turb, name):
+ """
+ Returns the request's dict which has at least the following keys:
+
+ ['spreadsheetId']: ID for this spreadsheet
+ ['spreadsheetUrl']: URL of this spreadsheet
+ ['sheets'][0]['properties']['sheetId']: ID of first sheet inside
"""
# Create a new spreadsheet
}
}
- new_sheet = turb.sheets.create(body=spreadsheet_body).execute()
+ spreadsheet = turb.sheets.create(body=spreadsheet_body).execute()
- # Now that we've created a new sheet, we need to also allow anyone
- # with the link to the sheet to be able to edit it.
- turb.permissions.create(fileId=new_sheet["spreadsheetId"],
+ # Now that we've created a new spreadsheet, we need to also allow
+ # anyone with the link to the sheet to be able to edit it.
+ turb.permissions.create(fileId=spreadsheet["spreadsheetId"],
body={'type': 'anyone', 'role': 'writer'},
fields='id').execute()
- return {
- 'id': new_sheet["spreadsheetId"],
- 'url': new_sheet["spreadsheetUrl"]
- }
+ return spreadsheet
def sheets_create_for_puzzle(turb, puzzle):
"""Creates a new sheet for a puzzle of the given name
Like sheets_create(), but also copies the puzzle template sheet.
Here, 'puzzle' is a dict that must have a 'name' key and may optionally
- have a 'channel_url' or 'url' key."""
+ have a 'channel_url' or 'url' key.
+ """
+
+ # First create the new spreadsheet
+ spreadsheet = create_spreadsheet(turb, puzzle['name'])
+ spreadsheet_id = spreadsheet['spreadsheetId']
+
+ # Then, copy some useful sheets over from the Template spreadsheet
+
+ response = turb.sheets.get(spreadsheetId=PUZZLE_TEMPLATE_ID).execute()
+
+ for sheet in response["sheets"]:
+ if sheet["properties"]["title"] in PUZZLE_TEMPLATE_SHEETS:
+ res = turb.sheets.sheets().copyTo(
+ spreadsheetId=PUZZLE_TEMPLATE_ID,
+ sheetId=sheet["properties"]["sheetId"],
+ body={
+ "destinationSpreadsheetId": spreadsheet_id
+ }).execute()
+ # Rename each copied sheet to match original name
+ rename_sheet(turb, spreadsheet_id, res['sheetId'],
+ sheet["properties"]["title"])
- # First create the new sheet
- new_sheet = sheets_create(turb, puzzle['name'])
+ # Next, delete the blank sheet that's was created before the template
+ sheet_id = spreadsheet['sheets'][0]['properties']['sheetId']
+ delete_sheet(turb, spreadsheet_id, sheet_id)
- # Insert some useful links into the sheet
- url_link=''
+ # Insert some useful links into the sheet where expected
if 'url' in puzzle:
- url_text = "Original puzzle is at: {}".format(puzzle['url'])
url_link = puzzle['url']
- else:
- url_text = ''
+ url_text = "Original puzzle is at: {}".format(url_link)
+ spreadsheet_insert_data(
+ turb, spreadsheet_id, "B1:B1",
+ '=HYPERLINK("{}","{}")'.format(url_link, url_text))
- channel_url_link = ''
if 'channel_url' in puzzle:
- channel_url_text = "Discussion for this puzzle is at: {}".format(
- puzzle['channel_url'])
- channel_url_link = puzzle['channel_url']
- else:
- channel_url_text = ''
+ url_link = puzzle['channel_url']
+ url_text = "Discussion for this puzzle is at: {}".format(url_link)
+ spreadsheet_insert_data(
+ turb, spreadsheet_id, "B2:B2",
+ '=HYPERLINK("{}","{}")'.format(url_link, url_text))
+
+ return {
+ 'id': spreadsheet_id,
+ 'url': spreadsheet['spreadsheetUrl']
+ }
+
+def spreadsheet_insert_data(turb, spreadsheet_id, range, text):
turb.sheets.values().append(
- spreadsheetId=new_sheet['id'],
- range='A1:A2',
+ spreadsheetId=spreadsheet_id,
+ range=range,
valueInputOption='USER_ENTERED',
insertDataOption='INSERT_ROWS',
body={
- 'range': 'A1:A2',
+ 'range': range,
'values': [
- ['=HYPERLINK("'+url_link+'","'+url_text+'")'],
- ['=HYPERLINK("'+channel_url_link+'","'+channel_url_text+'")']
+ [text]
]
- }).execute()
-
- # Copy some sheets from the Template spreadsheet
+ }
+ ).execute()
- response = turb.sheets.get(spreadsheetId=PUZZLE_TEMPLATE_ID).execute()
+def delete_sheet(turb, spreadsheet_id, sheet_id):
- for sheet in response["sheets"]:
- if sheet["properties"]["title"] in PUZZLE_TEMPLATE_SHEETS:
- res = turb.sheets.sheets().copyTo(
- spreadsheetId=PUZZLE_TEMPLATE_ID,
- sheetId=sheet["properties"]["sheetId"],
- body={
- "destinationSpreadsheetId": new_sheet['id']
- }).execute()
- rename_sheet(turb, new_sheet['id'], res['sheetId'],
- sheet["properties"]["title"])
+ body = {
+ 'requests': [{
+ 'deleteSheet': {
+ 'sheetId': sheet_id,
+ },
+ }]
+ }
- return new_sheet
+ turb.sheets.batchUpdate(spreadsheetId=spreadsheet_id,
+ body=body
+ ).execute()
def rename_sheet(turb, spreadsheet_id, sheet_id, name):
body=body
).execute()
-def rename_spreadsheet(turb, url, newName):
- id = extractIdFromSheetUrl(url)
- requests = []
- requests.append({
- 'updateSpreadsheetProperties': {
- 'properties': {
- 'title': newName
- },
- 'fields': 'title'
- }
- })
+def spreadsheet_id_from_url(url):
+ # Google sheet ids are between the /d/ and /edit in the url, like
+ # https://docs.google.com/spreadsheets/d/1dxHBzjen...-LaXeVPrg/edit#gid=0
+ start = url.find('/d/') + 3
+ end = url.find('/edit')
+ return url[start:end]
+
+def rename_spreadsheet(turb, spreadsheet_url, name):
+
+ spreadsheet_id = spreadsheet_id_from_url(spreadsheet_url)
body = {
- 'requests': requests
+ 'requests': [{
+ 'updateSpreadsheetProperties': {
+ 'properties': {
+ 'title': name
+ },
+ 'fields': 'title'
+ }
+ }]
}
- turb.sheets.batchUpdate(spreadsheetId = id,
+ turb.sheets.batchUpdate(spreadsheetId=spreadsheet_id,
body=body
).execute()
-
-def extractIdFromSheetUrl(url):
- # Google sheet ids are between the /d/ and /edit in the url, like
- # https://docs.google.com/spreadsheets/d/1dxHBzjen...-LaXeVPrg/edit#gid=0
- startIndex = url.find('/d/') + 3
- endIndex = url.find('/edit')
- return url[startIndex : endIndex]
projects / turbot / commitdiff