CODEWITHSUNDEEP
javascriptobjectgoogle-apps-script
samdaily34
samdaily34

Reputation: 67

Preventing duplicates from being appended to a target sheet

I am trying to append values onto a new row of a target sheet each time a button is clicked. I created an object to check the target sheets values at a specified column. Then, I created a for loop to check values being appended against values already in the object, to prevent duplicates, however the object appears as "null" and does not prevent duplicate rows from being appended.

I have tried creating an object to compare new values with values already existing in the target sheet. I have tried appending a new column with a Boolean of "TRUE" and checking if that columns value is = to "TRUE", then don't append the row.

function sendToOutreach() {
  var ss = SpreadsheetApp.openById('someid')
  var sheet = ss.getSheetByName('39')
  var outreachSheet = SpreadsheetApp.openById('anotherid')
  var outreachTab = outreachSheet.getSheetByName("Sheet1")
  var tab = ss.getSheetByName(sheet)
  var range = sheet.getRange('A2:O' + sheet.getLastRow()).getDisplayValues()
  var rx = outreachTab.getRange('c2:c' + outreachTab.getLastRow()).getDisplayValues()

  var targetRange = outreachSheet.getRange('A2:Q' + outreachSheet.getLastRow()).getDisplayValues()
  var timestamp = Utilities.formatDate(new Date(),"CST","MM/dd/yyyy HH:mm:ss")
  var user =  Session.getActiveUser()
  var object = createObject(targetRange)
  Logger.log(object)
  for (var a = 0; a<range.length;a++){
    if(!object[[a][0]]){
      var checkCode = range[a][14]
      if(checkCode == 'reason'||checkCode == 'reason'||checkCode == 'reason'||checkCode == 'reason'|| checkCode == 'reason'){
        var Info = ([user,timestamp,range[a][0],range[a][1],range[a][2],range[a][3],range[a][4],range[a][5],range[a][6],range[a][7],range[a][9],
                            range[a][10],range[a][11],range[a][12],range[a][13],range[a][14]])
        outreachSheet.appendRow(Info)

      }
    }
  }
}

function createObject (range) {
  var object = {}
  for(var c=0;c<range.length;c++){
    var rx = range[c][2]
    if(!object[rx]){
      object[rx] = {"rx":rx}
    }
  }
  return object
}

I keep getting the same rows appended when they shouldn't be appended.

Upvotes: 1

Views: 41

Answers (1)

Cooper
Cooper

Reputation: 64062

Try this:

function sendToOutreach() {
  var ss = SpreadsheetApp.openById('someid')
  var sheet = ss.getSheetByName('39')
  var outreachSheet = SpreadsheetApp.openById('anotherid')
  var outreachTab = outreachSheet.getSheetByName("Sheet1")
  var tab = ss.getSheetByName(sheet)
  var range=sheet.getRange(2,1,sheet.getLastRow()-1,15).getDisplayValues();
  var rx=outreachTab.getRange(2,3,outreachTab.getLastRow()-1,1).getDisplayValues();
  var targetRange=outreachSheet.getRange(2,1,outreachSheet.getLastRow()-1,17).getDisplayValues();
  var timestamp = Utilities.formatDate(new Date(),"CST","MM/dd/yyyy HH:mm:ss")
  var user =  Session.getActiveUser()
  var object = createObject(targetRange)
  Logger.log(object)
  for (var a=0; a<range.length;a++){
    if(!object[range[a][0]]){
      var checkCode = range[a][14]
      if(checkCode == 'reason'||checkCode == 'reason'||checkCode == 'reason'||checkCode == 'reason'|| checkCode == 'reason'){
        var Info = ([user,timestamp,range[a][0],range[a][1],range[a][2],range[a][3],range[a][4],range[a][5],range[a][6],range[a][7],range[a][9],
                            range[a][10],range[a][11],range[a][12],range[a][13],range[a][14]])
        outreachSheet.appendRow(Info)

      }
    }
  }
}

function createObject (range) {
  var object={};
  for(var c=0;c<range.length;c++){
    if(!object.hasOwnProperty(range[c][2])) {
      object[range[c][2]]=c+1;
    }
  }
  return object
}

Upvotes: 3

Related Questions