git.cworth.org Git - turbot/blob - turbot/sheets.py

   1 PUZZLE_TEMPLATE_ID = "1drSoyrE4gM3JaGweDkOybwXWdKPIDTfUmB1gQCYS3Uw"
   2 PUZZLE_TEMPLATE_SHEETS = ["Text", "Square grid", "Hex Grid",
   3                           "Formula reference: indexing"]
   4
   5 def sheets_create(turb, name):
   6     """Create a new spreadsheet with the given name.
   7
   8     Returns a dict with 'id' and 'url' of the spreadsheet
   9     """
  10
  11     spreadsheet = create_spreadsheet(turb, name)
  12
  13     return {
  14         'id': spreadsheet['spreadsheetId'],
  15         'url': spreadsheet['spreadsheetUrl']
  16     }
  17
  18 def create_spreadsheet(turb, name):
  19     """
  20     Returns the request's dict which has at least the following keys:
  21
  22         ['spreadsheetId']: ID for this spreadsheet
  23         ['spreadsheetUrl']: URL of this spreadsheet
  24         ['sheets'][0]['properties']['sheetId']: ID of first sheet inside
  25     """
  26
  27     # Create a new spreadsheet
  28     spreadsheet_body = {
  29         'properties': {
  30             'title': name
  31         }
  32     }
  33
  34     spreadsheet = turb.sheets.create(body=spreadsheet_body).execute()
  35
  36     # Now that we've created a new spreadsheet, we need to also allow
  37     # anyone with the link to the sheet to be able to edit it.
  38     turb.permissions.create(fileId=spreadsheet["spreadsheetId"],
  39                             body={'type': 'anyone', 'role': 'writer'},
  40                             fields='id').execute()
  41
  42     return spreadsheet
  43
  44 def sheets_create_for_puzzle(turb, puzzle):
  45     """Creates a new sheet for a puzzle of the given name
  46
  47     Like sheets_create(), but also copies the puzzle template sheet.
  48
  49     Here, 'puzzle' is a dict that must have a 'name' key and may optionally
  50     have a 'channel_url' or 'url' key.
  51     """
  52
  53     # First create the new spreadsheet
  54     spreadsheet = create_spreadsheet(turb, puzzle['name'])
  55     spreadsheet_id = spreadsheet['spreadsheetId']
  56
  57     # Then, copy some useful sheets over from the Template spreadsheet
  58
  59     response = turb.sheets.get(spreadsheetId=PUZZLE_TEMPLATE_ID).execute()
  60
  61     for sheet in response["sheets"]:
  62         if sheet["properties"]["title"] in PUZZLE_TEMPLATE_SHEETS:
  63             res = turb.sheets.sheets().copyTo(
  64                 spreadsheetId=PUZZLE_TEMPLATE_ID,
  65                 sheetId=sheet["properties"]["sheetId"],
  66                 body={
  67                     "destinationSpreadsheetId": spreadsheet_id
  68                 }).execute()
  69             # Rename each copied sheet to match the name from the template
  70             sheet_name = sheet["properties"]["title"]
  71             # Except for "Text" which we rename to the puzzle name
  72             if sheet_name == "Text":
  73                 sheet_name = puzzle['name']
  74             rename_sheet(turb, spreadsheet_id, res['sheetId'], sheet_name)
  75
  76     # Next, delete the blank sheet that's was created before the template
  77     sheet_id = spreadsheet['sheets'][0]['properties']['sheetId']
  78     delete_sheet(turb, spreadsheet_id, sheet_id)
  79
  80     # Insert some useful links into the sheet where expected
  81     if 'url' in puzzle:
  82         url_link = puzzle['url']
  83         url_text = "Original puzzle link"
  84         spreadsheet_insert_data(
  85             turb, spreadsheet_id, "B1:B1",
  86             '=HYPERLINK("{}","{}")'.format(url_link, url_text))
  87
  88     if 'channel_url' in puzzle:
  89         url_link = puzzle['channel_url']
  90         url_text = "Slack channel link"
  91         spreadsheet_insert_data(
  92             turb, spreadsheet_id, "B2:B2",
  93             '=HYPERLINK("{}","{}")'.format(url_link, url_text))
  94
  95     return {
  96         'id': spreadsheet_id,
  97         'url': spreadsheet['spreadsheetUrl']
  98     }
  99
 100 def spreadsheet_insert_data(turb, spreadsheet_id, range, text):
 101
 102     turb.sheets.values().append(
 103         spreadsheetId=spreadsheet_id,
 104         range=range,
 105         valueInputOption='USER_ENTERED',
 106         insertDataOption='INSERT_ROWS',
 107         body={
 108             'range': range,
 109             'values': [
 110                 [text]
 111             ]
 112         }
 113     ).execute()
 114
 115 def delete_sheet(turb, spreadsheet_id, sheet_id):
 116
 117     body = {
 118         'requests': [{
 119             'deleteSheet': {
 120                 'sheetId': sheet_id,
 121             },
 122         }]
 123     }
 124
 125     turb.sheets.batchUpdate(spreadsheetId=spreadsheet_id,
 126                             body=body
 127                             ).execute()
 128
 129 def rename_sheet(turb, spreadsheet_id, sheet_id, name):
 130
 131     body = {
 132         'requests': [{
 133             'updateSheetProperties': {
 134                 'properties': {
 135                     'sheetId': sheet_id,
 136                     'title': name
 137                 },
 138                 'fields': 'title'
 139             }
 140         }]
 141     }
 142
 143     turb.sheets.batchUpdate(spreadsheetId=spreadsheet_id,
 144                             body=body
 145                             ).execute()
 146
 147 def spreadsheet_id_from_url(url):
 148     # Google sheet ids are between the /d/ and /edit in the url, like
 149     # https://docs.google.com/spreadsheets/d/1dxHBzjen...-LaXeVPrg/edit#gid=0
 150     start = url.find('/d/') + 3
 151     end = url.find('/edit')
 152     return url[start:end]
 153
 154 def rename_spreadsheet(turb, spreadsheet_url, name):
 155
 156     spreadsheet_id = spreadsheet_id_from_url(spreadsheet_url)
 157
 158     body = {
 159         'requests': [{
 160             'updateSpreadsheetProperties': {
 161                 'properties': {
 162                     'title': name
 163                 },
 164                 'fields': 'title'
 165             }
 166         }]
 167     }
 168
 169     turb.sheets.batchUpdate(spreadsheetId=spreadsheet_id,
 170                             body=body
 171                             ).execute()