Google Sheets

1. Overview

Google Sheets is a cloud-based spreadsheet application that allows users to create, edit, and share spreadsheets online. It offers real-time collaboration capabilities and seamless integration with other Google Workspace applications.

Through GoInsight's Google Sheets node, you can seamlessly integrate spreadsheet operations into your automation workflows. You can achieve comprehensive lifecycle management of spreadsheets, sheets, and cell data, including:

• Creating and deleting entire spreadsheet files and individual sheets.
• Reading, appending, updating, and clearing data within sheets.
• Deleting specific rows or columns from your spreadsheets.
• Managing sheet structure by creating new sheets or removing existing ones.

2. Prerequisites

Before using this node, you need to have a valid Google account with access to Google Sheets. You may need administrator or specific permissions to create and manage API credentials for programmatic access to your Google Sheets data.

3. Credentials

For detailed guidance on how to obtain and configure credentials, please refer to our official documentation: Credentials Configuration Guide.

4. Supported Operations

This node primarily operates around three main resources: Spreadsheets (entire files), Sheets (individual tabs within a spreadsheet), and Rows/Columns (data within sheets).

Summary

Operation Details

Create SpreadSheets

Creates a new Google Spreadsheet with the given title and optional sheet data provided as a JSON or CSV formatted string.

Input Parameters:

• Title: The title for the spreadsheet.

Options:

• SheetData: Sheet data as a JSON or CSV formatted string.

Output:

• SpreadsheetId (string): The ID of the created spreadsheet.
• SpreadsheetUrl (string): The URL of the created spreadsheet.
• StatusCode (number): HTTP status code of the API response, -1 for parameter errors, 500 for exceptions.
• ErrorMessage (string): Error message if an error occurs; empty string if successful.

Delete SpreadSheet

Deletes the specified Google Sheets spreadsheet.

Input Parameters:

• SpreadsheetId: the Id of the spreadsheet

Output:

• Deleted (bool): Whether the deleted operation was successful, true for success, false for failure
• StatusCode (number): HTTP status code of the API response, -1 for parameter errors, 500 for exceptions.
• ErrorMessage (string): Error message if an error occurs; empty string if successful.

Create Sheet

Create a new sheet

Input Parameters:

• SpreadsheetId: the ID for the Spreadsheet
• SheetTitle: the title for the sheet

Output:

• SheetCreated (bool): Whether the created operation was successful, true for success, false for failure
• SheetId (string): The ID of the created sheet
• StatusCode (number): HTTP status code of the API response, -1 for parameter errors, 500 for exceptions.
• ErrorMessage (string): Error message if an error occurs; empty string if successful.

Delete Sheet

Delete a sheet

Input Parameters:

• SpreadsheetId: the ID of the spreadsheetId
• SheetName: the name of the sheet

Output:

• SheetDeleted (bool): Whether the deleted operation was successful, true for success, false for failure
• StatusCode (number): HTTP status code of the API response, -1 for parameter errors, 500 for exceptions.
• ErrorMessage (string): Error message if an error occurs; empty string if successful.

Clear Sheet

Clears all data in the specified range of a Google Sheets spreadsheet.

Input Parameters:

• SpreadsheetId: Google Sheets spreadsheet ID
• RangeName: To clear the data in the cell range (for example, "Sheet1!A1:Z1000")

Output:

• Cleared (bool): Indicates whether the data was successfully cleared.
• ClearedRange (string): To clear the data in the cell range (for example, "Sheet1!A1:Z1000")
• StatusCode (number): HTTP status code of the API response, -1 for parameter errors, 500 for exceptions.
• ErrorMessage (string): Error message if an error occurs; empty string if successful.

Get Rows in Sheet

Read all rows in a sheet

Input Parameters:

• SpreadsheetId: the Id of the spreadsheet
• SheetName: the name of the sheet
• RowRange: Specify the line number or line range, for example, '3' (indicating line 3) or '2:5' (indicating lines 2 to 5). If it is empty, read all rows.

Output:

• Rows (string): Acquired contents
• StatusCode (number): HTTP status code of the API response, -1 for parameter errors, 500 for exceptions.
• ErrorMessage (string): Error message if an error occurs; empty string if successful.

Append Row in Sheet

Create a new row

Input Parameters:

• SpreadsheetId: the ID of the spreadsheet
• RangeName: The range of data append is usually in the format of "Sheet1!A:Z"
• RowValues: The row of data to be added is organized in a list form. Example: ["value1", "value2", "value3"]

Output:

• Appended (bool): Whether the append operation was successful, true for success, false for failure
• Updates (object): Updated results
• StatusCode (number): HTTP status code of the API response, -1 for parameter errors, 500 for exceptions.
• ErrorMessage (string): Error message if an error occurs; empty string if successful.

Append or Update Row in Sheet

Append a new row, or update the current one if it already exists.

Input Parameters:

• SpreadsheetId: the ID of the created spreadsheet
• SheetName: the name of the sheet
• RowData: The data for appending or updating rows in sheet. For example: ["value1", "value2", "value3"]

Options:

• RowIndex: the row number to update. specify the row number for update operation (if empty or None, append operation is performed)

