Raspberry Pi cases, Raspberry Pi, Arduino, micro:bit, robots

Log Raspberry Pi data to Google Sheets

Log Raspberry Pi data to Google Sheets

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’ 

enable API

Enter the project name, in the popup. If you agree with terms of service select Yes and click on next.

New project

In the new window, download the Client configuration file(credentials.json) to the same directory where your code will be.

download client

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.

google sheet

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

 

Previous article Micro:bit programming with blocks
Next article Control your Home Appliances with GUI on Raspberry Pi

Leave a comment

Comments must be approved before appearing

* Required fields

x



Net Orders Checkout

Item Price Qty Total
Subtotal £0.00 GBP
Shipping
Total

Shipping Address

Shipping Methods