Documentation: Google Sheets Action

Overview

The Google Sheets Action is an automation node that lets you interact with Google Sheets spreadsheets directly from your automations. You can read data, write data, create sheets, clear ranges, and perform various operations on your spreadsheets automatically.

When to Use This Action

Use this action when you need to: - Log data automatically to spreadsheets (logs, events, metrics) - Read data from spreadsheets for use in other actions - Update existing records based on conditions - Create automated reports in spreadsheet format - Sync data between systems and Google Sheets - Manage sheets (create, delete, clear) programmatically

Prerequisites

Before using this action, you need:

1. A Google account with access to Google Sheets
2. A Service Account credentials file from Google Cloud Platform
3. A Google Sheets spreadsheet created and shared with the service account
4. The spreadsheet ID (taken from the URL)

How to Get the Spreadsheet ID

The spreadsheet ID is in the Google Sheets URL:

https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit
                                                    ↑
                                            This is the ID

How to Create a Service Account and Get Credentials

1. Go to Google Cloud Console
2. Create a new project or select an existing one
3. Enable Google Sheets API for your project
4. Go to IAM & Admin → Service Accounts
5. Create a new Service Account or use an existing one
6. Click the Service Account → Keys → Add Key → Create new key
7. Choose JSON as the format
8. Download the JSON file (this is the credentials file you need)
9. Share your spreadsheet with the Service Account email (shown as client_email in the JSON)

Node Configuration

Node configuration is split into three main sections that you can switch between using the selector at the top:

Section 1: Basic Configuration

Step 1: Configure Spreadsheet ID

1. In the "Spreadsheet ID" field, enter your Google Sheets spreadsheet ID
2. Example: 1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms

Note: Ensure the Service Account has read/write access to this spreadsheet.

Step 2: Configure Sheet Name

1. In the "Sheet Name" field, enter the exact name of the sheet within the spreadsheet
2. Example: Sheet1, Data, Records, etc.

Important: The name must match the sheet name exactly, including case.

Step 3: Select Action Type

1. In the "Action Type" field, select the operation you want to perform:

Available actions:

• Add or Update Row (append_or_update): Adds a new row or updates an existing one if a row with the same value in the upsert column already exists
• Add New Row (append): Appends a new row at the end of the sheet
• Update Row (update_row): Updates a specific row by its index
• Get Rows (get_rows): Fetches rows from a specific range
• Clear Range (clear): Clears the content of a cell range
• Create New Sheet (create): Creates a new sheet within the spreadsheet
• Delete Sheet (delete): Deletes a sheet from the spreadsheet
• Delete Rows/Columns (delete_rows_cols): Deletes specific rows or columns

Step 4: Configure Row Data (For write actions)

If you selected Add New Row, Add or Update Row, or Update Row:

1. In the "Row Data" field, enter the values for each column
2. Each field represents a column in order (left to right)
3. Click "Add Field" to add more columns
4. Click the delete icon (🗑️) to remove a column

Example: - Field 1: John Doe - Field 2: john@example.com - Field 3: 2024-01-15 - Field 4: Active

This creates a row with these values in columns A, B, C, and D respectively.

Note: You can use automation context variables with the {{variable.name}} syntax.

Section 2: Operation Configuration

This section shows additional fields depending on the selected action type.

For "Add or Update Row" and "Update Row"

Upsert Column: - The column (letter, e.g. A, B, C) used to identify whether a row already exists - For "Add or Update Row": If a row with the same value in this column exists, it is updated; otherwise a new row is added - For "Update Row": This column is used together with the row index

Row Index: - The row number to update (1 = first data row, excluding headers) - Example: 1 updates the first data row, 2 the second, etc.

For "Get Rows"

Start Row: - First row to read (1 = first data row)

End Row: - Last row to read

Start Column: - Initial column letter (e.g. A, B, C)

End Column: - Final column letter (e.g. D, E, F)

Example: - Start Row: 1 - End Row: 10 - Start Column: A - End Column: D

This reads rows 1 to 10 and columns A to D.

For "Delete Rows/Columns"

Delete Rows (JSON array): - Enter a JSON array with the row numbers to delete - Example: [5, 6, 7] deletes rows 5, 6, and 7

Delete Columns (JSON array): - Enter a JSON array with the column letters to delete - Example: ["B", "C"] deletes columns B and C

Note: Row numbers and column letters must be in valid JSON format.

For "Clear Range"

Clear Range: - The cell range to clear, in A1 notation - Examples: - A1:D10 — Clears from A1 to D10 - B2:B100 — Clears column B from row 2 to 100 - A1:Z1 — Clears the entire first row

