Google Sheets Node
Overview
The Google Sheets Node is a data synchronization component that automates data extraction and synchronization from Google Spreadsheets. This node supports reading from multiple sheets, handling various data formats including text, numbers, dates, formulas, and cell formatting, enabling regular data synchronization to support data processing and analysis flows.
Node Type Information
Type | Description | Status |
---|---|---|
Batch Trigger | Starts the flow on a schedule or batch event. Ideal for periodic data processing. | ✅ True |
Event Trigger | Starts the flow based on external events (e.g., webhook, user interaction). | ❌ False |
Action | Executes a task or logic as part of the flow (e.g., API call, transformation). | ❌ False |
This node is a Batch Trigger node that automates data extraction and synchronization from Google Spreadsheets on a scheduled basis.
This node is a Batch Trigger node that streamlines data collection from Google Sheets and maintains data consistency through scheduled synchronization for automated data processing flows.
Features
Key Functionalities
- Batch Trigger: Automates data extraction and synchronization on a schedule using Cron expressions for regular execution.
- Data Format Support: Handles various data types including text, numbers, dates, formulas, and cell formatting.
- Multi-Sheet Support: Ability to read from multiple sheets within a workbook with configurable sheet selection.
- Synchronization Modes: Supports both incremental (process new/updated rows only) and full-refresh (reprocess all data) modes.
- Batch Processing: Configurable batch sizes for efficient data processing of large spreadsheets.
Benefits
- Streamlined Data Collection: Automates the process of collecting data from Google Sheets, reducing manual effort and ensuring consistency.
- Data Consistency: Maintains data consistency through scheduled synchronization, keeping information current and accurate.
- Automated Processing: Enables automated data processing flows by providing structured data output.
- Flexible Configuration: Supports multiple sheets and various data formats to accommodate different use cases.
- Efficient Processing: Batch processing capabilities for handling large datasets efficiently.
Prerequisites
Before using Google Sheets Node, ensure the following:
- Google Sheets API Credentials: Valid Google authentication details for accessing the target spreadsheet.
- Target Spreadsheet URL: The specific Google Sheets URL from which data will be extracted.
- Cron Expression Knowledge: Understanding of Cron expressions for scheduling flows.
- Sheet Access Permissions: Proper permissions to access the target spreadsheet and sheets.
Installation
Step 1: Set Up Google Sheets Access
-
Create Google Sheets API Credentials:
- Set up Google OAuth credentials for Sheets API access
- Ensure proper permissions for the target spreadsheet
- Test spreadsheet accessibility and data permissions
-
Identify Target Spreadsheet:
- Copy the Google Sheets URL
- Verify spreadsheet contains the required data
- Ensure spreadsheet is accessible with provided credentials
Step 2: Configure Google Sheets Credentials
Use the following format to set up your credentials:
Key Name | Description | Example Value |
---|---|---|
Credential Name | Name to identify this set of credentials | my-google-sheets-creds |
Google OAuth | Google authentication details for Sheets access | Google Sheets OAuth |
Step 3: Set Up Lamatic Flow
-
Create a Custom Flow for Google Sheets:
- Configure the Google Sheets node
- Set up scheduling parameters
- Define synchronization preferences
-
Use Pre-built Usecases from the Lamatic Studio
Configuration Reference
Batch Trigger Configuration
Parameter | Description | Required | Example |
---|---|---|---|
Name | Display name for the node | ✅ | Google Sheets |
Credentials | Google authentication details required to connect to the spreadsheet | ✅ | my-google-sheets-credentials |
Spreadsheet | The spreadsheet URL from which data will be extracted | ✅ | https://docs.google.com/spreadsheets/d/{spreadsheet-id} |
Sheet Names | Specific sheets to read from (empty for all sheets) | ❌ | Sheet1,Sheet2 |
Sync Mode | Specify either incremental (process new/updated rows only) or full-refresh (reprocess all data) | ✅ | incremental |
Sync Schedule | Frequency for running the flow, specified using a Cron expression | ✅ | 0 0 05 1/1 * ? * UTC |
Batch Size | Number of rows to process in each batch | ❌ | 200 |
Names Conversion | Convert column names to standardized format | ❌ | false |
Low-Code Example
triggerNode:
nodeId: triggerNode_1
nodeType: googleSheetsNode
nodeName: Google Sheets
values:
credentials: TestCred
syncMode: incremental_append
cronExpression: 0 0 00 1/1 * ? * UTC
sheetName: AS
batchSize: '200'
namesConversion: 'false'
spreadSheetLink: https://docs.google.com/spreadsheets/d/{your-spreadsheet-id}
Output Schema
sheet_name
: String identifier for the processed sheetdata
: Array of objects containing row data with column names as keysmetadata
: Additional information about the data extractiontotal_rows
: Number of rows processedtotal_columns
: Number of columns in the sheetlast_updated
: Timestamp of last data update
Output Schema
Batch Trigger Output
sheet_name
: String identifier for the processed sheetdata
: Array of objects containing row datametadata
: Additional extraction informationtotal_rows
: Integer count of processed rowstotal_columns
: Integer count of columnslast_updated
: ISO timestamp of last updatesync_mode
: String indicating sync mode usedbatch_number
: Integer indicating current batch (if applicable)
Troubleshooting
Common Issues
Problem | Solution |
---|---|
Invalid Credentials | Verify that the correct Google Sheets API credentials are provided |
Spreadsheet Not Found | Ensure the spreadsheet URL is accurate and accessible |
Sync Not Working | Check the Cron expression for correctness |
Data Format Issues | Verify that the data in sheets follows expected format (dates, numbers, etc.) |
Sheet Access Errors | Confirm sheet names match exactly if specified and have proper permissions |
Batch Processing Issues | Verify batch size configuration and spreadsheet size compatibility |
Column Name Issues | Check names conversion settings and column header formatting |
Debugging
- Check Lamatic Flow logs for detailed error messages
- Verify Google Sheets API access permissions
- Test the spreadsheet URL to ensure it's valid and accessible
- Confirm sheet names match exactly if specified
- Validate Cron expression syntax using online Cron validators
- Monitor data processing logs for specific row or column errors
- Test with a small spreadsheet before processing large datasets
- Verify network connectivity and API rate limits
- Check column header formatting and data type consistency