From e924689416526d215a5f4aac61966992c5eceed8 Mon Sep 17 00:00:00 2001 From: Carl Worth Date: Sat, 9 Jan 2021 07:19:21 -0800 Subject: [PATCH] Rewrite all of sheets.py to look a little more like turbot code The only actual intended behavioral change here is that the puzzle sheet's now nuke the blank first sheet, (and instead have only sheets copied over from the template). --- turbot/sheets.py | 165 +++++++++++++++++++++++++++++------------------ 1 file changed, 101 insertions(+), 64 deletions(-) diff --git a/turbot/sheets.py b/turbot/sheets.py index ff8b142..341a792 100644 --- a/turbot/sheets.py +++ b/turbot/sheets.py @@ -3,9 +3,25 @@ PUZZLE_TEMPLATE_SHEETS = ["Text", "Square grid", "Hex Grid", "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 @@ -15,18 +31,15 @@ def sheets_create(turb, name): } } - 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 @@ -34,56 +47,81 @@ def sheets_create_for_puzzle(turb, puzzle): 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): @@ -103,29 +141,28 @@ 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] -- 2.43.0