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