If you have ever wanted to bridge the gap between your GTM implementation and your reporting workflow in Google Sheets you are not alone. Google Tag Manager is brilliant at firing tags and capturing client-side activity, yet extracting a tidy list of what sits in every container is anything but plug and play. Apps Script makes the link you need.
This guide shows how to pull metadata from every GTM container you can access into one Google Sheet. The script calls the Tag Manager API, no GA4 or BigQuery required, and writes tags, triggers and variables to separate sheets so you can audit or document many containers without repetitive clicks.
You will need a working GTM setup, access to the Tag Manager API and some fluency with Apps Script. If you are comfortable reading JSON, deploying scripts and navigating the GTM interface you are in the right place.
Step 1 Enable the Google Tag Manager API and create OAuth credentials
Open the Google Cloud Console.
Create a new project or choose an existing one.
APIs & Services ▸ Library and enable Tag Manager API. Go to APIs & Services ▸ Credentials and add an OAuth 2.0 Client ID. Choose Web application.
Set the authorised redirect URI to https://script.google.com/oauthcallback
Copy the client ID and client secret. You will paste both in the script.
Step 2 Create the Apps Script project
Open a new Google Sheet.
Choose Extensions ▸ Apps Script.
Delete any starter code, add a file named GTM_Audit.gs and paste the codeblock below.
Add the OAuth2 library before you save:
Click Libraries ▸ + Add a library.
Paste the script ID 1B7Xn2N4wweGzz8Ax-R4T0ACZLk8vP8bbuVnRzOKMzEJtSxNy3NnG8Y0v and press Look up.
Select the latest numbered version, click Add then Save Project.
Enable the Tag Manager API in Advanced Google Services:
Open the left-hand panel labelled Services.
Find Tag Manager API in the list and switch it on.
Now, paste the following script into a .gs file in Apps Script:
/* ---------------------------------------------------------------
1 OAuth2 setup – requires the OAuth2 library above
--------------------------------------------------------------- */
const CLIENT_ID = 'YOUR_CLIENT_ID';
const CLIENT_SECRET = 'YOUR_CLIENT_SECRET';
function getService() {
return OAuth2.createService('GTM')
.setAuthorizationBaseUrl('https://accounts.google.com/o/oauth2/auth')
.setTokenUrl('https://oauth2.googleapis.com/token')
.setClientId(CLIENT_ID)
.setClientSecret(CLIENT_SECRET)
.setCallbackFunction('authCallback')
.setPropertyStore(PropertiesService.getUserProperties())
.setScope('https://www.googleapis.com/auth/tagmanager.readonly');
}
function authCallback(request) {
const service = getService();
const ok = service.handleCallback(request);
return HtmlService.createHtmlOutput(
ok ? 'Authorisation successful. You can close this tab.' : 'Authorisation denied.'
);
}
function showSidebar() {
const service = getService();
if (!service.hasAccess()) {
const url = service.getAuthorizationUrl();
const html = HtmlService.createHtmlOutput(
'<a href=\"' + url + '\" target=\"_blank\">Click here to authorise</a>'
);
SpreadsheetApp.getUi().showSidebar(html);
} else {
SpreadsheetApp.getUi().alert('You are already authorised');
}
}
/* ---------------------------------------------------------------
2 GTM helper functions
--------------------------------------------------------------- */
function listAccounts() {
const svc = getService();
const res = UrlFetchApp.fetch(
'https://www.googleapis.com/tagmanager/v2/accounts',
{headers:{Authorization:'Bearer ' + svc.getAccessToken()}}
);
return (JSON.parse(res).account) || [];
}
function listContainers(accountId) {
const svc = getService();
const res = UrlFetchApp.fetch(
'https://www.googleapis.com/tagmanager/v2/accounts/' + accountId + '/containers',
{headers:{Authorization:'Bearer ' + svc.getAccessToken()}}
);
return (JSON.parse(res).container) || [];
}
function listWorkspaceId(accountId, containerId) {
const svc = getService();
const res = UrlFetchApp.fetch(
'https://www.googleapis.com/tagmanager/v2/accounts/' + accountId + '/containers/' + containerId + '/workspaces',
{headers:{Authorization:'Bearer ' + svc.getAccessToken()}}
);
const ws = JSON.parse(res).workspace;
return ws && ws.length ? ws[0].workspaceId : null;
}
function listTags(accountId, containerId, workspaceId) {
const svc = getService();
const res = UrlFetchApp.fetch(
'https://www.googleapis.com/tagmanager/v2/accounts/' + accountId + '/containers/' + containerId + '/workspaces/' + workspaceId + '/tags',
{headers:{Authorization:'Bearer ' + svc.getAccessToken()}}
);
return (JSON.parse(res).tag) || [];
}
function listVariables(accountId, containerId, workspaceId) {
const svc = getService();
const res = UrlFetchApp.fetch(
'https://www.googleapis.com/tagmanager/v2/accounts/' + accountId + '/containers/' + containerId + '/workspaces/' + workspaceId + '/variables',
{headers:{Authorization:'Bearer ' + svc.getAccessToken()}}
);
return (JSON.parse(res).variable) || [];
}
/* ---------------------------------------------------------------
3 Write one container’s data to Sheets
--------------------------------------------------------------- */
function exportGTMConfig(accountId, container) {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const workspaceId = listWorkspaceId(accountId, container.containerId);
if (!workspaceId) return;
/* Tags */
const tags = listTags(accountId, container.containerId, workspaceId);
if (tags.length) {
const tagSheet = ss.getSheetByName('Tags_' + container.publicId)
|| ss.insertSheet('Tags_' + container.publicId);
tagSheet.clear();
tagSheet.appendRow(['Name','Type','Firing Triggers','Blocking Triggers']);
tags.forEach(t => {
tagSheet.appendRow([
t.name,
t.type,
(t.firingTriggerId || []).join(', '),
(t.blockingTriggerId || []).join(', ')
]);
});
}
/* Variables */
const vars = listVariables(accountId, container.containerId, workspaceId);
if (vars.length) {
const varSheet = ss.getSheetByName('Variables_' + container.publicId)
|| ss.insertSheet('Variables_' + container.publicId);
varSheet.clear();
varSheet.appendRow(['Name','Type']);
vars.forEach(v => varSheet.appendRow([v.name,v.type]));
}
}
/* ---------------------------------------------------------------
4 Batch exporter – run after authorisation
--------------------------------------------------------------- */
function exportAllContainers() {
const accounts = listAccounts();
accounts.forEach(acc => {
const containers = listContainers(acc.accountId);
containers.forEach(c => exportGTMConfig(acc.accountId, c));
});
SpreadsheetApp.getUi().alert('All containers exported');
}
How the script works
When you launch the script, it first takes care of authentication. Apps Script presents the familiar Google consent screen, exchanges the one-time code for an OAuth token, and stores that token so the script can make read-only calls to the Google Tag Manager API on your behalf. Once this handshake is complete, the script can see every GTM account and container your Google account can access.
>With access secured, the script moves on to data collection. It walks through each account, drills into every container’s default workspace, and asks the API for full lists of tags, triggers, and variables. The responses arrive as structured JSON, which the script holds in memory long enough to consolidate each container’s configuration—names, IDs, types, and linked trigger references—into tidy arrays.
Finally, the script writes everything to your spreadsheet. For every container it creates (or refreshes) two dedicated tabs—one for tags and one for variables—naming each tab with the container’s public ID so nothing collides. Rows are laid out consistently, making it easy to filter or pivot the data. In a single run you end up with a clear, searchable audit of every GTM setup you have permission to view, all without ever opening the GTM interface.
Run order
With the code saved run showSidebar.A sidebar opens, click the link, complete the Google consent screen and close the tab.
Back in the editor rerun showSidebar to check access is granted.
Run exportAllContainers. The script loops through every account and container the current Google account can view and adds a sheet for the tags and another for the variables of each container.
Final notes and use cases
The Google account that runs the Sheet must have at least Read permission in every GTM account you want to audit.
The Tag Manager API allows ten thousand daily requests which is more than enough for audits but the per-minute rate cap can throttle very large estates. If you see 429 Too Many Requests wait a moment and rerun.
Add a time driven trigger if you would like a nightly backup of all containers. Go to Triggers ▸ Add Trigger and call exportAllContainers every day at a quiet hour.
This workflow reads data only. The API also supports writes but pushing changes without safeguards can overwrite a live container. Test in a staging workspace before you attempt writes.
With one Sheet you can now audit containers for multiple clients, freeze a snapshot before major changes, analyse tag usage or hand over clear documentation to another team.