Add automatic refreshing of the Google Sheets token

[turbot] / turbot / sheets.py

diff --git a/turbot/sheets.py b/turbot/sheets.py
index 50c472271da8c32dc2c48b664c338ec1b4f2b2f6..a578b100267243ff07539afc375637a95e177c98 100644 (file)
--- a/turbot/sheets.py
+++ b/turbot/sheets.py
@@ -3,10 +3,11 @@ import pickle
 import os.path
 import os
 
+from google.auth.transport.requests import Request
 from googleapiclient.discovery import build
 
-# If modifying these scopes, delete the file token.pickle.
-SCOPES = ['https://www.googleapis.com/auth/spreadsheets']
+TEMPLATE_SHEET_ID = "1drSoyrE4gM3JaGweDkOybwXWdKPIDTfUmB1gQCYS3Uw"
+TEMPLATE_SHEET_SHEETS = ["Text", "Grid"]
 
 turbot_deploy_dir = os.environ.get('TURBOT_DEPLOY_DIR', '.')
 TOKEN_FILE = "{}/.gsheets-token.pickle".format(turbot_deploy_dir)
@@ -20,12 +21,16 @@ def sheets_create(name):
     """
     global creds
 
-    # The file token.pickle stores token from last login/refresh
+    # The token file stores token from last login/refresh
     if not creds:
         if os.path.exists(TOKEN_FILE):
             with open(TOKEN_FILE, 'rb') as token:
                 creds = pickle.load(token)
 
+    # Refresh credentials if necessary
+    if creds and not creds.valid:
+        creds.refresh(Request())
+
     # If there are no (valid) credentials available, give up
     if not creds or not creds.valid:
         current_app.logger.error("No token found in {}".format(TOKEN_FILE))
@@ -33,11 +38,30 @@ def sheets_create(name):
         return None
 
     service = build('sheets', 'v4', credentials=creds)
+    sheets = service.spreadsheets()
+
+    # Create a new spreadsheet
+    spreadsheet_body = {
+        'properties': {
+            'title': name
+        }
+    }
+
+    new_sheet = sheets.create(body=spreadsheet_body).execute()
+    spreadsheet_url = new_sheet["spreadsheetUrl"]
+    spreadsheet_id = new_sheet["spreadsheetId"]
+
+    # Copy some sheets from the Template spreadsheet
 
-    # Create a new sheet
-    spreadsheet_body = {}
+    response = sheets.get(spreadsheetId=TEMPLATE_SHEET_ID).execute()
 
-    request = service.spreadsheets().create(body=spreadsheet_body)
-    response = request.execute()
+    for sheet in response["sheets"]:
+        if sheet["properties"]["title"] in TEMPLATE_SHEET_SHEETS:
+            sheets.sheets().copyTo(spreadsheetId=TEMPLATE_SHEET_ID,
+                                   sheetId=sheet["properties"]["sheetId"],
+                                   body={
+                                       "destinationSpreadsheetId":
+                                       spreadsheet_id
+                                   }).execute()
 
-    return response["spreadsheetUrl"]
+    return spreadsheet_url