Add data to google sheet api

Google Sheets, part of Google Workspace, allows teams to create and share spreadsheets online and has grown into a widely used business tool. For many processes, all you need is a shared spreadsheet and a robot 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 robot will then need to be added as a collaborator to the sheet(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 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.

Robot example

Now that our account setup is complete, 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:

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.9.13
  - pip=22.1.2
  - pip:
      - rpaframework-google==6.0.0

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

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    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}
    IF    "values" in ${spreadsheet_content}
        Log Many    ${spreadsheet_content["values"]}
    END

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

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}
    IF    "values" in ${spreadsheet_content}
        Log Many    ${spreadsheet_content["values"]}
    END

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 Sheet Values keyword returns a dictionary with a values item containing a list of rows. To make the robot more robust, we check the existence of the values key before accessing it (an empty sheet would cause the values to be missing). 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 new row 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. Instead of reading the credentials from the service_account.json file, our robot can use the vault feature 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
   

August 31, 2022