Output:

• Appended (bool): Whether the append operation was successful, true for success, false for failure
• Updated (bool): Whether the update operation was successful, true for success, false for failure
• UpdatedRange (string): The range returned by the update operation , returned if the update was successful, otherwise an empty string.
• StatusCode (number): HTTP status code of the API response, -1 for parameter errors, 500 for exceptions.
• ErrorMessage (string): Error message if an error occurs; empty string if successful.

Update Row in Sheet

Updates an existing row in a Google Spreadsheet with the provided data.

Input Parameters:

• SpreadsheetId: The unique identifier of Google Spreadsheet
• SheetName: The name of the sheet where the row will be updated.
• RowIndex: The row number to be updated (must be greater than 0).
• UpdateValues: The new values to update. If a list or dict, it will be converted to JSON format.

Output:

• Results (object): API response payload in JSON string format.
• StatusCode (number): HTTP response code (-1 for parameter errors, 500 for exceptions).
• ErrorMessage (string): Error description if the update operation failed.

Delete Rows or Columns

Deletes rows or columns in a specified Google Sheets document and sheet using provided indices.

Input Parameters:

• SpreadsheetId: The ID of the Google Sheets document.
• SheetId: The target sheet's ID.
• Dimension: The dimension to delete, either "ROWS" or "COLUMNS".
• StartIndex: The starting index (inclusive) for deletion.
• EndIndex: The ending index (exclusive) for deletion.

Output:

• Deleted (bool): Indicates whether the rows or columns were successfully deleted.
• StatusCode (number): HTTP status code (-1 for validation error, 500 for exceptions, 200/201 for success).
• ErrorMessage (string): Error message if deletion failed.

5. Example Usage

This section will guide you through creating a simple workflow to create a new Google Spreadsheet using the Google Sheets node.

Let's walk through the process of setting up a workflow that creates a new spreadsheet with some initial data.

Workflow Overview: This example workflow consists of Start -> Create SpreadSheets -> Answer.

Step-by-Step Guide:

1. Add Tool Node:
   • In the workflow canvas, click the "+" button to add a new node.
   • In the popup panel, select the "Tools" tab.
   • Find and select Google Sheets from the tool list.
   • In the list of supported operations for Google Sheets, click to select Create SpreadSheets, which will add a corresponding node to the canvas.
2. Configure Node:
   • Click on the newly added Create SpreadSheets node, and the configuration panel will expand on the right side.
   • Credentials Configuration: At the top of the panel, find the credentials field. Click the dropdown menu and select your pre-configured Google Sheets credentials.
   • Parameter Configuration: Fill in the input parameters in detail:
   • For the Title parameter: Enter a title for your new spreadsheet, such as "My Budget Tracker" or "Sales Report 2024". This will be the name displayed in your Google Drive.
   • For the SheetData parameter (optional): If you want to populate the spreadsheet with initial data, provide it as a JSON or CSV formatted string. For example, you could enter: [["Name", "Email", "Phone"], ["John Doe", "john@email.com", "555-1234"]] to create a simple contact list.
3. Run and Verify:
   • Once all required parameters are correctly filled, the error indicators in the upper right corner of the workflow canvas will disappear.
   • Click the "Test Run" button in the upper right corner of the canvas to execute the workflow.
   • After successful execution, you can click the log icon in the upper right corner to view the detailed input and output of the node, verifying that the operation was successful.

Final Workflow Summary: After completing the above steps, your entire workflow is configured. Click "Test Run" and a new spreadsheet will be successfully created in your Google Sheets with the specified title and any initial data you provided.

6. FAQs

Q: Why am I getting a 401/403 error when trying to access Google Sheets?

A: This typically indicates an authentication or authorization issue. Please check the following:

• Ensure your Google Sheets API credentials are correctly configured in GoInsight.
• Verify that the Google Cloud project has the Google Sheets API enabled.
• Check that the service account has the necessary permissions to access the target spreadsheet.
• Make sure the spreadsheet is shared with the service account email if using service account authentication.

Q: What format should I use for the SheetData parameter when creating spreadsheets?

A: The SheetData parameter accepts either JSON or CSV formatted strings. For JSON format, use a nested array structure like [["Header1", "Header2"], ["Value1", "Value2"]]. For CSV format, use comma-separated values with line breaks, such as "Header1,Header2\nValue1,Value2".

Q: How do I specify cell ranges in Google Sheets operations?

A: Use the standard A1 notation for cell ranges. Examples include:

• Sheet1!A1:C10 - refers to cells A1 through C10 on Sheet1
• Sheet1!A:A - refers to the entire column A on Sheet1
• Sheet1!1:1 - refers to the entire first row on Sheet1
• A1:C10 - refers to cells A1 through C10 on the default sheet

Q: What happens if I try to delete a sheet that doesn't exist?

A: The operation will fail and return an error message indicating that the sheet was not found. Always verify the sheet name exists before attempting to delete it. You can use the "Get Rows in Sheet" operation to verify sheet existence first.

7. Official Documentation

For more detailed information about the Google Sheets API capabilities and advanced usage, please refer to the Google Sheets Official API Documentation.