1.5: Introduction to APIs-Google Sheets

Note

UALR students should use their personal Google account for this activity.

 

When you collect sensor data using Raspberry Pi, you can store it in a local file or upload it to Google Sheets using Google Sheets API and Google Drive API. Uploading your information to the cloud gives you the ability to effortlessly share real time data with other and have access to it from anywhere. We are going to set up a new Google Sheet that we will use to upload real sensor data in the next lesson.

Create New Google Sheet

1. Make sure you are logged in to your personal Google account. Navigate to https://docs.google.com/spreadsheets
2. Click on "Blank"

   

   Figure \(\PageIndex{1}\): Start a new spreadsheet. (Copyright; E. Lisitsyna CC0.0)

3. Change the name of your new Google Sheet from "Untitled Spreadsheet" to "RasPi Data"

   

   Figure \(\PageIndex{2}\): Change the name of your spreadsheet. (Copyright; E. Lisitsyna CC0.0)

4. Create column headers by typing "DateTime" in cell A1 and "Data" in cell B1

Enable Google APIs

Create New Project

1. Open a new tab and go to the Google API console http://console.developers.google.com
2. If this is your first time accessing Google Cloud Platform, you will be greeted with a welcome message. Select your country and check the checkbox, then click Agree and Continue.

   

   Figure \(\PageIndex{3}\): Agree to Terms of Service. (Copyright; E. Lisitsyna CC0.0)

3. This window might look different if you don't have any projects in your Google Cloud Platform. We will be creating a new project for this class. Click on the triange next to the project name (it might also say "Select a Project" if you are new to this platform)

   

   Figure \(\PageIndex{4}\): Select the project. (Copyright; E. Lisitsyna CC0.0)

4. In the overlay, click "New Project"

   

   Figure \(\PageIndex{5}\): Create a new project. (Copyright; E. Lisitsyna CC0.0)

5. Type "mydata" in the Project Name field. Click "Create"

   

   Figure \(\PageIndex{6}\): Name your new project. (Copyright; E. Lisitsyna CC0.0)

6. Wait for a few seconds. You will be notified when it's done. Click Select Project. This will bring you to your project dashboard

   

   Figure \(\PageIndex{7}\): Navigate to the created project. (Copyright; E. Lisitsyna CC0.0)

Enable APIs

1. Open the navigational menu (three lines next to the "Google Cloud Platform"), then select "APIs & Services"

   

   Figure \(\PageIndex{8}\): Go to APIs and Services. (Copyright; E. Lisitsyna CC0.0)

2. Click on "Enable APIs and Services"

   

   Figure \(\PageIndex{9}\): Click on the plus sign. (Copyright; E. Lisitsyna CC0.0)

3. In the search bar, type "Sheets" and hit Enter. This will show you the Google Sheets API that we need. Click on it

   

   Figure \(\PageIndex{10}\): Search for Google Sheets API. (Copyright; E. Lisitsyna CC0.0)

4. On the next page click "Enable"

   

   Figure \(\PageIndex{11}\): Enable API. (Copyright; E. Lisitsyna CC0.0)

5. Now we need to create credentials in order to be able to talk to Google Sheets. Click on "Create Credentials"

   

   Figure \(\PageIndex{12}\): Click Create Credentials. (Copyright; E. Lisitsyna CC0.0)

6. Make sure your select the options indicated in the image below. Then click "Next" to go to the next step. DO NOT click on "Done" as we are not done yet

   

   Figure \(\PageIndex{13}\): Create Credentials: Step 1. (Copyright; E. Lisitsyna CC0.0)

7. Next, type "MyRasPi" into the Service account name. You will see the email address populated below. Copy it by clicking on the copy icon. Use Sticky Notes app or any text document to paste this email. We will need it later. Click "Create and Continue" when you are done.

   

   Figure \(\PageIndex{14}\): Create Credentials: Step 1. (Copyright; E. Lisitsyna CC0.0)

8. In the next step, select "Owner" in the role field. This will give your service account MyRasPi full permissions. Click "Continue" to move onto the next step

   

   Figure \(\PageIndex{15}\): Create Credentials: Step 2. (Copyright; E. Lisitsyna CC0.0)

9. Don't change anything in step 3. Simply click "Done"
10. Raspberry Pi script will be using a JSON key to communicate with the Google Sheets. To create a JSON file navigate to Credentials. You should see your newly created service account. Click on the edit icon (pencil) 

    

    Figure \(\PageIndex{16}\): Edit service account. (Copyright; E. Lisitsyna CC0.0)

11. Go to the Keys tab, then click "Add Key" --> "Create new key" 

    

    Figure \(\PageIndex{17}\): Add new key. (Copyright; E. Lisitsyna CC0.0)

12. Make sure that JSON is selected and click "Create". The key will be downloaded automatically 
13. Last thing that we need to do is to enable Google Drive API. To do that, repeat steps 1-4 of this section. In step 3 search for "Google Drive". Select Google Drive API by Google Enterprise API, You don't need to create credentials for this API, since we already did that for Google Sheets API  

    

    Figure \(\PageIndex{18}\): Enable Google Drive API. (Copyright; E. Lisitsyna CC0.0)

