In this article, we’ll log Raspberry Pi's data in the google spreadsheet.
For IoT projects, we need a cloud database so that it can be accessed from outside our local network. There are many IoT database providers like Amazon AWS, Microsoft Azure but most of them are paid.
Google provides its services like docs and sheets for free up to a limit. We can use Google’s spreadsheet as our IoT database. We can log sensor data into a spreadsheet and fetch that data for processing.
Google spreadsheets have official API for popular languages, In this article, we’ll use python on Raspberry Pi to log some data into our spreadsheet.
Create a New Spreadsheet
If you don’t have a google account, create an account and create a new spreadsheet. Go to the below link -
https://docs.google.com/spreadsheets/u/0/
Start a new blank spreadsheet, by clicking on the plus icon.
Change the name of the spreadsheet for your convenience, by editing on the ‘Untitled spreadsheet’ textbox in the top left of the sheet.
Fetch the Spreadsheet ID
Every API method requires a spreadsheet parameter that is used to identify which spreadsheet is to be accessed or altered. This ID is the value between the "/d/" and the "/edit" in the URL of your spreadsheet. For example, consider the following URL that references a Google Sheets spreadsheet:
https://docs.google.com/spreadsheets/d/spreadsheetId/edit#gid=0
Reference: https://developers.google.com/sheets/api/guides/concepts
Copy the spreadsheet ID from the spreadsheet you just created.
Configure Google sheet API for
Visit the following link,
https://developers.google.com/sheets/api/quickstart/python
In step 1, click the ‘ENABLE THE GOOGLE SHEETS API’
Enter the project name, in the popup. If you agree with terms of service select Yes and click on next.
In the new window, download the Client configuration file(credentials.json) to the same directory where your code will be.
Install the Google client Libraries
Open terminal and type
pip3 install --upgrade google-api-python-client google-auth-httplib2 google-auth-oauthlib
Code
Python code to log data into a spreadsheet
import pickle
from os import path
from googleapiclient.discovery import build
from google_auth_oauthlib.flow import InstalledAppFlow
from google.auth.transport.requests import Request
class Sheets_Logging:
# The ID and range of a sample spreadsheet.
SPREADSHEET_ID = 'xxxxxxxxxxxxxxxxxxxxxxxxxxx'
RANGE_NAME = 'Sheet1'
# If modifying these scopes, delete the file token.pickle.
SCOPES = ['https://www.googleapis.com/auth/spreadsheets',
'https://www.googleapis.com/auth/drive.file',
'https://www.googleapis.com/auth/drive']
def __init__(self):
self.service = None
self.credentials = self.auth()
def auth(self):
"""Shows basic usage of the Sheets API.
Prints values from a sample spreadsheet.
"""
creds = None
# The file token.pickle stores the user's access and refresh tokens, and is
# created automatically when the authorization flow completes for the first
# time.
if path.exists('token.pickle'):
with open('token.pickle', 'rb') as token:
creds = pickle.load(token)
# 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', self.SCOPES)
creds = flow.run_local_server(port=0)
# Save the credentials for the next run
with open('token.pickle', 'wb') as token:
pickle.dump(creds, token)
self.service = build('sheets', 'v4', credentials=creds)
def read_data(self):
# Call the Sheets API
service = self.service
sheet = service.spreadsheets()
result = sheet.values().get(spreadsheetId=self.SPREADSHEET_ID,
range=self.RANGE_NAME).execute()
values = result.get('values', [])
if not values:
print('No data found.')
return None
else:
return values
def write_data(self, data):
service = self.service
values = [data]
body = {
'values': values
}
range_name = 'Sheet1'
result = service.spreadsheets().values().append(
spreadsheetId=self.SPREADSHEET_ID, range=range_name,
valueInputOption='USER_ENTERED', body=body).execute()
print('{0} cells appended.'.format(result \
.get('updates') \
.get('updatedCells')))
Copy the following code and make a python file.
Change the SPREADSHEET_ID variable with your spreadsheet’s ID.
The RANGE_NAME variable is used to give the address of the cell to write data. The first string is the sheet name which is by default Sheet1if you have changed the sheet name edit the RANGE_NAME variable.
You can read data from your spreadsheet with the read_data function, and write data with write_data function in the Sheets_Logging class.
Write your sensor’s code and pass its output data into the data argument of the write_data function as a list.
Here is a code that generates a random number, date, and fetches Raspberry Pi's temperature.
rom os import popen
from random import randrange
from datetime import datetime
def measure_temp():
temp = popen("vcgencmd measure_temp").readline()
return (temp.replace("temp=","").replace('\n', ''))
def gen_data():
temp = measure_temp()
random_number = randrange(100)
date = datetime.now()
return [str(date).split('.')[0], random_number, temp]
if __name__ == '__main__':
doc = Sheets_Logging()
data = gen_data()
doc.write_data(data=data)
You can modify the gen_data function according to your sensor type and log sensor values according to your requirements
Running code for the first time
While running your code keep in mind that credentials.json file is in the working directory. When you are running the code for the first time, the code will respond with
"Please visit this URL to authorize this application: https://someurl.com/"
The URL will automatically open in a web browser if it doesn’t, copy the URL and open the website.
A Gmail sign-in page will open asking to select the Gmail account.
Give all the permissions, and allow. When the process is completed you will see “The authentication flow has completed, you may close this window.” message. And a pickle. the token file will be generated in your current working directory.
Check if the spreadsheet is updating the script execution.
Conclusion
Your data will be now logged in the Google spreadsheet and you can do whatever you want with it, make a chart and use the data to control other devices.
For API reference visit
https://developers.google.com/sheets/api/guides/concepts
More google sheet related code snippets can be found at
https://github.com/gsuitedevs/python-samples/blob/master/sheets/snippets/spreadsheet_snippets.py
This article is published by SB Components
Check out our new Raspberry Pi 4 Clear case
