Skip to content

Instantly share code, notes, and snippets.

@sahava

sahava/LookupTableGenerator.gs

Created August 4, 2020 08:40
Show Gist options
  • Star 3 You must be signed in to star a gist
  • Fork 3 You must be signed in to fork a gist
  • Save sahava/d032099a84c0eeb31709204172e7707c to your computer and use it in GitHub Desktop.
Save sahava/d032099a84c0eeb31709204172e7707c to your computer and use it in GitHub Desktop.
Download ZIP
Raw
LookupTableGenerator.gs
// 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);
}
@StarWhiz
Copy link

StarWhiz commented Jun 14, 2023 •
edited

Error

It seems I get the error below when running populateSheet

3:57:29 PM
Error
TypeError: Cannot read properties of undefined (reading 'toString')
(anonymous)
@ Code.gs:77 
(anonymous)
@ Code.gs:73
populateSheet
@ Code.gs:72

Solution

// 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

  const json = JSON.stringify(lookupTable);
  const cellLimit = 50000;
  const chunks = Math.ceil(json.length / cellLimit);

  for (let i = 0; i < chunks; i++) {
    const start = i * cellLimit;
    const end = start + cellLimit;
    const chunk = json.substring(start, end);
    sheet.getRange(`Z${i + 1}`).setValue(chunk);
  }

  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;
        if (key === undefined) {
          key = '';
        }
        if (value === undefined) {
          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