Log Missing Column in Destination Spreadsheet-CodePudding

I have the code below which allows me to update the existing spreadsheet (much like an matrix lookup). And what I am doing right now is to include a error log which will be updated in another sheet, this includes Missing data in Column A in Destination Spreadsheet and Missing Column in Destination Spreadsheet. I have worked with Missing data in Column A, my problem was the Missing Column, cause instead of the column name (found in the row 1) the result shows the column index:

code:

function updateShadowSKU() {
  var source = SpreadsheetApp.getActive().getSheetByName('Sheet1');
  var dest = SpreadsheetApp.openById('179PCrIWe1mvbbzOi9ySEHxzBlFaXpCB2i0wHlYVE2vg').getSheetByName('Sheet1');
  var destRange = dest.getDataRange();
  var destValues = destRange.getValues();
  var destHeaders = destValues[0];
  var destIds = destValues.map(e => e[0]);
  var values = source.getDataRange().getValues().map(e => e.filter((f, i) => !i || i > 10));
  var colMap = values[0].map(e => destHeaders.indexOf(e));
  Logger.log(colMap);
  values = values.map((e, i, arr) => e.map((f, j) => [e[0], colMap[j], f, values[0][j], i, j])).flat().filter(e => e[0] && e[1] && e[2] && e[3] && e[4] && e[5]);
  Logger.log(values);

  // Initialize an array to store log entries
  var logEntries = [];

  // Check for missing SKUs
  values.forEach(function(e) {
    if (!destIds.includes(e[0])) {
      logEntries.push(["Missing SKU", e[0], e[3]]);
    }
  });

  // Check for missing column headers
colMap.forEach(function(e, i) {
  if (e == -1) {
    var index = destHeaders.indexOf(values[0][i]);
    colMap[i] = index;
    if (index == -1) {
      logEntries.push(["Missing column header", values[0][e], ""]);
    }
  }
});

  if (!values.length) {
    logEntries.push(["No changes to make", "", ""]);
  } else {
    values = values.map(e => [destIds.indexOf(e[0]), e[1], e[2]]);
    Logger.log(values.length   ' changes to make');
    Logger.log(values);
    values.forEach(function(e) {
      try {
        destValues[e[0]][e[1]] = e[2];
      } catch (err) {}
    });
    destRange.setValues(destValues);
  }

  // Write log entries to the 'Shadow Log' sheet in the destination spreadsheet
  if (logEntries.length > 0) {
    var logSheet = SpreadsheetApp.openById('179PCrIWe1mvbbzOi9ySEHxzBlFaXpCB2i0wHlYVE2vg').getSheetByName("Shadow Log");
    if (logSheet == null) {
      logSheet = SpreadsheetApp.openById('179PCrIWe1mvbbzOi9ySEHxzBlFaXpCB2i0wHlYVE2vg').insertSheet("Shadow Log");
    }
    logSheet.clear();
    logSheet.getRange(1, 1, logEntries.length, 3).setValues(logEntries);
  }
}

the code block for getting the missing data in Column A destination spreadsheet works fine, but what I am having a hard time is getting the column name. The code block below shows only the column index instead of the comlumn title/header:

// Check for missing column headers
colMap.forEach(function(e, i) {
  if (e == -1) {
    var index = destHeaders.indexOf(values[0][i]);
    colMap[i] = index;
    if (index == -1) {
      logEntries.push(["Missing column header", values[0][e], ""]);
    }
  }
});

sample sheets: source:

You may change the range if you should add more columns. I changed values[0][e] to sourceCol[0][i] to access the wanted column header.

The new code should look like this: (I added comments for further guidance)

function updateShadowSKU() {
  var source = SpreadsheetApp.getActive().getSheetByName('Sheet1');
  var dest = SpreadsheetApp.openById('Destination Sheet ID').getSheetByName('Sheet1');
  var destRange = dest.getDataRange();
  var destValues = destRange.getValues();
  var destHeaders = destValues[0];
  var destIds = destValues.map(e => e[0]);
  var values = source.getDataRange().getValues().map(e => e.filter((f, i) => !i || i > 10));

  //-----------------------------------------------------------------------------------------------
  //added sourceCol variable to specifically get the array of all Column Headers from the Source
  var sourceCol = source.getDataRange().getValues().map(e => e.filter((f, i) => i >= 3 && i <= 17));
  var colMap = sourceCol[0].map(e => destHeaders.indexOf(e));
  //-----------------------------------------------------------------------------------------------

  values = values.map((e, i, arr) => e.map((f, j) => [e[0], colMap[j], f, values[0][j], i, j])).flat().filter(e => e[0] && e[1] && e[2] && e[3] && e[4] && e[5]);


  // Initialize an array to store log entries
  var logEntries = [];

  // Check for missing SKUs
  values.forEach(function (e) {
    if (!destIds.includes(e[0])) {
      logEntries.push(["Missing SKU", e[0], e[3]]);
    }
  });

  // Check for missing column headers
  colMap.forEach(function (e, i) {
    if (e == -1) {
      //--------------------------------------------------------------
      //change values[0][e] to sourceCol[0][i]
      logEntries.push(["Missing column header", sourceCol[0][i], ""]);
      //--------------------------------------------------------------
    }
  });

  if (!values.length) {
    logEntries.push(["No changes to make", "", ""]);
  } else {
    values = values.map(e => [destIds.indexOf(e[0]), e[1], e[2]]);
    Logger.log(values.length   ' changes to make');
    Logger.log(values);
    values.forEach(function (e) {
      try {
        destValues[e[0]][e[1]] = e[2];
      } catch (err) { }
    });
    destRange.setValues(destValues);
  }
    // Write log entries to the 'Shadow Log' sheet in the destination spreadsheet
    // Added an else statement to clear the 'Shadow Log` when the log is empty.
  var logSheet = SpreadsheetApp.openById('Destination Sheet ID').getSheetByName("Shadow Log");
  if (logEntries.length > 0) {
    if (logSheet == null) {
      logSheet = SpreadsheetApp.openById('Destination Sheet ID').insertSheet("Shadow Log");
    }
    logSheet.clear();
    logSheet.getRange(1, 1, logEntries.length, 3).setValues(logEntries);
  }
  else {
    logSheet.clear();
  }
    }

Output

In testing the code, I deleted 5 column headers (as seen below):

When I ran the code with the modification, I got the following output:

Output

References