CODEWITHSUNDEEP
androidgoogle-apps-scriptgoogle-sheets
kshitij Sabale
kshitij Sabale

Reputation: 101

Google Apps Script: Appending data to a Spreadsheet if it is not already present

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.

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); 
}

Upvotes: 1

Views: 1578

Answers (1)

Marios
Marios

Reputation: 27400

Explanation:

I can't test your code myself but I am sure this will help you out.

  • It is very inefficient to use a for loop to check if a particular value scannedData exists in a single column (1D array). You can only consider the first column of your data by using map() data.map(r => r[0]) and then use includes() to check whether a particular value belongs to this 1D array.

  • It is safer to consider the displayed values of your spreadsheet, to be sure you are comparing the same things. That comes in handy, especially when you have dates in the spreadsheet file. Therefore, I would advice you to use getDisplayValues() instead of getValues().

Code snippet:

Try this:

function insert(e,sheet) {
  var scannedData = e.parameter.sdata;
  var range = sheet.getDataRange().getDisplayValues().map(r => r[0]); // -> get display Data as 1D array - Col A
  if(!range.includes(scannedData)) { // use includes to see if scannedData is included in the array
      sheet.appendRow([scannedData]);
       return ContentService.createTextOutput("Success").setMimeType(ContentService.MimeType.JAVASCRIPT);
    }
  } 
  return ContentService.createTextOutput("Already Present !").setMimeType(ContentService.MimeType.JAVASCRIPT); 
}

Upvotes: 3

Related Questions