-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
+def sheets_create_folder(turb, folder_name, parents = None):
+ """Create a new folder within Google Drive
-# If modifying these scopes, delete the file token.pickle.
-SCOPES = ['https://www.googleapis.com/auth/spreadsheets']
+ Returns the id of the created folder."""
-turbot_deploy_dir = os.environ.get('TURBOT_DEPLOY_DIR', '.')
-TOKEN_FILE = "{}/.gsheets-token.pickle".format(turbot_deploy_dir)
+ body = {
+ "name": folder_name,
+ "mimeType": "application/vnd.google-apps.folder"
+ }
+
+ if parents:
+ body["parents"] = parents
+
+ folder = turb.files.create(body=body, fields='id').execute()
+
+ return folder.get('id')
+
+def sheets_create(turb, name, folder_id):
+ """Create a new spreadsheet with the given name.
+
+ Returns a dict with 'id' and 'url' of the spreadsheet
+ """
+
+ body = {
+ "name": name,
+ "parents": [folder_id],
+ "mimeType": "application/vnd.google-apps.spreadsheet"
+ }
-creds = None
+ spreadsheet = turb.files.create(body=body, fields='id').execute()
+ id = spreadsheet['id']
-def sheets_create(name):
- """Create a new sheet with the given name.
+ # The files.create call gives us the ID for our new sheet, but we
+ # need the URL for it as well. So we get that with the sheets API.
+ spreadsheet = turb.sheets.get(spreadsheetId=id,
+ fields='spreadsheetUrl').execute()
+ url = spreadsheet['spreadsheetUrl']
- Returns the URL for the spreadsheet.
+ # Finally, we want to also allow anyone with the link to the sheet
+ # to be able to edit it.
+ turb.permissions.create(fileId=id,
+ body={'type': 'anyone', 'role': 'writer'},
+ fields='').execute()
+
+ return {
+ 'id': id,
+ 'url': url
+ }
+
+def sheets_create_for_puzzle(turb, puzzle, folder_id):
+ """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.
"""
- 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)
-
- # Create a new sheet
- spreadsheet_body = {
- 'properties': {
- 'title': name
+
+ # First create the new spreadsheet
+ spreadsheet = sheets_create(turb, puzzle['name'], folder_id)
+ spreadsheet_id = spreadsheet['id']
+ spreadsheet_url = spreadsheet['url']
+
+ # And fetch the individual "sheets" from the spreadsheet
+ spreadsheet = turb.sheets.get(spreadsheetId=spreadsheet_id,
+ fields='sheets').execute()
+ first_sheet_id = spreadsheet['sheets'][0]['properties']['sheetId']
+
+ # 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 the name from the template
+ sheet_name = sheet["properties"]["title"]
+ # Except for "Text" which we rename to the puzzle name
+ if sheet_name == "Text":
+ sheet_name = puzzle['name']
+ rename_sheet(turb, spreadsheet_id, res['sheetId'], sheet_name)
+
+ # Next, delete the blank sheet made at spreadsheet creation time
+ delete_sheet(turb, spreadsheet_id, first_sheet_id)
+
+ # Insert some useful links into the sheet where expected
+ if 'url' in puzzle:
+ url_link = puzzle['url']
+ url_text = "Original puzzle link"
+ spreadsheet_update_data(
+ turb, spreadsheet_id, "B1:B1",
+ '=HYPERLINK("{}","{}")'.format(url_link, url_text))
+
+ if 'channel_url' in puzzle:
+ url_link = puzzle['channel_url']
+ url_text = "Slack channel link"
+ spreadsheet_update_data(
+ turb, spreadsheet_id, "B2:B2",
+ '=HYPERLINK("{}","{}")'.format(url_link, url_text))
+
+ return {
+ 'id': spreadsheet_id,
+ 'url': spreadsheet_url
+ }
+
+def spreadsheet_update_data(turb, spreadsheet_id, range, text):
+
+ turb.sheets.values().update(
+ spreadsheetId=spreadsheet_id,
+ range=range,
+ valueInputOption='USER_ENTERED',
+ body={
+ 'range': range,
+ 'values': [
+ [text]
+ ]
}
+ ).execute()
+
+def delete_sheet(turb, spreadsheet_id, sheet_id):
+
+ body = {
+ 'requests': [{
+ 'deleteSheet': {
+ 'sheetId': sheet_id,
+ },
+ }]
}
- request = service.spreadsheets().create(body=spreadsheet_body)
- response = request.execute()
+ turb.sheets.batchUpdate(spreadsheetId=spreadsheet_id,
+ body=body
+ ).execute()
+
+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 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': [{
+ 'updateSpreadsheetProperties': {
+ 'properties': {
+ 'title': name
+ },
+ 'fields': 'title'
+ }
+ }]
+ }
- return response["spreadsheetUrl"]
+ turb.sheets.batchUpdate(spreadsheetId=spreadsheet_id,
+ body=body
+ ).execute()
projects / turbot / blobdiff