How to automate the Google Sheet behind your Data Studio report

To keep the Google Sheet behind a Data Studio report filled without anyone touching it, give the Sheet its own schedule (an Apps Script time-driven trigger, an import formula, or a connector add-on), and remember that Data Studio adds up to 15 more minutes of caching on top. Which one fits depends on where the data comes from and how stale you can live with.

To automate Google Sheets for a report, you have two jobs: filling the Sheet, and getting Data Studio (formerly Looker Studio) to show what landed. This covers:

• the two clocks behind every Sheet-backed chart, and how old a number can get
• five ways to fill a Sheet on a schedule, compared in one table
• an Apps Script that appends and skips duplicates instead of wiping the tab
• the Sheet rules that stop the data source breaking, and what to check when the numbers stop moving

Connecting a Sheet in the first place is in my guide to connecting Google Sheets to Data Studio. The evidence here is Google’s documentation (read 13 October 2026), vendor pages and Google’s Data Studio forum. The intervals are theirs; I haven’t run a timing test.

How a Sheet-backed report actually updates

Google says two things about Sheets data sources that sound contradictory and are both true:

1. Data Studio checks the Sheet for changed values and new rows every time a query runs on a report that uses it.
2. It also caches queries for the data freshness interval: 15 minutes by default for Google Sheets, adjustable to 1, 4 or 12 hours.

The trick is the word query. Data freshness is how long Data Studio may answer a chart from memory. A query it has seen within that window gets the remembered answer; a new one (a different date range, a new filter, a chart nobody has loaded yet) goes to the Sheet. Google promises the data “won’t be older than” the setting, not that it stays cached for all of it.

The worst case is the two clocks added together. A few details change who sees what:

• Credentials. With owner’s credentials, every viewer shares one freshness clock; with viewer’s credentials, each viewer has their own (data sources and credentials covers the choice).
• Blends take the shortest freshness of their sources.
• Refresh data. Editors can press it any time (More options → Refresh data), viewers only if allowed, with a one-minute cooldown. The last refresh time sits bottom left.
• Embeds. Embedded viewers can’t refresh; the report updates when the window runs out.

Caching across every connector is in why your Data Studio report shows yesterday’s numbers. For a Sheet, Data Studio is rarely the slow clock. Whatever fills the Sheet usually is.

Five ways to fill a Sheet on a schedule, compared

Formulas and Apps Script are free and built in. Connector add-ons are the practical answer for non-Google ad platforms. Zapier, Make and n8n suit data that arrives one event at a time. Connected Sheets only makes sense if the data already lives in BigQuery.

Intervals from Google’s Sheets, Apps Script and Connected Sheets help, Zapier’s, Make’s and n8n’s docs, and each vendor’s plan page, read 13 October 2026. “Worst case” is the fill interval plus Data Studio’s default freshness: arithmetic, not a measurement.

Formulas: IMPORTRANGE, IMPORTDATA and a QUERY roll-up

The quickest automation there is needs no code: one Sheet that pulls from others. IMPORTRANGE imports a range from another spreadsheet, IMPORTDATA imports a CSV or TSV from a URL, and QUERY filters and tidies the result. Google itself recommends giving Data Studio a separate worksheet filled by functions like QUERY, which is exactly this.

A common setup, and the answer to a 2024 thread on Google’s forum from someone adding a new Sheet every week: a roster of client Sheets, stacked into one tab.

Tab "Roster":  A = client, B = spreadsheet URL, C = range (e.g. Data!A2:F)

Tab "Rollup", cell A2 (headers typed in row 1):
=QUERY({
  IMPORTRANGE(Roster!B2, Roster!C2);
  IMPORTRANGE(Roster!B3, Roster!C3);
  IMPORTRANGE(Roster!B4, Roster!C4)
}, "select * where Col1 is not null", 0)

Tab "Feed", cell A1 (a published CSV):
=IMPORTDATA("https://example.com/export.csv")

The braces stack the ranges, and QUERY drops the blank rows open-ended ranges drag in. Every source needs the same columns in the same order, and the first import from each asks you to Allow access.

Now the catch. Google describes IMPORTRANGE’s refresh in terms of an open document: it “automatically checks for updates every hour while the document is open”, and reloads when the document is first opened or was opened in the past five minutes. Google’s calculation-settings page (File → Settings → Calculation) gives a different number for the same function: 30 minutes for IMPORTRANGE, an hour for IMPORTDATA.

