How to read from and write into Google Sheets from your robots
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.
- Access the Google APIs Console while logged into your Google account.
- Create a new project and give it a name.
- Click on
ENABLE APIS AND SERVICES
. - Find and enable the
Google Sheet API
. - Create new credentials to the
Google Sheets API
. SelectOther UI
from the dropdown and selectApplication Data
. Then click on theWhat credentials do I need?
button. - On the next screen, choose a name for your service account, assign it a role of
Project
->Editor
, and clickContinue
. - 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.
- 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
- Create or select an existing Google Sheet.
- Open the
service_account.json
file and find theclient_email
property. - 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. - 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 ishttps://docs.google.com/spreadsheets/d/1234567890123abcf/edit#gid=0
, the ID will be1234567890123abcf
.
Robot example
Now that our account setup is complete, we will build a robot that:
- Reads the existing data from a Google Spreadsheet and logs it.
- 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
- Create a new robot using the VS Code Robocorp extension.
- Edit the
conda.yaml
file in your robot like this:
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.
Robot script explained
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.
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 theA2
cell, and ends with theD10
cell of the first sheet, so our value will beSheet1!A2:D10
.
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:
In this task, we add some arbitrary data to a new row in the spreadsheet.
- Using the
Evaluate
keyword, we create a variable with the values for the row. - 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 theROWS
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.
-
Create a new vault in the robot's workspace. Assign it the name
GoogleSheets
. -
Create a new secret in the vault. Give it a key of
service_account
, and paste the contents of theservice_account.json
file into the value field: -
Modify the
***Settings***
section of the script to configure theRPA.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