Google Apps Script - Running a Trigger to a code that compares formResponse to a Column of names and-CodePudding

Good day! Following up from my last question, I am now struggling in running the code through a trigger set up in Apps Script. It works properly through running it straight from the code but acts unusual on trigger as it deletes anything that is submitted through the form. I was hoping anyone could help me with this because I cannot see any fix around this on the internet.

var formResponses = form.getResponses();
var resValues = [];
for (var i = 0; i < formResponses.length; i  ) {
  var formResponse = formResponses[i];
  var itemResponses = formResponse.getItemResponses();
  for (var j = 0; j < itemResponses.length; j  ) {
    var itemResponse = itemResponses[1];
    var finalitemResponse = itemResponse.getResponse();
    resValues.push(finalitemResponse)
  }
}
resValues = [...new Set(resValues)];
var values = hospitalSheet.getRange(2, 4, hospitalSheet.getLastRow(), 1).getValues();
var rows = values.reduce((ar, [d], i) => {
  if (resValues.includes(d)) {
    ar.push(i   2);
  }
  return ar;
}, []).reverse().forEach(e => hospitalSheet.deleteRow(e));

var formResponses = form.getResponses();
var resValues = [];
for (var i = 0; i < formResponses.length; i  ) {
  var formResponse = formResponses[i];
  var itemResponses = formResponse.getItemResponses();
  for (var j = 0; j < itemResponses.length; j  ) {
    var itemResponse = itemResponses[1];
    var finalitemResponse = itemResponse.getResponse();
    resValues.push(finalitemResponse)
  }
}
resValues = [...new Set(resValues)];
var range = hospitalSheet.getDataRange();
var [header, ...values] = range.getValues();
var newValues = [header, ...values.filter(r => !resValues.includes(r[3]))];
range.clearContent();
hospitalSheet.getRange(1, 1, newValues.length, newValues[0].length).setValues(newValues);

Both beautifully done codes were provided by @Tanaike from my last question. Any help would be much appreciated. Thanks! This is what im currently using on script which is coded in Google Spreasheets.

function onSubmit(){
var hospitalSheet=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("ADELA SERRA TY MEMORIAL MEDICAL CENTER");
var targetSheet=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Running Balance");
var lastrow = hospitalSheet.getLastRow();

var form = FormApp.openById('1i3HGBR0Akl8S4gTet_kiiYsZKbQO-6ctq_3Tl77DcBE');
var formResponses = form.getResponses();
var resValues = [];
for (var i = 0; i < formResponses.length; i  ) {
  var formResponse = formResponses[i];
  var itemResponses = formResponse.getItemResponses();
  for (var j = 0; j < itemResponses.length; j  ) {
    var itemResponse = itemResponses[1];
    var finalitemResponse = itemResponse.getResponse();
    resValues.push(finalitemResponse)
  }
}
resValues = [...new Set(resValues)];
var range = hospitalSheet.getDataRange();
var [header, ...values] = range.getValues();
var newValues = [header, ...values.filter(r => !resValues.includes(r[3]))];
range.clearContent();
hospitalSheet.getRange(1, 1, newValues.length, newValues[0].length).setValues(newValues);
}

CodePudding user response：

From your question, I thought that you might be required to use the response of the 2nd item from the last submitted answers. If my understanding is correct, when your showing script is modified, how about the following modification?

Modified script:

var finalitemResponse = form.getResponses().pop().getItemResponses()[1].getResponse();
var values = hospitalSheet.getRange(2, 4, hospitalSheet.getLastRow(), 1).getValues();
values.reduce((ar, [d], i) => {
  if (finalitemResponse == d) {
    ar.push(i   2);
  }
  return ar;
}, []).reverse().forEach(e => hospitalSheet.deleteRow(e));

var finalitemResponse = form.getResponses().pop().getItemResponses()[1].getResponse();
var range = hospitalSheet.getDataRange();
var [header, ...values] = range.getValues();
var newValues = [header, ...values.filter(r => finalitemResponse != r[3])];
range.clearContent();
hospitalSheet.getRange(1, 1, newValues.length, newValues[0].length).setValues(newValues);

Note:

From running the code through a trigger set up in Apps Script, if you can use the event object, I thought that the submitted response can be retrieved from the event object. But, from your question, I cannot know whether your Google Apps Script is the container-bound script of Google Forms or Google Spreadsheet. So, I proposed the above modification. In this case, you can also directly run the script with the script editor.

Added:

From I had an errand that I had to attend to. I did the script in Google Spreadsheet. and your showing script, when your showing script is modified using the event object, how about the following modification?

Modified script:

function onSubmit(e) {
  var hospitalSheet = e.source.getSheetByName("ADELA SERRA TY MEMORIAL MEDICAL CENTER");
  var finalitemResponse = e.values[2];
  var range = hospitalSheet.getDataRange();
  var [header, ...values] = range.getValues();
  var newValues = [header, ...values.filter(r => finalitemResponse != r[3])];
  range.clearContent();
  hospitalSheet.getRange(1, 1, newValues.length, newValues[0].length).setValues(newValues);
}

By this modification, when the form is sumbmitted, the response of the 2nd item from the submitted answers is retrieved, and using this value, the column "D" is filtered.
In this case, when you directly run the script with the script editor, an error like Cannot read property 'source' of undefined occurs. Please be careful about this.