X-Git-Url: https://git.cworth.org/git?a=blobdiff_plain;f=turbot%2Fsheets.py;h=df2e4e9b10828068048f752b1b57bf3ed9912c63;hb=4271515e50671c4fdc9b4cc4b437c7c0a4937aa3;hp=8ab302d38579a8f744a475a34a39c769f715813a;hpb=c5b9b6e3ae85b40b79c0650eeee93cf79b1d23b5;p=turbot

diff --git a/turbot/sheets.py b/turbot/sheets.py
index 8ab302d..df2e4e9 100644
--- a/turbot/sheets.py
+++ b/turbot/sheets.py
@@ -1,10 +1,27 @@
 PUZZLE_TEMPLATE_ID = "1drSoyrE4gM3JaGweDkOybwXWdKPIDTfUmB1gQCYS3Uw"
-PUZZLE_TEMPLATE_SHEETS = ["Text", "Grid"]
+PUZZLE_TEMPLATE_SHEETS = ["Text", "Square grid", "Hex Grid",
+                          "Formula reference: indexing"]
 
 def sheets_create(turb, name):
-    """Create a new sheet with the given name.
+    """Create a new spreadsheet with the given name.
 
-    Returns the dict with 'id' and 'url'of the spreadsheet.
+    Returns a dict with 'id' and 'url' of the spreadsheet
+    """
+
+    spreadsheet = create_spreadsheet(turb, name)
+
+    return {
+        'id': spreadsheet['spreadsheetId'],
+        'url': spreadsheet['spreadsheetUrl']
+    }
+
+def create_spreadsheet(turb, name):
+    """
+    Returns the request's dict which has at least the following keys:
+
+        ['spreadsheetId']: ID for this spreadsheet
+        ['spreadsheetUrl']: URL of this spreadsheet
+        ['sheets'][0]['properties']['sheetId']: ID of first sheet inside
     """
 
     # Create a new spreadsheet
@@ -14,32 +31,141 @@ def sheets_create(turb, name):
         }
     }
 
-    new_sheet = turb.sheets.create(body=spreadsheet_body).execute()
+    spreadsheet = turb.sheets.create(body=spreadsheet_body).execute()
 
-    return {
-        'id': new_sheet["spreadsheetId"],
-        'url': new_sheet["spreadsheetUrl"]
-    }
+    # Now that we've created a new spreadsheet, we need to also allow
+    # anyone with the link to the sheet to be able to edit it.
+    turb.permissions.create(fileId=spreadsheet["spreadsheetId"],
+                            body={'type': 'anyone', 'role': 'writer'},
+                            fields='id').execute()
+
+    return spreadsheet
 
-def sheets_create_for_puzzle(turb, name):
+def sheets_create_for_puzzle(turb, puzzle):
     """Creates a new sheet for a puzzle of the given name
 
-    Like sheets_create(), but also copies the puzzle template sheet."""
+    Like sheets_create(), but also copies the puzzle template sheet.
 
-    # First create the new sheet
-    new_sheet = sheets_create(turb, name)
+    Here, 'puzzle' is a dict that must have a 'name' key and may optionally
+    have a 'channel_url' or 'url' key.
+    """
 
-    # Copy some sheets from the Template spreadsheet
+    # First create the new spreadsheet
+    spreadsheet = create_spreadsheet(turb, puzzle['name'])
+    spreadsheet_id = spreadsheet['spreadsheetId']
+
+    # Then, copy some useful sheets over from the Template spreadsheet
 
     response = turb.sheets.get(spreadsheetId=PUZZLE_TEMPLATE_ID).execute()
 
     for sheet in response["sheets"]:
         if sheet["properties"]["title"] in PUZZLE_TEMPLATE_SHEETS:
-            turb.sheets.sheets().copyTo(spreadsheetId=PUZZLE_TEMPLATE_ID,
-                                        sheetId=sheet["properties"]["sheetId"],
-                                        body={
-                                            "destinationSpreadsheetId":
-                                            new_sheet['id']
-                                        }).execute()
-
-    return new_sheet
+            res = turb.sheets.sheets().copyTo(
+                spreadsheetId=PUZZLE_TEMPLATE_ID,
+                sheetId=sheet["properties"]["sheetId"],
+                body={
+                    "destinationSpreadsheetId": spreadsheet_id
+                }).execute()
+            # Rename each copied sheet to match the name from the template
+            sheet_name = sheet["properties"]["title"]
+            # Except for "Text" which we rename to the puzzle name
+            if sheet_name == "Text":
+                sheet_name = puzzle['name']
+            rename_sheet(turb, spreadsheet_id, res['sheetId'], sheet_name)
+
+    # Next, delete the blank sheet that's was created before the template
+    sheet_id = spreadsheet['sheets'][0]['properties']['sheetId']
+    delete_sheet(turb, spreadsheet_id, sheet_id)
+
+    # Insert some useful links into the sheet where expected
+    if 'url' in puzzle:
+        url_link = puzzle['url']
+        url_text = "Original puzzle link"
+        spreadsheet_insert_data(
+            turb, spreadsheet_id, "B1:B1",
+            '=HYPERLINK("{}","{}")'.format(url_link, url_text))
+
+    if 'channel_url' in puzzle:
+        url_link = puzzle['channel_url']
+        url_text = "Slack channel link"
+        spreadsheet_insert_data(
+            turb, spreadsheet_id, "B2:B2",
+            '=HYPERLINK("{}","{}")'.format(url_link, url_text))
+
+    return {
+        'id': spreadsheet_id,
+        'url': spreadsheet['spreadsheetUrl']
+    }
+
+def spreadsheet_insert_data(turb, spreadsheet_id, range, text):
+
+    turb.sheets.values().append(
+        spreadsheetId=spreadsheet_id,
+        range=range,
+        valueInputOption='USER_ENTERED',
+        insertDataOption='INSERT_ROWS',
+        body={
+            'range': range,
+            'values': [
+                [text]
+            ]
+        }
+    ).execute()
+
+def delete_sheet(turb, spreadsheet_id, sheet_id):
+
+    body = {
+        'requests': [{
+            'deleteSheet': {
+                'sheetId': sheet_id,
+            },
+        }]
+    }
+
+    turb.sheets.batchUpdate(spreadsheetId=spreadsheet_id,
+                            body=body
+                            ).execute()
+
+def rename_sheet(turb, spreadsheet_id, sheet_id, name):
+
+    body = {
+        'requests': [{
+            'updateSheetProperties': {
+                'properties': {
+                    'sheetId': sheet_id,
+                    'title': name
+                },
+                'fields': 'title'
+            }
+        }]
+    }
+
+    turb.sheets.batchUpdate(spreadsheetId=spreadsheet_id,
+                            body=body
+                            ).execute()
+
+def spreadsheet_id_from_url(url):
+    # Google sheet ids are between the /d/ and /edit in the url, like
+    # https://docs.google.com/spreadsheets/d/1dxHBzjen...-LaXeVPrg/edit#gid=0
+    start = url.find('/d/') + 3
+    end = url.find('/edit')
+    return url[start:end]
+
+def rename_spreadsheet(turb, spreadsheet_url, name):
+
+    spreadsheet_id = spreadsheet_id_from_url(spreadsheet_url)
+
+    body = {
+        'requests': [{
+            'updateSpreadsheetProperties': {
+                'properties': {
+                    'title': name
+                },
+                'fields': 'title'
+            }
+        }]
+    }
+
+    turb.sheets.batchUpdate(spreadsheetId=spreadsheet_id,
+                            body=body
+                            ).execute()