Last active
September 4, 2022 16:10
-
-
Save emmanuelnk/4fd725edb21534c069af2c9f1b630c55 to your computer and use it in GitHub Desktop.
CompareSheetsModifiedColumns
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/** | |
* 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 | |
} | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment