How can I compare two ranges of data and move rows from one to another if data doesn't exist in-CodePudding

I have one 'reporting_sheet' and one 'data_sheet'. I would like to make the 'reporting_sheet' check for data that exists in the 'data_sheet' but doesn't exist in the 'reporting_sheet' and move that over as soon as the data is entered in 'data_sheet' (On Edit)

reporting_sheet :

data_sheet :

result expected in reporting_sheet :

I tried this:

function onEdit(e) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const [sheet1, sheet2] = ["reporting_sheet", "data_sheet"].map(s => ss.getSheetByName(s)); // set sheet names
  var Avals = sheet1.getRange("A1:A").getValues();  // getting last row of column A
  var lastRowSheet1 = Avals.filter(String).length;
  const sheet1Obj = sheet1.getRange("A2:A"   lastRowSheet1).getValues().reduce((o, [a]) => (o[a] = true, o), {});
  const sheet2Values = sheet2.getRange("A2:E"   sheet2.getLastRow()).getValues();
  const values = sheet2Values.filter(([a]) => !sheet1Obj[a]);
  if (values.length == 0) return;
  sheet1.getRange(lastRowSheet1   1, 1, values.length, 5).setValues(values);
  // sheet1.sort(1);
}

Issue with the code : It only compares the first column between both sheets and dumps the data if there's a new unique value in the A column of 'data_sheet' which doesn't exist in the 'reporting_sheet'. How do I make it look for columns A to E?

Sorry I am a newbie.

Link - https://docs.google.com/spreadsheets/d/e/2PACX-1vSDW0zMBpBWK-RUMyn6JOqE0AvdwNdOCRVyE0UmVUUq2nfaS8koGxs_sCXQ0MApNk7t4GHx0GR2e-Ld/pubhtml

CodePudding user response：

If you make sure that all the dates have the same format this modification should do the job:

function myFunction() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const [sheet1, sheet2] = ["reporting_sheet", "data_sheet"].map(s => ss.getSheetByName(s));
  const lastRowSheet1 = sheet1.getLastRow();

  // make 'keys' (A B C D E) from the rows of reporting_sheet
  const keys = sheet1.getRange("A2:E"   lastRowSheet1).getDisplayValues()
    .map(x => x.join('')); // [a,b,c,d,e] => 'abcde'

  // get all the rows from data_sheet 
  // and keep only the ones which A B C D E aren't among the 'keys'
  const rows = sheet2.getRange("A2:E"   sheet2.getLastRow()).getDisplayValues()
    .filter(x => !keys.includes(x.join('')));

  // return if there is no rows
  if (rows.length == 0) return;

  // set the rows at end of the reporting_sheet
  sheet1.getRange(lastRowSheet1   1, 1, rows.length, rows[0].length).setValues(rows);

  // sheet1.sort(1);
}

CodePudding user response：

Try this:

function lfunko() {
  const ss = SpreadsheetApp.getActive();
  const rsh = ss.getSheetByName("Sheet0");
  const rvs = rsh.getRange(3,1,rsh.getLastRow() - 2,5).getDisplayValues();
  const dsh = ss.getSheetByName("Sheet1");
  const dvs = dsh.getRange(3,1,dsh.getLastRow() - 2,5).getDisplayValues()
  const rA = rvs.map(r => r.join(""));
  dvs.forEach((r,i) => {
    if(!~rA.indexOf(r.join(""))) {
      rsh.appendRow(r)
    }
  })
}