Complex Multi-Sheet Spreadsheets

The template-based approach

DriveMate supports creating spreadsheets from scratch using Flow actions - but real-world spreadsheets are often far more complex than a single sheet with a few columns. When you need multiple sheets, cross-sheet formulas, charts, conditional formatting, hidden sheets, or named ranges, building all of that from Flow actions would be extremely tedious and error-prone.

The template-based approach solves this: design a complex spreadsheet once in Google Sheets and let your Flows copy and populate it for each record.

See it in action

The Sales Calculator guide walks through a complete template-based example with a hidden data sheet, an analysis dashboard with KPIs, ARRAYFORMULA-driven tables, what-if scenarios, and charts.

How it works

1. Create a spreadsheet template in Google Drive with all the structure you need - multiple sheets, formulas, formatting, charts, hidden sheets, everything.
2. Copy the template into a record-specific folder when a Flow runs (using the Copy File action).
3. Sync Salesforce data to the designated sheets in the copied spreadsheet using the standard row actions (Append Row, Edit Rows, Delete Rows by Criteria).

The copy preserves everything - all sheets, formulas, cross-sheet references, formatting, charts, conditional formatting, data validation, and hidden sheet states are duplicated exactly as they exist in the template.

Designing the template

When setting up your template spreadsheet, separate Salesforce-managed data from spreadsheet-only logic:

• Data sheets - These are the sheets where your Flows will write Salesforce data. They can contain just headers and be otherwise empty, waiting for rows to be appended by your automations. You can even hide these sheets so end users only interact with the presentation layer.
• Presentation / calculation sheets - These sheets contain formulas that reference the data sheets (e.g. =SUM('Sales Data'!G:G), =VLOOKUP(B2, 'Products'!A:C, 3, FALSE)). They can include charts, pivot-style summaries, dashboards - anything Google Sheets supports. Since they reference the data sheets by formula, they update automatically as Salesforce data flows in.

This separation means your Flows only need to manage raw data writes. All the complex logic lives in the template and just works after copying.

Dynamic tables with ARRAYFORMULA

When the number of rows in a data sheet is unpredictable, use ARRAYFORMULA on the presentation sheet to dynamically pull and transform data. A single formula in one cell automatically expands to cover all rows that have data:

=ARRAYFORMULA(IF('Raw Data'!A2:A="", "", 'Raw Data'!B2:B))

This avoids hardcoding row references and ensures the presentation sheet adapts as Salesforce adds or removes rows. Calculated columns (sums, percentages, lookups) can use the same pattern.

Example workflow

Consider a financial planning spreadsheet that has:

• A hidden Raw Data sheet where Salesforce line items are synced.
• A Summary sheet with formulas that aggregate the raw data into totals, averages, and charts.
• A Configuration sheet where the user sets tax rates, discount tiers, and other parameters.
• A Report sheet that combines everything into a printable view.

Your Flows would look like this:

Flow	Trigger	Actions Used
Initialize Planner	Record created	Create Folder, Copy File, Refresh DriveMate Component
Sync Line Items	Line item added	Append Row to Sheet (targeting the Raw Data sheet)
Sync Line Item Edits	Line item edited	Edit Rows in Sheet (targeting the Raw Data sheet)
Sync Line Item Deletion	Line item removed	Delete Rows by Criteria (targeting the Raw Data sheet)

The Summary, Configuration, and Report sheets are never touched by your Flows - they are defined entirely in the template and work automatically through formulas.

Key benefits

• Design once, copy many - The template is your single source of truth for spreadsheet structure. Update the template and all future copies inherit the changes.
• No formula management in Flows - Complex formulas, cross-sheet references, and charts are all part of the template. Your Flows only write plain data values.
• Hidden sheets for clean UX - Keep Salesforce data sheets hidden so users see only the polished presentation layer.
• Full Google Sheets power - Anything you can build in Google Sheets (pivot tables, charts, conditional formatting, data validation, named ranges) works in the template and carries over to every copy.