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 original name
  70             rename_sheet(turb, spreadsheet_id, res['sheetId'],
  71                          sheet["properties"]["title"])
  72
  73     # Next, delete the blank sheet that's was created before the template
  74     sheet_id = spreadsheet['sheets'][0]['properties']['sheetId']
  75     delete_sheet(turb, spreadsheet_id, sheet_id)
  76
  77     # Insert some useful links into the sheet where expected
  78     if 'url' in puzzle:
  79         url_link = puzzle['url']
  80         url_text = "Original puzzle link"
  81         spreadsheet_insert_data(
  82             turb, spreadsheet_id, "B1:B1",
  83             '=HYPERLINK("{}","{}")'.format(url_link, url_text))
  84
  85     if 'channel_url' in puzzle:
  86         url_link = puzzle['channel_url']
  87         url_text = "Slack channel link"
  88         spreadsheet_insert_data(
  89             turb, spreadsheet_id, "B2:B2",
  90             '=HYPERLINK("{}","{}")'.format(url_link, url_text))
  91
  92     return {
  93         'id': spreadsheet_id,
  94         'url': spreadsheet['spreadsheetUrl']
  95     }
  96
  97 def spreadsheet_insert_data(turb, spreadsheet_id, range, text):
  98
  99     turb.sheets.values().append(
 100         spreadsheetId=spreadsheet_id,
 101         range=range,
 102         valueInputOption='USER_ENTERED',
 103         insertDataOption='INSERT_ROWS',
 104         body={
 105             'range': range,
 106             'values': [
 107                 [text]
 108             ]
 109         }
 110     ).execute()
 111
 112 def delete_sheet(turb, spreadsheet_id, sheet_id):
 113
 114     body = {
 115         'requests': [{
 116             'deleteSheet': {
 117                 'sheetId': sheet_id,
 118             },
 119         }]
 120     }
 121
 122     turb.sheets.batchUpdate(spreadsheetId=spreadsheet_id,
 123                             body=body
 124                             ).execute()
 125
 126 def rename_sheet(turb, spreadsheet_id, sheet_id, name):
 127
 128     body = {
 129         'requests': [{
 130             'updateSheetProperties': {
 131                 'properties': {
 132                     'sheetId': sheet_id,
 133                     'title': name
 134                 },
 135                 'fields': 'title'
 136             }
 137         }]
 138     }
 139
 140     turb.sheets.batchUpdate(spreadsheetId=spreadsheet_id,
 141                             body=body
 142                             ).execute()
 143
 144 def spreadsheet_id_from_url(url):
 145     # Google sheet ids are between the /d/ and /edit in the url, like
 146     # https://docs.google.com/spreadsheets/d/1dxHBzjen...-LaXeVPrg/edit#gid=0
 147     start = url.find('/d/') + 3
 148     end = url.find('/edit')
 149     return url[start:end]
 150
 151 def rename_spreadsheet(turb, spreadsheet_url, name):
 152
 153     spreadsheet_id = spreadsheet_id_from_url(spreadsheet_url)
 154
 155     body = {
 156         'requests': [{
 157             'updateSpreadsheetProperties': {
 158                 'properties': {
 159                     'title': name
 160                 },
 161                 'fields': 'title'
 162             }
 163         }]
 164     }
 165
 166     turb.sheets.batchUpdate(spreadsheetId=spreadsheet_id,
 167                             body=body
 168                             ).execute()