Merge ~dannf/canonical-server-hwe-utils:dgx2-performance-regression-googlesheet into canonical-server-hwe-utils:master

[dann frazier]

[Andrew Cloke (andrew-cloke)]

I'll be honest that I've not used any python libraries to automate gdoc uploads, but this small utility programme all seems clear and logical.

review: Approve

diff --git a/lp-scripts/dgx2-performance-regression-googlesheet.py b/lp-scripts/dgx2-performance-regression-googlesheet.py
new file mode 100755
index 0000000..e08edf4
--- /dev/null
+++ b/lp-scripts/dgx2-performance-regression-googlesheet.py
@@ -0,0 +1,90 @@
+#!/usr/bin/env python3
+#
+# Updates an existing google sheet that tracks performance regression bugs.
+# To run, you'll need google sheet API credentials, and a number of pip
+# libraries installed (a virtualenv is recommended). See
+#  https://developers.google.com/sheets/api/quickstart/python
+# And, of course, you'll need write-access to the spreadsheet.
+#
+import pickle
+import os.path
+from googleapiclient.discovery import build
+from google_auth_oauthlib.flow import InstalledAppFlow
+from google.auth.transport.requests import Request
+from launchpadlib.launchpad import Launchpad
+
+FIELDNAMES = ["Link", "Title", "Importance", "Status"]
+GOOGLE_SPREADSHEET_ID = "1CkjLn_yWxR_LN2nhOHCkyiJq-UvuI2XTGjHIiUAW7mU"
+GOOGLE_RANGE_NAME = "Sheet1!A2"
+
+
+def update_google_sheet(data):
+    # If modifying these scopes, delete the file token.pickle.
+    scopes = ["https://www.googleapis.com/auth/spreadsheets"]
+
+    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.pickle"):
+        with open("token.pickle", "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.pickle", "wb") as token:
+            pickle.dump(creds, token)
+
+    service = build("sheets", "v4", credentials=creds)
+
+    result = (
+        service.spreadsheets()
+        .values()
+        .clear(spreadsheetId=GOOGLE_SPREADSHEET_ID, range=GOOGLE_RANGE_NAME,)
+        .execute()
+    )
+
+    body = {"values": data}
+    result = (
+        service.spreadsheets()
+        .values()
+        .update(
+            spreadsheetId=GOOGLE_SPREADSHEET_ID,
+            range=GOOGLE_RANGE_NAME,
+            valueInputOption="USER_ENTERED",
+            body=body,
+        )
+        .execute()
+    )
+    print("{0} cells updated.".format(result.get("updatedCells")))
+
+
+if __name__ == "__main__":
+    lp = Launchpad.login_with("lpbugs", "production", version="devel")
+
+    p = lp.projects["ubuntu"]
+
+    data = [FIELDNAMES]
+
+    for task in p.searchTasks(tags=["dgx2-performance-regression"], status=[]):
+        row = []
+        bug = int(task.web_link.split("/")[-1])
+        for field in FIELDNAMES:
+            if field == "Link":
+                row.append('=HYPERLINK("{}", "LP: #{}")'.format(task.web_link, bug,))
+            if field == "Title":
+                row.append(lp.bugs[bug].title)
+            if field == "Importance":
+                row.append(task.importance)
+            if field == "Status":
+                row.append(task.status)
+        data.append(row)
+
+    update_google_sheet(data)