How to connect MongoDB to Google Sheets when documents are not rows

A spreadsheet is a rectangle. A MongoDB collection is not, and the gap between them is the entire difficulty of this particular export. Documents nest, arrays hold several values in one field, and two documents in the same collection can have different fields entirely.

Something has to decide what the columns are. Doing that deliberately, in an aggregation pipeline, produces a sheet people can use. Letting an export tool guess produces a sheet with three hundred columns, most of them empty.

What we'll cover

• Documents are not rows
• Decide the columns first
• Building it with CodeWords
• Flattening in the pipeline
• Arrays, and the row-multiplying decision
• Fields only half the collection has
• Types that confuse a spreadsheet
• When the spreadsheet should not be the destination
• Making it survive
• Limits worth knowing about
• What to set up first
• Frequently asked questions

Documents are not rows

Nesting has no spreadsheet equivalent. A sub-document becomes either several columns with dotted names or a cell containing unreadable text, and neither is automatic.

Arrays hold several values in one field, and a spreadsheet cell holds one. Something must choose between joining them, taking the first, counting them, or producing one row per element.

Fields are optional by design, so a column exists for some documents and not others, and the export must decide what the missing ones become.

The same field can hold different types across documents, which a spreadsheet column cannot represent without picking one.

Document order is not row order. Without an explicit sort, two exports of unchanged data can produce different orders, which makes comparing them by eye impossible.

Each of these is a decision. An export that does not make them explicitly makes them implicitly, and usually wrongly.

Decide the columns first

The habit that turns this from a recurring problem into a solved one.

Write down the columns the sheet needs, before looking at the documents. Usually a dozen, often fewer.

Project exactly those in the pipeline, so the sheet's shape is a decision rather than an accident.

Name them for the reader, not for the schema. customer_email beats contact.primary.addr, and the reader does not know or care about the document structure.

Fix the order. A sheet whose columns move between runs breaks every formula pointing at it.

Add a column deliberately when somebody needs one, rather than exporting everything in case. The everything approach is what produces the three-hundred-column sheet.

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 collection, which documents, which fields, and which sheet.
3. Authorize the connection with a connection string pointing at a secondary where possible, using a user scoped to the specific database, plus access to the target spreadsheet.
4. Describe the exceptions: a document missing an expected field, a field of an unexpected type, a result larger than the sheet can hold.
5. Run it against a restored snapshot and check the columns before pointing anything 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.

Flattening in the pipeline

Do the work in the database rather than after the documents arrive.

Project nested values to top-level names, so the pipeline emits documents that are already flat and already named for the reader.

Match first and early, so the pipeline can use an index rather than scanning.

Compute derived values in the pipeline — a total, a count, a days-since — rather than as a spreadsheet formula that somebody will later break.

Sort explicitly, always, so consecutive exports are comparable.

Limit deliberately, so a growing collection does not eventually produce a sheet that fails to write.

Keep the pipeline somewhere people can read it. Aggregation pipelines become dense quickly, and one buried inside an automation is untrustworthy the moment a number is questioned.

Arrays, and the row-multiplying decision

The decision with the biggest effect on what the sheet means.

One row per document keeps the sheet countable — a hundred rows is a hundred orders — and requires collapsing arrays into a single cell or a count.

One row per array element makes the detail available and breaks counting, because the same order now appears four times and anybody summing a column will double-count.

Say which you chose, in the sheet, because the reader cannot tell by looking and will assume the one that suits their question.

Collapsing is usually right for a report; expanding is usually right when the sheet feeds another process that needs the detail.

Never expand silently. A sheet that unexpectedly has four times the rows somebody expected is the fastest way to a wrong total in a meeting.

Fields only half the collection has

Schema drift arrives in a spreadsheet as empty cells, and empty cells lie.

A missing field is not zero, but a spreadsheet formula averaging that column treats it as one.

Decide per field what absent means — blank, zero, or a marker such as "n/a" — and be consistent.

Report the proportion missing. If a column is empty for a third of rows, that fact is more important than the column's average, and it belongs in the sheet.

Sample the whole history, not recent documents, when deciding the columns, because the drift lives in the old ones.

Expect old documents to have old field names, which means a column that looks sparse may actually be two columns that should be merged.

