How to connect Firebase to Google Sheets without paying per row

Firestore charges for every document a query returns, whether or not you use it. That makes a scheduled export of a collection into a spreadsheet the most expensive possible way to find out how many things there are — and the cost grows with the collection, quietly, every day.

The interesting question is therefore not how to move documents into a sheet. It is what number somebody actually wants, and which of three routes produces it without reading the whole collection.

What we'll cover

• Reads are the bill
• Three routes, three costs
• Building it with CodeWords
• Maintain the number instead of counting it
• When the BigQuery export is the answer
• Rules do not protect the export
• Shapes that do not fit a spreadsheet
• Realtime Database, which behaves differently
• Making it survive
• Limits worth knowing about
• What to set up first
• Frequently asked questions

Reads are the bill

Every document a query returns is a billed read, including ones you filter out afterwards.

A scheduled full-collection scan costs the collection size every run. Hourly, that is the collection size times twenty-four, every day, forever.

It grows with the data. An export that costs little at launch costs meaningfully more a year later, with no change to the code and nothing to alert anybody.

Filtering in the query is the whole game. Narrow queries with indexes cost a fraction of broad ones, and client-side filtering costs the same as no filtering at all.

Deletes and writes are billed too, which surprises people running a cleanup.

The habit that follows: before building any export, ask what the sheet is for, then find the cheapest query that answers it — which is frequently no query at all.

Three routes, three costs

A filtered query returning a few dozen documents is cheap and appropriate for a report about recent or exceptional records.

A maintained aggregate — a counter document updated as things happen — costs one read to answer a question about a million documents. Covered below, and it is the right answer more often than people expect.

The BigQuery export streams your data into a warehouse where scanning is billed differently and analytical queries are practical. The right answer for anything analytical.

A full collection scan is the fourth route and is almost never the right one. If you are reaching for it, one of the three above is what you actually wanted.

Building it with CodeWords

CodeWords connects to more than 3,000 integrations, and the connection is made once and reused.

1. Open CodeWords and start a new automation.
2. Describe what should happen in plain language to Cody, the automation builder: which documents or which number, how often, and which sheet.
3. Authorize the connection with a service account scoped to the project, plus access to the target spreadsheet.
4. Describe the exceptions: a document missing an expected field, a query needing an index that does not exist, a result larger than expected.
5. Run it against a non-production project and check both the numbers and the read count before pointing it at production.

You describe the outcome; Cody builds it, connects it, and deploys it. The free plan covers light use, with Pro at $39 per month and Business at $100 per month as usage grows; details are on the pricing page.

Maintain the number instead of counting it

The pattern that removes most of the cost, and it is a design change rather than an export change.

Keep a counter document updated when things are created and deleted, so "how many" is one read.

Use the aggregation support for counts where it fits, which avoids reading the documents themselves.

Maintain daily rollups, a small document per day holding the figures somebody asks for, written as events happen or by a scheduled job that reads only that day's documents.

Export the rollups, not the source. A sheet of daily summaries is small, cheap, and more useful than raw documents anyway.

Accept some approximation where exactness is not required. A counter that is occasionally off by one is fine for a dashboard and considerably cheaper than exactness.

Start the rollups before you need the history, since they only cover from when you built them.

When the BigQuery export is the answer

Anything analytical — grouping, trends, joins against other data — belongs in a warehouse rather than in Firestore or a spreadsheet.

The export is continuous once enabled, streaming changes into BigQuery without further reads billed against Firestore.

It only covers from when you enable it, so turn it on now even if nobody queries it yet. This is the single most common regret in Firebase reporting.

Query BigQuery and write the small result to the sheet, which gives you spreadsheet convenience with warehouse economics.

The schema takes getting used to, and it is the price of not paying per document read for every report.

Rules do not protect the export

Worth stating because it surprises people building their first backend job.

The Admin SDK bypasses security rules completely. Everything your rules carefully restrict is available to the export.

Validation living only in rules does not apply, which is often how documents in impossible states got there in the first place.

A service account key is a full-access credential, so it belongs in a secret store and should be rotated.

Restrict what the export can reach at the project level where you can, rather than relying on it being well-behaved.

A spreadsheet has its own sharing, entirely separate from your Firebase access control, so an export of user documents becomes visible to everybody the sheet is shared with.

Shapes that do not fit a spreadsheet

Nested maps have to be flattened to dotted column names or they become unreadable text in a cell.