Stream Data from Raspberry Pi to Google Sheets

Share the Google Sheets with your Service Account

1. Return to the Google Sheet you've created. Click on "Share" 

   

   Figure \(\PageIndex{19}\): Share your Google Sheet with your service account. (Copyright; E. Lisitsyna CC0.0)

2. You will need the service account email address from earlier. When you share your Google Sheet with the service account, make sure it is added as an editor 

   

   Figure \(\PageIndex{20}\): The service account requires editor permissions. (Copyright; E. Lisitsyna CC0.0)

 

Copy JSON Key to Raspberry Pi

The Python script will be referencing the JSON key that we downloaded from the Google Cloud Platform. This means that you need to copy the file from your device to the Raspberry Pi. We can use VNC Viewer for this. 

1. Open VNC Viewer and connect to your Raspberry Pi. Move your cursor to the top boarder of the VNC window, you will see the control panel slide down. Select the Icon with two arrows 

   

   Figure \(\PageIndex{21}\): Transfer files via VNC. (Copyright; E. Lisitsyna CC0.0)

2. Click "Send Files" and select the JSON file from the Downloads folder on your device (unless you saved it to a different folder). Click Open 
3. The file will appear on your Raspberry Pi desktop. Close the file transfer window. 
4. Move your JSON file to the folder with Python scripts and rename it to mydata-key.json

Static Data Upload

1. Open terminal and install gspread module: 

   pip3 install gspread
   

2. Open Thonny, copy and paste the following script. Make sure to save your Python script to the same location where you moved your JSON file to 

   #!/usr/bin/python3
   
   import datetime, time
   import gspread
   from google.auth.transport.requests import AuthorizedSession
   from google.oauth2 import service_account
   
   #Access Google API
   googleAPI = 'mydata-key.json'
   scope = ['https://www.googleapis.com/auth/drive']
   credentials = service_account.Credentials.from_service_account_file(googleAPI)
   scopedCreds = credentials.with_scopes(scope)
   gc = gspread.Client(auth=scopedCreds)
   gc.session = AuthorizedSession(scopedCreds)
   sheet = gc.open("RasPi Data")
   worksheet = sheet.worksheet("Sheet1")
   
   #Upload data to Google Sheets
   time = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
   data = 0
   values =[time, data]
   worksheet.insert_row(values, 2, value_input_option='RAW')
   print("Google Sheet Updated")
   

3. Check your Google Sheet. You should see date and time in the first column and "0" in the second column.

 

Using PubChem API to Upload Data Through Google Sheet API

When we introduced concatenation in section 2.3.3.1 we showed how you could combine strings to create a uri (universal resource identifier) that allows you to extract data from thorough a chemical compound database API like that in PubChem. In this activity you will make a new tab on your Google Sheet and then merge the script from two scripts to create a program that will only upload data to your Google Sheet when you ask it to. 

1. Create a new tab in your Google Sheet labeled Sheet 2
2. Edit the column headers in Google Sheet 2. Put "Compound Name" in cell B1, "Mass, g" in C1, "Moles" in D1 and leave "DateTime" in cell A1
3. Copy and paste the following script to your Thonny IDE, but don't run it yet:

   #!/usr/bin/python3
   
   import datetime
   from time import sleep
   import gspread
   from google.auth.transport.requests import AuthorizedSession
   from google.oauth2 import service_account
   import requests
   
   #Access Google API
   googleAPI = 'mydata-key.json'
   scope = ['https://www.googleapis.com/auth/drive']
   credentials = service_account.Credentials.from_service_account_file(googleAPI)
   scopedCreds = credentials.with_scopes(scope)
   gc = gspread.Client(auth=scopedCreds)
   gc.session = AuthorizedSession(scopedCreds)
   sheet = gc.open("RasPi Data") #Change this to match your spreadsheet's name
   worksheet = sheet.worksheet("Sheet2")
   
   #Send request to Pubchem 
   def getMoles():
       uri = ("https://pubchem.ncbi.nlm.nih.gov/rest/pug/compound/name/"
              +str(compound_name) +"/property/MolecularWeight/txt")
       res = requests.get(uri)
       molar_mass=float(res.text.rstrip()) 
       moles = mass/molar_mass
       return moles
   
   #Main loop
   while True:
       compound_name = input("Input compound's name: ")
       mass = float(input("input mass of sample in units of g: "))
       moles = getMoles()
       time = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
       values =[time, compound_name, mass, moles]
       worksheet.insert_row(values, 2, value_input_option='RAW')
       print("Google Sheet Updated")
       sleep(1)
   

     
4. Before running the program above figure out what each line of code is supposed to do when the program run.
5. Run the script and see the output in your Google Sheet

Contributors

Elena Lisitsyna created this module using prior material developed with assistance from Dr. Sunhwan Kim and Bob Belford.