CompareSheetsModifiedColumns

CompareSheetsModifiedColumns.js

/** 
 * HOW TO USE 
 * 
 * 1. From your spreadsheet, go to Extensions > Apps Script
 * 2. Paste the following function and click Run
 * 3. You may be asked to give Apps Script permission to your sheet (Accept)
 * 
 * IMPORTANT
 * 
 * The first sheet MUST be the old sheet
 * The second sheet MUST be the new sheet
 *
 * The row that is considered constant e.g. name cannot change between sheets else this script won't work correctly
 * When the function is executed, a new sheet containing the results will be created
 * */

/** 
 * Modify this section accordingly 
 * */
const COLUMN_HEADERS = ['name', 'email', 'password']
const CONSTANT_COLUMN = 1 // e.g. this has to be constant between the new and old sheets e.g. name
const MODIFIABLE_COLUMN = 2 // e.g. the column that may have had modification e.g. email

 /** Do not modify this function */
function compareSheets() {
  const ss = SpreadsheetApp.getActiveSpreadsheet()
  const oldSheet = ss.getSheets()[0]
  const newSheet = ss.getSheets()[1]
  const finalSheet = ss.insertSheet()

  const oldSheetValues = oldSheet.getDataRange().getValues()
  oldSheetValues.shift() // excludes header row

  const newSheetValues = newSheet.getDataRange().getValues()
  newSheetValues.shift() // excludes header row

  finalSheet.appendRow([...COLUMN_HEADERS, 'status']);
  const finalSheetRange = finalSheet.getDataRange()
  const finalSheetValues = finalSheetRange.getValues()
  finalSheetValues.shift() // excludes header row


  for(const oldItem of oldSheetValues) {
    const newItem = newSheetValues.find(newItem => newItem[CONSTANT_COLUMN - 1] == oldItem[CONSTANT_COLUMN - 1])
    const finalItemIndex = finalSheetValues.findIndex(finalItem => (newItem && newItem[CONSTANT_COLUMN - 1] || oldItem[CONSTANT_COLUMN - 1]) == finalItem[CONSTANT_COLUMN - 1])

    if(!newItem) {
      if(finalItemIndex > -1)
        finalSheetRange.getCell(finalItemIndex + 2, oldItem.length + 1).setValue('REMOVED')
      else
        finalSheet.appendRow([...oldItem, 'REMOVED']);
      continue
    }
    
    if(oldItem[MODIFIABLE_COLUMN - 1] !== newItem[MODIFIABLE_COLUMN - 1]) {
      if(finalItemIndex > -1)
        finalSheetRange.getCell(finalItemIndex + 2, oldItem.length + 1).setValue('MODIFIED')
      else
        finalSheet.appendRow([...oldItem, 'MODIFIED']);
      continue
    }

    if(finalItemIndex > -1)
      finalSheetRange.getCell(finalItemIndex + 2, oldItem.length + 1).setValue('NO_CHANGE')
    else
      finalSheet.appendRow([...oldItem, 'NO_CHANGE']);
  }

  for(const newItem of newSheetValues) {
    const oldItem = [...oldSheetValues, ...finalSheetValues].find(oldItem => newItem[0] == oldItem[0])

    if(!oldItem) {
      finalSheet.appendRow([...newItem, 'ADDED']);
      continue
    }
  }
}