Types that confuse a spreadsheet

ObjectIds are not numbers. Write them as text, or the spreadsheet will mangle them.

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

Large integers lose precision when Sheets stores them as floating point, so identifiers and large counts belong in text columns.

Decimal values round, which matters for anything financial.

Booleans become TRUE and FALSE, which most people are happy with until they try to sum them.

When the spreadsheet should not be the destination

Worth asking, because a recurring export is a sign that somebody needs something the sheet is a poor substitute for.

If people are pivoting it every week, they want a reporting tool, and the sheet is the workaround.

If another process reads the sheet, the sheet is now infrastructure with no tests, no schema enforcement, and an owner who can reorder the columns by accident.

If the row count keeps growing, the destination will fail eventually, and a warehouse table with a proper tool on top is the answer.

If several sheets pull overlapping data, each with its own filters, the definitions have already diverged and somebody is reconciling them by hand.

If it contains personal data, a spreadsheet is a copy outside your retention and access controls that nothing will clean up.

A sheet is a good destination for a small, stable answer that people read and occasionally annotate. When it is doing more than that, the honest recommendation is to say so rather than to keep making the export more elaborate.

Making it survive

Read from a secondary where you have one, so a report does not compete with the application.

Project only the fields you need, which is faster and moves far less over the network.

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

Write the run time and the row count into the sheet, visibly.

Report the outcome. Documents read, rows written, and documents skipped with the reason — the last figure is where drift becomes visible.

Limits worth knowing about

Sheets has a total cell limit, which an expanded array export reaches surprisingly fast.

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

Aggregation stages have memory limits, and exceeding them fails unless disk use is allowed — filtering earlier usually resolves it better than raising limits.

Very large result sets should be paged rather than materialised in one go.

A secondary is slightly behind the primary, which is immaterial for a report and matters if you are checking something just written.

What to set up first

One report, with the columns written down before the pipeline is built, sorted explicitly, replacing a named range, with the run time and row count in the sheet. Pick whichever export somebody currently does by hand, because it already has a definition and an audience.

Two habits make the difference. Decide the columns before you look at the documents, since letting the export discover them is what produces unusable sheets. And state in the sheet whether a row is a document or an array element, because that single ambiguity produces more wrong totals than anything else here.

Frequently asked questions

Why does my export have hundreds of columns?

Because something inferred the columns from the documents rather than being told. Project exactly the fields you want in the aggregation pipeline — usually a dozen — and name them for the reader.

How should arrays be handled?

Decide between one row per document with the array collapsed or counted, and one row per element. Both are valid; expanding breaks counting, so say which you chose in the sheet or somebody will double-count.

Why are some cells empty?

Because those documents do not have that field, which is normal in MongoDB. Decide what absent means per field, be consistent, and report the proportion missing — an average over a column that is a third empty is misleading.

Why do identifiers look wrong in the sheet?

ObjectIds and large integers both suffer. Sheets stores numbers as floating point and loses precision, so write identifiers as text rather than numbers.

Should the export read from the primary?

From a secondary where you have one, so a report does not compete with application traffic. The slight replication lag is immaterial for reporting and only matters if you are checking something just written.

Why do two exports of the same data look different?

No explicit sort. Document order is not guaranteed, so add a deterministic sort to the pipeline — without it, comparing two exports by eye is impossible.

Where should the flattening happen?

In the aggregation pipeline, in the database. It is faster, it moves less data, and it makes the shape of the sheet an explicit decision stored somewhere people can read rather than something that emerged.

When is a spreadsheet the wrong destination?

When people pivot it weekly, when another process reads it, when the row count keeps growing, or when several sheets pull overlapping data with their own filters. Each is a sign the sheet has become infrastructure, and it is infrastructure with no schema and no tests.

Is it a problem if another system reads the sheet?

Yes, quietly. Anybody can reorder columns, insert a row, or rename a tab, and nothing enforces the shape the consumer expects. If a process depends on it, the data should be somewhere with a schema.

How do I find the fields worth exporting?

Sample across the whole history, not recent documents, and report which fields appear on what proportion of documents. The drift lives in the old ones, and a column that looks sparse is often two fields that were renamed at some point.

Related reading

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