upload-devices.py

#!/usr/bin/env python3

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

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 = '1CVenJIZyjYV_BMwGju6rR7hCe8TrpavvqrG_HxFK-8s'


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 update_devices(service, sheetname):

    src_tsv = sheetname.lower() #[:4]

    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 sheetname in title2sheetid, sheetname+'\n'+pprint.pformat(title2sheetid)

    values = []
    with open('devices-{}.tsv'.format(src_tsv), newline='') as f:
        c = csv.reader(f, delimiter='\t')
        values.append(next(c))
        for r in c:
            o = []
            for i in r:
                if i == 'r':
                    i = "'r'"
                elif i == 'c':
                    i = "'c'"
                elif i == 'f':
                    i = "'f'"
                elif i == 'all':
                    i = "'all'"
                try:
                    i = eval(i)
                except Exception:
                    pass

                if isinstance(i, list):
                    i = ', '.join(i)
                elif isinstance(i, dict):
                    i = repr(i)
                else:
                    assert isinstance(i, (str, float, int, bool)), (i, r)

                o.append(i)
            values.append(o)

    result = service.spreadsheets().values().clear(
        spreadsheetId=gsheetId,
        range=sheetname,
    ).execute()


    result = service.spreadsheets().values().update(
        spreadsheetId=gsheetId,
        valueInputOption='RAW',
        range=sheetname,
        body=dict(
            majorDimension='ROWS',
            values=values)
    ).execute()

    header = values[0]
    BASE_IDX = header.index('base')
    requests = [{
        "updateCells": {
            "range": {
                "sheetId": title2sheetid[sheetname],
            },
            "fields": "userEnteredFormat",
        }
    }]
    for i, r in enumerate(values):
        if i == 0:
            continue

        if values[i-1][BASE_IDX] == r[BASE_IDX]:
            continue

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

    result = service.spreadsheets().batchUpdate(
        spreadsheetId=gsheetId,
        body={'requests':requests},
    ).execute()

    print("Uploaded", src_tsv, "(%d rows)" % len(values))


def main(argv):

    service = get_service()

    update_devices(service, "Capacitors")
    update_devices(service, "Diodes")
    update_devices(service, "FETs")
    update_devices(service, "BJTs")
    update_devices(service, "Resistors")
    update_devices(service, "Vias")
    return

    update_devices(service, "flashs", "")
    update_devices(service, "inductors", "")
    update_devices(service, "srams", "")



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