Google Sheets VBO
The Google Sheets VBO enables Blue Prism to create and update spreadsheets in Google Sheets. It allows Blue Prism to interact with a single cell or range of cells to get and set values and formulas in those cell(s).
The following actions are included in the VBO:
- Append Data – Adds the given data to a specified cell or range of cells.
- Clear Range – Clears all data from the a specified cell or range of cells.
- Get Range Values – Retrieves values or formulas from a single cell or range of cells.
- Set Range Values – Sets the values or formulas for a single cell or range of cells.
- Create Workbook – Creates a new document in Google Sheets and returns its Sheet ID.
This document guides a user through the process of installing and setting up the VBO and provides some guidance on its use.
To configure and use the Google Sheets VBO, a Google account is required with the Google Sheets API enabled. The associated Google client ID and secret are required to configure the environment variables needed to access Google sheets for that account.
To enable the API and for access to the client ID and secret for a Google account client ID and secret, go to: https://developers.google.com/sheets/api/quickstart/go
Install and configure
On each device that the VBO will be used, i.e. runtime resources and relevant developer machines:
Run the Blue Prism installer and select Google Sheets automation from the Custom Install page.
On a single device in your environment:
- Import the VBO so it is available in the object store
Create two Environment Variables in Blue Prism:
Google Sheets API Client ID
Your client ID
Google Sheets API Client Secret
Your client secret
The variable names must exactly match the text above and are case sensitive.
Using the VBO
The VBO includes the following actions, available when it is selected as a business object in an action stage:
- Get Ranges – Gets a collection of values from a range in a Google Sheets document.
- Set Range Values – Sets a range of values in a Google Sheets document.
- Append Data – Appends the given data to a table found in a sheet. Tables are discovered programatically based on the given range and the data is added at the end of the table.
- Clear Range – Clears all values in the given range.
- Create Workbook – Creates a new Google Sheets document and returns its Google Sheet ID.
To interact with existing spreadsheets, the Google Sheet ID number is required as an input parameter in actions to connect to the correct sheet. The Sheet ID is found in the address bar of the internet browser:
More information about the VBO can be found in the API documentation, accessed from the Blue Prism console: Help > API Documentation.