Compare arrays on two different sheets and take only unique values

Question

I have a script that creates a timed trigger to move data from a source sheet into a destination sheet. What I would like to add is the ability to only take unique/new values from the source sheet that aren't present in the destination and add them to the destination sheet.

The script I currently have passes all non-empty values from the source sheet into an array that is then appended or written over the destination data.

I tried reading the values of both sheets and using a for loop through both sets and pushing non-equal values into a new array but that ended up just copying all the values for the entire length of the sheet. I have also tried concat'ing both arrays and then looping through that for unique values but that ended up giving me blanks as well.

https://stackoverflow.com/questions/15912538/get-the-unique-values-from-two-arrays-and-put-them-in-another-array — aNewb, Jan 07 '21 at 19:00
Hi, welcome to stack overflow. Could you please include some code in your answer and possibly more details ( as long as you add some code your answer it will be acceptable ) — Baby_Boy, Jan 07 '21 at 19:01
@aNewb the link you provided has also been flagged a duplicate, could you edit? — Baby_Boy, Jan 07 '21 at 19:02
How about this link https://stackoverflow.com/questions/51624717/google-apps-script-comparing-arrays-for-unique-values/51630118 — aNewb, Jan 07 '21 at 19:39
Define unique values. Do you mean unique rows, or unique values in certain columns? — Cooper, Jan 07 '21 at 20:58
@Cooper I think I mean unique rows as each row is entered as 1 element. — David-, Jan 07 '21 at 21:09

Cooper · Accepted Answer · 2021-01-08T19:48:13.233

function appendUniqueRows() {
  const ss1=SpreadsheetApp.openById("ss1id");//source
  const sh1=ss1.getSheetByName('ss1shname')
  const rg1=sh1.getDataRange();//this range should be data only
  const v1=rg1.getValues();
  const ss2=SpreadsheetApp.openById("ss2id");//destination
  const sh2=ss2.getSheetByName("ss2shname");
  const rg2=sh2.getDataRange();//this range should be  dataonly
  const v2=rg2.getValues();
  const d=v2.map(r=>{return r.join("")});//current rows in destination
  v1.forEach(r=>{if(!d.includes(r.join(""))){d.push(r.join(""));sh2.appendRow(r);}});//append unique rows
}

Using setValues() with an output array

function appendUniqueRows() {
  const ss1=SpreadsheetApp.openById("ss1id");//source
  const sh1=ss1.getSheetByName('ss1shname')
  const rg1=sh1.getDataRange();//this range should be data only
  const v1=rg1.getValues();
  const ss2=SpreadsheetApp.openById("ss2id");//destination
  const sh2=ss2.getSheetByName("ss2shname");
  const rg2=sh2.getDataRange();//this range should be  dataonly
  const v2=rg2.getValues();
  const d=v2.map(r=>{return r.join("")});//current rows in destination
  let oA=[];
  v1.forEach(r=>{if(!d.includes(r.join(""))){d.push(r.join(""));oA.push(r)}});
  sh2.clearContents();
  sh2.getRange(1,1,oA.length,oA[0].length).setValues(oA);
}

@ Cooper Yes! This is essentially what I needed, the only request I have is if there is anyway to have this passed into an array and then use the .setValues() method to save time? This is meant to deal with large sums of data is why. — David-, Jan 08 '21 at 18:52

Compare arrays on two different sheets and take only unique values

1 Answers1