Skip to content

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

[SCREENSHOT NEEDED: Google Sheets OAuth connection screen]

Quick Start

  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

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.

Real-World Examples

Example 1: Lead Logging System

Workflow: 1. Website Form - Website form 2. Match to Client - Find existing contact 3. Google Sheets - Log to spreadsheet

Google Sheets Configuration: 

Operation: Append
Spreadsheet: Lead Tracking 2024
Sheet: Incoming Leads
Data:
  Column A: {{task_48001_current_datetime}}
  Column B: {{task_49001_name}}
  Column C: {{task_49001_email}}
  Column D: {{task_49001_phone}}
  Column E: {{task_49001_message}}
  Column F: {{task_49001_form_source}}
  Column G: {{task_15001_contact_id}}
  Column H: =HYPERLINK("https://app.basecloud.com/contacts/" & G2, "View")

Result: Real-time lead log with clickable links to CRM.

Example 2: Daily Sales Report

Workflow: 1. Timer Trigger - Daily at 8 AM 2. MySQL Query - Get yesterday's sales 3. Code Task - Format data 4. Google Sheets - Write report

MySQL Query: 

SELECT 
  salesperson,
  COUNT(*) as deals_closed,
  SUM(deal_value) as total_revenue,
  AVG(deal_value) as avg_deal_size
FROM deals
WHERE DATE(closed_date) = CURDATE() - INTERVAL 1 DAY
  AND status = 'Won'
GROUP BY salesperson
ORDER BY total_revenue DESC

Code Task: 

const results = JSON.parse(input.task_43001_results_JSON);
const today = new Date().toLocaleDateString();

// Format for sheets
const rows = results.map(r => [
  r.salesperson,
  r.deals_closed,
  `$${r.total_revenue.toLocaleString()}`,
  `$${r.avg_deal_size.toLocaleString()}`
]);

// Add header with date
rows.unshift(['Daily Sales Report - ' + today]);
rows.unshift(['Salesperson', 'Deals', 'Revenue', 'Avg Deal Size']);

return {
  formatted_rows: JSON.stringify(rows)
};

Google Sheets: 

Operation: Update Range
Spreadsheet: Sales Dashboard
Sheet: Daily Reports
Range: A1:D20
Data: {{task_42001_formatted_rows}}

Format: 

Apply Formatting:
  Row 1: Bold, Size 14, Center
  Row 2: Bold, Background #4285F4, White text
  Column C-D: Currency format

Example 3: Bidirectional Sync

Workflow A - CRM to Sheets: 1. CRM Trigger - Contact updated 2. Google Sheets - Find row by contact ID 3. Google Sheets - Update row

Find row: 

Operation: Find
Spreadsheet: Contact Database
Sheet: Contacts
Search Column: A (Contact ID)
Search Value: {{task_47001_contact_id}}

Update row: 

Operation: Update
Row Number: {{task_44001_found_row}}
Data:
  Column B: {{task_47001_full_name}}
  Column C: {{task_47001_email}}
  Column D: {{task_47001_phone}}
  Column E: {{task_47001_custom_status}}
  Column F: {{task_48001_current_datetime}}

Workflow B - Sheets to CRM: 1. Timer Trigger - Every 15 minutes 2. Google Sheets - Read changes since last sync 3. Loop - Process each change 4. Match to Client - Find contact 5. Edit Client - Update CRM

Example 4: Inventory Tracking

Workflow: 1. Webhook In - Order placed 2. Loop - For each item 3. Google Sheets - Read current inventory 4. Code Task - Calculate new quantity 5. Google Sheets - Update inventory 6. If Task - Check if low stock 7. Email - Alert if low

Read inventory: 

Operation: Find
Sheet: Inventory
Search Column: A (SKU)
Search Value: {{task_29001_product_sku}}

Calculate new quantity: 

const current = parseInt(input.task_44001_cell_B_value) || 0;
const ordered = parseInt(input.task_29001_quantity) || 0;
const newQty = current - ordered;
const lowStock = newQty < 10;

return {
  new_quantity: newQty,
  is_low_stock: lowStock
};

Update inventory: 

Operation: Update Cell
Cell: B{{task_44001_found_row}}
Value: {{task_42001_new_quantity}}

Low stock alert: 

If {{task_42001_is_low_stock}} = true:
  Email: warehouse@company.com
  Subject: Low Stock Alert - {{task_29001_product_name}}
  Body: Only {{task_42001_new_quantity}} units remaining

Example 5: Form Response Collection

Workflow: 1. Website Form - Event registration 2. Google Sheets - Append to responses 3. Code Task - Calculate totals 4. Google Sheets - Update summary

Append response: 

Operation: Append
Spreadsheet: Event Registrations 2024
Sheet: Responses
Data:
  Timestamp: {{task_48001_current_datetime}}
  Name: {{task_49001_full_name}}
  Email: {{task_49001_email}}
  Ticket Type: {{task_49001_ticket_type}}
  Quantity: {{task_49001_quantity}}
  Total: ={{task_49001_quantity}}*IF(D2="VIP",100,50)

Read totals: 

Operation: Read
Range: F2:F1000 (Total column)

Calculate summary: 

const totals = JSON.parse(input.task_44001_range_JSON);
const revenue = totals.reduce((sum, val) => sum + parseFloat(val || 0), 0);
const registrations = totals.length;

return {
  total_revenue: revenue,
  total_registrations: registrations
};

Update summary sheet: 

Operation: Update Range
Sheet: Summary
Range: B2:B3
Data: [
  [{{task_42001_total_registrations}}],
  ["${{task_42001_total_revenue}}"]
]

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

  • 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