Raspberry Pi Data to Google Sheets

A Raspberry Pi 3B is configured to bring in sensor data through a ADC (analog to digital converter) connected to the pi’s I2C (Inter-Integrated Circuit) serial interface. (I’m using the ADS7830 module that came with a kit that contained all kinds of fun project goodies). I have 2 potentiometers wired to the ADC that I can turn by hand to give me 2 real voltage values. The pi has internet access through wifi.

ADC to Pi

I followed the kit‘s instructions for the python language to

• enable the I2C interface in the Pi’s configuration
• install the needed os packages
• import the needed python modules
• wire the ADC module to the Pi
• wire the pots to the ADC

I wrote a function so I can get the ADC values from other programs:

#!/usr/bin/env python3
########################################################################
# Filename    : myADC.py
# Description : Use ADC module to read the voltage values
# Author      : sparkygeek.com
# modification: 2020/04/26
########################################################################
from ADCDevice import *
adc = ADS7830()
    
def getValue(chan = 0):
    value = adc.analogRead(chan)    # read the ADC value of channel 0
    voltage = value / 255.0 * 3.3  # calculate the voltage value
    print ('Channel %d ADC Value : %d, Voltage : %.2f'%(chan,value,voltage))
    return voltage

Pi to Google

Follow the directions for getting the Google Python Quickstart project working. (Enable Google Cloud Platform Project API, Create credentials, download your credential file, and install the needed Google client libraries) The quickstart code uses the spreadsheets.values.get method, but we are going to use the spreadsheets.values.append method to add new values to our spreadsheet. Here is the code I’m using to do this:

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
import myADC
from time import sleep

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

# The ID and range of a sample spreadsheet.
SPREADSHEET_ID = 'xxxxxxxxxxRb6BXNBORw72feWN3iiaz_xxxxxx'
RANGE_NAME = 'Sheet1'

def main():
    """Shows basic usage of the Sheets API.
    Prints values from a sample spreadsheet.
    """
    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('sheets', 'v4', credentials=creds)

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

    newData = [myADC.getValue(0),myADC.getValue(1)]  # get data from ADC module

    body = {
            "values": [
                newData
            ]
           }
    result = sheet.values().append(
           spreadsheetId=SPREADSHEET_ID, range=RANGE_NAME,
           valueInputOption='USER_ENTERED', body=body).execute()
    print('{0} cells appended.'.format(result \
                                          .get('updates') \
                                          .get('updatedCells')))

if __name__ == '__main__':
    while(True):
        main()
        sleep(2.5)

See it in action!