scratchpad for sheets

quickstart.py

from __future__ import print_function
import os.path
from googleapiclient.discovery import build
from google_auth_oauthlib.flow import InstalledAppFlow
from google.auth.transport.requests import Request
from google.oauth2.credentials import Credentials
from datetime import datetime
from random import randint
from time import sleep
from sds011 import SDS011
import gspread

num_per_upload = 1
wake_time = 15
interval_time = 20*60-wake_time #in minutes


sensor1 = SDS011("/dev/tty.wchusbserial1410") #Left USB Port
sensor2 = SDS011("/dev/tty.wchusbserial1420") #Right USB Port

# If modifying these scopes, delete the file token.json.
SCOPES = ['https://www.googleapis.com/auth/spreadsheets']


def read_sensor():
    #turn on sensors
    sensor1.sleep(sleep=False)
    sensor2.sleep(sleep=False)
    #15 seconds for sensor to get ready
    sleep(wake_time)

    d1 = sensor1.query()
    d2 = sensor2.query()
    sensor1.sleep();
    sensor2.sleep();
    return d1 + d2


def main():
    """Shows basic usage of the Drive v3 API.
    Prints the names and ids of the first 10 files the user has access to.
    """
    creds = None
    # The file token.json 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.json'):
        creds = Credentials.from_authorized_user_file('token.json', SCOPES)
    # 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.json', 'w') as token:
            token.write(creds.to_json())

#    service = build('drive', 'v3', credentials=creds)
#
#    # Call the Drive v3 API
#    results = service.files().list(
#        pageSize=2, fields="nextPageToken, files(id, name)").execute()
#    items = results.get('files', [])
#    if not items:
#        print('No files found.')
#    else:
#        print('Files:')
#        for item in items:
#            print(u'{0} ({1})'.format(item['name'], item['id']))



    service = build('sheets', 'v4', credentials=creds)
    sheet = service.spreadsheets()
    #1O3RSnI1VI3oWsBHE7IFx5JsdpIiDpnSijUiuQjSszg0
    
    result = sheet.values().get(spreadsheetId='1O3RSnI1VI3oWsBHE7IFx5JsdpIiDpnSijUiuQjSszg0',
                                range='Sheet1!A1:E').execute()
    values = result.get('values', [])
                                
    if not values:
        print('No data found.')
    else:
        print('Name, Major:')
        for row in values:
            # Print columns A and E, which correspond to indices 0 and 4.
            #print('%s, %s' % (row[0], row[0]))
            print(row)
    
    while True:
        values = []
        for x in range(num_per_upload):
            print('new bulk loop started')
            print('sample {0} out of {1}'.format(x+1, num_per_upload))
            data = read_sensor()
            now = datetime.now()
            print('S1: PM2.5 :' + str(data[0]) + ' PM10 : ' +str(data[1]))
            print('S2: PM2.5 :' + str(data[2]) + ' PM10 : ' +str(data[3]))
            current_time = now.strftime("%H:%M:%S")
            current_date = now.strftime("%y/%m/%d")
            entry = [current_date, current_time ] + list(data)
            values.append(entry)
            
            
            
        body = {
            'values': values
        }
        result = service.spreadsheets().values().append(spreadsheetId='1O3RSnI1VI3oWsBHE7IFx5JsdpIiDpnSijUiuQjSszg0',
                                                range='Sheet1!A:A',
                                                valueInputOption="USER_ENTERED", body=body).execute()
        
        print('{0} cells uploaded.'.format(result \
                                       .get('updates') \
                                       .get('updatedCells')))
        sleep(interval_time) #45 + 15 startup = 60 second poll interval

if __name__ == '__main__':
    main()