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.
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)
}
}
I believe your goal is as follows.
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?
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);
}
note
is set to column "B" of the rows.