Google Apps Script Prevent duplicate data Submit on Google Sheets Form-CodePudding

I need to submit data from one sheet to another using google script. When I click submitData, it should check whether it is previously entered data or not. If there was no same data, the code must add new data. But, if ther was same data, the code must update the existing.

Their Identity Number is mentioned in "H11" cell on "Form" sheet. The identity number is stored in "Column D" on "Data" sheet. Now When I run it , it's submitting to "Data" sheet even DUPLICATE data entered in "H11" Cell

function submitData() {
    var ss         = SpreadsheetApp.getActive();
    var formSS     = ss.getSheetByName("Form"); //Form Sheet
    var datasheet  = ss.getSheetByName("Data"); //Data Sheet
    var ui         = SpreadsheetApp.getUi(); //get UI
    var IDform     = formSS.getRange("H11").getValue();
    var IDdata     = datasheet.getRange(2, 4, datasheet.getLastRow()-1, 1).getValues();
    var peringatan = formSS.getRange("H8").getDisplayValue()  "'s Passenger"   ' on '   formSS.getRange("H10").getDisplayValue()   ' is '   formSS.getRange("H12").getDisplayValue()   ' passenger?'
    
    // Input Values
        var values = [[ formSS.getRange("H4").getValue(),
                        formSS.getRange("H8").getValue(),
                        formSS.getRange("H10").getValue(),
                        formSS.getRange("H11").getValue(),
                        formSS.getRange("H12").getValue()]];
                        
        var clform = [[ formSS.getRange("H4").clear(),
                        formSS.getRange("H6").clear(),
                        formSS.getRange("H8").clear(),
                        formSS.getRange("H10").clear(),
                        formSS.getRange("H12").clear()]];
    
    // Save New Data
    if (formSS.getRange("H4").getValue() == "") {
        ui.alert('Data Tidak Boleh Kosong');
    } {
    let idx = IDdata.indexOf(IDform);
    if (~idx) {
      if (ui.alert('Data akan diperbarui - '   peringatan, ui.ButtonSet.OK_CANCEL) == ui.Button.OK) {
        datasheet.getRange(idx, 1, 1, 5).setValues(values); //update data
        formSS.getRange("H4").clear();
        formSS.getRange("H6").clear();
        formSS.getRange("H8").clear();
        formSS.getRange("H10").clear();
        formSS.getRange("H12").clear();
      }
    } else {
      if (ui.alert('Data baru akan ditambahkan - '   peringatan, ui.ButtonSet.OK_CANCEL) == ui.Button.OK) {
        datasheet.getRange(datasheet.getLastRow() 1, 1, 1, 5).setValues(values); //new data
        formSS.getRange("H4").clear();
        formSS.getRange("H6").clear();
        formSS.getRange("H8").clear();
        formSS.getRange("H10").clear();
        formSS.getRange("H12").clear();
      }
    }
  }
}

I've tried to remove the Bitwise Not (~), so it tried to call the //update data condition but it's alerted Exception: The starting row of the range is too small.

CodePudding user response：

Try flattening your nested array this way:

var IDdata = datasheet.getRange(2, 4, datasheet.getLastRow()-1, 1).getValues().flat();

Because you're reading a column, each element in the array is itself an array with one element that contains the value. Without flattening, you're searching for a value inside a list of arrays.