0

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); 
}
1
  • PS : I also tried changing the getRange to point to the first column but the result is same . – kshitij Sabale Oct 22 '20 at 8:32
0

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

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.