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 Google Sheets task
2. Connect Google account (OAuth)
3. Select spreadsheet
4. Choose operation (Read/Write/Update)
5. Configure data mapping
6. Test connection
7. Save

Simple Example - Append Row:

Spreadsheet: Lead Log
Sheet: Leads
Operation: Append
Data: {{task_47001_full_name}}, {{task_47001_email}}, {{task_47001_phone}}

Connecting Google Account¶

First-Time Setup¶

1. Click "Connect Google Account"
2. Sign in to Google
3. Grant permissions:
4. View and manage spreadsheets
5. See spreadsheet names and metadata
6. Authorization complete

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

Read Operations¶

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.

Read Entire Sheet¶

Configuration:

Operation: Read
Spreadsheet: Sales Data 2024
Sheet Name: January
Range: (leave empty for all data)

Output:

{{task_44001_rows_JSON}} - Array of all rows
{{task_44001_row_count}} - Number of rows
{{task_44001_first_row_column_A}} - First row, column A

Read Specific Range¶

Configuration:

Range: A2:D10

Reads cells A2 through D10.

Output:

{{task_44001_cell_A2}} - Cell A2 value
{{task_44001_cell_B3}} - Cell B3 value
{{task_44001_range_JSON}} - All cells as JSON

Read Single Column¶

Configuration:

Range: B:B

Reads entire column B.

Read with Headers¶

Configuration:

Has Headers: Yes
Header Row: 1

Output fields use header names:

{{task_44001_rows_JSON}} contains:
[
  {"Name": "John", "Email": "john@example.com", "Phone": "123-456-7890"},
  {"Name": "Jane", "Email": "jane@example.com", "Phone": "098-765-4321"}
]

Access individual fields:

{{task_44001_row_1_Name}} - "John"
{{task_44001_row_1_Email}} - "john@example.com"

Write Operations¶

Append New Row¶

Adds row at bottom of sheet.

Configuration:

Operation: Append
Spreadsheet: Lead Log
Sheet Name: Leads
Data:
  Column A: {{task_47001_full_name}}
  Column B: {{task_47001_email}}
  Column C: {{task_47001_phone}}
  Column D: {{task_48001_current_datetime}}

Alternative - Array format:

Row Data: [
  "{{task_47001_full_name}}",
  "{{task_47001_email}}",
  "{{task_47001_phone}}",
  "{{task_48001_current_datetime}}"
]

Update Existing Row¶

Modify specific row by row number.

Configuration:

Operation: Update
Row Number: 5
Data:
  Column A: {{task_16001_full_name}}
  Column C: "Contacted"
  Column D: {{task_48001_current_date}}

Update Specific Cell¶

Configuration:

Operation: Update Cell
Cell: B5
Value: {{task_43001_total_sales}}

Batch Update¶

Update multiple rows at once:

Configuration:

Operation: Batch Update
Range: A2:C5
Data: {{task_42001_formatted_data_JSON}}

Sheet Management¶

Create New Sheet¶

Configuration:

Operation: Create Sheet
Sheet Name: {{task_48001_current_month}}_Data

Clear Sheet¶

Configuration:

Operation: Clear
Range: A2:Z1000

Clears data while keeping headers (row 1).

Delete Sheet¶

Configuration:

Operation: Delete Sheet
Sheet Name: Old_Data

Formatting¶

Apply Formatting¶

Bold header row:

Operation: Format
Range: A1:Z1
Bold: Yes
Background: #4285F4
Text Color: #FFFFFF

Number Formatting¶

Currency:

Cell: C2:C1000
Format: Currency
Symbol: $
Decimals: 2

Date:

Cell: D2:D1000
Format: Date
Pattern: MM/DD/YYYY

Conditional Formatting¶

Highlight high values:

Range: E2:E1000
Condition: Greater than
Value: 1000
Background: #00FF00

Formulas¶

Add Formula to Cell¶

Sum column:

Cell: E51
Formula: =SUM(E2:E50)

Average:

Cell: F51
Formula: =AVERAGE(F2:F50)

Conditional:

Cell: G2
Formula: =IF(E2>1000,"High","Low")

Note: Formulas calculate in Google Sheets, not BaseCloud.

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 - Respect API quotas (100 requests/100 seconds/user)
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. Add Delay task between requests (1-2 seconds)
2. Batch operations instead of individual writes
3. Process in smaller batches
4. Schedule during off-peak hours

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?¶

Yes, use Format operation:

• Bold, italic, font size
• Background/text color
• Number/date/currency format
• Borders and alignment

What happens if workflow fails mid-update?¶

Google Sheets operations are atomic:

• Completed operations persist
• Failed operations don't execute
• No automatic rollback
• Best practice: Log operations for manual cleanup

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 Find operation before Append:

1. Google Sheets - Find email in column B
2. If Task - Check {{task_44001_found}} = false
3. If not found: Google Sheets - Append row

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?¶

Respect API quotas:

• 100 read requests per 100 seconds per user
• 100 write requests per 100 seconds per user
• Recommended: Every 5-15 minutes for continuous sync

Related Tasks¶

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