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