1: Upload Data to Google Sheet
- Page ID
- 433539
\( \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.
- 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) - 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
- The file will appear on your Raspberry Pi desktop. Close the file transfer window.
- 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
- Open terminal and install gspread module:
pip3 install gspread
- 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")
- 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.png](https://chem.libretexts.org/@api/deki/files/435730/clipboard_efcf3b9fb7bc95b0e604b77c79da425ea.png?revision=1)
- Create a new tab in your Google Sheet labeled PubChem
- 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
- 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)
- 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.png](https://chem.libretexts.org/@api/deki/files/435745/clipboard_e57483fc5e27263c35f43043945f21413.png?revision=1)
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.