For "Create New Sheet"

New Sheet Name: - Name for the new sheet - Example: January Report, Imported Data, etc.

Sheet Properties (JSON): - Advanced sheet properties in JSON format (optional) - Example:

{
  "title": "User Data",
  "gridProperties": {
    "rowCount": 1000,
    "columnCount": 26
  }
}

Section 3: Credentials

Step 5: Upload Service Account Credentials

1. Click the "Upload Service Account JSON File" button
2. Select the JSON credentials file you downloaded from Google Cloud Console
3. The system will validate the file and show a success message if valid
4. You will see confirmation info including Project ID and Client Email

Important: - The file must be valid JSON with all required fields - Do not share this file publicly; it contains sensitive information - To remove credentials, click "Clear credentials"

Required JSON fields: - type - project_id - private_key_id - private_key - client_email - client_id - auth_uri - token_uri - auth_provider_x509_cert_url - client_x509_cert_url - universe_domain

Usage Examples

Example 1: Log Event to Spreadsheet

Basic configuration: - Spreadsheet ID: 1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms - Sheet Name: Events - Action Type: Add New Row

Row Data: - Field 1: {{context.event_name}} - Field 2: {{context.timestamp}} - Field 3: {{context.object_id}} - Field 4: {{context.status}}

Use case: Each time an event fires, it is automatically logged to the spreadsheet with the event data.

Example 2: Update or Add User Record

Basic configuration: - Spreadsheet ID: 1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms - Sheet Name: Users - Action Type: Add or Update Row

Row Data: - Field 1: {{context.user_email}} - Field 2: {{context.user_name}} - Field 3: {{context.last_login}} - Field 4: {{context.status}}

Operation configuration: - Upsert Column: A (email column)

Use case: If the email already exists in column A, the row is updated; otherwise a new row is added. Useful for keeping an up-to-date user log.

Example 3: Read Data from a Sheet

Basic configuration: - Spreadsheet ID: 1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms - Sheet Name: Configuration - Action Type: Get Rows

Operation configuration: - Start Row: 1 - End Row: 50 - Start Column: A - End Column: C

Use case: Reads the first 50 rows and columns A to C. The data is available in the context for use in downstream nodes.

Example 4: Clear Range Before Writing New Data

Basic configuration: - Spreadsheet ID: 1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms - Sheet Name: Daily Report - Action Type: Clear Range

Operation configuration: - Clear Range: A2:Z1000

Use case: Clears the specified range before writing new data. Useful for reports that are regenerated daily.

Example 5: Create New Sheet for Monthly Report

Basic configuration: - Spreadsheet ID: 1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms - Sheet Name: Sheet1 (any name; a new sheet will be created) - Action Type: Create New Sheet

Operation configuration: - New Sheet Name: January 2024 Report - Sheet Properties (JSON):

{
  "title": "January 2024 Report",
  "gridProperties": {
    "rowCount": 5000,
    "columnCount": 26
  }
}

Use case: Automatically creates a new sheet each month to store reports.

Example 6: Delete Old Rows

Basic configuration: - Spreadsheet ID: 1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms - Sheet Name: Logs - Action Type: Delete Rows/Columns

Operation configuration: - Delete Rows (JSON array): [1, 2, 3, 4, 5]

Use case: Deletes the first 5 data rows. Useful for keeping only the most recent records.

Automation Workflow

Typical Structure

1. Trigger (e.g., Object State Change Trigger) ↓
2. Google Sheets Action (reads or writes data) ↓
3. Other Actions (use the read data or confirm the write)

Accessing Results

When you use the Get Rows action, the read data is available in the automation context and can be used by downstream nodes. Data typically comes as a two-dimensional array.

Example data structure:

{
  "rows": [
    ["Value1", "Value2", "Value3"],
    ["Value4", "Value5", "Value6"]
  ]
}

JSON Editor (Advanced)

If you are comfortable with technical configuration, you can edit the setup directly in JSON using the "JSON Editor" tab. The structure is:

{
  "spreadsheet_id": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
  "sheet_name": "Sheet1",
  "action": "append",
  "row_data": ["Value1", "Value2", "Value3"],
  "data": [],
  "config": {
    "upsert_column": "A",
    "row_index": 1
  },
  "credentials": {
    "type": "service_account",
    "project_id": "your-project",
    "private_key_id": "...",
    "private_key": "...",
    "client_email": "...",
    "client_id": "...",
    "auth_uri": "https://accounts.google.com/o/oauth2/auth",
    "token_uri": "https://oauth2.googleapis.com/token",
    "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
    "client_x509_cert_url": "...",
    "universe_domain": "googleapis.com"
  }
}

