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

Google Sheets, separate of Google Workspace, allows teams to create and contribution spreadsheets on-line and has grown into a widely used business tool. For many processes, all you need is a share spreadsheet and a automaton using it !

Which automation library should you use?

The RPA.Cloud.Google library, part of RPA Framework, enables interaction with Google Sheets .

Installation and setup

Your robots will need to authenticate with Google to be able to interact with Google Sheets spreadsheets, using the concept of service accounts. The account used by the automaton will then need to be added as a collaborator to the sail ( s ) you want to access .

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.
   
3. Click on ENABLE APIS AND SERVICES.
4. Find and enable the 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.
   
6. On the next screen, choose a name for your service account, assign it a role of Project->Editor, and click Continue.
   
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.
   

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

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.

Robot example

now that our account setup is complete, we will build a automaton that :

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

hera ‘s our exemplar spreadsheet with some test data :

Create a new robot and add the RPA.Cloud.Google library

1. Create a new robot using the VS Code Robocorp extension.
2. Edit the conda.yaml file in your robot like this:

channels:
  - conda-forge
dependencies:
  - python=3.7.5
  - pip=20.1
  - pip:
      - rpaframework-google==1.0.2

The google package in RPA Framework is not included by nonpayment because of the size of its dependencies. By adding the - rpaframework-google==1.0.2 credit line you are adding it explicitly to your automaton .

Robot script

authoritative ! Remember to add the service_account.json charge to the etymon directory of your automaton .

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

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

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

*** Tasks ***
Add values to the Google Sheet
    ${values}=    Evaluate    [["Mark", "The Monkey", 100000, 10000]]
    Insert Sheet Values
    ...    ${SHEET_ID}
    ...    ${SHEET_RANGE}
    ...    ${values}
    ...    ROWS

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    service_account.json

In the *** Settings *** section, the Documentation adjust explains what our automaton does. We then add the RPA.Cloud.Google library. last, we use the Suite Setup setting to initialize the Google Sheets client. This way, it will be initialized only once, even if our automaton 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 Sheet Values
    ...    ${SHEET_ID}
    ...    ${SHEET_RANGE}
    Log Many    ${spreadsheet_content["values"]}

In this tax, 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 Sheet 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 :

*** Tasks ***
Add values to the Google Sheet
    ${values}=    Evaluate    [["Mark", "The Monkey", 100000, 10000]]
    Insert Sheet Values
    ...    ${SHEET_ID}
    ...    ${SHEET_RANGE}
    ...    ${values}
    ...    ROWS

In this task, we add some arbitrary data to a newfangled rowing in the spreadsheet .

1. Using the Evaluate keyword, we create a variable with the values for the row.
2. We pass the values, the sheet id, and range to the Insert Sheet Values keyword. The values will be added to the first available row using the ROWS major dimension option.

Storing the credentials in Control Room Vault

You should never include passwords or credential files directly into the code of your robot. alternatively of reading the credentials from the service_account.json file, our automaton can use the vault feature of speech of Control Room.

1. Set up your robot to run in Control Room
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:
   
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
   ...               vault_name=GoogleSheets
   ...               vault_secret_key=service_account
   Suite Setup       Init Sheets    use_robocorp_vault=True
   

Learn more about the libraries mentioned on this page :

• RPA.Cloud.Google

December 9, 2021