14.6: Upload Data to Google Sheet

These activity assumes you have (1) created a Google Service Account, (2) Created a Key, (3) Enabled Google Drive and Sheets and (4) Created a Google sheet that is shared with the email associated with the key of your Google Service Account.

 

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{1}\): 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 assignment folder where you will create your 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 
    

   #Static Data Upload Code
   
   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 we showed how you could combine strings to create a uri (universal resource identifier) that allows you to extract data from 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. 

You want to create a Python program that inputs the name and mass of a chemical and outputs to a spreadsheet the request date stamp, chemical name, molar mass, mass and moles in a sample, as indicated in figure \(\PageIndex{2}\).

Figure \(\PageIndex{2}\): Copy and Paste Caption here. (Copyright; author via source)

 

1. Create a new tab in your Google Sheet labeled PubChem
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 = 'YOUR_JASON_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("YourSpreadSheet") #Change this to match your spreadsheet's name
   worksheet = sheet.worksheet("PubChem")
   
   #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
   
   # def getMolarMass():
   #     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()) 
   #     return molar_mass
   
   
   #Main loop
   another_chemical = True 
   while another_chemical is True:
       compound_name = input("Input compound's name: ")
       mass = float(input("input mass of sample in units of g: "))
       moles = getMoles()
       #molar_mass=getMolarMass()
       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 \n Would you like to test another chemical")
       sleep(1)
   
   
   
   

     
4. You need to alter the above script by inputting your jason key and the name of your Google Sheet.  When you run it the output will not be what you want, but what is in figure \(\PageIndex{3}\). You should look at the parameters of the worksheet.insert_row method, see what happens when you change the "2", and think about the python list "values".

Figure \(\PageIndex{3}\): Clearly the output is not what we desire. (Belford cc 0.0)

Hint: Analyze the commented out code, and remember the Thonnyt IDE shorcuts,  <alt>+3  to comment out code and <alt>+4 to uncomment code. 

Contributors

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