Excellent Data Filler

Appearance

Fetch Google Sheet Data ​

Retrieve real-time data from cloud-hosted Google Spreadsheets using the Google Sheets API for automated form input population.

Overview ​

The Fetch Google Sheet Data field type queries specific cell ranges from a Google Sheet and populates retrieved values directly into form fields or variables. It provides built-in browser caching, automatic retry handling, and formatting controls for dates and numbers.

Configuration Options ​

OptionTypeDescriptionRequired
Google Service AccountDropdownAuthorized Google account with Google Sheets API access.Yes
Spreadsheet IDText InputUnique identifier string extracted from the Google Sheet URL.Yes
Sheet NameText InputSpecific tab name within the spreadsheet (e.g., Sheet1, Inventory).Yes
Output RangeText InputA1-notation cell coordinate or range (e.g., B2, A2:D2).Yes
Value Render OptionDropdownFormats values: FORMATTED_VALUE (as displayed) or UNFORMATTED_VALUE (raw values).No
Date Time Render OptionDropdownFormats dates: SERIAL_NUMBER or FORMATTED_STRING.No
Enable CachingToggle SwitchCaches retrieved values locally to minimize API quota usage.No
Cache DurationNumber InputLifetime of cached records in seconds.When caching enabled
Enable RetryToggle SwitchRe-attempts queries if temporary network errors occur.No
Maximum RetriesNumber InputMaximum number of retry attempts.When retry enabled

Detailed Settings Breakdown ​

Google Service Account ​

Select the Google account registered in Google Service. The account must hold read permissions on the target sheet.

Locating the Spreadsheet ID ​

Extract the ID token directly from your browser address bar:

text
https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit
                                        └─────────────────── SPREADSHEET ID ───────────────────┘

In this example, the Spreadsheet ID is: 1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms.

Output Range (A1 Notation) ​

Specify the cell or range to fetch:

  • A2: Single cell value.
  • B2:E2: Multiple columns from row 2.
  • Data!C5: Specific sheet and cell reference.

Caching for High Performance ​

  • Enable Caching: Stores retrieved cell values in browser memory for the specified Cache Duration (e.g., 300 seconds).
  • Benefit: Greatly accelerates repetitive executions and prevents hitting Google Sheets API rate limits.

Practical Examples ​

Example 1: Fetching Single User Record ​

text
Google Service Account: [email protected]
Spreadsheet ID: 1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms
Sheet Name: Users
Output Range: B2
Value Render Option: FORMATTED_VALUE
Enable Caching: ON
Cache Duration: 300

Example 2: Real-Time Dynamic Price Lookup ​

text
Google Service Account: [email protected]
Spreadsheet ID: 1a2b3c4d5e6f7g8h9i0...
Sheet Name: Rates
Output Range: C10
Enable Caching: OFF
Enable Retry: ON
Maximum Retries: 3

Best Practices ​

  • Use Formatted Values: Choose FORMATTED_VALUE when you want currency symbols, commas, or custom decimal places preserved.
  • Enable Caching for Static Tables: If data rarely updates during an automation session, set Cache Duration to 600 (10 minutes).
  • Verify Sheet Sharing: Make sure the target Google Sheet is shared with the authorized Google Service account.