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

   1 PUZZLE_TEMPLATE_ID = "1drSoyrE4gM3JaGweDkOybwXWdKPIDTfUmB1gQCYS3Uw"
   2 PUZZLE_TEMPLATE_SHEETS = ["Text", "Grid"]
   3
   4 def sheets_create(turb, name):
   5     """Create a new sheet with the given name.
   6
   7     Returns the dict with 'id' and 'url'of the spreadsheet.
   8     """
   9
  10     # Create a new spreadsheet
  11     spreadsheet_body = {
  12         'properties': {
  13             'title': name
  14         }
  15     }
  16
  17     new_sheet = turb.sheets.create(body=spreadsheet_body).execute()
  18
  19     # Now that we've created a new sheet, we need to also allow anyone
  20     # with the link to the sheet to be able to edit it.
  21     turb.permissions.create(fileId=new_sheet["spreadsheetId"],
  22                             body={'type': 'anyone', 'role': 'writer'},
  23                             fields='id').execute()
  24
  25     return {
  26         'id': new_sheet["spreadsheetId"],
  27         'url': new_sheet["spreadsheetUrl"]
  28     }
  29
  30 def sheets_create_for_puzzle(turb, puzzle):
  31     """Creates a new sheet for a puzzle of the given name
  32
  33     Like sheets_create(), but also copies the puzzle template sheet.
  34
  35     Here, 'puzzle' is a dict that must have a 'name' key and may optionally
  36     have a 'channel_url' or 'url' key."""
  37
  38     # First create the new sheet
  39     new_sheet = sheets_create(turb, puzzle['name'])
  40
  41     # Insert some useful links into the sheet
  42     url_link=''
  43     if 'url' in puzzle:
  44         url_text = "Original puzzle is at: {}".format(puzzle['url'])
  45         url_link = puzzle['url']
  46     else:
  47         url_text = ''
  48
  49     channel_url_link = ''
  50     if 'channel_url' in puzzle:
  51         channel_url_text = "Discussion for this puzzle is at: {}".format(
  52             puzzle['channel_url'])
  53         channel_url_link = puzzle['channel_url']
  54     else:
  55         channel_url_text = ''
  56
  57     turb.sheets.values().append(
  58         spreadsheetId=new_sheet['id'],
  59         range='A1:A2',
  60         valueInputOption='USER_ENTERED',
  61         insertDataOption='INSERT_ROWS',
  62         body={
  63             'range': 'A1:A2',
  64             'values': [
  65                 ['=HYPERLINK("'+url_link+'","'+url_text+'")'],
  66                 ['=HYPERLINK("'+channel_url_link+'","'+channel_url_text+'")']
  67             ]
  68         }).execute()
  69
  70     # Copy some sheets from the Template spreadsheet
  71
  72     response = turb.sheets.get(spreadsheetId=PUZZLE_TEMPLATE_ID).execute()
  73
  74     for sheet in response["sheets"]:
  75         if sheet["properties"]["title"] in PUZZLE_TEMPLATE_SHEETS:
  76             turb.sheets.sheets().copyTo(spreadsheetId=PUZZLE_TEMPLATE_ID,
  77                                         sheetId=sheet["properties"]["sheetId"],
  78                                         body={
  79                                             "destinationSpreadsheetId":
  80                                             new_sheet['id']
  81                                         }).execute()
  82
  83     return new_sheet
  84
  85 def renameSheet(turb, url, newName):
  86     id = extractIdFromSheetUrl(url)
  87     requests = []
  88     requests.append({
  89         'updateSpreadsheetProperties': {
  90             'properties': {
  91                 'title': newName
  92             },
  93             'fields': 'title'
  94         }
  95     })
  96
  97     body = {
  98         'requests': requests
  99     }
 100
 101     turb.sheets.batchUpdate(spreadsheetId = id,
 102                             body=body
 103                             ).execute()
 104
 105 def extractIdFromSheetUrl(url):
 106     # Google sheet ids are between the /d/ and /edit in the url, like
 107     # https://docs.google.com/spreadsheets/d/1dxHBzjen...-LaXeVPrg/edit#gid=0
 108     startIndex = url.find('/d/') + 3
 109     endIndex = url.find('/edit')
 110     return url[startIndex : endIndex]