Appending data to a Spreadsheet if it is already not present

Question

I am new to Google apps script and I am trying to write a program where whenever I scan a data. The scannedData should only be appended to the spreadsheet if and only if the data is not already present in the specific column (the First Column). I have written the following code but for some reason the data still gets appended even if it is present. If someone could please help me out, I would really appreciate that.

function insert(e,sheet) {
  var scannedData = e.parameter.sdata;
  var range = sheet.getDataRange().getValues();
  for(var i=0, iLen=range.length; i<iLen; i++) {
    if(range[i][0]!=scannedData) {
      sheet.appendRow([scannedData]);
       return ContentService.createTextOutput("Success").setMimeType(ContentService.MimeType.JAVASCRIPT);
    }
  } 
  return ContentService.createTextOutput("Already Present !").setMimeType(ContentService.MimeType.JAVASCRIPT); 
}

Answer 1

Try this

function insert(e,sheet) {
  var scannedData = e.parameter.sdata;
  var range = sheet.getRange(1,1,sheet.getLastRow()).getValues();
  if(!range.some(a => a === scannedData )){
      sheet.appendRow([scannedData]);
       return ContentService.createTextOutput("Success").setMimeType(ContentService.MimeType.JAVASCRIPT);
    }
  } else { 
    return ContentService.createTextOutput("Already Present !").setMimeType(ContentService.MimeType.JAVASCRIPT); 
  }
}

I don't know if it's necessary to replace getDataRange() by getRange(1,1,sheet.getLastRow()) but it's very likely that this way is better.

Array.prototype.some "tests whether at least one element in the array passes the test implemented by the provided function. It returns a Boolean value".

Reference

• https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Array/some