1.6: Writing to Google Sheets
- Page ID
- 211799
hypothes.is tag:
Download Assignment:
Learning Objectives
Students will be able to:
Content:
- Use Google Sheets to store data from remote source
- Set up Raspberry Pi to send data to Google Sheet
Process:
- Set up Google Sheet to receive external data
- Set up Raspberry Pi to send data to Google Sheet
Prior Knowledge
- Python and IoT concepts from previous Activities
Further Reading
Task 1: Setting up a Google Sheet to accept raspberry pi data
Suppose you want your RasPi to save collected from a program/sensor to a Google Sheet that you can publish on the web or share the link. Google has set up an API (Application Programming Interface) to allow you to do that.
To set this up:
1. Go to sheets.google.com and log into the google account you set up in Activity 3a for gmail. Click on start a new blank spreadsheet.
When the sheet is created, you should have an “Untitled Spreadsheet”
In the section where it says “Untitled spreadsheet” give it a name. For this activity, let’s call it Raspi_data
In the first row create a column headers of Datetime and data
2. Open a new tab and go to the Google api console http://console.developers.google.com
You will be creating a new project.
In the New project window, you will see a place to type project name. Give it the name mydata
Notice that you have a project ID that automatically is generated. Write this name and number down. You don’t need to change the location.
Click create. You will see something indicating your project is being created:
Click the reload icon on the google browser
Next to the Google APIs header, select mydata from the dropdown menu to select this project.
The myproject project should be in the header for Google APIs:
Click on Enable APIS and SERVICES:
In the search window, type sheets, and you should see the following:
Click on the Google Sheets API that comes up as the result. Then click the ENABLE button.
It will indicate that it is enabling the API and the following scree will come up. At this point, notice that you have mydata as the active project (1), and then you will click on the “CREATE CREDENTIALS” tab (2).
First you need to choose the API you are using. From the dropdown menu, choose the Google Sheets API:
Now the screen will prompt you for where you will be getting data from. In this box you will choose “Other non-UI (e.g. cron job, daemon) and the data type is application data and not using a compute engine, then click what credentials do I need.
You will now be adding credentials to your project:
Fill in the service account name. Use MyRasPi
Select Role: choose project \ Owner
Key Type is JSON.
Make a note of your service account ID as well. Then click continue. You will get an automatically downloaded JSON key that you need to use later. It will be in your downloaded files folder. Do not lose this.
You now have set up the credentials for this account.
Using a text Editor, open the JSON downloaded file. You will need the email address called client_email.
Copy that email address into your clipboard (using Crtr-C) because we will need for the google sheet.
Go back to your Google sheet (Raspi_data) and click on file / share or click on the green share button in the upper right side of screen.
You will then paste that email address into the share email, then click send.
You will need to copy that JSON file into the directory where you write your python programs on your raspberry pi. You should have been using /home/pi/Python_programs or something similar. Copy the JSON file that you downloaded to that directory. You will need to have access to it from the python programs that you write that will access the google sheet. The file that I downloaded was called mydata-7ad604e1aaef.json when I put it in my python programs folder, I renamed it to mydata.json so that I could easily reference it later.
There is one last thing to enable on Google. Go back to the console:
http://console.developers.google.com
Select your mydata project. Click on ENABLE APIs and SERVICES again.
This time in the search window, fill in drive and select the Google Drive API:
This will bring up a new page that will allow you to enable the Google Drive API.
You will need both the Google Drive API and the Google sheets API enabled to be successful in writing to the google sheet. Since you already created credentials for the Sheets API, you should not need new credentials for the Google Drive API.
If you click on the GoogleAPIs logo again, and select project mydata, you should see at the bottom of the screen that both the APIs are enabled:
3. Install some software on the RasPi
Now we have to install the google spreadsheet and authorization client python modules. Since we are using python3, we need to use pip3 as our package manager. Open a terminal window and type the following:
pi@hostname:~ $sudo apt-get update
pi@hostname:~ $sudo apt-get upgrade
pi@hostname:~ $sudo pip3 install gspread oauth2client
pi@hostname:~ $sudo pip3 install -–upgradegoogle-auth-oauthlib
Task 2: Enter the following program on your Raspberry pi
Save it to your Python_programs directory as test_sheets.py and make sure the mydata.json file is in the same directory as this program.
Task 2 Python Program
| Python Program 1 |
|---|
import datetime, time
import gspread
from oauth2client.service_account import ServiceAccountCredentials
scope = ['https://spreadsheets.google.com/feeds','https://www.googleapis.com/auth/drive']
creds = ServiceAccountCredentials.from_json_keyfile_name('mydata.json', scope)
#replace mydata.json with the name of your data file
client = gspread.authorize(creds)
sheet = client.open("Raspi_data").sheet1
time = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S.%f')
print(time)
data = 1
values =[time,data]
sheet.append_row(values)
|
Critical Thinking Questions:
- Before running the program above figure out what each line of code is supposed to do when the program run.
- Run the program a few times, and check your google sheet called Raspi_data. What have you accomplished with Task 1 and Task 2 today?
Task 3: Enter the following program on your Raspberry pi
Save it to your Python_programs directory as test_sheets2.py and make sure the mydata.json file is in the same directory as this program.
Task 3 Python Program
| Python Program 2 |
|---|
import datetime, time
import gspread
from oauth2client.service_account import ServiceAccountCredentials
scope = ['https://spreadsheets.google.com/feeds','https://www.googleapis.com/auth/drive']
creds = ServiceAccountCredentials.from_json_keyfile_name('mydata.json', scope)
#replace mydata.json with the name of your data file
client = gspread.authorize(creds)
sheet = client.open("Raspi_data").sheet1
data = 1
def append_sheet(data):
time = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S.%f')
print("*"*80)
values =[time,data]
print(values)
sheet.append_row(values)
print("Google Sheet Updated")
print("*"*80)
while data<10:
client.login()
append_sheet(data)
data += 1
print("Waiting")
for i in range (10):
print(i)
time.sleep(1)
|
3. Before running the program above figure out what each line of code is supposed to do when the program run.