Skip to main content
Chemistry LibreTexts

1.6: Writing to Google Sheets

  • Page ID
    211799
  • hypothes.is tag:
    Download Assignment:

    Learning Objectives

    Students will be able to:

    Content:

    • Use Google Sheets to store data from remote source
    • Set up Raspberry Pi to send data to Google Sheet

    Process:

    • Set up Google Sheet to receive external data
    • Set up Raspberry Pi to send data to Google Sheet    

    Prior Knowledge

    • Python and IoT concepts from previous Activities

    Further Reading

    Task 1: Setting up a Google Sheet to accept raspberry pi data

    Suppose you want your RasPi to save collected from a program/sensor to a Google Sheet that you can publish on the web or share the link. Google has set up an API (Application Programming Interface) to allow you to do that.

    To set this up:

    1. Go to sheets.google.com and log into the google account you set up in Activity 3a for gmail. Click on start a new blank spreadsheet.

    Create new Google Sheet
    Figure \(\PageIndex{1}\): Create new Google Sheet

    When the sheet is created, you should have an “Untitled Spreadsheet”

    Untitled Spreadsheet
    Figure \(\PageIndex{2}\): Untitled Spreadsheet

    In the section where it says “Untitled spreadsheet” give it a name. For this activity, let’s call it Raspi_data

    Give a name to your spreadsheet
    Figure \(\PageIndex{3}\): Give a name to your spreadsheet

    In the first row create a column headers of Datetime and data

    Create a column headers
    Figure \(\PageIndex{4}\): Create a column headers

    2. Open a new tab and go to the Google api console http://console.developers.google.com

    You will be creating a new project.

    Select a project
    Figure \(\PageIndex{5}\): Select a project

     

    New Project
    Figure \(\PageIndex{6}\): New Project

    In the New project window, you will see a place to type project name. Give it the name mydata

    Notice that you have a project ID that automatically is generated. Write this name and number down. You don’t need to change the location.

    Write down the name and number
    Figure \(\PageIndex{7}\): Write down the name and number

    Click create. You will see something indicating your project is being created:

    Click Create
    Figure \(\PageIndex{8}\): Click Create

    Click the reload icon on the google browser

    Reload the page
    Figure \(\PageIndex{9}\): Reload the page

    Next to the Google APIs header, select mydata from the dropdown menu to select this project.

    Select your project
    Figure \(\PageIndex{10}\): Select your project

    The myproject project should be in the header for Google APIs:

    Myproject project should be in the header
    Figure \(\PageIndex{11}\): Myproject project should be in the header

    Click on Enable APIS and SERVICES:

    Enable APIs and Services
    Figure \(\PageIndex{12}\): Enable APIs and Services

    In the search window, type sheets, and you should see the following:

    Search for sheets
    Figure \(\PageIndex{13}\): Search for sheets

    Click on the Google Sheets API that comes up as the result. Then click the ENABLE button.

    Enable Sheets API
    Figure \(\PageIndex{14}\): Enable Sheets API

    It will indicate that it is enabling the API and the following scree will come up. At this point, notice that you have mydata as the active project (1), and then you will click on the “CREATE CREDENTIALS” tab (2).

    Click Create Credentials
    Figure \(\PageIndex{15}\): Click Create Credentials

    First you need to choose the API you are using. From the dropdown menu, choose the Google Sheets API:

    Choose Google Sheets API
    Figure \(\PageIndex{16}\): Choose Google Sheets API

    Now the screen will prompt you for where you will be getting data from. In this box you will choose “Other non-UI (e.g. cron job, daemon) and the data type is application data and not using a compute engine, then click what credentials do I need.

    Make changes as indicated above
    Figure \(\PageIndex{17}\): Make changes as indicated above

    You will now be adding credentials to your project:

    Fill in the service account name. Use MyRasPi

    Select Role: choose project \ Owner

    Key Type is JSON.

    Add credentials to your project
    Figure \(\PageIndex{18}\): Add credentials to your project

    Make a note of your service account ID as well. Then click continue. You will get an automatically downloaded JSON key that you need to use later. It will be in your downloaded files folder. Do not lose this.

    You now have set up the credentials for this account.

    Credentials
    Figure \(\PageIndex{19}\): Credentials

     

    JSON key will automatically download 
    Figure \(\PageIndex{20}\): JSON key will automatically download 

    Using a text Editor, open the JSON downloaded file. You will need the email address called client_email.

    Copy client_email
    Figure \(\PageIndex{21}\): Copy client_email

    Copy that email address into your clipboard (using Crtr-C) because we will need for the google sheet.

    Go back to your Google sheet (Raspi_data) and click on file / share or click on the green share button in the upper right side of screen.

    Click Share
    Figure \(\PageIndex{22}\): Click Share

    You will then paste that email address into the share email, then click send.

    Paste client_email here
    Figure \(\PageIndex{23}\): Paste client_email here

    You will need to copy that JSON file into the directory where you write your python programs on your raspberry pi. You should have been using /home/pi/Python_programs or something similar. Copy the JSON file that you downloaded to that directory. You will need to have access to it from the python programs that you write that will access the google sheet. The file that I downloaded was called mydata-7ad604e1aaef.json   when I put it in my python programs folder, I renamed it to mydata.json so that I could easily reference it later.

    There is one last thing to enable on Google. Go back to the console:

    http://console.developers.google.com

    Select your mydata project. Click on ENABLE APIs and SERVICES again.

    Click on Enable APIs and Services again
    Figure \(\PageIndex{24}\): Click on Enable APIs and Services again

    This time in the search window, fill in drive and select the Google Drive API:

    Select Google Drive API
    Figure \(\PageIndex{25}\): Select Google Drive API

    This will bring up a new page that will allow you to enable the Google Drive API.

    Click Enable
    Figure \(\PageIndex{26}\): Click Enable

    You will need both the Google Drive API and the Google sheets API enabled to be successful in writing to the google sheet. Since you already created credentials for the Sheets API, you should not need new credentials for the Google Drive API.

    If you click on the GoogleAPIs logo again, and select project mydata, you should see at the bottom of the screen that both the APIs are enabled:

    Make sure both the APIs are enabled
    Figure \(\PageIndex{27}\): Make sure both the APIs are enabled

    3. Install some software on the RasPi

    Now we have to install the google spreadsheet and authorization client python modules. Since we are using python3, we need to use pip3 as our package manager. Open a terminal window and type the following:

    pi@hostname:~ $sudo apt-get update
    pi@hostname:~ $sudo apt-get upgrade
    pi@hostname:~ $sudo pip3 install gspread oauth2client
    pi@hostname:~ $sudo pip3 install -–upgrade google-auth-oauthlib

    Task 2: Enter the following program on your Raspberry pi

    Save it to your Python_programs directory as test_sheets.py and make sure the mydata.json file is in the same directory as this program.

    Task 2 Python Program

    Python Program 1
    import datetime, time
    import gspread
    from oauth2client.service_account import ServiceAccountCredentials
     
    scope = ['https://spreadsheets.google.com/feeds','https://www.googleapis.com/auth/drive']
    creds = ServiceAccountCredentials.from_json_keyfile_name('mydata.json', scope) 
    #replace mydata.json with the name of your data file
    client = gspread.authorize(creds)
    sheet = client.open("Raspi_data").sheet1
     
    time = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S.%f')
    print(time)
    data = 1
    values =[time,data]
    sheet.append_row(values)

     Critical Thinking Questions:

    1. Before running the program above figure out what each line of code is supposed to do when the program run.
    2. Run the program a few times, and check your google sheet called Raspi_data. What have you accomplished with Task 1 and Task 2 today?

    Task 3: Enter the following program on your Raspberry pi

    Save it to your Python_programs directory as test_sheets2.py and make sure the mydata.json file is in the same directory as this program.

    Task 3 Python Program

    Python Program 2
    import datetime, time
    import gspread
    from oauth2client.service_account import ServiceAccountCredentials
     
    scope = ['https://spreadsheets.google.com/feeds','https://www.googleapis.com/auth/drive']
    creds = ServiceAccountCredentials.from_json_keyfile_name('mydata.json', scope) 
    #replace mydata.json with the name of your data file
    client = gspread.authorize(creds)
    sheet = client.open("Raspi_data").sheet1
     
    data = 1
     
    def append_sheet(data):
        time = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S.%f')
        print("*"*80)
        values =[time,data]
        print(values)
        sheet.append_row(values)
        print("Google Sheet Updated")
        print("*"*80)
     
    while data<10:
        client.login()
        append_sheet(data)
        data += 1
        print("Waiting")
        for i in range (10):
            print(i)
            time.sleep(1)
    

    3. Before running the program above figure out what each line of code is supposed to do when the program run.