Skip to main content
Chemistry LibreTexts

14.6: Upload Data to Google Sheet

  • Page ID
    472142
  • \( \newcommand{\vecs}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \)

    \( \newcommand{\vecd}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash {#1}}} \)

    \( \newcommand{\id}{\mathrm{id}}\) \( \newcommand{\Span}{\mathrm{span}}\)

    ( \newcommand{\kernel}{\mathrm{null}\,}\) \( \newcommand{\range}{\mathrm{range}\,}\)

    \( \newcommand{\RealPart}{\mathrm{Re}}\) \( \newcommand{\ImaginaryPart}{\mathrm{Im}}\)

    \( \newcommand{\Argument}{\mathrm{Arg}}\) \( \newcommand{\norm}[1]{\| #1 \|}\)

    \( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\)

    \( \newcommand{\Span}{\mathrm{span}}\)

    \( \newcommand{\id}{\mathrm{id}}\)

    \( \newcommand{\Span}{\mathrm{span}}\)

    \( \newcommand{\kernel}{\mathrm{null}\,}\)

    \( \newcommand{\range}{\mathrm{range}\,}\)

    \( \newcommand{\RealPart}{\mathrm{Re}}\)

    \( \newcommand{\ImaginaryPart}{\mathrm{Im}}\)

    \( \newcommand{\Argument}{\mathrm{Arg}}\)

    \( \newcommand{\norm}[1]{\| #1 \|}\)

    \( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\)

    \( \newcommand{\Span}{\mathrm{span}}\) \( \newcommand{\AA}{\unicode[.8,0]{x212B}}\)

    \( \newcommand{\vectorA}[1]{\vec{#1}}      % arrow\)

    \( \newcommand{\vectorAt}[1]{\vec{\text{#1}}}      % arrow\)

    \( \newcommand{\vectorB}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \)

    \( \newcommand{\vectorC}[1]{\textbf{#1}} \)

    \( \newcommand{\vectorD}[1]{\overrightarrow{#1}} \)

    \( \newcommand{\vectorDt}[1]{\overrightarrow{\text{#1}}} \)

    \( \newcommand{\vectE}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash{\mathbf {#1}}}} \)

    \( \newcommand{\vecs}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \)

    \( \newcommand{\vecd}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash {#1}}} \)

    \(\newcommand{\avec}{\mathbf a}\) \(\newcommand{\bvec}{\mathbf b}\) \(\newcommand{\cvec}{\mathbf c}\) \(\newcommand{\dvec}{\mathbf d}\) \(\newcommand{\dtil}{\widetilde{\mathbf d}}\) \(\newcommand{\evec}{\mathbf e}\) \(\newcommand{\fvec}{\mathbf f}\) \(\newcommand{\nvec}{\mathbf n}\) \(\newcommand{\pvec}{\mathbf p}\) \(\newcommand{\qvec}{\mathbf q}\) \(\newcommand{\svec}{\mathbf s}\) \(\newcommand{\tvec}{\mathbf t}\) \(\newcommand{\uvec}{\mathbf u}\) \(\newcommand{\vvec}{\mathbf v}\) \(\newcommand{\wvec}{\mathbf w}\) \(\newcommand{\xvec}{\mathbf x}\) \(\newcommand{\yvec}{\mathbf y}\) \(\newcommand{\zvec}{\mathbf z}\) \(\newcommand{\rvec}{\mathbf r}\) \(\newcommand{\mvec}{\mathbf m}\) \(\newcommand{\zerovec}{\mathbf 0}\) \(\newcommand{\onevec}{\mathbf 1}\) \(\newcommand{\real}{\mathbb R}\) \(\newcommand{\twovec}[2]{\left[\begin{array}{r}#1 \\ #2 \end{array}\right]}\) \(\newcommand{\ctwovec}[2]{\left[\begin{array}{c}#1 \\ #2 \end{array}\right]}\) \(\newcommand{\threevec}[3]{\left[\begin{array}{r}#1 \\ #2 \\ #3 \end{array}\right]}\) \(\newcommand{\cthreevec}[3]{\left[\begin{array}{c}#1 \\ #2 \\ #3 \end{array}\right]}\) \(\newcommand{\fourvec}[4]{\left[\begin{array}{r}#1 \\ #2 \\ #3 \\ #4 \end{array}\right]}\) \(\newcommand{\cfourvec}[4]{\left[\begin{array}{c}#1 \\ #2 \\ #3 \\ #4 \end{array}\right]}\) \(\newcommand{\fivevec}[5]{\left[\begin{array}{r}#1 \\ #2 \\ #3 \\ #4 \\ #5 \\ \end{array}\right]}\) \(\newcommand{\cfivevec}[5]{\left[\begin{array}{c}#1 \\ #2 \\ #3 \\ #4 \\ #5 \\ \end{array}\right]}\) \(\newcommand{\mattwo}[4]{\left[\begin{array}{rr}#1 \amp #2 \\ #3 \amp #4 \\ \end{array}\right]}\) \(\newcommand{\laspan}[1]{\text{Span}\{#1\}}\) \(\newcommand{\bcal}{\cal B}\) \(\newcommand{\ccal}{\cal C}\) \(\newcommand{\scal}{\cal S}\) \(\newcommand{\wcal}{\cal W}\) \(\newcommand{\ecal}{\cal E}\) \(\newcommand{\coords}[2]{\left\{#1\right\}_{#2}}\) \(\newcommand{\gray}[1]{\color{gray}{#1}}\) \(\newcommand{\lgray}[1]{\color{lightgray}{#1}}\) \(\newcommand{\rank}{\operatorname{rank}}\) \(\newcommand{\row}{\text{Row}}\) \(\newcommand{\col}{\text{Col}}\) \(\renewcommand{\row}{\text{Row}}\) \(\newcommand{\nul}{\text{Nul}}\) \(\newcommand{\var}{\text{Var}}\) \(\newcommand{\corr}{\text{corr}}\) \(\newcommand{\len}[1]{\left|#1\right|}\) \(\newcommand{\bbar}{\overline{\bvec}}\) \(\newcommand{\bhat}{\widehat{\bvec}}\) \(\newcommand{\bperp}{\bvec^\perp}\) \(\newcommand{\xhat}{\widehat{\xvec}}\) \(\newcommand{\vhat}{\widehat{\vvec}}\) \(\newcommand{\uhat}{\widehat{\uvec}}\) \(\newcommand{\what}{\widehat{\wvec}}\) \(\newcommand{\Sighat}{\widehat{\Sigma}}\) \(\newcommand{\lt}{<}\) \(\newcommand{\gt}{>}\) \(\newcommand{\amp}{&}\) \(\definecolor{fillinmathshade}{gray}{0.9}\)

    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 
      clipboard_e67856bbf394536302bd9fa202aa3bd24.png
      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}\).

    clipboard_efcf3b9fb7bc95b0e604b77c79da425ea.pngFigure \(\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".
    clipboard_e57483fc5e27263c35f43043945f21413.pngFigure \(\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.


    This page titled 14.6: Upload Data to Google Sheet is shared under a not declared license and was authored, remixed, and/or curated by Robert Belford.

    • Was this article helpful?