Pablo Coca

March 2017 Release Deep Dive: Google Sheets Connector

Blog Post created by Pablo Coca Employee on Mar 8, 2017

We're excited to announce the availability of the Google Sheets connector in the March 2017 release. Let's take a closer look at this connector and how to configure it within your integration processes.

 

For complete connector documentation, see Google Sheets connector in the AtomSphere User Guide.

 

 

Glossary

Before we jump in, here's a glossary to clarify Google Sheets terminology:

  • Google Sheets is a web-based application that enables users to create, update and modify spreadsheets and share the data live online. More details on: https://www.google.com/sheets/about

  • Spreadsheet is an online file that contains one or more sheets.

  • Sheet is a matrix (of column and row dimensions) of cells.

  • Cell is the rectangle in a sheet that is the intersection of a column and a row.

  • Column is a group of cells that runs vertically from top-to-bottom. Columns are identified by letters with values from A to ZZZ.

  • Row is a group of cells that runs horizontally from side to side. Rows are identified by numbers.

  • Record Data is an internal concept in the Google Sheets connector which represents the data in a group of cells that runs horizontally from an initial column to a final column.

 

Configuring the Connections

 

Google Credential Setup

Before creating a project, enable Google Drive and Google Sheets APIs in the Library section of the Google Developers Console.

 

01. Create or select a project in the Google Developers Console and automatically turn on the API using this wizard. Click Continue then go to the Credentials section.

 

NOTE: With the previous URL you should have enabled the Google Drive & Google Sheets APIs, if not please enable these on the Library section.

 

02. On the Add credentials to your project page, click the Cancel button.

 

03. At the top of the page, select the OAuth consent screen tab. Select an Email address, enter a Product name if not already set, and click the Save button.

 

04. On the Credentials tab, click the Create credentials button, select OAuth client ID, and then perform these steps:

  1. Select the application type Web Application
  2. Enter a name (for example, “Dell Boomi AtomSphere”)
  3. On Authorized JavaScript origins add the URL: https://platform.boomi.com
  4. On Authorized Redirect URIs add: https://platform.boomi.com/account/<Account_ID>/oauth2/callback

For example:

https://platform.boomi.com/account/your_account-C12B3/oauth2/callback

 

NOTE:

You can get your Account ID with AtomSphere by navigating to:

Setup > Account Information > Account ID

 

 

            e. Click the Create button.

 

05. In the dialog box that opens, copy these values for future reference and then click OK:

  • Client ID
  • Secret ID

 

Configuring the Connection Component

 

01. Now within AtomSphere platform, create a new Google Sheets connection which supports the Open Authentication v2.0 authorization type, so just leave the Connection Tab default as it is.

 

 

02. On the OAuth 2.0 tab, perform these steps:

  1. Enter the Client ID value you got previously.
  2. Enter the  Client Secret value you got previously.
  3. Ensure that popup windows is enabled on your browser
  4. Click on  “Generate…”.  A new browser window opens.
  5. Make offline access required clicking on Allow. You get a message indicating Authorization Code received.

 

Integrating with Google Sheets

The scenarios below describe some of the common use cases for reading and writing data with Google Sheets.

 

 

Scenario 1: Add a new row with data (RECORD DATA) on a Google Sheet

In this scenario, our goal is to add a new row with data (RECORD DATA) on this Google Sheet:

 

 

Let’s create a process:

 

 

01. Create a new process and name  it: CREATE RECORD DATA

02. Set the Start Shape type as: No Data

03. Add a new Message shape, name it: DATA TO ADD, and save it.

04. Connect the output of the Start shape to the input of the Message shape.

05. Add a new connector shape with these settings:

a. For the connector, select: Google Sheets

b. For Action, select: CREATE

c. For Connection, see section: Configuring the connection component

d. On Operation, create a new one and call it: CREATE DATA

e. Click on the Import button and set these values:

NOTE: For import details, the FAQ "What parameters should I use in the Google Sheets Import Wizard?" at the end of this article.

 

f. Click on Next

g. Select the Object Type: Record Data

h. Click on Next

i. Click on Finish

 

NOTE: You should now have Request and a Response Profiles associated to this operation like this:

j. Click Save and Close

 

06. Edit the Message shape we called: DATA TO ADD, and make these settings:

  1. Message: {1}
  2. Add a Parameter with:
    • Type: Static
    • Static Value:
{
    "spreadsheetId":    "1t5jyhJ8m26DZriwx169HXqCDX1x2ApQT3-OTqIZWUJE",
    "sheetTitle":       "People",
    "Last Name":        "John",
    "First Name":       "Smith",
    "Email":            "john@smith.com",
    "Status":           0
}

NOTE: Be sure to insert the proper spreadsheetId, sheetTitle, and other keys/values, as these are tightly related to the Google Sheet you want to connect.

c. Click OK

 

