Import data into Google Sheets – Celigo Help Center

Before you begin, verify that you have the following information needed to build the Google Sheets URI:

Spreadsheet ID: If you open the Google sheet in a browser, the spreadsheet ID is displayed in the URL.
https://docs.google.com/spreadsheets/d/{spreadsheet ID}/edit#gid=12345678
The range you want to edit: A1:Z100
The method or action you want to use to modify the spreadsheet.

The URI you build is relative to https://sheets.googleapis.com/ and will use the following format: https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}/values/{range}:{method}

Methods

Set the Google Sheets URI according to the action you want to perform.

append: Appends values to a spreadsheet.
batchClear: Clears one or more ranges of values from a spreadsheet.
batchClearByDataFilter: Clears one or more ranges of values from a spreadsheet.
batchGet: Returns one or more ranges of values from a spreadsheet.
batchGetDataByFilter: Returns one or more ranges of values that match the specified data filters.
batchUpdate: Sets values in one or more ranges of a spreadsheet.
batchUpdateByDataFilter: Sets values in one or more ranges of a spreadsheet.
clear: Clears values from a spreadsheet.
get: Returns a range of values from a spreadsheet.
update: Sets values in a range of a spreadsheet.

For example:
https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}/values/{range}:{method}
https://sheets.googleapis.com/v4/spreadsheets/12345678910/values/A1:Z100:append

Clear and update a Google Sheet

In this scenario, you'll be retrieving data in a Google folder from an FTP site, then you'll configure an import step to clear all data from your spreadsheet. Finally, you'll update the Google Sheet.

Export data from an FTP spreadsheet

Export your files from an FTP spreadsheet

Clear all data from your Google Sheet

In this step, you’re clearing your data from the Google Sheet you want to update with new data.

Choose the POST method.
Set your Relative URI to point to your desired sheet. Use the following format:
v4/spreadsheets/{spreadsheetId}/values/{range}:{method}
For example:
v4/spreadsheets/abcd123hijk456/values/A1:G300:clear

If you have multiple sheets in your range should include your sheet name:
v4/spreadsheets/abcd123hijk456/values/Sheet1!A1:Z9000:clear

Populate your Google Sheet with new data

In this import step, you'll populate your data from Google Sheets.

Choose the POST method.
In the Relative URI, specify the sheet, range, and valueInputOption. For example:
v4/spreadsheets/abcd123hijk456/values/Sheet2!A2:Z9000:append?valueInputOption=RAW&insertDataOption=INSERT_ROWS

When populating the data, you must build the HTTP request body to populate each line of data where each of the values in the expression match the headers in your spreadsheet:

      {

  "values": [

    ["companyid", "CompanyName", "ContactName", "Address", "City", "PostalCode", "Country"],

    {{#each data}}

    ["{{companyid}}", "{{CompanyName}}", "{{ContactName}}", "{{Address}}", "{{City}}", "{{PostalCode}}", "{{Country}}"]

{{#unless @last}},{{/unless}}{{/each}}

  ]

}

Sort and group records

A Sorting and Grouping dropdown allows you to manage your flow’s data by sorting and grouping records by field.

Google Sheets – NetSuite

There is a Google Sheets – NetSuite template that lets you update your NetSuite customers and purchase orders into Google Sheets. You can also manually create a flow between Google Sheets and NetSuite.