Google Sheets is the most underrated data source in Data Studio. It’s free, native, and it’s the escape hatch for every platform that has no connector — if you can get data into a spreadsheet, you can report on it.
This covers connecting a sheet, the formatting rules that decide whether it works, and the three jobs Sheets does better than any paid connector.
Why bother, when connectors exist
Because a spreadsheet is something you own. A third-party connector can change its pricing, rename a field or deprecate an endpoint; a Sheet does none of those things. Unglamorous, stable data sources are consistently the best ones — bulky, ugly-looking spreadsheets and BigQuery datasets make far better foundations than they look like they should.
Sheets is also the only free route for three genuinely common jobs, covered below.
Get your sheet ready first
Most Sheets problems are formatting problems, and they’re much easier to prevent than to debug.
- One header row, at the very top. No title above it, no merged cells, no blank spacer row. Data Studio reads row 1 as your field names.
- One kind of thing per column. A column mixing text and numbers is imported as text, and you can’t sum text.
- Dates as
YYYY-MM-DD. This is the single most common cause of broken charts. If several people maintain the sheet, agree the format explicitly — a mixed date column breaks the chart silently rather than erroring. - No merged cells anywhere in the data range.
- No totals row at the bottom — it becomes a data row and inflates everything.
Connect the sheet, step by step
- Create → Data source in Data Studio.
- Choose “Google Sheets” — By Google, free and native.
- Pick the spreadsheet, then the worksheet (the tab).
- Leave “Use first row as headers” ticked.
- Set an explicit range (e.g.
A1:F500) if the tab holds anything besides the table. - Do not tick “Include hidden and filtered cells” unless you specifically need them — this one quietly breaks date dimensions.
- Connect, then check the field types on the next screen. Fix any date or number that came through as text now, not later.
Three jobs Sheets does better than a paid connector
1. Annotations — turning a dashboard into a report
This is the highest-value use, and it’s the difference between a wall of charts and something a stakeholder values. Keep a sheet with three columns — Date, Event, Description — and blend it onto your report on Date with a left join. Now “traffic dropped in March” sits next to “site migration, 14 March”.
It’s also insurance: when a chart breaks, a page carrying commentary is still useful to whoever opened it. Annotations are how you put your voice next to the numbers instead of leaving the reader to interpret them alone.
2. Data no connector provides
Budgets, targets, offline sales, call tracking, a manual competitor list, ranking data from a tool with no connector. Anything that lives in a spreadsheet or can be exported to one becomes reportable — for free.
A budget sheet is the classic: pair monthly targets with actual spend from Google Ads and you have pacing, which no single connector gives you.
3. Lookup tables and relabelling
A sheet mapping campaign names to friendly labels, or URLs to content categories, blended onto your data lets you group things the source can’t. It’s the cheapest way to add structure a platform doesn’t have.
Refresh behaviour, and the trap in it
Sheets data sources cache, and the default refresh is every 15 minutes — configurable in the data source settings. That’s fine for most reporting.
The trap is different: new columns don’t appear automatically. Add a column to your sheet and it won’t show up until you edit the data source and click Refresh fields. Charts referencing it will error until you do.
Two more things that will bite:
- Renaming a column breaks every chart using it. Data Studio matches on the header text. Decide your headers before you build.
- Sharing permissions travel with the sheet. If a viewer can’t see the underlying sheet and the data source uses Viewer’s credentials, they see nothing. Set the data source to Owner’s credentials for reports you share widely.
When a sheet is the wrong answer
Sheets has real limits, and it’s worth knowing where they are:
- Volume. Tens of thousands of rows will make reports sluggish. At that scale, BigQuery is the right home.
- Anything needing manual updating. If a human has to paste data in every Monday, it will eventually not happen. That’s precisely when a paid connector earns its money — for Meta, LinkedIn, TikTok or Shopify, an automated pull beats a spreadsheet someone forgets. Coupler.io and Windsor.ai both write into Sheets as well as Data Studio, if you want automation while keeping the spreadsheet in the middle.
- Many people editing. Shared sheets drift. One person breaking the date format breaks the dashboard, and nobody notices for a fortnight.
Frequently asked questions
Is the Google Sheets connector free?
Yes, it’s built by Google and free, like Data Studio itself.
Why is my date column not working as a date?
Almost always mixed formats in the sheet. Use YYYY-MM-DD consistently, then edit the data source and set the field type to Date. Also check you didn’t tick “Include hidden and filtered cells”, which breaks date dimensions.
I added a column and it’s not showing. Why?
New columns aren’t picked up automatically. Edit the data source and click Refresh fields.
How often does Sheets data refresh?
Every 15 minutes by default, configurable in the data source settings.
Can viewers see my report without access to the sheet?
Only if the data source uses Owner’s credentials. With Viewer’s credentials, anyone without sheet access sees nothing.
How much data can a sheet handle?
A few thousand rows is comfortable. Tens of thousands will make reports slow — move to BigQuery at that point.