Find Difference Rows between two Sheets after comparing their Codes in a Column

Question

I am trying to find the NEW rows added to a Data Sheet by comparing its Codes located in a column, with the Codes located in a column of a Database Sheet. I am doing this by finding the difference between the 2 codes.

...
// Get Mappings array
let Maplist = shtMap.getRange(2, 1, r_Map - 1, 2).getValues();

// find new codes that have come in the Data sheet
let DataCodes = [...new Set(shtData.getRange(2, 1, r_Data - 1, 1).getValues().flat())];
let DatabaseCodes = [...new Set(shtDatabase.getRange(2, 2, r_Database - 1, 1).getValues().flat())];

let diff =[];
// ===> Below is the line I want to modify to incorporate all the logic given in my Notes section.
diff = DataCodes.reduce( (diff,x) => !DatabaseCodes.includes(x) ? [...diff, [x]] : diff, []);

// Map Emp name to diff Array ===> not working....getting error!
diff = diff.map(function(x, i , arr){
    if(arr.indexOf(x) == i){
        return [...diff, [arr[i][1]]);
    }
}, (Maplist));

// Add Data Validation to `Status` column
var Rng = shtDatabase.getRange(r_Database + 1, 7, diff.length -1, 1);
Rng.clearDataValidations().clearContent();
var rule = SpreadsheetApp.newDataValidation().requireValueInList([`Open`,`Complete`], true).build();
Rng.setDataValidation(rule); // Update `Status` value
...

The above codes gives me the Difference i.e. new Codes that have come into Database Sheet. Is there a way to modify the above Reduce function code to pull in the entire new rows, so that the diff Array can then be appended to the end of the Database Sheet?

Note:

• Here the Delivery Date in Database Sheet refers to the FinalDate in Data Sheet. Can the above code be modified to take into account the mismatched column name, so that the FinalDate column values are placed exactly in the Delivery Date column?

• I need to also add a DataValidation dropdown containing values Open,Complete into the Status Column of Database Sheet, for each new row in diff array. How can the above code take into account Data Validation code?

• As you can see, the matching columns in Database Sheet are not contiguous and in same order of the Data Sheet, but spread across the Sheet. Can the code be modified to properly place each diff array value into their respective columns?

• Finally, i have to compare eachOwner name in the Database Sheet with an Mapping Sheet Array (which contains the Owner name and it's corresponding Emp name), then match its corresponding Emp name and add this to the Emp column in Database Sheet accordingly.

Reason:

• I would like to do all this in memory and finally transfer this memory Array to Database Sheet in one go. Otherwise, repeatedly accessing (read/write) the Sheets takes a lot of time for the code to run, especially when the data is large. Is this possible with some consice code?

Data Sheet: Database Sheet: Mapping Sheet

Here is the link to the [Sample File][4].

[4]:

Edit: https://docs.google.com/spreadsheets/d/1DARGtbN8EyEKyF9ceuOusStvuPIXjUR_8OrqfhPxpNQ/edit?usp=sharing

@TheMaster, Here is the entire code written in the file:

function myTest(){

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const shtData = ss.getSheetByName("Data");
  const shtMap = ss.getSheetByName("Mapping");
  const shtDatabase = ss.getSheetByName("Database");

    // get Data Sheet unique list of Owners
  let r_Data = FindLastRow(shtData);
  let c_Data = FindLastColumn(shtData);
  let list = shtData.getRange(2, 4, r_Data - 1, 1).getValues();
  let Datalist = [...new Set(list.flat())];

  // get Mapping Sheet unique list of Owners
  r_Map = FindLastRow(shtMap);
  c_Map = FindLastColumn(shtMap);
  list = shtMap.getRange(2, 1, r_Map - 1, 1).getValues();
  let Maplist = [...new Set(list.flat())];
  
  // find new codes that have come in the Data sheet
  r_Database = FindLastRow(shtDatabase);
  c_Database = FindLastColumn(shtDatabase);
  let DataCodes = [...new Set(shtData.getRange(2, 1, r_Data - 1, 1).getValues().flat())];
  let DatabaseCodes = [...new Set(shtDatabase.getRange(2, 2, r_Database - 1, 1).getValues().flat())];
  
  // find the difference between the 2 arrays and append the new ones to Mapping Sheet, then sort.
  let diff = Datalist.reduce( (diff,x) => !Maplist.includes(x) ? [...diff, [x,'Unassigned']] : diff,[]);
  if(diff.length !== 0){
    shtMap.getRange(r_Map + 1, 1, diff.length, diff[0].length).setValues(diff);
  }

  // Sort the Mappings Sheet on 2nd column, then 1st column
  shtMap.getDataRange().offset(1, 0, shtMap.getDataRange().getNumRows() - 1).sort([{column: 2, ascending: true}, {column: 1, ascending: true}]);
  

//  diff =[];
  // ===> Below is the line I want to modify to incorporate all the logic given in my Notes section.
  diff = DataCodes.reduce( (diff,x) => !DatabaseCodes.includes(x) ? [...diff, [x]] : diff, []);


  // From Notes : Map Emp name to diff Array ===> not working....getting error!
  diff = diff.map( function(x, i, arr){
    if(arr.indexOf(x) == i){
      return [...diff, arr[1][i]];
    }
  }, (Maplist));
  
  // From Notes : Add Data Validation to `Status` column
  var Rng = shtDatabase.getRange(r_Database + 1, 7, diff.length -1, 1);
  Rng.clearDataValidations().clearContent();
  var rule = SpreadsheetApp.newDataValidation().requireValueInList([`Open`,`Complete`], true).build();
  Rng.setDataValidation(rule); // Update `Status` value
}


function FindLastRow(sht) {
  return sht.getRange(1,1).getDataRegion().getLastRow();
};

function FindLastColumn(sht) {
  return sht.getRange(1,1).getDataRegion().getLastColumn();
};

Edit: @Tanaike, i have updated the screenshots in the post as well as updated the spreadsheet also. for e.g. the Yellow rows in Data sheet are new rows and so need to be placed in the Database sheet correctly and also add Data validation in Status column, Emp name in Emp column (after matching Owner with Mapping Sheet). FinalDate column in Data sheet is the Delivery Date column in Database sheet.

The idea is to create a consice code that would create a single 2D array (which should include the datavalidation & Emp name), to place all the new data correctly in the Database sheet. I want to avoid writing data to sheet everytime for updating each columns in Database sheet.

Answer 1

How about adding the following sample script to the bottom of your myTest()?

Modified script:

  var Rng = shtDatabase.getRange(r_Database + 1, 7, diff.length -1, 1);
  Rng.clearDataValidations().clearContent();
  var rule = SpreadsheetApp.newDataValidation().requireValueInList([`Open`,`Complete`], true).build();
  Rng.setDataValidation(rule); // Update `Status` value
}

  var Rng = shtDatabase.getRange(r_Database + 1, 7, diff.length -1, 1);
  Rng.clearDataValidations().clearContent();
  var rule = SpreadsheetApp.newDataValidation().requireValueInList([`Open`,`Complete`], true).build();
  Rng.setDataValidation(rule); // Update `Status` value

  // I added below script.
  const mapObj = shtMap.getRange("A2:B" + shtMap.getLastRow()).getValues().reduce((o, [a, b]) => Object.assign(o, {[a]: b}), {});
  const existingIdsObj = shtDatabase.getRange("B2:B" + shtData.getLastRow()).getValues().reduce((o, [b]) => Object.assign(o, {[b]: true}), {});
  const putValues = shtData.getRange("A2:G" + shtData.getLastRow()).getValues().reduce((ar, [a,b,c,d,e,f,g]) => {
    if (!existingIdsObj.hasOwnProperty(a)) ar.push([b, a, f, c, , , , d, e, , , g, , mapObj[d]]);
    return ar;
  }, []);
  shtDatabase.getRange(shtDatabase.getLastRow() + 1, 1, putValues.length, putValues[0].length).setValues(putValues);
}

• In order to check the duplicated ID and put Emp value, I used the JSON objects of mapObj and existingIdsObj.