upload-sheet-audit-data.py

#!/usr/bin/env python3

import csv
import os
import os.path
import pickle
import pprint
import re
import sys

from IPython import embed

from googleapiclient.discovery import build
from google_auth_oauthlib.flow import InstalledAppFlow,Flow
from google.auth.transport.requests import Request


SCOPES = ['https://www.googleapis.com/auth/spreadsheets']

gsheetId = '1oXS-a_XD7AzWOEnbJB9VvEISLW_5DydZQeVtdOuHFVE'


def pformat(*args, **kw):
    return pprint.pformat(*args, width=200, **kw)


def get_service():
    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(
                'client_secrets.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)

    return build('sheets', 'v4', credentials=creds)



def main(argv):

    service = get_service()

    # Upload the names2files-b.csv to the sheet
    SHEET_NAME = "Imported from Audit - New names"

    sheet_metadata = service.spreadsheets().get(spreadsheetId=gsheetId).execute()
    sheets = sheet_metadata.get('sheets', '')
    title2sheetid = {}
    for s in sheets:
        title = s.get("properties", {}).get("title", "Sheet1")
        sheet_id = s.get("properties", {}).get("sheetId", 0)
        title2sheetid[title] = sheet_id

    assert SHEET_NAME in title2sheetid, SHEET_NAME+'\n'+pformat(title2sheetid)
    SHEET_ID = title2sheetid[SHEET_NAME]

    rows = []
    requests = [{
        "updateCells": {
            "range": {
                "sheetId": SHEET_ID,
            },
            "fields": "userEnteredFormat",
        }
    }]

    DIR_NAME_IDX = 3
    with open('names2files-b.csv', newline='') as f:
        for i, r in enumerate(csv.reader(f)):
            if i > 0:
                r[2] = int(r[2])

            rows.append(r)

            if i == 0:
                continue
            if r[DIR_NAME_IDX] == rows[-2][DIR_NAME_IDX]:
                continue

            requests.append({
              "updateBorders": {
                "range": {
                  "sheetId": SHEET_ID,
                  "startRowIndex": i-1,
                  "endRowIndex": i,
                  "startColumnIndex": 0,
                },
                "bottom": {
                  "style": "DASHED",
                  "width": 1,
                  "color": {
                    "blue": 1.0
                  },
                },
              }
            })

    result = service.spreadsheets().values().clear(
        spreadsheetId=gsheetId,
        range="Imported from Audit - New names",
    ).execute()

    result = service.spreadsheets().values().update(
        spreadsheetId=gsheetId,
        valueInputOption='RAW',
        range="Imported from Audit - New names",
        body=dict(
            majorDimension='ROWS',
            values=rows)
    ).execute()
    print("Imported values for 'New names'")

    result = service.spreadsheets().batchUpdate(
        spreadsheetId=gsheetId,
        body={'requests':requests},
    ).execute()
    print("Updating formatting for 'New names'")


if __name__ == "__main__":
    sys.exit(main(sys.argv))