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);
}