问Google脚本-根据列名而不是硬编码范围比较两个工作表

EN

// Create Top Menu
function onOpen() {
  let ui = SpreadsheetApp.getUi();
  ui.createMenu('>> REPORTS <<').
  addItem('Highlight Closed Cases', 'closedCases').
  addItem('Check for new Cases', 'addCases').addToUi();
}

// IN PROGRESS (Outcome 2) - Add and highlight new cases in CASES sheet
function addCases() {

  let ss = SpreadsheetApp.getActiveSpreadsheet();

  // Get column index number for Case Number
  let activeImportCol = getColumnIndex("Case Number", "IMPORT");
  let activeCasesCol = getColumnIndex("Case Number", "CASES");

  let importHeaders = loadHeaderNames("IMPORT");
  let casesHeaders = loadHeaderNames("CASES");

  // Load Case Number columns values into array
  let loadImportValues = getColumnValues("Case Number", "IMPORT");
  let loadCasesValues = getColumnValues("Case Number", "CASES");
  // Convert to 1D array
  let newImportValues = loadImportValues.map(function (row) { return row[0]; });
  let newCasesValues = loadCasesValues.map(function (row) { return row[0]; });

  // Get number of columns
  var numImportCol = ss.getSheetByName("IMPORT").getLastColumn();

  // Loop through IMPORT sheet "Case Number" column to find new Case Numbers - execute OUTCOME 3 or 2
  for (var line in newImportValues) {
    var isMatched = newCasesValues.indexOf(newImportValues[line]);
    if (isMatched !== -1) {
      

      // "Case Number" from the IMPORT sheet WAS FOUND in the CASES sheet - EXECUTE OUTCOME 3
      // ****************************************************************************************
      // For the matching Case Number records, I need to validate if there were any changes in any CASES sheet columns since last week
      // Action: If a change was found in any of the cells, update the cell with new data in CASES sheet 
      // and change the cell background colour to yellow to highlight the cell was updated. For cells without changes, skip.

      
    } else {
      
      // "Case Number" from the IMPORT sheet was NOT FOUND in the CASES sheet - EXECUTE OUTCOME 2
      // ****************************************************************************************
      // Copy the new data row from the IMPORT sheet to the CASES sheet and paste it in the correct columns 
      // at the bottom and highlight the entire row as green to indicate a new data entry.
      // For all non-existing/not matching column names in the CASES sheet that are not in IMPORT sheet, those should be skipped.
      
    }
  }
}

// COMPLETED (Outcome 1) - Highlight entire row grey for missing values in CASES sheet
function closedCases() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // Load all Casen Number columns values into array
  var importValues = getColumnValues("Case Number", "IMPORT");
  var casesValues = getColumnValues("Case Number", "CASES");

  // Convert to 1D array
  var newImportValues = importValues.map(function (row) { return row[0]; });
  var newCasesValues = casesValues.map(function (row) { return row[0]; });

  // Get column index number for Case Number
  var activeCol = getColumnIndex("Case Number", "CASES");

  // Get number of columns
  var numCol = ss.getSheetByName("CASES").getLastColumn();

  // Loop though CASES "Case Number" column and highlight closed cases (not found in IMPORT tab)
  for (var line in newCasesValues) {
    var isMatched = newImportValues.indexOf(newCasesValues[line]);
    if (isMatched !== -1) {

      // If found then...
      ss.getSheetByName("CASES").getRange(+line + 2, 1, 1, numCol).setBackground(null);

    } else {
      // Higlight row with missing cases - grey
      ss.getSheetByName("CASES").getRange(+line + 2, 1, 1, numCol).setBackground("#d9d9d9");
    };
  }
}

// Load column values
function getColumnValues(label, sheetName) {

  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);

  // Get column number for Case Number
  var colIndex = getColumnIndex(label, sheetName);

  // Get number of rows in Case Number
  var numRows = ss.getLastRow() - 1;

  // Load Case Number values into array
  var colValues = ss.getRange(2, colIndex, numRows, 1).getValues();

  return colValues;

}

// Load column header names
function loadHeaderNames(sheetName) {

  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);

  let HeaderArray = ss.getRange(1, 1, 1, ss.getLastColumn()).getValues()[0];
  let colidx = {};
  HeaderArray.forEach((h, i) => colidx[h] = i);

  return HeaderArray;

}

// Get column name index value
function getColumnIndex(label, sheetName) {

  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);

  // Find last column
  var lc = ss.getLastColumn();

  // Load headers into array
  var lookupRangeValues = ss.getRange(1, 1, 1, lc).getValues()[0];

  // Search for label and return the column number
  var index = lookupRangeValues.indexOf(label) + 1;

  return index;

}