How to read from and write into Google Sheets from your robots

The RPA.Cloud.Google library, part of RPA Framework, enables your robots to interact directly with Google Sheets.

NOTE: Support for Google Sheets has been added to RPA Framework from version 5.1 onwards.

Installation and setup

Create a Google Service Account

To access the data stored in Google Sheets, you will need to create a service account and get a set of OAuth2 credentials from the Google API Console.

  1. Access the Google APIs Console while logged into your Google account.
  2. Create a new project and give it a name. Create a new project
  3. Click on Enable APIS and Services.
  4. Find and enable the Google Sheet API. Enable Google Sheet API
  5. Create new credentials to the Google Sheets API. Select Other UI from the dropdown and select Application Data. Then click on the What credentials do I need? button. Create credentials
  6. On the next screen, choose a name for your service account, assign it a role of Project->Editor, and click Continue. Create credentials step 2
  7. The credentials JSON file will be downloaded by your browser.

    The credentials file allows anyone to access your cloud resources, so you should store it securely. More information from Google.

  8. Find the downloaded file and rename it to service_account.json.

Create a new Google Sheet and add the Service Account as an editor to it

  1. Create or select an existing Google Sheet.
  2. Open the service_account.json file and find the client_email property.
  3. Click on the Share button in the top right, and add the email address of the service account as an editor. Add user as an editor

    If you want only to allow the account read access to the spreadsheet, assign it the Viewer role instead.

  4. Take note of the ID of the Google Sheet document, which is contained in its URL, after the /d element. So, for example, if the URL of your document is https://docs.google.com/spreadsheets/d/1234567890123abcf/edit#gid=0, the ID will be 1234567890123abcf.

Create a new robot and add the RPA Framework library, including the optional google package

  1. Create a new robot in Robocorp Lab or using the Robocorp Code VS Code extension.
  2. Edit the conda.yaml file in your robot like this:
channels:
  - defaults
  - conda-forge
dependencies:
  - python=3.7.5
  - pip=20.1
  - pip:
      - rpaframework[google]

The google package in RPA Framework is not included by default because of the size of its dependencies. By adding the - rpaframework[google] line you are adding it explicitly to your robot.

Robot example

We will build a robot that:

  1. reads the existing data from a Google Spreadsheet and logs it.
  2. adds more data to the Google Sheet.

Here's our example spreadsheet with some test data:

Example Spreadsheet

Robot script

Important! Remember to add the service_account.json file to the root directory of your robot.

*** Settings ***
Documentation     An example robot that reads and writes data
...               into a Google Sheet document.
Library           RPA.Cloud.Google
Suite Setup       Init Sheets Client    service_account.json

*** Variables ***
${SHEET_ID}       1234567890123abcf
${SHEET_RANGE}    Sheet1!A2:D10

*** Tasks ***
Read values from the Google Sheet
    ${spreadsheet_content}=    Get Values
    ...    ${SHEET_ID}
    ...    ${SHEET_RANGE}
    Log Many    ${spreadsheet_content["values"]}

*** Tasks ***
Add values to the Google Sheet
    ${values}=    Create List
    ...    Mark
    ...    The Monkey
    ...    100000
    ...    10000
    Insert Values    ${SHEET_ID}    ${SHEET_RANGE}    ${values}

Robot script explained

*** Settings ***
Documentation     An example robot that reads and writes data
...               into a Google Sheet document.
Library           RPA.Cloud.Google
Suite Setup       Init Sheets Client    service_account.json

In the *** Settings *** section, the Documentation setting explains what our robot does. We then add the RPA.Cloud.Google library. Finally, we use the Suite Setup setting to initialize the Google Sheets client. This way, it will be initialized only once, even if our robot has multiple *** Tasks *** sections.

You can learn more about Suite Setup and Teardown in the Robot Framework User Guide

*** Variables ***
${SHEET_ID}       1234567890123abcf
${SHEET_RANGE}    Sheet1!A2:D10

In the *** Variables *** section, we set two variables:

  • ${SHEET_ID} will hold the id of our Google Sheet document.
  • ${SHEET_RANGE} is the range of cells that we want to work on, written in A1 notation. In our case, the area we are interested in in our spreadsheet starts from the A2 cell, and ends with the D10 cell of the first sheet, so our value will be Sheet1!A2:D10.
*** Tasks ***
Read values from the Google Sheet
    ${spreadsheet_content}=    Get Values
    ...    ${SHEET_ID}
    ...    ${SHEET_RANGE}
    Log Many    ${spreadsheet_content["values"]}

In this task, we are reading the rows specified by the ${SHEET_RANGE} of our Google Sheet, which is identified by the ${SHEET_ID}, into the ${spreadsheet_content} variable.

The Get Values keyword returns a dictionary with a values item in it containing a list of rows. Using the Log Many keyword, we can log that row data:

Execution log

*** Tasks ***
Add values to the Google Sheet
    ${values}=    Create List
    ...    Mark
    ...    The Monkey
    ...    100000
    ...    10000
    Insert Values    ${SHEET_ID}    ${SHEET_RANGE}    ${values}

In this task, we add some arbitrary data to a new row in the spreadsheet.

  1. Using the Create List keyword, we create a variable with the values for the row.
  2. We pass the values, the sheet id, and range to the Insert Values keyword. The values will be added to the first available row.

Robot Running

Storing the credentials in Robocorp Cloud Vault

You should never include passwords or credential files directly into the code of your robot. Instead of reading the credentials from the service_account.json file, our robot can use the vault feature of Robocorp Cloud.

  1. Set up your robot to run in Robocorp Cloud
  2. Create a new vault in the robot's workspace. Assign it the name GoogleSheets.
  3. Create a new secret in the vault. Give it a key of service_account, and paste the contents of the service_account.json file into the value field: Vault configuration
  4. Modify the ***Settings*** section of the script to configure the RPA.Cloud.Google library to use the vault:
    *** Settings ***
    Documentation     An example robot that reads and writes data
    ...               into a Google Sheet document.
    Library           RPA.Cloud.Google    robocloud_vault_name=GoogleSheets
    ...               robocloud_vault_secret_key=service_account
    Suite Setup       Init Sheets Client    use_robocloud_vault=True