Google Sheets VBO

Click this icon on the toolbar to view and download a PDF version of this guide.

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.

Prerequisites

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

Installation and setup

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:

  1. Import the VBO so it is available in the object store
  2. Create two Environment Variables in Blue Prism:

  • Name

    Value

    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.