The code given is an example of what I would like to happen but I don't know how to apply it in a way that it would run smoothly or if it is possible at all. I just want to know how to do it and what other ways I can do it for educational purposes and for future coding projects.

function DuplicatePatient(triggerPatient) {
  var ss=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Form Responses 1");
  var values = SpreadsheetApp.getActiveSheet().getRange("B:B").getValues();
  var columnB = ["B"]
  var lastrow = ss.getLastRow();
  var patient = ss.getRange(columnB   lastrow);
  var triggerPatient = patient.getValue();
  var ui = SpreadsheetApp.getUi
  var row = [rownumber]

  if (triggerPatient == values){
    return ui.alert("Duplicate Found on row"   row)
  }
}

I would totally appreciate it if you guys would enlighten me with the subject at hand and I hope it can help other people as reference. Thanks alot!

CodePudding user response：

A few corrections

You can achieve the desired functionality based on your code snippet when implementing the following modifications:

• getValues() returns a 2-D array. To know if a value is already contained in values, you need to convert this 2-D array into a 1-D one (for example with flat()).
• The next step would be to either loop through each single value and look for correspondance with triggerPatient via the == operator or - more elegant to use a method that tells you directly either triggerPatient is contained in the values array.
• For the latter you can use indexOf, which not only tells you if the duplicate is found, but also at which index (position) of the array. This allows you to calculate the corresponding row.
• Keep in mind that "B:B" contains all values of column B - inlcluding triggerPatient in the last row. To look for duplicates you want to excluded this last row from the comparison. You can do so for example by removing the last value from the values array with pop().
• Note that SpreadsheetApp.getUi() is a method and thus needs to be called with () at the end.

A sample what a working code could look like:

function DuplicatePatient() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Form Responses 1");
  var values = SpreadsheetApp.getActiveSheet().getRange("B:B").getValues();
  values = values.flat();
  values.pop();
  // just to visualize the results:
  console.log(values);
  var columnB = ["B"];
  var lastrow = ss.getLastRow();
  var patient = ss.getRange(columnB   lastrow);
  var triggerPatient = patient.getValue();
  var ui = SpreadsheetApp.getUi();
  var duplicateRow = 1   values.indexOf(triggerPatient);
  if (duplicateRow > 0){
    return ui.alert("Duplicate Found on row "   duplicateRow)
  }
}

If you have any further doubts about the methods used, I invite you to have a closer look at the respective documentation.