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.

Availability:
  • A connector in IBM App Connect Enterprise as a ServiceApp Connect Enterprise as a Service connector
  • A connector in IBM App Connect on IBM CloudCloud-managed connector
  • A local connector in a Designer instance of IBM App Connect in containers (Continuous Delivery release)Local connector in containers (Continuous Delivery release) 11.0.0.10-r1 or later
  • A local connector in a Designer instance of IBM App Connect in containers (Extended Update Support)Local connector in containers (Extended Update Support release)
  • A local connector in a Designer instance of IBM App Connect in containers (Long Term Support)Local connector in containers (Long Term Support release)
The following information describes how to use IBM App Connect to connect Google Sheets to your other applications.

Connecting to Google Sheets

To connect App Connect to Google Sheets, you can use the website OAuth 2.0 authorization method. You need to provide an OAuth 2.0 client ID, client secret, and update the authorized redirect URI for your Google application. For more information, see Connecting to a Google application using the website OAuth 2.0 authorization method.

To connect App Connect to Google Sheets, you can also use basic authentication with OAuth credentials. You need to provide an OAuth 2.0 client ID, client secret, access token, and refresh token with selected scopes for Google APIs. For more information, see Getting OAuth client ID, client secret, access token, and refresh token for Google applications.

To connect to Google Sheets from the catalog for the first time, expand Google Sheets, then click Connect. For more information about ways to connect to Google Sheets, see Managing accounts in App Connect.

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 Catalog 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 performing the actions in the flow.

Rows
New complete row appended
Spreadsheets
New spreadsheet

Google Sheets actions

These are actions on this application that you want a flow to complete.

Rows
Create row
Retrieve rows
Spreadsheets
Create spreadsheet
Retrieve spreadsheets
Worksheets
Create worksheet

Examples

For examples of using Google Sheets in flows, see the following resources:
Screenshot of the dashboard tile for a template that uses Google Sheets

Create new leads in Marketo by capturing lead names and details in Slack.

Use this template to record your leads in the Slack phone application, record the contact in a Google Sheets spreadsheet (to audit the creation), then create the contact in Marketo. A response is sent through Slack that provides the new Marketo contact ID.

Learn more

Screenshot of the dashboard tile for a template that uses Google Sheets

Create an event-driven flow to connect Salesforce to Google Sheets.

Use this template to connect Salesforce to Google Sheets. Every time a new lead is added in Salesforce, details of that lead are added automatically to a row in a Google Sheets spreadsheet.

Learn more