How to Build a Weekly Report in Google Sheets That Updates Itself (No More Monday Morning Data Pulls)

Every Monday morning, someone on your team opens Google Sheets and starts copying numbers from HubSpot, GA4, QuickBooks, and three other tabs into a report that will be stale by Tuesday. Here is the short answer: you can build a Google Sheets report that refreshes itself on a schedule, emails your team automatically, and requires zero manual intervention: using either free Google Apps Script triggers or a third-party connector like brooked.io. The sections below walk you through every method, from simplest to most powerful.

The Problem: Your Team Is a Report Factory

"Every Monday morning for two years, I spent the first 90 minutes pulling data from six different tools. HubSpot for deals, GA4 for traffic, QuickBooks for revenue. By the time I finished, half the meeting was over." (Operations manager, SaaS company)

"I calculated it once. Eight hours a month on that one report. At $50 an hour, that's $1,300 a year, spent doing something a computer should do." (Growth analyst, e-commerce brand)

"The worst part isn't the time. It's that the data is wrong half the time. You miss a filter, copy the wrong cell, and now the exec team is making decisions on bad numbers." (Head of marketing, B2B startup)

These are not edge cases. They are the default state of reporting at companies that rely on Google Sheets. The tools that generate data (ad platforms, CRMs, databases, payment processors) do not talk to Google Sheets natively. Someone has to be the bridge, and that someone is usually the most analytical person on the team, doing the least analytical work.

The downstream consequences compound:

• Decisions get delayed. The report isn't ready until mid-Monday. The standup runs on last week's gut feel.
• Errors creep in silently. Manual copy-paste introduces transposition errors that nobody catches until a QBR.
• Institutional knowledge concentrates. Only one person knows which export to run, which filter to apply, which cell to update. They take a vacation. Chaos follows.
• Teams become reactive. When reporting consumes eight hours a month, there is no time for analysis, experimentation, or strategy.

The fix is not a better spreadsheet. It is a spreadsheet that feeds itself.

What "Automated" Actually Means

Before we get into methods, a critical distinction: a report that recalculates when you open it is not automated. It is just a live model.

Google Sheets functions like =TODAY(), =IMPORTDATA(), and pivot tables refresh when triggered by user interaction. They do not run on a schedule. They do not send emails. They do not pull fresh data from HubSpot at 6 AM so it is ready when your team arrives.

True automation has three components:

1. Scheduled data refresh, data is pulled from source systems on a time-based trigger, not on user action.
2. No human in the loop. The process runs whether or not anyone opens the sheet.
3. Delivery. The finished report reaches stakeholders automatically, by email, Slack, or dashboard.

Keep this definition in mind as you evaluate each method below.

The Solutions

Method 1: IMPORTRANGE + IMPORTDATA (Free, Limited)

Google Sheets has two native import functions that pull external data:

• **=IMPORTRANGE("spreadsheet_url", "range")**, pulls a cell range from another Google Sheet. Useful for consolidating data across multiple team sheets into one master report.
• **=IMPORTDATA("url")**, fetches a CSV or TSV from a public URL and parses it into cells.
• **=IMPORTFEED("url")**, imports RSS or Atom feeds.
• **=IMPORTHTML("url", "table", index)**, scrapes an HTML table or list from a public webpage.

How to use it: If your CRM exports a live CSV feed, or if your data already lives in other Google Sheets, IMPORTRANGE is a fast zero-code solution. In your master report sheet, enter =IMPORTRANGE("https://docs.google.com/spreadsheets/d/YOUR_SHEET_ID", "Sheet1!A1:Z500") and grant access when prompted.

Hard limits:

• IMPORTRANGE only works between Google Sheets, not from HubSpot, GA4, QuickBooks, or any other external system.
• IMPORTDATA only works with publicly accessible URLs: nothing behind authentication.
• These functions refresh on sheet open, not on a schedule. If nobody opens the sheet Monday morning, the data is not fresh.
• Google throttles IMPORT functions heavily. Complex sheets with many IMPORTRANGE calls become slow and unreliable.

Best for: Teams that already store operational data in Google Sheets and need to consolidate it into a reporting sheet.

Method 2: Google Apps Script with Scheduled Triggers (Free, Code Required)

Google Apps Script is a JavaScript-based scripting environment built into Google Workspace. It can call external APIs, write data into Sheets, and (critically) run on a time-based trigger without any user interaction.

Setting up a time-based trigger:

1. Open your Google Sheet. Go to Extensions → Apps Script.
2. Write a function that fetches data from an API and writes it to the sheet.
3. In the Apps Script editor, go to Triggers (clock icon) → Add Trigger.
4. Set the trigger to run on a time-driven schedule, for example, every Monday at 6 AM.

Basic example: fetching data from a REST API:

function fetchWeeklyData() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data");
  const url = "https://api.yourcrm.com/v1/deals?status=closed&period=last_week";
  const options = {
    method: "GET",
    headers: {
      Authorization: "Bearer YOUR_API_TOKEN"
    }
  };
  const response = UrlFetchApp.fetch(url, options);
  const data = JSON.parse(response.getContentText());

  // Clear old data and write new rows
  sheet.clearContents();
  data.deals.forEach((deal, i) => {
    sheet.getRange(i + 1, 1, 1, 3).setValues([[deal.name, deal.value, deal.close_date]]);
  });
}

Trigger configuration: Set this function to run weekly on Monday between 6 AM and 7 AM. Apps Script will execute it server-side with no browser required.

Real limits to know:

• Apps Script has a 6-minute maximum execution time per run. Complex reports that pull from multiple APIs may hit this wall.
• The free tier allows 90 minutes of total script runtime per day across all scripts.
• API credentials are stored in plain script properties, not ideal for production security.
• Error handling is minimal. If the API goes down or changes its schema, the script fails silently unless you build in alerting.
• Maintaining scripts across API version changes, authentication rotations, and schema updates requires ongoing developer time.

Best for: Developers or technical analysts who are comfortable with JavaScript and want a free, flexible solution for pulling data from one or two API sources.

Method 3: Scheduled Email Delivery with Apps Script

Once your data refresh runs, you can add a second function (or extend the first) to email the report to your team automatically.

function emailWeeklyReport() {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const url = spreadsheet.getUrl();

  const recipients = ["[email protected]", "[email protected]"];
  const subject = "Weekly Sales Report - " + new Date().toDateString();
  const body = `Your weekly report is ready.\n\nView it here: ${url}\n\nThis report was generated automatically.`;

  GmailApp.sendEmail(recipients.join(","), subject, body);
}

For a PDF attachment, use DriveApp to export the sheet as a PDF and attach it to the email:

function emailReportAsPdf() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const ssId = ss.getId();
  const sheetId = ss.getSheetByName("Report").getSheetId();

  const exportUrl = `https://docs.google.com/spreadsheets/d/${ssId}/export?format=pdf&gid=${sheetId}&portrait=true&fitw=true`;

  const token = ScriptApp.getOAuthToken();
  const response = UrlFetchApp.fetch(exportUrl, {
    headers: { Authorization: `Bearer ${token}` }
  });

  const pdf = response.getBlob().setName("Weekly_Report.pdf");

  GmailApp.sendEmail(
    "[email protected]",
    "Weekly Report - " + Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "MMM d, yyyy"),
    "Please find this week's report attached.",
    { attachments: [pdf] }
  );
}

Chain this with your data fetch trigger, or create a separate trigger that runs 10 minutes after the data refresh completes.

Method 4: Google Sheets Add-ons and Data Connectors

The Google Workspace Marketplace has dozens of add-ons that connect specific data sources to Sheets without code. Common options include:

How they work: You install the add-on, authenticate your data sources via OAuth or API key, configure which data to pull and what format to write it in, and set a refresh schedule. The add-on handles authentication, API pagination, rate limits, and schema mapping.

Trade-offs: Add-ons are per-source. Supermetrics is excellent for marketing data but does not connect to your database. Coefficient covers Salesforce well but has limited support for custom APIs. If your report spans five data sources, you may need multiple add-ons, and multiple subscription costs.

Method 5: Full Automation with brooked.io or Supermetrics

For teams that need data from multiple source categories (CRM + marketing + finance + database) in a single automated report, a dedicated data integration platform is the right tool.

brooked.io is built specifically for this use case. It connects to your data sources (HubSpot, GA4, QuickBooks, MySQL, PostgreSQL, Facebook Ads, Stripe, and more), transforms the data into the shape your report expects, writes it directly into Google Sheets on a schedule you define, and optionally triggers the email delivery.

The workflow:

1. Connect your sources in brooked.io (OAuth for most SaaS tools, connection string for databases).
2. Map fields from each source to columns in your Google Sheet.
3. Set the refresh schedule, for example, every Monday at 6 AM UTC.
4. Configure email delivery if desired.

No code. No maintenance. No 6-minute execution limit. When HubSpot changes their API, brooked.io's connectors update. Your report keeps running.

Supermetrics is the established market leader for marketing data specifically, with 150+ connectors focused on ad platforms, analytics, and SEO tools. It is the right choice if your report is marketing-only. For cross-functional reports that span marketing, sales, finance, and operations data, brooked.io's broader connector library is a better fit.

Method Comparison Table

Concrete Example: Weekly Sales Dashboard

Here is a specific build: a weekly sales report that pulls HubSpot deal data, GA4 traffic, and QuickBooks revenue into one dashboard that refreshes every Monday at 6 AM and emails your team.

Sheet structure:

With Apps Script (free path):

1. Write three functions: fetchHubSpotDeals(), fetchGA4Data(), fetchQuickBooksRevenue(). Each calls the respective API and writes to its tab.
2. Write a master function runWeeklyReport() that calls all three in sequence, then calls emailReport().
3. Set a weekly time trigger on runWeeklyReport() for Monday 6–7 AM.
4. Build your Dashboard tab using =QUERY(), =SUMIF(), and =VLOOKUP() formulas against the data tabs. These recalculate automatically when the data updates.

With brooked.io (no-code path):

1. Connect HubSpot, GA4, and QuickBooks in brooked.io.
2. Create three data flows, each targeting the corresponding Sheet tab.
3. Set all three to run Monday at 6 AM.
4. Configure email delivery with the Sheet link or PDF.

The Dashboard tab formulas work identically in both paths. The difference is who runs the data refresh.

Common Pitfalls and Troubleshooting

The trigger runs but the sheet does not update. Check the Apps Script execution log (View → Executions). The most common causes are expired OAuth tokens (re-authorize the script), API rate limits (add exponential backoff), or a response schema change (the API returned a field name that changed).

The report emails the wrong data. If your email trigger fires before the data refresh completes, the email will contain stale data. Add a 5-minute buffer between the refresh trigger and the email trigger, or chain the email call directly after the refresh function rather than using a separate trigger.

IMPORTRANGE shows "#REF!" errors. The source sheet owner needs to grant access explicitly. Open the source sheet, go to the IMPORTRANGE cell, and click "Allow access." This must be done once per sheet. If the source sheet moves or is deleted, IMPORTRANGE breaks permanently.

Apps Script hits the 6-minute execution limit. Split your data pull into multiple smaller functions with separate triggers staggered 10 minutes apart. Alternatively, use incremental fetching: pull only records updated since the last run rather than full dataset exports.

Data looks right in the Sheet but wrong in the Dashboard tab. Formula errors in summary tabs often stem from the data tab having extra header rows, inconsistent date formats, or text values where numbers are expected. Use =IFERROR() wrappers and validate your data tab structure after every API change.

The report runs on weekday mornings but your team is in a different time zone. Apps Script triggers run in the script owner's time zone, not UTC. Check your script time zone under Project Settings and adjust your trigger accordingly. brooked.io and most add-ons let you set the time zone explicitly.

The Bottom Line

A weekly report that updates itself is not a luxury. It is table stakes for any team that wants to make decisions on current data. The path you take depends on your technical resources and the number of data sources involved:

• One or two sources, a developer on staff: Google Apps Script with time triggers is free and flexible. Build it once, add error notifications, and maintain it as APIs evolve.
• Marketing-focused report: Supermetrics or Two Minute Reports get you to automated refresh in an afternoon without code.
• Cross-functional report spanning CRM, analytics, finance, and ops: brooked.io is the cleanest path: connect all your sources in one place, schedule the refresh, deliver the report, and free your team from Monday morning data duty permanently.

The $1,300/year in analyst time you save in year one is not the real return. The real return is the decisions that get made faster, on better data, by people who are analyzing instead of assembling.

Get Your Report Running on Autopilot

brooked.io connects to HubSpot, GA4, QuickBooks, Stripe, Facebook Ads, PostgreSQL, and 100+ other sources. Set your refresh schedule, point it at your Google Sheet, and your Monday morning report writes itself, no code, no maintenance, no 6 AM alarm.

Start your free trial at brooked.io →

Frequently asked questions