Google DRIVE API - Append data to a sheet within a Google workbook

Andrew_239256
Andrew_239256 Posts: 16 admin
edited January 2022 in API Actions

The following guide utilizes the Google Sheets API to append data to a sheet within a Google workbook using a single Catalytic action.

While native actions in Catalytic allow builders to interact with Google Sheets in several ways, builders can further leverage the standard Google DRIVE integration using the existing GET, POST, PUT, PATCH, and DELETE API actions to access a broader set of endpoints within the Google Sheets API library.

With Catalytic managing API authentication, this functionality allows builders a quick and simple way of extending Catalytic’s Google DRIVE integration to accomplish custom Google Sheets actions within a workflow.

To get started with appending data to a Google sheet within a workbook using a custom API endpoint, please follow the instructions below:

Step 1: Set up Google DRIVE integration

Note: If a Google DRIVE integration has already been set up, move to step 2.

For detailed instructions on how to set up a Google DRIVE integration, reference the following help article within the Catalytic knowledgebase:
https://help.catalytic.com/docs/google-drive-integration-setup-guide/

Step 2: Obtain spreadsheetId from the Google Workbook.

Navigate to desired sheet within applicable Google workbook.

The spreadsheetId can be found within the Workbook URL as illustrated in the screenshot below.

spreadsheetId is the workbook.

General Format:
https://sheets.googleapis.com/v4/spreadsheets/spreadsheetId/values/Sheet1!A1:E1:append?valueInputOption=USER_ENTERED

Step 3: Configure Instance Fields

Create one new Instance Field for reference within the workflow.
Field one:
Instance field one: spreadsheetId
Type: Short Text
Default value:

Step 4: Configure Catalytic action to clear a Google Sheet

In a new or existing workflow, add a new action of type ‘Web API: Send POST Request’. Configure as follows:

Integration:
API endpoint:
https://sheets.googleapis.com/v4/spreadsheets/{{spreadsheetid}}/values/Sheet1!A1:F1:append?valueInputOption=USER_ENTERED

Headers: Leave blank

Request body:

{
  "range": "Sheet1!A1:F1",
  "majorDimension": "ROWS",
  "values": [
    ["Data", 123.45, 100, true, "=MAX(B1:C1)", "10"]
  ]
}

*Note: The code above will write values to range A1:F1 in Sheet1 of the designated workbook. The valueInputOption parameter in the endpoint is required and determines how the values will be parsed.

Use Catalytic to dynamically create the values contained within the request body. Values must follow Google's standard formatting outlined within their API documents.

Examples of additional request bodies:

Example A:

API endpoint:
https://sheets.googleapis.com/v4/spreadsheets/{{spreadsheetid}}/values/Sheet1!A1:D1:append?valueInputOption=USER_ENTERED

Request body:

{
  "range": "Sheet1!A1:D1",
  "majorDimension": "ROWS",
  "values": [
    ["Door", "$15", "2", "3/15/2016"],
    ["Engine", "$100", "1", "3/20/2016"],
  ],
}

Example B:

API endpoint:
https://sheets.googleapis.com/v4/spreadsheets/{{spreadsheetid}}/values/Sheet1!A1:E1:append?valueInputOption=USER_ENTERED

Request body:

{
  "range": "Sheet1!A1:E1",
  "majorDimension": "ROWS",
  "values": [
    ["Row1", 1, 100, true, "10"],
    ["Row2", 2, 100, true, "10"],
    ["Row3", 3, 100, true, "10"],
    ["Row4", 4, 100, true, "10"],
    ["Row5", 5, 100, true, "10"],
    ["Row6", 6, 100, true, "10"],
    ["Row7", 7, 100, true, "10"]
  ]
}

Full customization of the request body is beyond the scope of this guide.

Step 5: Test your workflow

By clicking on the test button, the designated Google Sheet should be successfully updated.

For additional information on the Google API library, please reference the Sheets API documentation on Google's developer website.
https://developers.google.com/sheets/api/samples/writing

Please reference the sample workflow attached for testing purposes. To customize for your building needs, update the spreadsheet ID and sheet name.