Magento's sales data exports beautifully. Its catalogue does not, and the difference between those two facts is where most Magento spreadsheets go wrong.
Start with the good news, because it's genuinely good: sales_order and sales_order_item are proper tables with proper columns. base_grand_total means what you'd expect, order state is a single field, and a revenue export is a straightforward query. If all you need is orders, Magento is one of the easier platforms to get out.
The catalogue is the opposite. Magento stores product attributes in EAV: catalog_product_entity holds the skeleton, and names, prices, statuses and everything else live in catalog_product_entity_varchar, _decimal, _int and _text, keyed by an attribute id you look up in eav_attribute. "Products with their names and prices" is a multi-join query, not a table. Export the tables raw into a spreadsheet and you've moved the puzzle, not solved it.
The admin's own grid export sidesteps some of that — it gives you the columns the grid shows — but it runs inside Magento, on production, under PHP's memory and execution limits. On a large order grid it's the kind of export that either takes several minutes or quietly fails at 30,000 rows. And whatever route you take, Sheets caps a spreadsheet at 10 million cells, which a raw Magento export reaches far sooner than anyone expects.
All three end in a spreadsheet. They differ in whether the EAV joins are your problem and where the query runs.
Sales → Orders (or the product grid), filter, then Export as CSV from the grid itself.
Apps Script with a JDBC connection, or a cron job running SQL and pushing CSV, with the EAV joins written by hand.
BeQuery clones Magento into an isolated PostgreSQL copy. You aggregate in the SQL editor and publish the result as a CSV link the sheet refreshes on its own.
Read-only MySQL credentials, host and port. BeQuery clones the database into an isolated PostgreSQL copy that only your team can reach — the shop itself is only ever read.
This is the step that decides whether the spreadsheet survives. In the SQL editor, aggregate first: revenue by month, margin by product, customers by cohort. You want the answer, which is hundreds of rows, not the raw orders table, which is hundreds of thousands.
Save the query, then either export CSV or XLSX from the editor for a one-off, or publish the query as a CSV link. XLSX exports are capped at 500,000 rows; CSV will happily hand you more than a spreadsheet can hold, which is another reason to aggregate first.
Drop =IMPORTDATA("…your link…") into a cell. Sheets fetches it on its own — roughly once an hour — so the tab stays current without anyone exporting anything again. Build your charts and pivots on top of that range. The link is a password in URL form: anyone who can see the formula can read the results, and you can revoke it from BeQuery at any time.
For Magento reporting, often yes — the sales tables suit it. The catalogue side is where it starts to strain.
Because there is no name column on catalog_product_entity. The name is a row in catalog_product_entity_varchar whose attribute_id points at the 'name' attribute in eav_attribute, so it only appears if your query joins for it explicitly. The admin grid does that work for you, which is why grid exports have names and raw table exports don't.
In practice: state in ('complete','processing') on sales_order, summed on base_grand_total. That excludes canceled, closed and pending_payment, which otherwise inflate every figure — and it's the definition BeQuery's Magento insights use, so the numbers stay consistent between the app and anything you export.
It can refresh itself. Publish a saved query as a CSV link, paste =IMPORTDATA("…") into a cell, and Sheets refetches it on its own — about once an hour. Worth being precise about the direction: BeQuery doesn't write into your spreadsheet, the spreadsheet pulls from BeQuery. Nothing is pushed, nothing is stored in Google on our side, and revoking the link stops it immediately.
Treat it as a password. It's fetched anonymously by Google, so the URL itself is the credential — anyone holding it, including anyone who can open your spreadsheet and read the formula, can see that query's results. It's deliberately narrow: one saved query, read-only, row-capped, and revocable. We store only a hash of it, which is why it's shown once when you create it and can't be retrieved afterwards. For anything sensitive shared widely, use Looker Studio on a read-only connection instead.
Google Apps Script can open a JDBC connection to MySQL, so it is technically possible. It means exposing your production database to Google's IP ranges, writing and maintaining the script, and having every refresh query the same server that serves your customers. It's a lot of moving parts to end up with a spreadsheet that occasionally times out.
Google Sheets caps a spreadsheet at 10 million cells across all its tabs. That sounds enormous until you divide by columns: a 20-column export runs out at around 500,000 rows, and performance gets unpleasant well before the hard limit. Aggregated results are never a problem; raw order lines from a busy store are.
Free tier, no credit card, read-only access. Production is never written to — and you can explore the demo store before connecting anything.