Google Sheets Task¶

Overview¶

The Google Sheets Task integrates with Google Sheets to read and write data. Use it to log leads, generate reports, sync data bidirectionally, track inventory, or collect form responses in spreadsheets.

When to use this task:

• Log leads to spreadsheet for sales team
• Export CRM data to shared reporting sheets
• Sync data between BaseCloud and Google Sheets
• Update inventory tracking sheets
• Read configuration data from sheets
• Generate automated reports
• Collect and process form responses

Key Features:

• OAuth2 authentication
• Read rows, ranges, or specific cells
• Append new rows
• Update existing rows
• Create new sheets
• Format cells and apply formulas
• Multiple operations per task
• Support for multiple Google accounts

Outputs¶

Which fields appear depends on the action — creating a spreadsheet populates different fields from reading rows.

Reference fields as {{task_<ID>_<field>}}.

Always emitted¶

Quick Start¶

Builder fields¶

The Field column is the label as it appears in the task builder; Key is the name the value is stored under and referenced by.

1. Add a Google Sheets task and pick the Google Sheets connection
2. Choose the action
3. Paste the Spreadsheet ID (from the sheet's URL) and the tab's Sheet Name
4. Fill in what the action needs, and run it to test

Simple Example - Append Row:

Action: Append Row
Spreadsheet ID: 1AbC…xyz
Sheet Name: Leads
Row Values: {{task_47001_full_name}}, {{task_47001_email}}, {{task_47001_phone}}

Connecting Google Account¶

First-Time Setup¶

1. Go to Settings → App Connections and add a Google Sheets connection
2. Sign in to Google and allow access
3. Pick the connection on the task

Required Permissions¶

BaseCloud requests:

• https://www.googleapis.com/auth/spreadsheets - Full sheets access
• https://www.googleapis.com/auth/drive.metadata.readonly - Find sheets

Privacy: BaseCloud only accesses sheets you explicitly configure in tasks. Credentials are encrypted.

Multiple Accounts¶

Use different Google accounts for different workflows:

• Select account when configuring task
• Each workflow can use different account
• Useful for client-specific sheets

Reading rows¶

Get Rows is the read action. The Spreadsheet ID is the long identifier in the sheet's URL, not its title; Sheet Name is the tab. A wide Range like A1:Z1000 is the usual starting point — it returns what exists rather than failing on empty cells.

With more than one row, the first row is treated as headers and each later row is also returned as an object in data — [{"Name": "John", "Email": "john@example.com"}, …] — while rows holds every row as it is, headers included. row_count counts the rows read.

To work through the rows one at a time, feed rows (or data) into a Loop.

Writing rows¶

Row values are comma-separated ({{task_47001_name}}, {{task_47001_email}}, Website) — wrap a value that has a comma in it in double quotes — or a JSON list (["a", "b, c", "d"]). Values are entered as if typed into Sheets, so =SUM(A2:A10) becomes a formula and 2026-10-01 a date.

• Append Row adds the values as a new row under the last one.
• Update Row writes the values into Row Number (counting from 1), starting at column A.
• Append or Update Row looks down Match Column for a cell exactly equal to Match Value: the first match is updated with Update Values, and with no match they are appended as a new row. action says which happened (updated or appended) and row_number where.

Managing spreadsheets and tabs¶

• Create Spreadsheet — a new spreadsheet named Spreadsheet Name, with a first tab named Sheet Name; returns its spreadsheet_id and spreadsheet_url.
• Delete Spreadsheet — deletes the spreadsheet with that ID.
• Create Sheet / Delete Sheet — adds or removes a tab.
• Clear Sheet — empties every cell on the tab, headers included.
• Delete Rows / Columns — removes rows or columns Start Index to End Index, both counted from 1 and both included.

Formatting, conditional formatting and charts are not set by this task — do them in Google Sheets.

Using it with AI Prompt¶

Attach a Google Sheets task to an AI Prompt task’s tools port. Its Google account, Spreadsheet ID and Sheet Name are what the AI can reach — it can never use another sheet. The task’s own action is not used; under What the AI may do you choose instead:

With both Add rows and Change rows ticked, the AI can also update a row or add it when it is not there yet, in one go.

Each change happens at most 5 times per reply. Creating or deleting spreadsheets and sheets, clearing a sheet and deleting rows are never available to the AI.

A prompt that uses it:

When someone asks to be kept up to date, find them in the Leads sheet by email. If they are not there, add a row: name, email, today’s date, “Chat”.

Real-World Examples¶

Log every new enquiry to a sheet¶

Form Submission
  └─ Google Sheets   append_row with the submitted fields

Work through a list someone maintains¶

Schedule
  └─ Google Sheets   get_rows
      └─ Loop        over {{task_44001.JSON.rows}}, action: array
          └─ Email   send to {{task_29001_loop_value_email}}

Keep a row in step with the CRM¶

CRM Trigger  (client updated)
  └─ Match to Client     look the client up
      └─ Google Sheets   append_or_update_row

Best Practices¶

Performance¶

1. Batch operations - Update multiple cells at once
2. Cache reads - Don't read same data repeatedly
3. Limit frequency - Google limits how often a sheet can be read and written
4. Use ranges - More efficient than cell-by-cell
5. Pagination - Process large sheets in chunks

Data Quality¶

1. Validate before write - Check required fields exist
2. Handle duplicates - Check before appending
3. Data types - Ensure numbers are numbers, dates are dates
4. Trim whitespace - Clean data before writing
5. Error logging - Track failed operations

Security¶

1. Limit permissions - Only grant necessary access
2. Separate accounts - Use different accounts for different clients
3. Audit logs - Track who changed what
4. Backup sheets - Keep copies of important data
5. Encrypt sensitive data - Don't store passwords/keys in sheets

Maintainability¶

1. Use named ranges - Easier than cell references
2. Document formulas - Comment complex calculations
3. Consistent formatting - Headers, dates, numbers
4. Version sheets - Date-stamp sheet names
5. Test with copy - Use test spreadsheet first

Troubleshooting¶

Authentication Failed¶

Error: "Could not connect to Google account"

Solutions:

1. Reconnect Google account in task settings
2. Check account has access to spreadsheet
3. Verify spreadsheet still exists
4. Re-authorize permissions

Spreadsheet Not Found¶

Error: "Spreadsheet ID not found"

Check:

1. Spreadsheet deleted or moved?
2. Account has access?
3. Spreadsheet ID correct?
4. Sharing settings allow access?

Solution: Re-select spreadsheet in task configuration.

Could Not Write Data¶

Error: "Failed to append row"

Causes:

• Sheet protected
• Invalid data format
• Cell limit reached
• API quota exceeded

Solutions:

1. Check sheet protection settings
2. Verify data format (arrays for ranges)
3. Check sheet size (max 5M cells)
4. Add delay between requests

Formula Not Calculating¶

Issue: Formula appears as text

Solution: Ensure formula starts with =:

Correct: =SUM(A2:A10)
Wrong: SUM(A2:A10)

Date Format Issues¶

Issue: Dates appear as numbers (44928)

Solution: Format column as date in Google Sheets, or use date formula:

=TEXT(A2,"MM/DD/YYYY")

API Quota Exceeded¶

Error: "Rate limit exceeded"

Solution:

1. BaseCloud retries a rate-limited call by itself
2. In a Loop writing many rows, add a Delay between them
3. Write fewer, larger updates where you can

Frequently Asked Questions¶

Is there a limit on spreadsheet size?¶

Google Sheets supports:

• Up to 5 million cells per spreadsheet
• 256 columns per sheet (A-IV)
• 40,000 new rows added per API request

Can I work with formulas?¶

Yes, you can:

• Write formulas to cells (they calculate in Google Sheets)
• Read calculated results
• Cannot execute formulas in BaseCloud

How do I handle multiple sheets in one spreadsheet?¶

Specify sheet name in task configuration:

Spreadsheet: Sales Data
Sheet Name: January

Can I format cells programmatically?¶

No — this task reads and writes values. Format the sheet in Google Sheets.

What happens if workflow fails mid-update?¶

Each task is one change in Google Sheets: what earlier tasks wrote stays, and nothing is rolled back.

Can I read from one sheet and write to another?¶

Yes, use two Google Sheets tasks:

1. Read from Sheet A
2. Process data
3. Write to Sheet B

How do I prevent duplicate rows?¶

Use Append or Update Row with the email as Match Value and its column as Match Column: an existing row is updated instead of a second one being added.

Can I use Google Sheets as a database?¶

For small datasets (<1000 rows), yes. For larger data or complex queries, use MySQL Query task instead.

How often can I sync data?¶

Google limits how many requests a user's connection can make per minute; a rate-limited call is retried by BaseCloud by itself. For a continuous sync, every few minutes is plenty.

Related Tasks¶

• MySQL Query - Alternative for large datasets
• Loop - Process multiple sheet rows
• Code - Format data for sheets
• If Statement - Conditional sheet operations
• Webhook Out - Sync to external systems