Google Apps Script: Trigger deletes a value placed from Google Forms and notifies the user due to a-CodePudding

Good Day! I am currently making a script in Google Apps Script Where the trigger deletes a value placed from Google Forms and notifies the user that the value was deleted due to a duplicate in the Spreadsheet through setNote(). I have tried using a modal popup but apparently it does not work for triggers. Any way around this or am I doing something wrong with the setNote() function? I am always getting a TypeError from it. Thanks for the help!

function DuplicatePatient() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Form Responses 1");
  var lastrow = ss.getLastRow();
  var values = SpreadsheetApp.getActiveSheet().getRange(1, 2, lastrow, 1).getValues();
  values = values.flat();
  values.pop();
  // just to visualize the results:
  console.log(values);
  var columnB = ["B"];
  var patient = ss.getRange(columnB   lastrow);
  var triggerPatient = patient.getValue();
  var duplicateRow = 1   values.indexOf(triggerPatient);
  var setNoteDestination = SpreadsheetApp.getActiveSheet().getRange(duplicateRow, 2, 1, 1).getValues();
  var setNote1Destination = SpreadsheetApp.getActiveSheet().getRange(lastrow, 2, 1, 1).getValues();
  var timestamp = SpreadsheetApp.getActiveSheet().getRange(duplicateRow, 1, 1, 1).getValues();
      timestampFlat = timestamp.flat();
      Logger.log(timestampFlat)
  if (duplicateRow > 0){
    ss.deleteRow(lastrow);
    setNoteDestination.setNote("⚠️ Duplicate found on row "  duplicateRow  " The input was recorded on " timestamp),
    setNote1Destination.setNote("⚠️ Duplicate found on row "  duplicateRow  " The input was recorded on " timestamp)   
  }
}

CodePudding user response：

I believe your goal is as follows.

You want to check the values of column "B" of the last row and column "B" of other rows except for the last row.
When the duplicate rows of them are existing, you want to set the note to the last row and other duplicated rows.

In your script, the values of setNoteDestination and setNote1Destination are not Class Range objects. By this, an error occurs at setNoteDestination.setNote(,,,). In this case, how about the following modification?

Modified script:

function DuplicatePatient() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Form Responses 1");
  var lastRow = sheet.getLastRow();
  var range = sheet.getRange("A2:B"   lastRow);
  var values = range.getDisplayValues();
  var search = values.pop();
  var { ranges, rows } = values.reduce((o, [, b], i) => {
    if (b == search[1]) {
      o.ranges.push(`B${i   2}`);
      o.rows.push(i   2);
    }
    return o;
  }, { ranges: [], rows: [] });
  if (ranges.length == 0) return;
  // range.clearNote(); // If you want to clear Notes, please use this line.
  ranges.push(`B${lastRow}`);
  rows.push(lastRow);
  var timestamp = search[0];
  var note = "⚠️ Duplicate found on row "   rows.join(",")   " The input was recorded on "   timestamp;
  sheet.getRangeList(ranges).setNote(note);
}

When this script is run, the duplicated values are checked from column "B". The value of the last row is used as the search value. When the duplicated values are found, the value of note is set to column "B" of the rows.

Modified script:

References: