There's a special kind of misery reserved for agency account managers: the monthly reporting cycle. Log into Google Analytics. Screenshot the traffic chart. Switch to Meta Ads Manager. Export the campaign data. Open Mailchimp. Pull the email metrics. Paste everything into a slide deck. Add commentary. Repeat for 14 more clients.

At 1 hour per client, that's 15 hours. Every month. For a task that's 90% data retrieval and 10% insight. The numbers could pull themselves. Your team just hasn't built the pipe.

Here's how agencies use Google Sheets to automate client dashboards and reclaim those 15 hours.

The Architecture

Every agency dashboard system has three layers:

  • Data layer: Hidden tabs where Apps Script dumps raw data from APIs (Google Analytics, Meta Ads, Google Ads, Mailchimp/Klaviyo) on a daily or weekly schedule
  • Logic layer: QUERY formulas that filter, aggregate, and calculate KPIs from the raw data. This is where "show me last month's Meta ROAS for Client X" becomes a formula.
  • Presentation layer: Client-facing dashboard tabs with charts, KPI summaries, and conditional formatting. Each client gets their own tab.

One master spreadsheet runs the whole operation. 15 clients, 15 dashboard tabs, all pulling from shared data layers.

Building the Data Pipeline

Pulling Google Analytics Data

The Google Analytics Data API (GA4) integrates directly with Apps Script. A daily trigger pulls sessions, users, conversions, and revenue by source/medium.

