1

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.

  • Welcome to Webapps. I think your issue is function onEdit(event). Have you tried using a trigger onFormSubmit ? – marikamitsos Aug 10 at 22:48
  • Thank you. I was trying to stay away from using that application, as my work network has banned using that webpage for some odd reason, but I think I will attempt it from home to at least set up the code. – John Thompson Aug 10 at 22:56
1

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

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.