Google Business Profile Insights works fine for one location. For a portfolio of dozens spread across different clients, different cities, and different industries, it's a fragmented mess. Here's how we built a Google Apps Script that pulls 8 performance metrics daily from the GBP API into per-client Google Sheets, then unified everything in Looker Studio so the client could see all stores as one entity and drill into each individually, while guaranteeing no client could ever see another's data.
8 Metrics
Per Location, Daily
365 Days
Rolling Window
Per-Client
Data Isolation
The Problem
This client managed a portfolio of Google Business Profile locations across multiple businesses: different brands, different cities, different industries. Each location had its own GBP Insights dashboard, and logging into each one individually to check performance was unsustainable. They needed a single dashboard showing every location's metrics side by side, with the ability to roll up all locations into one entity-level view.
The obvious solution, dumping all the data into one Google Sheet and connecting it to Looker Studio, had a fatal flaw. If any individual business owner looked at the source spreadsheet, they would see every other client's performance data. That was unacceptable. The data had to be unified for reporting but isolated at the storage layer.
On top of the privacy constraint, there were the usual GBP API challenges: paginated location listings across multiple business accounts, date objects that Looker Studio can't parse natively, and the need for a rolling 365-day window that self-maintained without manual intervention.
"The privacy requirement flipped the architecture. Instead of one big data pool, we needed many small ones, each locked to its own client, and a reporting layer that stitched them together without ever letting the raw data touch."
Our Solution
The architecture had three layers: a Google Apps Script that pulled data from the GBP API and wrote it to individual client spreadsheets, a file-management system that kept every client's data in its own isolated Google Sheet inside a dedicated Drive folder, and a Looker Studio configuration that connected to each spreadsheet as a separate data source, giving the client both a unified cross-location view and per-location drill-down, while guaranteeing that opening any individual spreadsheet only showed one client's numbers.
The script starts by reading a "Clients" sheet in the master spreadsheet. Column A holds Location IDs, one per row starting at row 2. Only locations found in this list get their metrics pulled. This means adding or removing a client is a single-row edit in a spreadsheet, not a code change. It also means the script never accidentally pulls data for locations that aren't supposed to be in the dashboard.
The script calls the Google Business Account Management API to retrieve all business accounts associated with the credentials. For each account, it makes a paginated API request to list all physical locations, fetching up to 100 per request and continuing until every page is retrieved. This means new locations added to any account are automatically discovered on the next run without anyone needing to update a configuration.
This is the step that solves the privacy constraint. For every discovered location, the script checks a "Client_Mapping" sheet in the master spreadsheet. If the location already has a Google Sheet ID logged there, it opens that existing sheet. If not, it creates a new Google Sheet named GBP Data - [Business Name], moves it into a dedicated Google Drive folder, and logs the new sheet's ID and URL in Client_Mapping.
Within each client spreadsheet, the script ensures a tab named GBP_Data exists with five column headers: Date, Location ID, Location Name, Metric Name, and Value. Every client's data lives in its own spreadsheet, in its own Drive folder. If a client opens their spreadsheet, they see only their own locations and their own metrics. Nothing else.
For every active location, the script calls the Business Profile Performance API and requests eight metrics:
The date range is a strict 365-day window ending on the current day. Every run overwrites the previous year's data for each location, so if Google backfills or corrects historical metrics, the sheet automatically reflects the updated numbers without accumulating duplicates.
The raw data from the GBP API needs work before Looker Studio can use it reliably. The script handles three cleaning steps on every run:
@) so Looker Studio never encounters a formatting discrepancy.
BUSINESS_IMPRESSIONS_DESKTOP_MAPS are mapped to human-readable names like "Desktop Maps" using a lookup object, so the dashboard labels are immediately understandable.
Once every client spreadsheet is populated and self-maintaining, Looker Studio connects to each one as a separate data source. The dashboard blends them into a single unified view of total impressions across all locations, total calls, and total direction requests, with filters and drill-downs that let the client isolate any individual location or group of locations for comparison. The client sees everything together. But if any individual business owner opens their source spreadsheet, they see only their own data.
Data Isolation Without Sacrificing Unified Reporting
The obvious architecture, one big Google Sheet with every location's data, is simpler to build. One script, one data source, one Looker Studio connection. But it fails the privacy test the moment any client asks to see the raw data behind their dashboard.
By giving each client their own spreadsheet in a dedicated Drive folder, the system guarantees data isolation at the storage layer. The Client_Mapping sheet in the master spreadsheet is the only place where all location-to-sheet relationships are recorded, and that sheet lives in a separate, access-controlled master spreadsheet that clients never see.
Looker Studio handles the unification. Because it can connect to multiple Google Sheets as separate data sources and blend them in a single report, the client gets the unified cross-location view they need without the raw data ever being pooled in one place. It's slightly more work to set up, since each new client spreadsheet needs to be added as a data source in Looker Studio, but the privacy guarantee is worth the extra configuration step.
The Results
Unified
Cross-Location View
All locations rolled up into one entity-level dashboard showing total impressions, clicks, calls, and directions across every store.
Per-Client
Data Isolation
Every client's data lives in its own Google Sheet. Open it and you see only that client's locations and metrics.
Self-Healing
Rolling 365-Day Window
Every run overwrites the trailing year. Backfilled or corrected Google data updates automatically with no duplicates or gaps.
This architecture applies to any business managing multiple Google Business Profile locations, whether they're all your own stores or you're an agency or franchise operator managing locations for multiple clients.
You manage multiple GBP locations and logging into each one individually is eating hours every week
You need to compare location performance: which store is winning on calls, which city drives the most direction requests
You can't pool all client data into one spreadsheet because each client must only see their own numbers
You're an agency or franchise operator and need to report GBP performance to multiple stakeholders
You're manually exporting GBP Insights data and building reports in Excel, and it's not sustainable
You want to track GBP performance trends over time, not just spot-check today's numbers
If you have multiple GBP locations and the data is scattered across individual Insights dashboards, we can build this. The script connects to the GBP API, the sheets isolate each client's data, and Looker Studio unifies the reporting. The only prerequisite is that the locations exist in a Google Business Account the script can authenticate against.
GBP Insights works for one location at a time. If you have 5, 20, or 50 locations, you're logging into each one separately, looking at different date ranges, and trying to compare numbers in your head or in a manual spreadsheet. It doesn't give you a unified view, it doesn't let you compare locations side by side, and it doesn't maintain a rolling historical record you can trend over time. The built-in dashboard is fine for the manager of a single store. For anyone managing a portfolio, it's a productivity bottleneck.
No. The script discovers all locations under your business accounts automatically on every run via the Account Management API. If you want the new location to appear in the dashboard, you add its Location ID to the "Clients" sheet in the master spreadsheet. One cell. The next time the script runs, it creates a new Google Sheet for that location (if one doesn't exist), pulls its 365 days of metrics, and the data is available in Looker Studio as soon as you connect the new sheet as a data source.
Two reasons. First, Google sometimes backfills or corrects historical GBP metrics, so a rolling overwrite ensures your data always matches what Google currently reports, rather than preserving stale numbers from an earlier pull. Second, Google Sheets has a cell limit (10 million cells), and unbounded accumulation would eventually hit it. A 365-day window with 8 metrics per location per day keeps the dataset lean and fast, and for most businesses a year of GBP data is more than enough to identify trends, seasonality, and performance patterns.
The GBP API returns dates as objects with separate year, month, and day fields. If you write those directly to Google Sheets, the sheet tries to interpret them as dates. Depending on the spreadsheet's locale settings, it might format them as MM/DD/YYYY, DD/MM/YYYY, or something else entirely. Looker Studio then reads those inconsistently formatted values and either misinterprets them or throws parsing errors. The fix is two-fold: the script converts every date to a strict YYYY-MM-DD string before writing, and it explicitly formats the entire Date column as Plain Text (@) so Google Sheets never tries to auto-format it. Looker Studio reads YYYY-MM-DD strings reliably regardless of locale. That's why the extra step exists.
The folder ID is hardcoded in the script (line 290). If you move the client spreadsheets to a new Drive folder, you need to update that one line. It's a 30-second change: replace the old folder ID string with the new one, save the script, and the next run will create new client sheets in the correct location. Existing sheets don't move automatically (Google Apps Script can't relocate files that already exist), but new client sheets will be created in the new folder from that point forward.
The architecture of per-client Google Sheets plus Looker Studio unification can work with any platform that exposes a metrics API. The script would need a separate module for each platform's API (authentication, endpoint structure, metric names), but the file-management layer (Client_Mapping, per-client sheet creation, date sanitization, duplicate prevention) is reusable. GBP is the most common starting point because it typically drives the highest volume of local impressions and actions, but the same pattern applies to any local listing platform with API access.
If you're logging into individual GBP Insights dashboards or manually exporting data into spreadsheets, that's time you're never getting back. We can build the automated pipeline and the unified dashboard. You open one report. Everything's there.