-from flask import current_app
-import pickle
-import os.path
-import os
+PUZZLE_TEMPLATE_ID = "1drSoyrE4gM3JaGweDkOybwXWdKPIDTfUmB1gQCYS3Uw"
+PUZZLE_TEMPLATE_SHEETS = ["Text", "Square grid", "Hex Grid",
+ "Formula reference: indexing"]
-from googleapiclient.discovery import build
-
-TEMPLATE_SHEET_ID = "1drSoyrE4gM3JaGweDkOybwXWdKPIDTfUmB1gQCYS3Uw"
-TEMPLATE_SHEET_SHEETS = ["Text", "Grid"]
-
-turbot_deploy_dir = os.environ.get('TURBOT_DEPLOY_DIR', '.')
-TOKEN_FILE = "{}/.gsheets-token.pickle".format(turbot_deploy_dir)
-
-creds = None
-
-def sheets_create(name):
+def sheets_create(turb, name):
"""Create a new sheet with the given name.
- Returns the URL for the spreadsheet.
+ Returns the dict with 'id' and 'url'of the spreadsheet.
"""
- global creds
-
- # The file token.pickle stores token from last login/refresh
- if not creds:
- if os.path.exists(TOKEN_FILE):
- with open(TOKEN_FILE, 'rb') as token:
- creds = pickle.load(token)
-
- # If there are no (valid) credentials available, give up
- if not creds or not creds.valid:
- current_app.logger.error("No token found in {}".format(TOKEN_FILE))
- current_app.logger.error("Try running ./gsheets-authenticate.py")
- return None
-
- service = build('sheets', 'v4', credentials=creds)
- sheets = service.spreadsheets()
# Create a new spreadsheet
spreadsheet_body = {
}
}
- new_sheet = sheets.create(body=spreadsheet_body).execute()
- spreadsheet_url = new_sheet["spreadsheetUrl"]
- spreadsheet_id = new_sheet["spreadsheetId"]
+ new_sheet = 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"],
+ body={'type': 'anyone', 'role': 'writer'},
+ fields='id').execute()
+
+ return {
+ 'id': new_sheet["spreadsheetId"],
+ 'url': new_sheet["spreadsheetUrl"]
+ }
+
+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."""
+
+ # First create the new sheet
+ new_sheet = sheets_create(turb, puzzle['name'])
+
+ # Insert some useful links into the sheet
+ url_link=''
+ if 'url' in puzzle:
+ url_text = "Original puzzle is at: {}".format(puzzle['url'])
+ url_link = puzzle['url']
+ else:
+ 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 = ''
+
+ turb.sheets.values().append(
+ spreadsheetId=new_sheet['id'],
+ range='A1:A2',
+ valueInputOption='USER_ENTERED',
+ insertDataOption='INSERT_ROWS',
+ body={
+ 'range': 'A1:A2',
+ 'values': [
+ ['=HYPERLINK("'+url_link+'","'+url_text+'")'],
+ ['=HYPERLINK("'+channel_url_link+'","'+channel_url_text+'")']
+ ]
+ }).execute()
# Copy some sheets from the Template spreadsheet
- response = sheets.get(spreadsheetId=TEMPLATE_SHEET_ID).execute()
+ response = turb.sheets.get(spreadsheetId=PUZZLE_TEMPLATE_ID).execute()
for sheet in response["sheets"]:
- if sheet["properties"]["title"] in TEMPLATE_SHEET_SHEETS:
- sheets.sheets().copyTo(spreadsheetId=TEMPLATE_SHEET_ID,
- sheetId=sheet["properties"]["sheetId"],
- body={
- "destinationSpreadsheetId":
- spreadsheet_id
- }).execute()
-
- return spreadsheet_url
+ 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"])
+
+ return new_sheet
+
+def rename_sheet(turb, spreadsheet_id, sheet_id, name):
+
+ body = {
+ 'requests': [{
+ 'updateSheetProperties': {
+ 'properties': {
+ 'sheetId': sheet_id,
+ 'title': name
+ },
+ 'fields': 'title'
+ }
+ }]
+ }
+
+ turb.sheets.batchUpdate(spreadsheetId=spreadsheet_id,
+ body=body
+ ).execute()
+
+def rename_spreadsheet(turb, url, newName):
+ id = extractIdFromSheetUrl(url)
+ requests = []
+ requests.append({
+ 'updateSpreadsheetProperties': {
+ 'properties': {
+ 'title': newName
+ },
+ 'fields': 'title'
+ }
+ })
+
+ body = {
+ 'requests': requests
+ }
+
+ turb.sheets.batchUpdate(spreadsheetId = 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 / blobdiff