From: Carl Worth <cworth@cworth.org>
Date: Tue, 29 Sep 2020 22:08:44 +0000 (-0700)
Subject: Initial implement of auto-creation of Google sheets
X-Git-Url: https://git.cworth.org/git?p=turbot;a=commitdiff_plain;h=d420bedebbb6b9c045cbf1ebbd9e65ad6830e2a3

Initial implement of auto-creation of Google sheets

This is implemented via a Slack event listener that's listening for
the "channel_created" event. So, whenever a new channel is created,
this code will create a new Google sheet and inject a message into
the new channel with the URL of the sheet.
---

diff --git a/.gitignore b/.gitignore
index 88a4036..4bbd199 100644
--- a/.gitignore
+++ b/.gitignore
@@ -1,3 +1,5 @@
 turbot.wsgi
 .slack-creds.env
+.gsheets-creds.json
+.gsheets-token.pickle
 __pycache__
diff --git a/Makefile b/Makefile
index defacbe..b71e017 100644
--- a/Makefile
+++ b/Makefile
@@ -2,7 +2,7 @@ DEPLOY_HOST=halibut.cworth.org
 DEPLOY_DIR=/srv/halibut.cworth.org/turbot
 export DEPLOY_DIR
 DO_NOT_DEPLOY=env .gitignore
-DO_NOT_DELETE=.slack-creds.env
+DO_NOT_DELETE=.slack-creds.env .gsheets-creds.json .gsheets-token.pickle
 
 help:
 	@echo "Available targets (in rough order of expected use):"
diff --git a/gsheets-authenticate.py b/gsheets-authenticate.py
new file mode 100755
index 0000000..7140b8e
--- /dev/null
+++ b/gsheets-authenticate.py
@@ -0,0 +1,41 @@
+#!/usr/bin/env python3
+
+import pickle
+import os.path
+from google_auth_oauthlib.flow import InstalledAppFlow
+from google.auth.transport.requests import Request
+
+# If modifying these scopes, delete the file token.pickle.
+SCOPES = ['https://www.googleapis.com/auth/spreadsheets']
+
+TOKEN_FILE = ".gsheets-token.pickle"
+
+def main():
+    """Allows user to authenticate for the Sheets API.
+
+    Resulting authorization token is stored in token.pickle.
+    """
+    creds = None
+    # The file token.pickle stores the user's access and refresh tokens, and is
+    # created automatically when the authorization flow completes for the first
+    # time.
+    if os.path.exists(TOKEN_FILE):
+        with open(TOKEN_FILE, 'rb') as token:
+            creds = pickle.load(token)
+
+    # If there are no (valid) credentials available, let the user log in.
+    if not creds or not creds.valid:
+        if creds and creds.expired and creds.refresh_token:
+            creds.refresh(Request())
+        else:
+            flow = InstalledAppFlow.from_client_secrets_file(
+                'credentials.json', SCOPES)
+            creds = flow.run_local_server(port=0)
+        # Save the credentials for the next run
+        with open(TOKEN_FILE, 'wb') as token:
+            pickle.dump(creds, token)
+
+    print("Token now saved in {}".format(TOKEN_FILE))
+
+if __name__ == '__main__':
+    main()
diff --git a/turbot.wsgi.in b/turbot.wsgi.in
index e594279..5a7c37b 100644
--- a/turbot.wsgi.in
+++ b/turbot.wsgi.in
@@ -1,7 +1,10 @@
 import sys
+import os
 sys.path.insert(0, '${DEPLOY_DIR}')
 
 from dotenv import load_dotenv
 load_dotenv('${DEPLOY_DIR}/.slack-creds.env')
 
+os.environ['TURBOT_DEPLOY_DIR']='${DEPLOY_DIR}'
+
 from turbot.turbot import app as application
diff --git a/turbot/sheets.py b/turbot/sheets.py
new file mode 100644
index 0000000..50c4722
--- /dev/null
+++ b/turbot/sheets.py
@@ -0,0 +1,43 @@
+from flask import current_app
+import pickle
+import os.path
+import os
+
+from googleapiclient.discovery import build
+
+# If modifying these scopes, delete the file token.pickle.
+SCOPES = ['https://www.googleapis.com/auth/spreadsheets']
+
+turbot_deploy_dir = os.environ.get('TURBOT_DEPLOY_DIR', '.')
+TOKEN_FILE = "{}/.gsheets-token.pickle".format(turbot_deploy_dir)
+
+creds = None
+
+def sheets_create(name):
+    """Create a new sheet with the given name.
+
+    Returns the URL for the spreadsheet.
+    """
+    global creds
+
+    # The file token.pickle 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)
+
+    # 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))
+        current_app.logger.error("Try running ./gsheets-authenticate.py")
+        return None
+
+    service = build('sheets', 'v4', credentials=creds)
+
+    # Create a new sheet
+    spreadsheet_body = {}
+
+    request = service.spreadsheets().create(body=spreadsheet_body)
+    response = request.execute()
+
+    return response["spreadsheetUrl"]
diff --git a/turbot/turbot.py b/turbot/turbot.py
index e7fc87e..a558d07 100755
--- a/turbot/turbot.py
+++ b/turbot/turbot.py
@@ -5,6 +5,7 @@ from slackeventsapi import SlackEventAdapter
 import os
 from turbot.rot import rot_route
 from turbot.slack import slack_send_message
+from turbot.sheets import sheets_create
 
 app = Flask(__name__)
 app.register_blueprint(rot_route)
@@ -16,9 +17,10 @@ slack_events = SlackEventAdapter(slack_signing_secret, "/slack/events", app)
 def handle_channel_created(event_data):
     event = event_data["event"]
     channel = event["channel"]
+    sheet_url = sheets_create(channel["name"])
     slack_send_message(channel["id"],
-                       "Cool. You made a channel named {}"
-                       .format(channel["name"]))
+                       "Auto-created a sheet for this channel: {}"
+                       .format(sheet_url))
 
 @slack_events.on("error")
 def handle_error(error):