prevent duplicates from being appended to google sheet from apps script?

Question

I have an apps script web form. I want to send the parcel dropdown and the status dropdown data to my google sheet "Statuses". It works, but I'm trying to avoid duplicate data from being appended. Column A is where parcel input goes, column B is where status input goes to. I wrote this to try and have it search google sheet after user submits to find duplicates and prevent appending the form data, but it's not working.

function doPost(e) {
  var parcel = e.parameter.parcel.toString();
  var substation = e.parameter.substation.toString();
  var comment = e.parameter.comment.toString();
  var status = e.parameter.status.toString();
  var date = new Date();
  
  // Check if the record already exists based on parcel and status
  if (!isDuplicateStatus(parcel, status)) {
    addRecord(comment, parcel, date);
    addToStatuses(parcel, status, date);
    var htmlOutput = HtmlService.createTemplateFromFile('DependentSelect');
    var subs = getDistinctSubstations();
    htmlOutput.message = 'Record Added';
    htmlOutput.subs = subs;
    return htmlOutput.evaluate(); 
  } else {
    return ContentService.createTextOutput("Duplicate status submission found. Record not added.");
  }
}

function isDuplicateStatus(parcel, status) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var statusSheet = ss.getSheetByName("Statuses");
  var dataRange = statusSheet.getRange(3, 1, statusSheet.getLastRow() - 2, statusSheet.getLastColumn());
  var data = dataRange.getValues();
  
  for (var i = 0; i < data.length; i++) {
    if (data[i][0] === parcel && data[i][1] === status) {
      return true; // Found a duplicate status submission
    }
  }

  return false; // No duplicate status submission found
}

I post my full code for js and html if needed. Any ideas why it still would be appending the row if it searched the rows in sheet and found that it exists?

Answer 1

SUGGESTION

You could try filtering your data set using filter in your isDuplicateStatus function like this:

/**
   * If 'isDuplicateStatus' returns 'true' based on parcel and status, SKIP the appending of the submitted data; otherwise, proceed in appending the data into the sheet.
   * 
   * If the entries exist in the sheet, the length of the filtered data will be greater than zero.
   */
function isDuplicateStatus(parcel, status) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var statusSheet = ss.getSheetByName("Statuses");
  var dataRange = statusSheet.getRange(3, 1, statusSheet.getLastRow() - 2, statusSheet.getLastColumn());
  var data = dataRange.getValues();

  //Return 'true' if a duplicate was found, 'false' otherwise 
  return data.filter(cellData => cellData.flat()[0] == parcel && cellData.flat()[1] == status).length > 0;
}

So, the whole structure of your sample script would look like this:

function doPost(e) {
  var parcel = e.parameter.parcel.toString();
  var substation = e.parameter.substation.toString();
  var comment = e.parameter.comment.toString();
  var status = e.parameter.status.toString();
  var date = new Date();

  // If 'isDuplicateStatus' returns 'true' based on parcel and status, SKIP the appending of the submitted data; otherwise, proceed in appending the data into the sheet.
  if (isDuplicateStatus(parcel, status)) {
    return ContentService.createTextOutput("Duplicate status submission found. Record not added.");
  } else {
    addRecord(comment, parcel, date);
    addToStatuses(parcel, status, date);
    var htmlOutput = HtmlService.createTemplateFromFile('DependentSelect');
    var subs = getDistinctSubstations();
    htmlOutput.message = 'Record Added';
    htmlOutput.subs = subs;
    return htmlOutput.evaluate();
  }
}

function isDuplicateStatus(parcel, status) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var statusSheet = ss.getSheetByName("Statuses");
  var dataRange = statusSheet.getRange(3, 1, statusSheet.getLastRow() - 2, statusSheet.getLastColumn());
  var data = dataRange.getValues();

  //Return 'true' if a duplicate was found, 'false' otherwise 
  return data.filter(cellData => cellData.flat()[0] == parcel && cellData.flat()[1] == status).length > 0; 
}

Demo

Sample 'Statuses' sheet:

In this test, I will log the result if duplicate data is found:

A sample form (a duplicate attempt will be made to submit).

Log result. (The sheet will not be appended with data).