Google DRIVE API - Clear a sheet within a Google workbook

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

The following guide utilizes the Google Sheets API to clear 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 clearing 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 and sheetId from Google Workbook.

Navigate to desired sheet within applicable Google workbook.

Both spreadsheetId and sheetId can be found within the Workbook URL as illustrated in the screenshot below.
spreadsheetId is the workbook.
sheetId is the sheet.

General Format:
https://docs.google.com/spreadsheets/d/spreadsheetId/edit#gid=sheetId

Step 3: Configure Instance Fields

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

Field two:
Instance field one: sheetId
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}}:batchUpdate

Headers: Leave blank

Request body:

{
  "requests": [
    {
      "deleteRange": {
        "range": {
          "sheetId": {{sheetid}},
          "startRowIndex": 1,
          "startColumnIndex": 0,
        },
        "shiftDimension": "ROWS"
      }
    }
  ]
}

*Note: Using a startRowIndex value of 1 will skip the header row in row 1. The script above will clear all rows and columns beginning on row 2.

Additional parameters can be adjusted, but are beyond the scope of this guide.

Step 5: Test your workflow

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

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/sheet

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

Tagged:

Categories