Skip to main content

Import example: Add rows to a Google sheet

Stephen Brandt
  • Updated

Many of the actions you can take in a Google sheet are also available through the Google Sheets API. As you encounter use cases where data must be added to Google Sheets to support business processes, you can build flows in integrator.io to automate these actions.

In this example, we’ll walk through part of a scenario where each time a new customer is created in NetSuite, a new row containing that customer’s data is added to an existing Google sheet. We’ll configure an import to the Google sheet using a universal REST API connector.

Contents

A. Prepare the Google spreadsheet

The Google spreadsheet that we want integrator.io to add rows to must exist prior to configuring the import. It’s also a best practice to ensure it contains at least one header line as the first row.

Google Sheets API documentation provides additional details and examples explaining the data operations available to manipulate spreadsheet data. For the purposes of this example, we’ll be working with a simple, existing spreadsheet that does not require data manipulation.

B. Learn about the API method

Before building an import, familiarize yourself with the destination application and its API. The Add row action in Google Sheets allows you to add new rows to a selected sheet. The corresponding method for this operation in the Google API is spreadsheets.values.append. This documentation indicates our request from integrator.io must include the following:

  • spreadsheetId – The value between the "/d/" and the "/edit" in the URL of your Google spreadsheet.
  • range (in A1 notation) – The {range} parameter can be the named spreadsheet (as in our example below where our NetSuite customers file has one spreadsheet with the default “Sheet1” name). In other cases, you may need to specify a range of cells within the sheet as shown in the Google Sheets API documentation.
Spreadsheet.png
  • valueInputOption – A mandatory query parameter that we will hardcode to RAW to prevent any parsing from occurring.

We’ll take a closer look at these parameters as we configure the import in integrator.io.

C. Configure the import

  1. In Flow Builder, click Add destination/lookup.
    The Create destination/lookup page appears.
  2. Select REST API as the application.

Note: The built-in Google connector does support import operations. 

  1. For What would you like to do?, select Import records into the destination application.
  2. For this example, we’ll select an existing connection to Google Sheets.
ImportStep1_596.png
  1. Click Next.
  2. On the Create import page, provide a meaningful name for the import, such as Add new rows to customer contact Google sheet.
  3. For the HTTP method, select POST.
  4. Next, we will configure the Relative URI to pass the required spreadsheetId, range, and valueInputOption parameters:
v4/spreadsheets/<spreadsheetId>/values/<range>:append?valueInputOption=RAW

This is automatically appended to the Base URI (http://sheets.googleapis.com/) supplied by the connection.

POST format:

https://sheets.googleapis.com/v4/spreadsheets/<spreadsheetId>/values/<range>:append?
valueInputOption=RAW
  • To find the <spreadsheetId> parameter, check the value between the "/d/" and the "/edit" in the URL of your Google spreadsheet.
  • The <range> parameter can be the named spreadsheet, such as “Sheet1”.
  • valueInputOption is a mandatory query parameter that we will hardcode to RAW.

Example:

https://sheets.googleapis.com/v4/spreadsheets/19BGsEBGM1M2GD3dWYjsKSO_0x0CTmoLEjmvrj
P943Rs/values/Sheet1:append?valueInputOption=RAW
  1. For the HTTP request body, use the format shown below that corresponds with the spreadsheet columns.

Request body format:

  {
  "values": [
  ["Col1_Value", "Col2_Value", "Col3_Value", "Col4_Value", "Col5_Value"],
  ["Col1_Value", "Col2_Value", "Col3_Value", "Col4_Value", "Col5_Value"]
  ]
  }

Example:

  {
  "values": [
  ["Anna", "Smith", "ANBC", "asmith@anbc.com", "6767676767"],
  ["Ramesh", "Tembe", "Charter Inc.", "rtembe@charterinc.com", "5551005555"],
  ["{{record.FirstName}}", "{{record.LastName}}", "{{record.Company}}", 
   "{{record.Email}}", "{{record.Phone}}"]
  ]
  }
ImportConfigureCORRECTED.png
  1. Click Save & close.

The import appears as part of a new flow. To complete this flow, we’d also need to configure a source on the left side: a listener to trigger our import each time a new customer is added to NetSuite (as shown below).

FullFlow922.png

The full flow is available as a template.

Install prebuilt solutions:

Need more help?

Check out our Google Sheets community posts:

Read more about this connector type:

Related articles

Comments

6 comments

Date Votes
  • Pangaia Admin
    Engaged
    Celigo University Level 1: Skilled

    Hi - could you make the template available on the eu.integrator.io sub-domain please?

    0
  • Kate Larson Senior UX Researcher Community moderator

    Hi Pangaia Admin -
    I believe the template was published to the eu.integrator.io subdomain last week (as indicated in this discussion). Could you please try again to access it and let me know? Thanks!

    0
  • Bhushan Rane Integration Engineer

    Quite helpful. Thank you Kate Larson

    0
  • Kate Larson Senior UX Researcher Community moderator

    Thanks, Bhushan Rane!

    0
  • Zachary Oliver
    Engaged
    Awesome Follow-up

    Hello,

    Please note that as the base url is getting set by the Google Sheets connection, so the relative URL should like the below. Regards, Zach

    v4/spreadsheets/<spreadsheetId>/values/<range>:append?
    valueInputOption=RAW
    0
  • Kate Larson Senior UX Researcher Community moderator

    Thanks for pointing this out, Zachary Oliver. I updated the article.

    0

Please sign in to leave a comment.