CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Sean Dooley
Sean Dooley

Reputation: 129

How do I Merge Column Values in Google Sheets with Google Apps Script

I recognize that this question is similar to this one. I'm sad to say, I just don't know enough to follow that one.

I also recognize that it is very similar to the Google tutorial, but they are creating a new array, and I need to combine arrays.

The problem

I'm trying to create a permanent list from a volatile list. The permanent list will be updated daily. I need the script to look for duplicates on one sheet, and copy everything new to another sheet. I'm pretty sure that my problem lies mostly in my logic.

I've been bashing together all the similar answers I could find to help (commented out) and I just can't figure it out. More explanation below.

Spreadsheet here

Script here

Code.gs

    function updateRoster() {
  var ss = SpreadsheetApp
    .openById("15DRZRQ2Hcd7MNnAsu_lnZ6n4kiHeXW_OMPP3squbTLE");
  var data = ss
    .getSheetByName("Volatile Data")
    .getDataRange()
    .getValues();
  var saveData = ss
    .getSheetByName("All Data")
    .getDataRange()
    .getValues();
  for (i in data) {
    var value = data[i][3];
    for (j in saveData) {
      var search = saveData[j][3]
      if(value == search) {
        Logger.log(data[i][3]);
      }
    }
  }
}

I get this in the Log:

[16-03-24 06:35:44:914 PDT] 1.00000006E8

[16-03-24 06:35:44:916 PDT] 1.00000012E8

[16-03-24 06:35:44:918 PDT] 1.00000022E8

Which is correct, those are the duplicates, but I need to select every other row. The ones that are not duplicates.

I've tried a bunch of changes, but I just keep getting the second sheet's list repeated. Please help.

Upvotes: 0

Views: 909

Answers (2)

Tesseract
Tesseract

Reputation: 8139

Simply replacing value == search with value != search will not work since then it would add the row to the second sheet if even one of the rows in saveData is different. But it needs to check if all rows are different.

If you are sure that your volatile data sheet doesn't have any duplicates in it, you can do it this way:

var saveSheet = ss.getSheetByName("All Data");
loopThroughData:
for (i in data) {
  var value = data[i][3];
  for (j in saveData) {
    var search = saveData[j][3]
    if(value === search) continue loopThroughData;
    //here we leave the inner loop and jump back to the top if
    //even one row is equal
  }
  saveSheet.appendRow(data[i]);
}

edit: However it's usually better to use functions instead of a combination of continue and labels. Therefore I would prefer this solution:

function updateRoster() {
  ...

  function areThereDuplicates(i) {
    var value = data[i][3];
    for(var j in saveData) {
      if(saveData[j][3] === value) return true;
    }
    return false;
  }

  for (i in data) {
    if(!areThereDuplicates(i)) saveSheet.appendRow(data[i]);
  }
}

Upvotes: 2

Juan Diego Antezana
Juan Diego Antezana

Reputation: 912

Well to get every OTHER value you could check for every row that is Different or "new"

if(value != search) {
    Logger.log(data[i][3]);
     }

and simply add the new rows to the file as 

 if(value != search) {
        Logger.log(data[i][3]);
        ss.getSheetByName("All Data").appendRow(data[i]);
      }

Good luck =)

Upvotes: 0

Related Questions