Apps Script - GA4 Daily Data Pull function pullGA4Data() { const propertyId = '123456789'; const sheet = SpreadsheetApp.getActive() .getSheetByName('_ga_raw'); const request = AnalyticsData.Properties.runReport({ dateRanges: [{ startDate: '30daysAgo', endDate: 'yesterday' }], dimensions: [ { name: 'date' }, { name: 'sessionDefaultChannelGroup' } ], metrics: [ { name: 'sessions' }, { name: 'totalUsers' }, { name: 'conversions' }, { name: 'totalRevenue' } ] }, 'properties/' + propertyId); // Clear and write data sheet.clearContents(); sheet.appendRow(['Date','Channel','Sessions', 'Users','Conversions','Revenue']); request.rows.forEach(row => { sheet.appendRow([ row.dimensionValues[0].value, row.dimensionValues[1].value, row.metricValues[0].value, row.metricValues[1].value, row.metricValues[2].value, row.metricValues[3].value ]); }); }

Pulling Meta Ads Data

The Meta Marketing API gives you spend, impressions, clicks, conversions, and ROAS by campaign. Apps Script calls it on a schedule, dumps it into a raw tab.

Apps Script - Meta Ads Data Pull function pullMetaAds() { const token = PropertiesService.getScriptProperties() .getProperty('META_TOKEN'); const adAccountId = 'act_123456789'; const url = `https://graph.facebook.com/v18.0/` + `${adAccountId}/insights` + `?fields=campaign_name,spend,impressions,` + `clicks,actions,action_values` + `&date_preset=last_30d` + `&level=campaign` + `&access_token=${token}`; const res = UrlFetchApp.fetch(url); const data = JSON.parse(res.getContentText()).data; const sheet = SpreadsheetApp.getActive() .getSheetByName('_meta_raw'); sheet.clearContents(); sheet.appendRow(['Campaign','Spend','Impressions', 'Clicks','Conversions','Revenue']); data.forEach(row => { const conversions = (row.actions || []) .find(a => a.action_type === 'purchase'); const revenue = (row.action_values || []) .find(a => a.action_type === 'purchase'); sheet.appendRow([ row.campaign_name, parseFloat(row.spend), parseInt(row.impressions), parseInt(row.clicks), conversions ? parseInt(conversions.value) : 0, revenue ? parseFloat(revenue.value) : 0 ]); }); }

The Dashboard Layer

Once your data tabs are populated, QUERY formulas do the heavy lifting. Each client's dashboard tab pulls from the raw data, filtered by client or campaign.

Client-specific campaign performance =QUERY(_meta_raw!A:F, "SELECT A, SUM(B), SUM(C), SUM(D), SUM(E), SUM(F) WHERE A CONTAINS '"&B1&"' GROUP BY A ORDER BY SUM(B) DESC LABEL SUM(B) 'Spend', SUM(C) 'Impressions', SUM(D) 'Clicks', SUM(E) 'Conversions', SUM(F) 'Revenue'", 1) // ROAS calculation =IF(B3>0, F3/B3, 0) // Month-over-month change =(B3 - QUERY(_meta_prev!A:F, "SELECT SUM(B) WHERE A CONTAINS '"&B1&"' LABEL SUM(B) ''", 0)) / B3

KPI Summary Row

Top of each client tab: the 5 numbers every client cares about.

  • Total Spend: =SUM(B3:B)
  • Total Revenue: =SUM(F3:F)
  • Blended ROAS: =IF(SUM(B3:B)>0, SUM(F3:F)/SUM(B3:B), 0)
  • Cost Per Acquisition: =IF(SUM(E3:E)>0, SUM(B3:B)/SUM(E3:E), 0)
  • Total Conversions: =SUM(E3:E)

Add SPARKLINE for trend visualization and conditional formatting (green for ROAS above target, red below), and you've got a dashboard that updates itself.

Automated Report Delivery

The dashboard exists. Now automate the delivery. Apps Script sends each client their dashboard as a PDF or a direct link every week or month.

Apps Script - Weekly Client Report Email function sendClientReports() { const ss = SpreadsheetApp.getActive(); const clients = ss.getSheetByName('Client List') .getDataRange().getValues(); clients.forEach((client, i) => { if (i === 0) return; // skip header const name = client[0]; const email = client[1]; const tabName = client[2]; const tab = ss.getSheetByName(tabName); if (!tab) return; // Generate PDF of client tab const url = ss.getUrl().replace(/edit.*/, '') + `export?format=pdf&gid=${tab.getSheetId()}` + `&portrait=false&size=letter`; const pdf = UrlFetchApp.fetch(url, { headers: { Authorization: 'Bearer ' + ScriptApp.getOAuthToken() } }).getBlob().setName(`${name} Report.pdf`); GmailApp.sendEmail(email, `Weekly Performance Report | ${name}`, `Hi ${name},\n\nPlease find your weekly ` + `performance dashboard attached.\n\n` + `View live dashboard: ${ss.getUrl()}` + `#gid=${tab.getSheetId()}\n\n` + `Best,\nYour Agency`, { attachments: [pdf] } ); }); }

Budget Alerts

Don't wait until month-end to discover a client overspent. An Apps Script trigger checks daily spend against budget and sends alerts when thresholds are hit.

Spend alert formula // In the dashboard tab - pacing check =IF(SUM(B:B) > Budget!B2 * DAY(TODAY())/DAY(EOMONTH(TODAY(),0)), "OVER PACE", "On track") // Percentage of budget used =SUM(B:B) / Budget!B2

The Cost Comparison

What agencies typically pay for reporting tools:

  • Looker Studio (Data Studio): Free but limited on data blending, no scheduling, no PDF exports without workarounds
  • Tableau: $70/user/month. Powerful but expensive and complex
  • Databox: $72/month for 10 sources. Looks nice, limited customization
  • AgencyAnalytics: $12/client/month. Adds up fast at 15+ clients ($180+/month)
  • Google Sheets: $0 + one-time build cost

A fully automated Sheets reporting system for 15 clients costs $3,000–6,000 to build. AgencyAnalytics for the same 15 clients costs $2,160/year. Tableau for 3 users costs $2,520/year. The Sheets system pays for itself in 12–18 months and then costs nothing forever.

Scaling the System

The beautiful thing about this architecture: adding a new client takes 10 minutes.

  • Duplicate a dashboard template tab
  • Change the client name in cell B1 (which drives all the QUERY filters)
  • Add their API credentials to the data pull script
  • Add them to the Client List tab for automated email delivery

15 clients managed from one spreadsheet. 50 clients managed from one spreadsheet. The data layer scales, the formulas adapt, and the automation handles delivery.

The best agency dashboards aren't the prettiest. They're the ones that send themselves, update themselves, and free your team to do the work that actually matters: strategy.