What neither page says is what happens when nobody has the Sheet open and Data Studio queries it. A user on Reddit (around 2023) reported having to open the Sheet first, let the formulas refresh, then open the report. It’s one person’s report, unconfirmed by Google or by me, but enough that I wouldn’t rely on formulas for a Sheet nobody opens. Use something that writes values: a script or an add-on.

The same roll-up suits an annotations tab several people maintain (adding annotations to Data Studio charts).

Apps Script: pull an API or CSV on a timer, append, don’t overwrite

Apps Script is the JavaScript that lives inside Google Sheets (Extensions → Apps Script), and a time-driven trigger runs a function on a schedule, as often as every minute or as rarely as once a month. It’s the free, unattended route, and it writes values, so nobody needs to open the Sheet.

A common pattern in scripts online clears the sheet each run, then writes today’s pull. Simple, with three side effects: history older than the API’s window disappears, the tab is empty mid-run (exactly when a Data Studio query might arrive), and the row count jumps around under the report. Appending is barely more code.

Set it up, step by step

1. Copy the starter Sheet (box below), or paste the script into your own Sheet via Extensions → Apps Script.
   
2. Fill in the Config tab: source URL (CSV or JSON), the key columns that make a row unique (say, date,campaign), and the schedule.
   
3. Run setup() once and accept the permissions prompt. It writes the Data headers and installs the trigger.
   
4. Or install the trigger by hand: Triggers (left) → Add Trigger → run, time-driven, hour timer. Or in code: ScriptApp.newTrigger('run').timeBased().everyHours(1).create().
   
5. Check it ran: the editor’s Executions list, and the Log tab (fetched, appended and skipped rows per run).
   
6. Connect the Data tab to Data Studio (how to connect a Sheet) and the Health tab for a “last updated” scorecard.

The core of the script

Written from Google’s docs, not tested here. The full version, with setup(), CSV and JSON parsing, and removeTriggers(), is in the starter Sheet.

function run() {
  var lock = LockService.getScriptLock();
  if (!lock.tryLock(30000)) return;            // a previous run is still going
  var t0 = Date.now();
  try {
    var cfg = readConfig_();                   // from the Config tab
    var records = fetchRecords_(cfg);          // UrlFetchApp + parseCsv / JSON.parse
    var data = SpreadsheetApp.getActive().getSheetByName('Data');
    var headers = data.getRange(1, 1, 1, data.getLastColumn()).getValues()[0];
    var keyIdx = cfg.keys.map(function (k) { return headers.indexOf(k); });

    var seen = {}, n = data.getLastRow() - 1;  // keys already in the tab
    if (n > 0) {                               // read only the key columns
      var cols = keyIdx.map(function (i) { return data.getRange(2, i + 1, n, 1).getValues(); });
      for (var r = 0; r < n; r++) {
        seen[cols.map(function (c) { return keyPart_(c[r][0]); }).join('|')] = true;
      }
    }

    var out = [], skipped = 0, now = new Date();
    records.forEach(function (rec) {
      var row = headers.map(function (h) {
        return h === 'imported_at' ? now : (rec[h] == null ? '' : rec[h]);
      });
      var key = keyIdx.map(function (i) { return keyPart_(row[i]); }).join('|');
      if (seen[key]) { skipped++; return; }
      seen[key] = true;
      out.push(row);
    });

    if (out.length) {                          // one batch write, below the last row
      data.getRange(data.getLastRow() + 1, 1, out.length, headers.length).setValues(out);
    }
    log_(records.length, out.length, skipped, (Date.now() - t0) / 1000, 'OK', '');
  } catch (e) {
    log_(0, 0, 0, (Date.now() - t0) / 1000, 'ERROR', String(e.message || e));
    throw e;                                   // so Apps Script emails the failure
  } finally {
    lock.releaseLock();
  }
}

Four details do the work. muteHttpExceptions: true makes UrlFetchApp.fetch return an error response instead of throwing, so the script checks the status and retries once. LockService stops runs overlapping. One setValues call writes every new row. And re-throwing keeps Apps Script's failure email: a failed trigger shows nothing on screen, but Google emails a "Summary of failures for Apps Script" with a link to fix it.

Two caveats. A trigger runs under the account of whoever created it, invisible to everyone else, so use an account that won't leave. And it isn't to the minute: a 9 AM trigger fires between 9 and 10 AM, then keeps that time.

How far the free quota stretches

From Google's Apps Script quotas page, read 13 October 2026. Google says limits can change without notice. Per-run budgets are the daily total divided by runs per day.

For a daily or hourly pull of marketing data, that's plenty. Every minute (everyMinutes(1)) leaves a free account under four seconds per run.

Connector add-ons that write to Sheets on a schedule

For Meta, LinkedIn, TikTok or Shopify data there's no native Google route into a Sheet, so a connector add-on (a vendor's Sheets extension that pulls a platform into a tab on a schedule) is the practical answer.

Prices and limits from each vendor's pricing page and Google Workspace Marketplace listing, checked 13 October 2026. USD and EUR as each vendor publishes them; no conversion.

Supermetrics' pricing page carried a refresh row on 9 September 2026 saying weekly, daily and hourly refreshes were "(Google Sheets only)"; on 13 October that row is gone. Whether the limits behind it changed, the page doesn't say. And permissions: the Sheets add-ons from Supermetrics, Windsor.ai and Catchr ask to run when you're not present and to see all your spreadsheets. Scheduled refreshes need that, but know it before installing on a shared account.

If Data Studio is the only destination, ask whether you need the Sheet at all: most of these vendors also connect to Data Studio directly. The Sheet earns its place for history you control, joins with your own columns and manual notes. See Data Studio connectors compared, Porter vs Windsor vs Supermetrics and Supermetrics pricing.

Zapier, Make and n8n: when rows should arrive on an event

Workflow tools shine when each new thing (a lead, an order, a form) should become one row. For "pull yesterday's metrics", they're a scheduler around an API call, which Apps Script does free.

From Zapier's Schedule help, Make's scheduling help and n8n's Schedule Trigger docs, read 13 October 2026.

The append rule applies here too: map the step to "add a row", not "update a range".

Connected Sheets, and when the Sheet should be BigQuery instead

Connected Sheets runs BigQuery queries from inside a Sheet, on request or on a schedule, and saves the results there. It needs Google Cloud access, BigQuery access and a project with billing (or a BigQuery trial). A scheduled refresh runs as the person who set it up, and it pauses if someone else adds or updates a data source. Extracts are capped at 500,000 rows or 5 million cells.

But if the data is already in BigQuery, Data Studio can read it directly, so the Sheet only earns its place for people who work in the spreadsheet. And if an append-only tab is heading for millions of cells, move the table to BigQuery.

Sheet rules that keep Data Studio from breaking

Automation changes the Sheet with nobody looking, so every rule here keeps the structure fixed and lets only rows change. Data Studio doesn't detect schema changes: an added, removed, renamed or reordered column needs Refresh fields (edit the data source, Refresh fields bottom left, Apply). On Google's forum in 2024, one edit to one column's formula broke around 30 reports; Google staff's answer: refresh the fields whenever the Sheet changes.

From Google's Sheets connector, Refresh data source fields and Drive file limits pages, read 13 October 2026.

Google also says long beats wide: a row per day, not a column per day. The formatting basics are in the connection guide; the starter Sheet has these rules as a checklist.

When the numbers stop updating: what to check, in order

Check the filler first, then the structure, then the cache, then access. Of the eleven threads I read for this piece, four were a Sheet that changed and broke the report, four were a report not showing new data, and two were platform problems Google fixed.

On that shared-drive row: Google's connector page says "Data Studio does not support accessing files on Team Drive" (the old name for shared drives). The line has no date of its own; the page was updated 9 October 2026.

And sometimes it isn't you. In January 2025, Google staff confirmed on their forum that Sheets data sources were showing wrong data across reports, and fixed it the next day. Look there before rebuilding anything. To spot all this early, add a data health page to client reports; the starter's Health tab is built to feed one. Chapter 24 of my ebook, Data Storytelling in Data Studio, covers how a health page carries a report's caveats, and how to show the reader how old a number is.

Where to go next

• Connecting Google Sheets to Data Studio
• Data freshness, caching and refresh in Data Studio
• Automated client reporting in Data Studio
• When a direct connector beats the Sheet
• Annotations: the other job a Sheet does well

Frequently asked questions

How often does Data Studio refresh Google Sheets data?

A query Data Studio hasn't run before reads the Sheet. Repeated queries can come from memory for up to 15 minutes by default, adjustable to 1, 4 or 12 hours.

Do IMPORTRANGE formulas update if nobody opens the sheet?

Google documents updates while the file is open, not when it's closed. For unattended Sheets, use Apps Script or an add-on, which write values.

Why did my report break after I edited the Sheet?

A column was added, renamed or moved. Edit the data source and click Refresh fields.

How many rows can a Sheet hold for Data Studio?

The file limit is 20 million cells or 100 MB. Google documents no separate row limit for the Sheets connector.

Do I need a paid connector to automate a Sheet?

Not for Google data or any API or CSV you can reach by URL. For Meta, LinkedIn, TikTok and similar platforms, in practice, yes.