Arrays need a decision — join, count, or one row per element — and the last one breaks counting.

Missing fields are normal, and a blank cell is not zero, though a spreadsheet formula will treat it as one.

Timestamps arrive in UTC and land in the spreadsheet's own timezone, which is a workbook setting nobody has checked.

Document identifiers are strings and belong in text columns, or the spreadsheet will reformat anything that looks numeric.

Subcollections do not come with the parent document, so a document's full picture may take several queries — which is several times the read cost.

Realtime Database, which behaves differently

Firebase has two databases and advice for one frequently does not apply to the other.

Pricing is by bandwidth and storage, not per read, so the cost arithmetic above changes entirely.

Downloading a large subtree is charged by size, which means a wide export is expensive in a different way — by bytes rather than by documents.

Queries are far more limited. There is no equivalent of Firestore's compound queries, so filtering often has to happen after the data arrives, which is exactly what you pay for.

The data is one JSON tree, so shallow, wide structures export more cleanly than deeply nested ones.

Indexing rules must be declared for anything you order or filter by, and without them the database fetches more and sorts client-side.

If you are on Realtime Database, the rule of thumb is to keep the exported subtree narrow rather than to keep the document count low, and to structure data for the reads you actually make.

Making it survive

Filter in the query, never afterwards, since client-side filtering costs the same as no filtering.

Report the read count alongside the rows written, so the cost stays visible as the collection grows.

Replace a named range rather than appending, unless the sheet is deliberately a log.

Create indexes deliberately rather than discovering the need in production.

Report the outcome. Documents read, rows written, and an estimate of the reads consumed.

Limits worth knowing about

Sheets has a total cell limit, which a collection export reaches quickly.

Sheets API writes are rate limited, so large writes need chunking.

Composite indexes are required for most multi-field queries and are not created automatically.

Firestore has no cheap full-collection aggregation beyond the count support, which is why maintained rollups exist.

The BigQuery export is not retroactive, covering only from when it was enabled.

What to set up first

A daily rollup document holding the numbers somebody asks for, written by a job that reads only that day's documents, exported to a sheet as one row per day. It is cheap on day one, it stays cheap as the collection grows, and it accumulates the history that a full-collection export can never reconstruct.

Two habits make the difference. Enable the BigQuery export at the same time, even if nobody queries it yet, because it only covers from when you turn it on. And report the read count in the sheet alongside the figures, so the cost of the reporting stays visible instead of appearing on a bill three months later.

Frequently asked questions

Why is exporting a collection to a spreadsheet expensive?

Because Firestore bills per document read, and a full-collection scan costs the collection size every run. It grows with the data, with no code change and nothing to alert anybody, which is why it usually shows up as an unexplained bill.

How do I count documents cheaply?

Maintain a counter, or use the aggregation support for counts. Reading the collection to count it charges a read per document, which is the most expensive possible way to obtain a single number.

Should I enable the BigQuery export?

Yes, now, even if nobody queries it yet. It is not retroactive, so the cost of enabling it late is a permanent gap in your history — and it is the right home for anything analytical.

Do security rules protect against a bad export?

No. The Admin SDK bypasses rules entirely, so validation living only in rules does not apply to a backend job. Restrict what the service account can reach instead, and treat its key as a full-access credential.

Why are cells empty in the sheet?

Because those documents do not have that field, which is normal in Firestore. A spreadsheet formula treats blank as zero, so decide what absent means per field and report the proportion missing.

How do I handle nested data?

Flatten it deliberately into named columns, and decide explicitly what arrays become — joined, counted, or one row per element. The last option breaks counting, so say which you chose in the sheet.

Is a spreadsheet the right destination at all?

For a small, stable summary people read, yes. For anything analytical, growing, or feeding another process, the warehouse is the destination and the sheet is a workaround that gets more expensive every month.

Does this apply to Realtime Database too?

Only partly. It is billed by bandwidth and storage rather than per read, so the cost of an export depends on the size of the subtree you download rather than the number of records. Keep the exported subtree narrow rather than the record count low.

Why is a Realtime Database query slow and expensive?

Usually a missing index rule, which makes the database fetch more data and sort it client-side. Declaring indexes for anything you order or filter by is the equivalent of Firestore's composite index step.

Related reading

• Firebase and CodeWords
• Connecting BigQuery to Google Sheets
• CodeWords integrations and templates