Google Sheet Read & Write for Maya

gistfile1.txt

#NOTE- I had to disable the MASH plugin to get this to work in Maya. MASH uses a different version of the Google Client API that causes this to not work.

# Useful links:
# https://developers.google.com/sheets/api/quickstart/python
# https://medium.com/analytics-vidhya/how-to-read-and-write-data-to-google-spreadsheet-using-python-ebf54d51a72c
# https://www.twilio.com/blog/2017/02/an-easy-way-to-read-and-write-to-a-google-spreadsheet-in-python.html

def getCredsGoogle():
    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(CREDS_PATH + r'\token.pickle'):
        with open(CREDS_PATH + r'\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(
                credsFile, SCOPES)
            creds = flow.run_local_server(port=0)
        # Save the credentials for the next run

        with open(CREDS_PATH + r'\token.pickle', 'wb') as token:
            pickle.dump(creds, token)

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

    return service

def readGoogleSheet(curSheet, service, range, charName):

    # Call the Sheets API
    sheet = service.spreadsheets()

    # make sure the sheet being looked for exists. We'll make it later if we need to, but not here.
    sh = sheet.get(spreadsheetId=curSheet).execute()

    sheetExists = False
    shData = sh.get('sheets', '')
    for data in shData:
        title = data.get("properties", {}).get("title", "Sheet1")
        if title == charName:
            sheetExists = True

    if sheetExists == True:
        # Use FORMULA as valueRenderOption (this is NOT the default, but you need to use it here if you have formulas or conditional formatting)
        result = sheet.values().get(spreadsheetId=curSheet, range=range, valueRenderOption='FORMULA').execute()
        values = result.get('values', [])
        
        #this is returned as animationSheetData
        return values

def writeGoogleSheet(curSheet, service, sheetName, renderList, animationSheetData, updateRevisionOnly = False):
    print 'writing out data to google sheet'
    # data should have the name of the animation, the data (just use the date that the render was made), the version. It should not replace any data in the Audio Update, Note, or Audio Status columns
    # renderList is a dict- {'path' : , 'charname': , 'filename': , 'revision': , 'version': , 'modified' : , 'audio' : , 'note' : , 'audioStatus' : , 'new_entry' :}

    # use animationSheetData to find/replace an existing entry, and new ones to the end. Use animationSheetData to write to google sheet
    for entry in renderList:
        if entry['new_entry'] == True:
            print 'add to sheet'
            newData = [entry['filename'], entry['version'], entry['modified'], entry['audio'], entry['note'], entry['audioStatus']]
            animationSheetData.append(newData)
        else:
            print 'update sheet entry'
            for existing in animationSheetData:
                if existing[0].lower() == entry['filename'].lower():
                    if updateRevisionOnly == False:
                        existing[2] = entry['modified']
                    existing[1] = entry['version']
                    break


    # Call the Sheets API
    sheet = service.spreadsheets()
    sh = sheet.get(spreadsheetId=curSheet).execute()
    sheetId = 0

    # clear the sheet first
    rangeAll = '{0}!A1:Z'.format(sheetName)
    body = {}
    resultClear = service.spreadsheets().values().clear(spreadsheetId=curSheet, range=rangeAll,
                                                        body=body).execute()

    updateSheets = sh.get('sheets', '')
    for i in updateSheets:

        title = i.get("properties", {}).get("title", "Sheet1")
        if title == sheetName:
            sheetId = i.get("properties", {}).get("sheetId", "Sheet1")


    body = {
        'values': animationSheetData
    }
    range = (sheetName + '!A1:L5000')

    #USER_ENTERED is important here- with RAW I would get a random quotation mark at the start of date and formula strings.
    result = service.spreadsheets().values().update(
        spreadsheetId=curSheet, range=range,
        valueInputOption='USER_ENTERED', body=body).execute()
    print('{0} cells updated.'.format(result.get('updatedCells')))