X-Git-Url: https://git.cworth.org/git?a=blobdiff_plain;f=turbot%2Fsheets.py;h=37bd363ac8a85270d90df6d16b4415b47dd72bf2;hb=6fe04c2dc64b09bb0b8bbe7885781358c28b0810;hp=a578b100267243ff07539afc375637a95e177c98;hpb=f8da7d3db7b4a0e12efd47a1784ed970e8ec6aed;p=turbot diff --git a/turbot/sheets.py b/turbot/sheets.py index a578b10..37bd363 100644 --- a/turbot/sheets.py +++ b/turbot/sheets.py @@ -1,44 +1,28 @@ -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 google.auth.transport.requests import Request -from googleapiclient.discovery import build +def sheets_create(turb, name): + """Create a new spreadsheet with the given name. -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): - """Create a new sheet with the given name. - - Returns the URL for the spreadsheet. + Returns a dict with 'id' and 'url' of the spreadsheet """ - global creds - # The token file 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) + spreadsheet = create_spreadsheet(turb, name) - # Refresh credentials if necessary - if creds and not creds.valid: - creds.refresh(Request()) + return { + 'id': spreadsheet['spreadsheetId'], + 'url': spreadsheet['spreadsheetUrl'] + } - # 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 +def create_spreadsheet(turb, name): + """ + Returns the request's dict which has at least the following keys: - service = build('sheets', 'v4', credentials=creds) - sheets = service.spreadsheets() + ['spreadsheetId']: ID for this spreadsheet + ['spreadsheetUrl']: URL of this spreadsheet + ['sheets'][0]['properties']['sheetId']: ID of first sheet inside + """ # Create a new spreadsheet spreadsheet_body = { @@ -47,21 +31,138 @@ def sheets_create(name): } } - new_sheet = sheets.create(body=spreadsheet_body).execute() - spreadsheet_url = new_sheet["spreadsheetUrl"] - spreadsheet_id = new_sheet["spreadsheetId"] + spreadsheet = turb.sheets.create(body=spreadsheet_body).execute() + + # 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 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. + """ - # Copy some sheets from the Template spreadsheet + # First create the new spreadsheet + spreadsheet = create_spreadsheet(turb, puzzle['name']) + spreadsheet_id = spreadsheet['spreadsheetId'] - response = sheets.get(spreadsheetId=TEMPLATE_SHEET_ID).execute() + # 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 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": spreadsheet_id + }).execute() + # Rename each copied sheet to match original name + rename_sheet(turb, spreadsheet_id, res['sheetId'], + sheet["properties"]["title"]) + + # 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 where expected + if 'url' in puzzle: + url_link = puzzle['url'] + url_text = "Original puzzle link" + spreadsheet_insert_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_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=spreadsheet_id, + range=range, + valueInputOption='USER_ENTERED', + insertDataOption='INSERT_ROWS', + body={ + 'range': range, + 'values': [ + [text] + ] + } + ).execute() + +def delete_sheet(turb, spreadsheet_id, sheet_id): + + body = { + 'requests': [{ + 'deleteSheet': { + 'sheetId': sheet_id, + }, + }] + } + + 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' + } + }] + } + + turb.sheets.batchUpdate(spreadsheetId=spreadsheet_id, + body=body + ).execute()