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 send us comments by email.
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.
- To connect IBM App Connect to Google Sheets, you use basic auth with OAuth credentials. You need to provide an OAuth 2.0 client ID, client secret, access token, and refresh token with appropriately-selected scopes for Google APIs. Getting those OAuth credentials is described in Getting OAuth client ID, client secret, access token, and refresh token for Google applications.
Using the New complete row appended event
If you want to use the New complete row appended event, you must ensure a few things:
- The worksheet you select has column names in the first row.
- When a row is added it is appended to the end of the worksheet.
- 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.
$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.
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.