Trigger bug when copying rows

Question

I have a function that copies rows from one sheet to another, which works when I run it manually:

function updateDataRange() {
  var formSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Form Responses 1");
  var lastFormRow = formSheet.getLastRow();
  var dataSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("DataRange");
  dataSheet.clear();
  for(var rowCounter = 1; rowCounter <= lastFormRow; rowCounter++) {
    var sourceRange = formSheet.getRange('B'+rowCounter+':H'+rowCounter);
    var insertRow = dataSheet.getLastRow() + 1;
    var targetRange = dataSheet.getRange('A'+insertRow);
    sourceRange.copyTo(targetRange);
  }
}

However, when I run this via a trigger (e.g. function onEdit(e) { updateDateRange(); } ), there are gaps in the rows and some of the values will be left out. In this case, I know there is a "fix" by using rowCounter instead of insertRow to write the files instead of using getLastRow(), but one can easily see how this is a problem in another scenario.

So I guess my question is simple: Why isn't this working correctly when using a trigger?

Edit: To clarify, I have a 3rd sheet with some conditions/cells (i.e. conditions that influence which rows are to be copied) and changing them (like changing a different date) trigger the function.

Answer 1

Explanation:

You are using an onEdit trigger to capture sheet changes. But onEdit triggers work only when the user changes the value of a cell. Referencing the official documentation:

The onEdit(e) trigger runs automatically when a user changes the value of any cell in a spreadsheet.

In your case, I think you are trying to execute this code when the Form Responses sheet is filled in with data by the form.

There are two kind of trigger functions that work with form submissions and they are both installable.

One is a google sheet event based trigger and the other one a form event based trigger.

Your goal is to execute some code from the google sheets side, so it makes sense to use the first one.

Modifications:

1. Change the name of the function from onEdit to a different name of your choice e.g. myFormSubmitTrigger.

2. Since the trigger is installable you need to "install" a onFormSubmit trigger for the myFormSubmitTrigger function. Here you can find some simple instructions on how to do that.

Answer 2

If your question is why doesn't your function work when the form makes changes to sheet Form Responses 1 then Marios has answered you question accept it and move on. If your trying to run your function when a user is making edits to sheet Form Responses 1 using a simple trigger then a possible explanation for not getting all of the rows completely copied is that simple trigger must complete in 30 seconds. If you continue to accept more data in Form 1 Responses then soon or later you will have problems but with this function it will be a lot later because it will run much faster than your code:

function updateDataRange() {
  const sss=SpreadsheetApp.openById('ssid');
  const ssh=e.source.getSheetByName('Form Responses 1');
  const sr=2;
  const svs=ssh.getRange(sr,2,sh.getLastRow()-ssr+1,7).getValues();
  const dsh=e.source.getSheetByName('DataRange');
  dsh.clear();
  dsh.getRange(1,1,svs.length,svs[0].length).setValues(svs);
}

However, I would recommend that you never edit a form responses sheet. I would consider using the onFormSubmit trigger to capture all of the data to a second sheet that is available to edit. And it will have additional data automatically appended to it via dsh.appendRow(e.values). And so now you would no longer require an onEdit trigger because your data sheet is kept upto data with an onFormSubmit trigger and you may feel free to edit the datasheet any time you wish.

If neither of these questions suffices then I would recommend that you be more clear about what you are asking.