Raspberry Pi Cron Job to Insert Into Google Sheets
October 5, 2019
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
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.
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).