In this guide
- You can read a sheet through the Sheets API with a service account or from a published CSV.
- It is slow and rate-limited. Cache the result, or use it only for small, rarely changing tables.
- For larger or faster needs, use a database or a lookup table in the container.
Marketers like spreadsheets, and a spreadsheet of product margins or campaign names is a natural source for a server container to read. It works, with limits. This page explains the options and the cautions. See where to keep margins, cloud databases for server GTM and the POAS Data Feed.
Options
| Option | How | Pros | Cons |
|---|---|---|---|
| Sheets API with a service account | The container authenticates and reads a range | Private, accurate | Needs a service account, API calls on each lookup, rate limits. With Open Tracked, the Google Service Account power-up (Pro) lets you connect a key |
| Published CSV | Publish the sheet to the web as CSV and read the URL | Simple, no credentials | The data is public to anyone with the link |
| Copy into a lookup table | Paste values into a lookup variable | Fast, no dependency | Manual updates |
Connected in minutes
Add your domain, install the pixel, connect your ad platforms and watch the first events land.
Reading with the Sheets API
Create a service account
In Google Cloud, enable the Sheets API and create a key.
Share the sheet with the service account
By its e-mail address, with view access.
Attach the key to your server
In Open Tracked, the Google Service Account power-up (Pro plan) does this.
Call the API
A variable or tag requests the range as JSON.
Cache
Store the result for a few minutes, so each event does not trigger a call.
Cautions
Watch events arrive, live
A sample feed. In Open Tracked you see the same stream for your own store.
When to use something else
- More than a few hundred rows that change often: use a database.
- Very fast lookups: use an in-container table or a key-value store.
- Customer data: do not keep it in a sheet.
A realistic example
A store keeps a spreadsheet of about 200 products with their margins, which the finance team updates monthly. It wants its server container to use those margins when it computes profit for each order. Reading the sheet on every purchase would be slow, so the team loads it once through a service account, caches the table for ten minutes and falls back to a default margin if the sheet is unavailable. After a month it moves the margins into Open Tracked's Products & costs and uses the POAS Data Feed, since the sheet had become a point of failure when someone sorted it by accident. The sheet was a good way to start, and a poor way to stay.
Keep it healthy
Protect the range the container reads. Lock the header row and the columns, name the sheet clearly and tell the finance team that the container depends on it. Add a check that alerts you when a lookup falls back to the default more often than usual. Sheets are easy to edit by mistake, which is both their strength and their weakness.
Runs on your domain, anywhere
Your tracking runs on your own domain, so a visit from any country is handled the same way.
Frequently asked questions
Can a server container read Google Sheets?
Yes, through the Sheets API with a service account, or from a published CSV.
Is it fast?
No. Cache the result, or use a lookup table for speed.
Is a published CSV safe?
It is public to anyone with the link, so use it only for non-sensitive data.
How do I give the container access?
Share the sheet with a service account and attach its key. Open Tracked's Google Service Account power-up (Pro) does this.
What is a better alternative?
A database or an in-container lookup table, depending on size and change rate.
Do I need spreadsheets to read my results?
No. Once tracking is live, the Open Tracked analytics shows spend, revenue, ROAS and profit, and you can ask Ari, the built-in AI assistant, questions about your own numbers in plain words. Ari's allowance depends on your analytics plan.
Is Open Tracked different from Open Track or Open Space Track?
No. Open Tracked is the official name of our server-side tracking platform, and the website is opentracked.io. Many people search for it as Open Track, OpenTrack or Open Space Track. All of them lead to the same product: server-side Google Tag Manager hosting, first-party analytics and a CRM pipeline.
Fix your tracking at the source
Open Tracked hosts your server-side Tag Manager, keeps first-party cookies alive and shows what each ad is really worth.
The Open Tracked team
We build Open Tracked: server-side tracking hosting, analytics and the tools around them, for stores that spend on ads. Questions about this guide, or an idea for the next one?
Comments
Loading comments...
Integrations · 5 min read