07. Click Save

08. Click Test

09. Select your desired atom

10. Click Run Test

 

You should get the Google Sheet with the new row with data (RECORD DATA) added:

 

 

Scenario 2: Update a row on a Google Sheet

In this scenario, our goal is to update the Status column from 0 to 1 in the 5th row on the following Google Sheet:

 

 

Let’s step through creating this  process:

 

01. Create a new process and name it: UPDATE RECORD DATA

02. Set the Start shape type as: No Data

03. Add a new Message shape, name it: DATA TO UPDATE, and save it.

04. Connect the output of the Start shape to the input of the Message shape.

05. Add a new Connector shape with these settings:

a. For connector, select: Google Sheets

b. For Action, select: UPDATE

c. For Connection, see section: Configuring the connection component

d. On Operation, create a new one and call it: UPDATE DATA

e. Click on the Import button and set these values:

NOTE: For import details, the FAQ "What parameters should I use in the Google Sheets Import Wizard?" at the end of this article.

 

f. Click Next

g. Select the Object Type: Record Data

h. Click Next

i. Click Finish

 

NOTE:

You should have a Request Profile associated to this operation like:

The rowIndex field is used on the Message shape to indicate the row you want to update.

 

You should have a Response Profile associated to this operation like:

j. Click Save and Close

 

06. Edit the Message shape we called: DATA TO UPDATE, and make these settings:

  1. Message: {1}
  2. Add a Parameter with:
    • Type: Static
    • Static Value:
{
    "spreadsheetId":    "1t5jyhJ8m26DZriwx169HXqCDX1x2ApQT3-OTqIZWUJE",
    "sheetTitle":       "People",
    "rowIndex":         5,
    "Status":           1
}

NOTE: Be sure to insert the proper spreadsheetId, sheetTitle, and other keys/values, as these are tightly related to the Google Sheet you want to connect.

 

c. Click OK

 

NOTE: The rowIndex is the row number you see on the Google Sheet online interface.
Here we want to update the status of the 5th row, which has these values:

{Smith, John, john@smith.com, 0}

You just have to enter the Request Profile fields that you want to update, not all of them.

 

07. Click Save

08. Click Test

09. Select the Atom to use

10. Click Run Test

You should get the Google Sheet with the 5th row updated like this:

 

Scenario 3 - Get all rows from a Google Sheet

In this scenario, our goal is to get all rows from the following Google Sheet:

 

 

Let’s create this process:

 

 

01. Create a new process and call it: QUERY RECORD DATA

02. Set the Start shape type as: Connector

a. For Connector, select: Google Sheets

b. For Action, select: QUERY

c. For Connection, see section: Configuring the connection component

d. For Operation, create a new one and call it: QUERY RECORD DATA

e. Click on the Import button and set these values:

NOTE: For import details, the FAQ "What parameters should I use in the Google Sheets Import Wizard?" at the end of this article.

 

f. Click Next

g. Select the Object Type: Record Data

h. Click Next

i. Click Finish

 

NOTE:  You should have a Response Profile associated to the operation like this:

j. Click Save and Close

 

03. Click Test

04. Select your desired Atom

05. Click Run Test

You should get four RECORD DATA documents on the output of the connector like this:

 

The 4th document, for example, has the following content:

{
    "spreadsheetId":    "1t5jyhJ8m26DZriwx169HXqCDX1x2ApQT3-OTqIZWUJE",
    "sheetTitle":       "People",
    "Last Name":        "Smith",
    "First Name":       "John",
    "Email":            "john@smith.com",
    "Status":           1
}

 

This is the content of the 5th row of the Google Sheet shown at the beginning of this section.

 

FAQ (Frequently Asked Questions)

 

How do I get the Spreadsheet ID of a Google Sheet?

On a browser, open the desired Google Sheet, the Spreadsheet ID is the value between /d/ and /edit on the URL.

 

For example:

 

 

How do I get the Sheet Title of a Google Sheet?

On a browser, open the desired Google Sheet. The Sheet Title is the label located on the active sheet of the Spreadsheet.

 

For example:

 

 

What parameters should I use in the Google Sheets Import Wizard?

 

  • Spreadsheet ID: Enter the spreadsheet ID to identify which spreadsheet is to be accessed or altered. The spreadsheet ID is the value between "/d/" and "/edit" in the spreadsheet URL.

  • Sheet Title: Optionally enter a sheet title. If blank, the title of the first tabbed sheet is used.

  • Has 1st Row of Headers ?: Turn on for RecordData objects if your sheet has headers in the first row.

  • Initial Column [A-ZZZ]: Enter the first column in the sheet in which you will create, query, or update RecordData objects.

  • Final Column [A-ZZZ]: Enter the last column in the sheet in which you will create, query, or update RecordData objects.

 

Which Google SDK version does the connector use?

The connector uses the Google API V4.

Outcomes