How to use IBM App Connect with Google Sheets

Google Sheets is a web-based application that you can use with your colleagues to create and modify spreadsheets online. Use IBM® App Connect to connect Google Sheets to other applications, such as a messaging application. For example, when a new row is added to a spreadsheet, you are notified immediately in the messaging application.

Supported product and API versions

To find out which product and API versions this connector supports, see Detailed System Requirements on the IBM Support page.

Connecting to Google Sheets

Complete the connection fields that you see in the App Connect Designer Connect > Applications and APIs page (previously the Catalog page) or flow editor. If necessary, work with your Google Sheets administrator to obtain these values.

Google Sheets authorization types and connection fields:

Authorization type	Connection fields	Applicability
Use the application's website to sign in (OAUTH 2.0 AUTH CODE)	Application client ID	App Connect Enterprise as a Service
	Application client secret	App Connect Enterprise as a Service
Provide credentials for App Connect to use (BASIC OAUTH)	Application client ID	App Connect Enterprise as a Service and App Connect in containers
	Application client secret
	OAuth Access token
	OAuth Refresh token

The following table contains descriptions of the connection fields.

Tip:

To connect using the website OAuth 2.0 authorization method, see Connecting to a Google application using the website OAuth 2.0 authorization method

To generate the values for Application client ID, Application client secret, Access token, and Refresh token see Getting OAuth client ID, client secret, access token, and refresh token for Google applications.

Connection field	Description
Application client ID	The unique identifier that is generated from the Google API Console that maps to specific project requests.
Application client secret	The application client secret for the unique application client ID created for a specific project.
OAuth Access token	The access token that is generated from the application client ID and client secret.
OAuth Refresh token	The refresh token that is generated from the application client ID and client secret.

To connect to a Google Sheets endpoint from the App Connect Designer Applications and APIs page for the first time, expand Google Sheets, then click Connect. For more information, see Managing accounts.

Tip:

Before you use the account that is created in App Connect in a flow, rename the account to something meaningful that helps you to identify it. To rename the account on the Applications and APIs page, select the account, open its options menu (⋮), then click Rename Account.

What to consider first

Before you use App Connect Designer with Google Sheets, take note of the following considerations:

You can see lists of the available trigger events and actions on the Catalog page of the App Connect Designer.
The performance of this connector is constrained by the API limits that are imposed by Google. For more information, see Limits and Quotas on API Requests.
(General consideration) If you are using multiple accounts for an application, the set of fields that is displayed when you select an action for that application can vary for different accounts. In the flow editor, some applications always provide a curated set of static fields for an action. Other applications use dynamic discovery to retrieve the set of fields that are configured on the instance that you are connected to. For example, if you have two accounts for two instances of an application, the first account might use settings that are ready for immediate use. However, the second account might be configured with extra custom fields.

Triggering a flow with the New complete row appended event

To use the New complete row appended event, ensure that your spreadsheet meets the following criteria.

The worksheet that you select must have column names in the first row.
When you add a row, you must add it to the end of the worksheet.
The new row must contain data for all the columns that are named in the first row.

If any of these conditions are not met (for example, if you add a row in the middle of the worksheet) the flow is not triggered.

Adding Google Sheets as your target application

To use a Google Sheets spreadsheet as your target application, you must select a worksheet that has column names in the first row. These column names are used as the target fields for your action. If the first row of your worksheet is not populated with column names, you cannot match fields between your source and target applications.

Note: If your worksheet contains duplicate column names, only the first instance of each duplicated column is used as a target field.

Processing the data from Retrieve rows action

When you use the Retrieve rows action to get all the data from a worksheet, you might retrieve an extra row that contains column names. If you plan to process the retrieved data (for example, by using a For each node), you must remove the row of column names first.

JSONata tip: To remove the first item in the array of objects that is returned by the Retrieve rows action, use the following JSONata expression.

$GoogleSheetsRetrieverows[[1..$count($GoogleSheetsRetrieverows)-1]]

This expression returns an array of objects.

Events and actions

Google Sheets events

These events are for changes in this application that trigger a flow to start completing the actions in the flow.

Note: In containers, only local accounts can be used for these events.

Rows

New complete row appended

Spreadsheets

New spreadsheet

Google Sheets actions

Your flow completes these actions on this application.

Rows

Create row
Retrieve rows

Spreadsheets

Create spreadsheet
Retrieve spreadsheets

Worksheets

Create worksheet

Examples

Dashboard tile for a template that uses Google Sheets

Use templates to quickly create flows for Google Sheets

Learn how to use App Connect templates to quickly create flows that complete actions on Google Sheets. For example, open the Templates gallery, and then search for Google Sheets.

Learn more