Good Day! I've been struggling in looking for a way to delete rows located using the form response which is then compared to a column full of names from previous form submissions in avoiding repeated data in the spreadsheet. The code below is what I have so far. I apologize if the code doesn't make sense as I was trying to fit previous codes given to me. It had a similar concept and I thought it would work somehow but its not working.

var formResponses = form.getResponses();
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();
    nameofclient_runningbalance.setValue(finalitemResponse);
    Logger.log(finalitemResponse);
    var values = hospitalSheet.getRange(2, 4, hospitalSheet.getLastRow(), 1).getValues();
    Logger.log(values);
    var { v, cells } = values.reduce((o, r, i) => {
    if (r[4] == finalitemResponse) {
      hospitalSheet.getRangeList(cells).deleteRows(cells.length, cells[0].length);
      o.cells.push(`R${i   2}`);
    }
    return o;
  }, { v: [], cells: [] });
  if (v.length == 0) return;
}
}

Something like a validation where in it deletes rows located using the form response given name and compared to a column full of Names. There are other ways through it but I want to know if there's a way through the method I am using now for extra knowledge purposes. Thanks a lot and God Bless!

CodePudding user response：

I believe your goal is as follows.

• You want to delete the rows when the response value of itemResponses[1] is the same as the column "D" of hospitalSheet.

Modification points:

• In your script, at var values = hospitalSheet.getRange(2, 4, hospitalSheet.getLastRow(), 1).getValues();, the values are retrieved from the column "D". But, in values.reduce((o, r, i) => {,,,}, r[4] == finalitemResponse is used. In this case, r[4] has no element. And, at hospitalSheet.getRangeList(cells).deleteRows(cells.length, cells[0].length);, cell is not declared.

• At nameofclient_runningbalance.setValue(finalitemResponse);, the values are put to the same range.

• In your script, I thought that when the delete process is moved to the outside of the loop, the process cost might be able to be reduced.

When these points are reflected in your script, how about the following modification?

Modified script:

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)
    // nameofclient_runningbalance.setValue(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));

or,

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)
    // nameofclient_runningbalance.setValue(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);

• When these scripts are run, the values are retrieved from itemResponses[1], and the rows are deleted by searching the retrieved values from the column "D" of hospitalSheet.

Note:

• In this modification, it supposes that form and hospitalSheet are declared elsewhere. Please be careful about this.

• In this modification, it supposes that from your showing script, the value is searched from column "D" of hospitalSheet. Please be careful about this. If this is different from your actual situation, can you provide the sample Spreadsheet as an image? By this, I would like to confirm it.

• If you are required to use nameofclient_runningbalance.setValue(finalitemResponse);, please remove //.

References:

• reduce()
• filter()