X-Git-Url: https://git.cworth.org/git?a=blobdiff_plain;f=turbot%2Fsheets.py;h=76515ca09547c660a94466fd6d223259211b7f44;hb=HEAD;hp=ff8b1423f3385c8bdb700ccbbbb14aa3c7c5d7a5;hpb=ee51d944119832b3272bf0bbd330066d6dd5d658;p=turbot diff --git a/turbot/sheets.py b/turbot/sheets.py index ff8b142..76515ca 100644 --- a/turbot/sheets.py +++ b/turbot/sheets.py @@ -2,74 +2,75 @@ PUZZLE_TEMPLATE_ID = "1drSoyrE4gM3JaGweDkOybwXWdKPIDTfUmB1gQCYS3Uw" PUZZLE_TEMPLATE_SHEETS = ["Text", "Square grid", "Hex Grid", "Formula reference: indexing"] -def sheets_create(turb, name): - """Create a new sheet with the given name. +def sheets_create_folder(turb, folder_name, parents = None): + """Create a new folder within Google Drive - Returns the dict with 'id' and 'url'of the spreadsheet. + Returns the id of the created folder.""" + + 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 """ - # Create a new spreadsheet - spreadsheet_body = { - 'properties': { - 'title': name - } + body = { + "name": name, + "parents": [folder_id], + "mimeType": "application/vnd.google-apps.spreadsheet" } - new_sheet = turb.sheets.create(body=spreadsheet_body).execute() + spreadsheet = turb.files.create(body=body, fields='id').execute() + id = spreadsheet['id'] - # 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"], + # 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'] + + # 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='id').execute() + fields='').execute() return { - 'id': new_sheet["spreadsheetId"], - 'url': new_sheet["spreadsheetUrl"] + 'id': id, + 'url': url } -def sheets_create_for_puzzle(turb, puzzle): +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.""" - - # 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 = '' + have a 'channel_url' or 'url' key. + """ - 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() + # First create the new spreadsheet + spreadsheet = sheets_create(turb, puzzle['name'], folder_id) + spreadsheet_id = spreadsheet['id'] + spreadsheet_url = spreadsheet['url'] - # Copy some sheets from the Template spreadsheet + # 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"]: @@ -78,12 +79,65 @@ def sheets_create_for_puzzle(turb, puzzle): spreadsheetId=PUZZLE_TEMPLATE_ID, sheetId=sheet["properties"]["sheetId"], body={ - "destinationSpreadsheetId": new_sheet['id'] + "destinationSpreadsheetId": spreadsheet_id }).execute() - rename_sheet(turb, new_sheet['id'], res['sheetId'], - sheet["properties"]["title"]) + # 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, + }, + }] + } - return new_sheet + turb.sheets.batchUpdate(spreadsheetId=spreadsheet_id, + body=body + ).execute() def rename_sheet(turb, spreadsheet_id, sheet_id, name): @@ -103,29 +157,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]