Issues with Google Script on collecting Form responses and moving to another sheet

Posted on

Issues with Google Script on collecting Form responses and moving to another sheet – Whether you’re just starting out with Google Sheets or are a seasoned pro, sooner or later one of your formulas will give you a formula or script parse error message rather than the result you want. However, every Formula user, irrespective of whether it’s a beginner or expert, has invariably come across a formula parse error in Google Sheets at least once in their life (and if you haven’t. you soon will). It can be frustrating, especially if it’s a longer formula where the formula parse error may not be obvious. In this article, we will talk about some common script or formula parse errors in Google Sheets, like Issues with Google Script on collecting Form responses and moving to another sheet, and how to fix it.

I have been trying to piece together some code for a task at work using Google Sheets and I am having some issues with what I can find versus my current knowledge level. I am trying to create a script that will collect responses from a Form, move them to the last row of a separate sheet, and then clear the row.

function onEdit(event) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = event.source.getActiveSheet();
  var r = event.source.getActiveRange();

  if(s.getName() == "Form Responses 2" && r.getColumn() == 1 && r.getValue() > 0) {
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet = ss.getSheetByName("TestEnd");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    s.getRange(row, 1, 1, numColumns).moveTo(target);
    s.deleteRow(row);
  }
}

This code works only when manually changing the data in column A but does not work when data is submitted via the Form response. I would greatly appreciate any help that is offered.

Answer :

Google Apps Script on edit triggers (simple and installable), as well as other similar triggers, only are fired by user actions made by using the Google Sheets user interface, but the on form submit installable triggers are fired when a form response is submitted.

References

Please have a read on how to use triggers on Apps Scripts:

Simple Triggers

Class trigger
Do pay attention on the restrictions

Following your comment you could also try the function onFormSubmit(event)

Follow this link to find out how to test a trigger function in GAS

Leave a Reply

Your email address will not be published.