Auto Move Google Form Response by ticking check box

Question

I have google Form. User fills google form and a sheet with name "Form Response" is created to record user responses. I would like to use script to move data if I tick mark in W column, from "Form Response" sheet to "Verified Form Responses".

Link of the form Response Sheet is

https://docs.google.com/spreadsheets/d/1LUUEZ7sSjBy-WWL3TZt8l6sqtrEBn3dZ03iRDTY1bF0/edit?usp=sharing

The script I am using is :

function onEdit(event) {
  // assumes source data in sheet named Needed
  // target sheet of move to named Acquired
  // test column with yes/no is col 23 or W
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = event.source.getActiveSheet();
  var r = event.source.getActiveRange();
  if(s.getName() == "Form responses 1" && r.getColumn() == 23 && r.getValue() == true) {
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet = ss.getSheetByName("Verified Form Responses");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    s.getRange(row, 1, 1, numColumns).copyTo(target);
    s.deleteRow(row);
  }
}

This code is not working. Please help.

What does it mean it is not working? do you see any errors in the execution page? do you see a behiavour that you don't expect? because what I see from the file you shared is that the code is working just fine. — soMario

Iamblichus Iamblichus · Accepted Answer · 2020-10-02T12:22:53

Issue:

For some reason the script is detecting content in all 1000 rows from Verified Form Responses sheet.

This can be checked by logging targetSheet.getDataRange().getA1Notation(): the logged notation is A1:Z1000, which means the script is seeing some content in row 1000. It can also be checked by using appendRow(rowContents): the provided data is appended to row 1001, not to the first row with supposedly no content. Things like deleting all values from these rows don't cause any change (the script still considers these rows to have content).

I'm unsure what's the reason for that, since I don't see any array formula that might be messing with that. But every new row that is added to the sheet is interpreted as having content.

In any case, since it's detecting content in row 1000 (the last one in the sheet), getLastRow() + 1 (that is, row 1001) is outside the dimensions of the sheet, so this error message appears:

The coordinates of the target range are outside the dimensions of the sheet.

Solutions:

Fortunately, you have many options to solve this issue. Choose the one you prefer:

Create a new target sheet, and send your data there instead of sending it to Verified Form Responses.
Remove all the supposedly empty rows from Verified Form Responses (that is, A42:Z1000) and use appendRow instead of copyTo (formatting will not be copied):

var targetSheet = ss.getSheetByName("Verified Form Responses");
targetSheet.appendRow(s.getRange(row, 1, 1, numColumns).getValues()[0]);

Find the actual first row without content by applying getDataRange() and filtering out rows for which all cells are empty (only works if there are no in-between empty rows):

var targetSheet = ss.getSheetByName("Verified Form Responses");
var realLastRow = targetSheet.getDataRange().getValues()
                             .filter(row => row.join('') != "").length;
var target = targetSheet.getRange(realLastRow + 1, 1);
s.getRange(row, 1, 1, numColumns).copyTo(target);

Auto Move Google Form Response by ticking check box

1 Answers

Issue:

Solutions: