Looker Studio Sheets connectors cache aggressively. A dedicated staging tab you rewrite on a schedule is more reliable than pointing reports at a volatile import sheet.
This script copies OrdersRaw into LookerStaging, formats OrderDate as ISO strings, and clears before setValues so column counts stay aligned.
RefreshLog stores RefreshedAt, RowCount, and who/what triggered the run — useful when someone asks whether the dashboard is stale.
Pair with a 15–60 minute time-driven trigger; in Looker Studio, set data freshness to match or use manual refresh after large loads.
Need this built? Hire a Google Apps Script developer →
Sheet / project setup
| Sheet | Column | Purpose |
|---|---|---|
| OrdersRaw | A–n | Source import (any width) |
| OrdersRaw | OrderDate | Date column normalized to yyyy-MM-dd |
| LookerStaging | A–n | Connector data source (rewritten) |
| RefreshLog | A RefreshedAt | Audit timestamp |
| RefreshLog | B RowCount | Rows written excluding header |
| RefreshLog | C TriggeredBy | User email or trigger |
What this script does
refreshLookerStaging() rebuilds LookerStaging from OrdersRaw and appends an audit row to RefreshLog.
Date coercion avoids connector timezone shifts when raw cells are Date objects.
Prerequisites
A spreadsheet Looker Studio can read, plus OrdersRaw and LookerStaging tabs.
- Connector pointed at LookerStaging
- Shared access for the viewer account
- Header named OrderDate if you want date formatting
Walkthrough
Read the raw matrix, normalize dates, clear staging, write the matrix in one setValues call, flush, then log.
Edge cases
If OrdersRaw gains columns, staging width changes — update Looker Studio field list after schema changes.
- clearContents removes formatting on staging
- Empty raw sheet throws instead of publishing blank
How to test
Change one OrdersRaw amount, run the function, refresh the Looker Studio report, confirm the metric moved.
Hardening for production
Wrap in try/catch and email on failure; use LockService if overlapping triggers are possible.
Variations
Filter staging to last 90 days, or write to an IMPORTRANGE hub spreadsheet for many workbooks.
Full code: refreshLookerStaging()
Run refreshLookerStaging() after OrdersRaw updates. Point Looker Studio at LookerStaging, not OrdersRaw.
/**
* Refresh an extract-style staging sheet that Looker Studio reads,
* then bump a RefreshLog timestamp the connector / cache can notice.
*/
const STAGING = "LookerStaging";
const RAW = "OrdersRaw";
const LOG = "RefreshLog";
function refreshLookerStaging() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const raw = ss.getSheetByName(RAW);
const staging = ss.getSheetByName(STAGING);
const log = ss.getSheetByName(LOG) || ss.insertSheet(LOG);
if (!raw || !staging) throw new Error("Need OrdersRaw and LookerStaging sheets");
const values = raw.getDataRange().getValues();
if (values.length < 2) throw new Error("OrdersRaw is empty");
// Normalize: keep header, coerce dates to ISO strings for connector stability
const header = values[0];
const dateCol = header.indexOf("OrderDate");
const out = [header];
for (let i = 1; i < values.length; i++) {
const row = values[i].slice();
if (dateCol >= 0 && row[dateCol] instanceof Date) {
row[dateCol] = Utilities.formatDate(row[dateCol], Session.getScriptTimeZone(), "yyyy-MM-dd");
}
out.push(row);
}
staging.clearContents();
staging.getRange(1, 1, out.length, out[0].length).setValues(out);
SpreadsheetApp.flush();
if (log.getLastRow() === 0) log.appendRow(["RefreshedAt", "RowCount", "TriggeredBy"]);
log.appendRow([new Date(), out.length - 1, Session.getActiveUser().getEmail() || "trigger"]);
Logger.log("LookerStaging refreshed with %s rows", out.length - 1);
}- Line 5: Destination tab the Looker Studio connector should use.
- Line 16: Reads the full used range of OrdersRaw including headers.
- Line 21: Finds the date column by header name.
- Line 26: Writes stable ISO dates for connector consistency.
- Line 31: Wipes old staging rows before writing the new matrix.
- Line 32: Batch-writes staging in one call.
- Line 35: Records refresh metadata for debugging stale dashboards.
Deploy this example
- 01
Open Apps Script
In the bound spreadsheet: Extensions → Apps Script. For standalone projects, create one at script.google.com and link your Sheet by ID.
- 02
Paste and save
Add a .gs file, paste the code below, rename constants at the top (sheet names, column letters, API property keys), then save.
- 03
Authorize once
Run the main function from the editor. Accept OAuth scopes when prompted — triggers cannot run until authorization succeeds once.
- 04
Add the trigger
Triggers → Add trigger → choose the handler function and event (time-driven, on edit, or on form submit). Delete test triggers before production.
Before you run: Looker staging
- 1OrdersRaw and LookerStaging sheets exist
- 2Looker Studio data source points at LookerStaging
- 3OrderDate header matches if you rely on date formatting
- 4Trigger account can edit the spreadsheet
- 5Data freshness in Looker Studio is not longer than your trigger cadence
- 6Test refresh on a copy before production
Frequently asked questions
This pattern refreshes the Sheet extract the connector reads. Looker Studio then picks up changes on its next data fetch or manual refresh.
Raw imports often reshuffle columns mid-day. Staging gives you a stable schema and a place to normalize types.
Match business need — every 15–60 minutes is common. Avoid overlapping runs; use LockService if cadence is aggressive.
It clears values only. Embedded charts on that sheet usually survive, but verify once.
The instanceof Date check skips formatting; leave text as-is or parse explicitly if formats vary.
Yes — loop sheet name pairs or call this function once per extract with different constants.
It distinguishes editor runs from trigger runs when debugging who published stale data.