Raspberry Pi Cron Job to Insert Into Google Sheets


Today I had some time to play with my Raspberry Pi. A particularly fun experiment today came from following this Google Sheets – Python API Tutorial. Having used Google Sheets scripting at work, I was excited about the idea of interacting with Sheets using Python on the Pi. The Pi could be used to inject and manipulate data in a Sheet where the Sheet is being used as a database. Then Sheets could run its own scripts for further functionality.

I wrote this code (sheets.py) to connect with a spreadsheet and insert a new row with an incremented id and the current time. In the gsheets directory there was a creds.json file with my Google Authentication information.

import gspread
from oauth2client.service_account import ServiceAccountCredentials
from pprint import pprint
import datetime

scope = ["https://spreadsheets.google.com/feeds",'https://www.googleapis.com/auth/spreadsheets',"https://www.googleapis.com/auth/drive.file","https://www.googleapis.com/auth/drive"]
creds = ServiceAccountCredentials.from_json_keyfile_name("/home/pi/Desktop/gsheets/creds.json", scope)
client = gspread.authorize(creds)
sheet = client.open("Python API").sheet1

# all the records with first row headers as keys
data = sheet.get_all_records()

# next row id number is length of data plus 1
nextId = len(data) + 1

# next row to insert that data is 1 more than the id due to the header
nextInsertRowNum = nextId + 1

# current timestamp
now = str(datetime.datetime.now().time())

# json data prep for insert
insertRowData = [nextId, now]

# insert the json into the nextInsertRowNum
sheet.insert_row(insertRowData, nextInsertRowNum)

It worked. I could send the id and time into the Sheet by executing the file. Success!

I wanted to take it one step further. By setting up a cron job I could insert a new row and time every minute.

* * * * * /usr/bin/python3 /home/pi/Desktop/gsheets/sheets.py

This is where I ran into some issues. The sheet was not getting updated from the cron job. I waited, and nothing happened…

To troubleshoot, I created a Python file to print the current time.

# cronwrite.py

import datetime

now = str(datetime.datetime.now().time())

print("Cron run " + now)

A cron job would run every minute to copy the print statement into a text file to make sure the cron jobs were in fact firing every minute.

* * * * * /usr/bin/python3 /home/pi/Desktop/gsheets/cronwrite.py > /home/pi/Desktop/gsheets/cronread.txt

cronread.txt was working correctly.

Cron run 18:35:02.198169

OK. So the cron job was in fact running every minute, but the script was not inserting data into Google Sheets. I tried running sheets.py from the command line. The code from the cron job worked, but it didn’t work when I added sudo in front. I then sudo pip3 installed the two dependencies so they could be accessed when the cron job ran with sudo pip3 install gspread oauth2client. Then, it started working.

Google Spreadsheet with ids and time
It’s beautiful…

The possibilities from here are endless. You could hook up a sensor device to the Pi and have it send data to a Sheet periodically. For example, a temperature reading could be saved once per hour. Or if a dog barked it could save the time and decibel level of the bark (or just bark back).