download-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)


RE_LAYERS_SPLIT = re.compile('[^a-z0-9:]+', flags=re.I)


def idx2col(i):
    assert i < 25, "FIXME: Can't handle multi-letter columns yet"
    return chr(ord('A')+i)


def cap_conv(header, r):
    METAL = header.index('metal')
    r[METAL] = [x for x in RE_LAYERS_SPLIT.split(r[METAL].lower()) if x]

    SHIELD = header.index('shield')
    if r[SHIELD] == 'None':
        r[SHIELD] = []
    else:
        r[SHIELD] = [x for x in RE_LAYERS_SPLIT.split(r[SHIELD].lower()) if x]

    FLOAT = header.index('float')
    r[FLOAT] = [x for x in RE_LAYERS_SPLIT.split(r[FLOAT].lower()) if x]
    return r


def res_conv(header, r):
    LAYERS = header.index('layers')
    r[LAYERS] = [x for x in RE_LAYERS_SPLIT.split(r[LAYERS].lower()) if x]
    return r


def download_custom(service, ctype, conversion_func):

    sheetname = 'Export to Audit - ' + ctype
    outfile = ctype.lower()[:3]+'_custom.csv'

    result = service.spreadsheets().values().get(
        spreadsheetId=gsheetId,
        range=sheetname+'!2:2',
    ).execute()
    header = result.get('values', [])[0]
    assert 'props' in header, header
    PROPS_IDX = header.index('props')
    PROPS_COL = idx2col(PROPS_IDX)

    result = service.spreadsheets().values().get(
        spreadsheetId=gsheetId,
        range=sheetname+'!A:'+PROPS_COL,
    ).execute()
    values = result.get('values', [])

    while not values[0]:
        values.pop(0)

    header = values.pop(0)
    assert 'props' == header[-1]
    assert header[0] == 'use', repr(header)
    assert header[1] == 'key', repr(header)
    keys = set()
    with open(outfile, 'w', newline='') as f:
        w = csv.writer(f)
        w.writerow(header[1:])
        for i, r in enumerate(values):
            if not r or not r[0] or r[0].strip() == '0':
                continue

            while len(r) < len(header):
                r.append('')

            assert len(header) == len(r), (
                    repr((len(header), len(r)))+'\n'+
                    repr(header)+'\n'+
                    repr(r))

            r = conversion_func(header, r)

            r.pop(0)

            assert r[0] not in keys, (
                    r[0]+'\n'+
                    pformat(r)+'\n'+
                    pformat(keys))
            keys.add(r[0])

            w.writerow(r)

    with open(outfile) as f:
        print()
        print(outfile, "info")
        print('-'*75)
        print(f.read())
        print('-'*75)




def main(argv):

    service = get_service()

    download_custom(
        service,
        'BJT',
        lambda h, r: r,
    )
    download_custom(
        service,
        'Capacitors',
        cap_conv,
    )
    download_custom(
        service,
        'Diodes',
        lambda h, r: r,
    )
    download_custom(
        service,
        'Resistors',
        res_conv,
    )
    download_custom(
        service,
        'Vias',
        res_conv,
    )
    download_custom(
        service,
        'Special',
        lambda h, r: r,
    )


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