You can use IBM® App Connect to pass key data between Google Sheets™ web-based spreadsheet program and other apps – automatically, in real time.

You can use App Connect with Google Sheets by configuration and data mapping without a need for coding, and can achieve a return on your investment in minutes/hours, not days/months.

This guide is to show you how…

If you can’t find what you want, or have comments about the “how to” information, please either add comments to the bottom of this page or .

What should I consider first?

You can see a full list of the events and the actions that are available with Google Sheets on the Applications tab on the App Connect Catalog page.

Using the New complete row appended event

If you want to use the New complete row appended event, you must ensure a few things:

  1. The worksheet you select has column names in the first row.
  2. When a row is added it is appended to the end of the worksheet.
  3. The new row has data for all of the columns 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, or omit data from any named column in the row) the flow will not be triggered.

Using Google Sheets as your target application

If you want 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 your worksheet does not have the first row populated with column names, you cannot match fields between your source and target applications.

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

Working with the data from Retrieve rows

When using the Retrieve rows action to get all the data from a worksheet, you might end up with the data you want plus a row containing the column names. If you plan to process this data, perhaps using a For each node, you’ll need to remove this row of column names first.

JSONata tip: Use $GoogleSheetsRetrieverows[[1..$count($GoogleSheetsRetrieverows)-1]] to remove the first item in the array of objects returned from Retrieve rows. This will return an array of objects.

Examples

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

Use this template to record your leads via the Slack phone app, record the contact in a Google sheet (to audit the creation), and then create the contact in Marketo. A response is then sent on Slack giving the newly created Marketo contact id.

The Templates gallery in Designer provides several templates that involve Google Sheets events and actions.

In Designer, open the Templates gallery and search for the template title or sheets. To refer to the instructions for this template while editing the flow or without using Designer, open the github page (opens in a new window).

Creating an event-driven flow to connect Salesforce to Google Sheets

Learn how easy it is to use App Connect to connect Salesforce to Google Sheets so that every time a new lead is added in Salesforce, details of that lead are added automatically to a row in a Google Sheets spreadsheet.

2 comments on"How to use IBM App Connect with Google Sheets web-based spreadsheet program"

  1. I’m wondering how IBM can use Google sheet if our email is not @gmail.com?

    • @Kit, Hi
      In IBM App Connect, you can set up Google Sheets connection with an email that is not @gmail.com, because Google resolves that email to its linked Google email (used for access to Google Sheets). For example, in my App Connect I set up Google Sheets with my old xxxx@yahoo.co.uk which was resolved to the linked xxxx@googlemail.com email. In App Connect, I’ve also set up Google Sheets with my IBM ID …@uk.ibm.com, which resolved to its linked Google email account.

      In the App Connect interface, such as on the Applications page, the Google Sheets account is shown with the actual or linked Google email account; eg xxxx@googlemail.com. In

      Regards,
      Ian

Join The Discussion

Your email address will not be published. Required fields are marked *