Code.gs

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  var menu = ui.createMenu('Scripts').addItem('Message Generator', 'showMessages').addItem('Template Creator', 'showMessagesConfig').addItem('Template Manager', 'showMessagesManager').addToUi();
}

// Show the template manager popup
function showMessagesManager() {
  var html = HtmlService.createHtmlOutputFromFile('messagemanager').setWidth(600).setHeight(450);
  SpreadsheetApp.getUi().showModalDialog(html, 'Template Manager');
}

// Delete a template
function deleteTemplate(template) {
  var curr = getTemplates();
  var index = curr.indexOf(template);
  if (index != -1) {
    curr.splice(index, 1);
  }
  PropertiesService.getDocumentProperties().setProperty("TEMPLATELIST", JSON.stringify(curr));
  PropertiesService.getDocumentProperties().deleteProperty("TEMPLATE_" + template);
  PropertiesService.getDocumentProperties().deleteProperty("MAPPINGS_" + template);
}

// Show the message generator popup
function showMessages() {
  var html = HtmlService.createHtmlOutputFromFile('message').setWidth(600).setHeight(450);
  SpreadsheetApp.getUi().showModalDialog(html, 'Message Generator');
}

// Show the config menu popup
function showMessagesConfig()  {
  var html = HtmlService.createHtmlOutputFromFile('messageconf').setWidth(600).setHeight(450);
  SpreadsheetApp.getUi().showModalDialog(html, 'Template Creator');
}

// Get all column headers
function getColumns() {
  return SpreadsheetApp.getActiveSheet().getRange(1, 1, 1, SpreadsheetApp.getActiveSheet().getLastColumn()).getValues()[0];
}

// Get a list of all templates
function getTemplates() {
  var templates = JSON.parse(PropertiesService.getDocumentProperties().getProperty("TEMPLATELIST"));
  if (!templates) {
    return [];
  }
  return templates;
}

// Get a template
function getTemplate(name) {
  return JSON.parse(PropertiesService.getDocumentProperties().getProperty("TEMPLATE_" + name));
}

// Create a new template
function newTemplate(name, content, mappings) {
  var templates = JSON.parse(PropertiesService.getDocumentProperties().getProperty('TEMPLATELIST'));
  if (templates) {
    templates.push(name);
    PropertiesService.getDocumentProperties().setProperty('TEMPLATELIST', JSON.stringify(templates));
  } else {
    PropertiesService.getDocumentProperties().setProperty('TEMPLATELIST', JSON.stringify([name]));
  }
  PropertiesService.getDocumentProperties().setProperty("TEMPLATE_" + name, JSON.stringify(content));
  mappings.forEach(mapping => {
    addMapping(name, mapping[1], mapping[0]);
  })
}

// Get template values
function getTemplateValues(name, row) {
  var parsed = [];
  var mappings = getMappings(name);
  var sheet = SpreadsheetApp.getActiveSheet();
  var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0].map(h => (h === null || h === undefined ? "" : String(h).trim()));
  var values = sheet.getRange(row, 1, 1, sheet.getLastColumn()).getDisplayValues()[0];
  mappings.forEach(mapping => {
    var [key, source] = mapping;
    source = String(source).trim();
    var pos = headers.indexOf(source);
    if (pos == -1) {
      return;
    }
    parsed.push([key, values[pos]]);
  });
  return JSON.stringify(parsed);
}

// Get all the mappings for a certain template
function getMappings(name) {
  return JSON.parse(PropertiesService.getDocumentProperties().getProperty("MAPPINGS_" + name));
}

// Saves a mapping to the documents local storage
function addMapping(name, key, source) {
  var mappings = JSON.parse(PropertiesService.getDocumentProperties().getProperty('MAPPINGS_' + name));
  if (mappings) {
    mappings.push([key, source])
    PropertiesService.getDocumentProperties().setProperty('MAPPINGS_' + name, JSON.stringify(mappings));
  } else {
    PropertiesService.getDocumentProperties().setProperty('MAPPINGS_' + name, JSON.stringify([[key, source]]));
  }
}

// Get the rows in the active sheet
function getRows() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var values = sheet.getRange("A1:A" + sheet.getLastRow()).getValues().flat();
  return values;
}

// Alias for getColumnIdByValue(...) that targets the active sheet specifically
function getRowNumber(name) {
  return getColumnIdByValue(name, SpreadsheetApp.getActiveSheet());
}

// A simple function to get the column containg a certain value on a certain sheet - this is copied from one of my other gscript projects
function getColumnIdByValue(searchValue, sheet) {
  var lastRow = sheet.getLastRow();
  var dataRange = sheet.getRange("A1:A" + lastRow);
  var data = dataRange.getValues();

  for (var row = 0; row < lastRow; row++) {
    if (data[row].toString() == searchValue) {
      var rowId = row;
      return (rowId + 1);
    }
  }

  return null;
}