Created
August 4, 2020 08:40
-
-
Save sahava/d032099a84c0eeb31709204172e7707c to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// Parse the IDs from the first four rows of the sheet | |
function getIds() { | |
const sheet = SpreadsheetApp.getActiveSheet(); | |
const ids = { | |
accountId: sheet.getRange('B1').getValue().toString(), | |
containerId: sheet.getRange('B2').getValue().toString(), | |
workspaceId: sheet.getRange('B3').getValue().toString(), | |
variableId: sheet.getRange('B4').getValue().toString() | |
}; | |
if (ids.workspaceId.toLowerCase() === 'default') ids.workspaceId = getDefaultWorkspaceId(ids.accountId, ids.containerId); | |
return ids; | |
} | |
// Get ID of workspace named "Default Workspace" | |
function getDefaultWorkspaceId(aid, cid) { | |
const workspaces = TagManager.Accounts.Containers.Workspaces.list(`accounts/${aid}/containers/${cid}`, {fields: 'workspace(name,workspaceId)'}).workspace; | |
const defaultWorkspace = workspaces.filter(w => w.name === 'Default Workspace').map(w => w.workspaceId).shift(); | |
return defaultWorkspace; | |
} | |
// Get the resource for the given variable ID | |
function getLookupTable(aid, cid, wid, vid) { | |
const lookupTable = TagManager.Accounts.Containers.Workspaces.Variables.get(`accounts/${aid}/containers/${cid}/workspaces/${wid}/variables/${vid}`); | |
return lookupTable; | |
} | |
// Update the variable using GTM API and the data in the sheet | |
function sendData() { | |
const {accountId, containerId, workspaceId, variableId} = getIds(); | |
const sheet = SpreadsheetApp.getActiveSheet(); | |
const source = JSON.parse(sheet.getRange('Z1').getValue()); | |
const rangeLength = sheet.getRange('A7:A').getValues().filter(String).length + 6; | |
const lookupMap = sheet.getRange(`A7:B${rangeLength}`).getValues(); | |
// Map key-value pairs in sheet to format required by GTM API | |
const list = lookupMap.map(pair => ({ | |
type: 'map', | |
map: [{ | |
type: 'template', | |
key: 'key', | |
value: pair[0].toString() | |
},{ | |
type: 'template', | |
key: 'value', | |
value: pair[1].toString() | |
}] | |
})); | |
// Update the source JSON with the new list | |
source.parameter.forEach(param => { | |
if (param.key === 'map') param.list = list; | |
}); | |
// Update the variable in the workspace | |
try { | |
TagManager.Accounts.Containers.Workspaces.Variables.update( | |
source, | |
`accounts/${accountId}/containers/${containerId}/workspaces/${workspaceId}/variables/${variableId}` | |
); | |
} catch(e) { | |
SpreadsheetApp.getUi().alert('Failed to update the variable.\n' + e); | |
} | |
} | |
// Populate the data from the sheet | |
function populateSheet() { | |
const sheet = SpreadsheetApp.getActiveSheet(); | |
const {accountId, containerId, workspaceId, variableId} = getIds(); | |
const lookupTable = getLookupTable(accountId, containerId, workspaceId, variableId); | |
// Write the lookup table source in cell Z1 | |
sheet.getRange('Z1').setValue(JSON.stringify(lookupTable)); | |
const table = lookupTable.parameter.filter(p => p.type === 'list').shift().list.map(p => p.map); | |
const map = []; | |
let key, value; | |
// Build key-value pairs of lookup table for insertion into sheet cells | |
table.forEach(t => { | |
t.forEach(m => { | |
if (m.key === 'key') { key = m.value; } | |
if (m.key === 'value') { | |
value = m.value; | |
map.push([key, value.toString()]); | |
} | |
}); | |
}); | |
// Add headers | |
const headers = sheet.getRange(6, 1, 1, 2); | |
headers.setValues([['Input', 'Output']]); | |
// Clear any previous values | |
const rangeLength = sheet.getRange('A7:A').getValues().filter(String).length + 6; | |
sheet.getRange(7, 1, rangeLength, 2).clearContent() | |
// Write lookup table key-value pairs to sheet | |
const range = sheet.getRange(7, 1, map.length, 2); | |
range.setNumberFormat('@STRING@'); | |
range.setValues(map); | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Error
It seems I get the error below when running populateSheet
Solution