Troubleshooting

Authentication Error

Problem: "Permission denied" or "The caller does not have permission"

Solutions: 1. Ensure you have shared the spreadsheet with the Service Account email (the client_email in the JSON file) 2. Make sure the Service Account has "Editor" permissions on the spreadsheet 3. Verify the credentials JSON file is valid and complete 4. Confirm Google Sheets API is enabled for your Google Cloud project

Error: "Sheet not found"

Problem: The specified sheet cannot be found

Solutions: 1. Verify the sheet name matches exactly (including case and spaces) 2. Ensure the sheet exists in the spreadsheet 3. If the sheet has special characters, check they are spelled correctly

Error: "Invalid spreadsheet ID"

Problem: The spreadsheet ID is not valid

Solutions: 1. Verify you copied the full ID from the URL 2. Ensure there are no leading or trailing spaces 3. Confirm the spreadsheet exists and is accessible

Data Not Written Correctly

Problem: Data appears in wrong columns or values are missing

Solutions: 1. Verify the order of fields in "Row Data" matches the column order 2. Ensure you have added enough fields for all columns you want to fill 3. If using variables, check they exist and have values in the context

Error Reading Data (Get Rows)

Problem: Cannot read data or range is invalid

Solutions: 1. Verify the specified rows and columns exist in the sheet 2. Ensure Start Row ≤ End Row and Start Column ≤ End Column (alphabetically) 3. Check that the column format is correct (letters: A, B, C, etc.)

Error Deleting Rows/Columns

Problem: Error processing the JSON array of rows/columns to delete

Solutions: 1. Verify the JSON format is valid: [1, 2, 3] for rows, ["A", "B"] for columns 2. Ensure row numbers are valid (≥ 1) 3. Verify column letters are valid (A–Z)

Best Practices

Security

1. Never share your credentials file publicly or in code repositories
2. Use dedicated Service Accounts for automations instead of personal accounts
3. Limit permissions so the Service Account can access only the spreadsheets it needs
4. Rotate credentials periodically when possible
5. Do not include credentials in code or documentation

Data Organization

1. Use the first row as headers to make reading and maintenance easier
2. Keep data formats consistent (dates, numbers, etc.)
3. Use separate sheets for different data types or time periods
4. Consider "Add or Update Row" with a unique column (e.g. ID or email) to avoid duplicates

Performance

1. Avoid reading very large ranges when unnecessary (use filters or limits)
2. Batch operations when possible instead of many small calls
3. Use "Clear Range" before writing large amounts of new data
4. Be aware of Google Sheets API write limits (currently 300 requests per minute per project)

Maintenance

1. Document the structure of your spreadsheets (which columns exist and what they mean)
2. Use descriptive names for sheets and columns
3. Version your automations so you can revert changes if needed
4. Monitor errors and adjust configuration as needed

Frequently Asked Questions

Q: Can I use multiple spreadsheets in a single automation?
A: Yes. You can use multiple Google Sheets nodes, each pointing to different spreadsheets.

Q: What if the sheet does not exist?
A: The action will fail with an error. Ensure the sheet exists before running the automation, or use the "Create New Sheet" action first.

Q: Can I use Google Sheets formulas in the data I write?
A: Yes. You can write formulas as text values. For example, you can use =SUM(A1:A10) as a cell value.

Q: Are there limits on how much data I can read or write?
A: Google Sheets API has quota limits. See Google’s official documentation for current limits. Typically you can read/write up to 10 million cells per request.

Q: Can I read data from multiple sheets in a single action?
A: No. Each action reads from a single sheet. If you need data from multiple sheets, use multiple Google Sheets nodes.

Q: What format are dates in when I read them?
A: Dates are read as strings in whatever format they have in the spreadsheet. You can convert them to date format in downstream nodes if needed.

Q: Can I use this action to create charts automatically?
A: Not directly. This action handles data; charts must be created manually in Google Sheets or via more advanced use of the Google Sheets API.

Q: What happens if I write to a cell that has a formula?
A: The formula will be replaced by the value you write. Be careful when writing to ranges that contain formulas.

Q: Can I use context variables in sheet names or IDs?
A: Yes. You can use variables with the {{variable.name}} syntax in text fields, including Spreadsheet ID and Sheet Name.

---

Last updated: [Date]
Version: 1.0