My google sheets function does the job when run from editor but gives different outcome when trigered by Form submit

Question

I have a google form and a sheet that collects the responses which of course always appear at the bottom. I have been using the following script to copy the last response (which is always on the last row) from the Response sheet (Form Responses 2) to row two of another sheet (All Responses). When run by a trigger on Form Submit the script inserts a blank row into All Responses, then the copied values into another row above the blank row. Please can you help and tell me why and how I might change the script so the blank row is not added:

function CopyLastrowformresponse () {
var ss = SpreadsheetApp.getActive();
var AR = ss.getSheetByName("All Responses");
var FR = ss.getSheetByName("Form responses 2");
var FRlastrow = FR.getLastRow();

AR.insertRowBefore(2);
FR.getRange(FRlastrow, 1, FRlastrow, 22).copyTo(AR.getRange("A2"), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);

  }

score 1 · Answer 1 · answered Nov 11 '19 at 05:40

1

A few things could be going on here.

You're getting a number of rows equal to FRlastrow, when I think you only want to be getting 1 row.
Apps Script has buggy behavior with onFormSubmit() triggers, so you may to check duplicate triggers (see this answer).
The script isn't fully exploiting the event object provided by onFormSubmit(). Specifically, rather than getting the last row from one sheet, you could use e.values, which is the same data.

I would change the script to be something like this:

function CopyLastrowformresponse (e) {
  if (e.values && e.values[1] != "") { // assuming e.values[1] (the first question) is required
    SpreadsheetApp.getActive()
      .getSheetByName("All Responses")
      .insertRowBefore(2)
      .getRange(2, 1, 1, e.values.length)
      .setValues([e.values]);
  }
}

But, ultimately, if all you want to do is simply reverse the order of the results, then I'd ditch Apps Script altogether and just use the =SORT() function.

=SORT('Form responses 2'!A:V, 'Form responses 2'!A:A, FALSE)

answered Nov 11 '19 at 05:40

Diego

9,261
2
19
33

Yes duplicate triggers was my first thought but seems not to be the problem. I need the copied cells to be values only so they can be changed so Sort would not work for me. I will try your suggestion - thanks – Roger Pollard Nov 11 '19 at 06:24
Just to add ... I need the values to be copies from 'Responses 2' to 'All Responses' not just on the same sheet – Roger Pollard Nov 11 '19 at 06:32
@RogerPollard Right. My version of your script is essentially doing that. It is not re-arranging the values on a sheet. – Diego Nov 11 '19 at 06:36
@RogerPollard Are the values you have on 'Responses 2' exactly the same as the form responses, or have they been modified? – Diego Nov 11 '19 at 06:50
`Yes duplicate triggers was my first thought but seems not to be the problem`: are you sure of that? There are currently [open bugs](https://issuetracker.google.com/issues/144212497) regarding duplicate form submissions in Issue Tracker. Your issue might be related to that. Also, as @Diego said, I don't think you want the number of rows to be copied to be equal to `FRlastrow`, but to 1. Is there a reason for that? – Iamblichus Nov 11 '19 at 12:45
To test if duplicate triggers I set up new sheet, (test sheet) new name for function, new form, new trigger so duplicate triggers is not the problem. I had misread your script so gave it a try on both live sheet and test sheet and get the same result. The form response is copied to row 2 but a blank row is added below. Must be the open bug referred to. Tried adding to my original script delete row 2 after inserting row and the result was no blank row and no form response values? – Roger Pollard Nov 12 '19 at 07:21
@RogerPollard You tried with the script exactly as I wrote it above? Was there an answer to the first question in the form? – Diego Nov 12 '19 at 07:28
Sorry - yes I just want the last row - what I get with your script and my script is the same: After setting a trigger (on Form Submit) when a form is submitted the last row of values (which are the answers given on the last form submitted) on the form responses sheet are copied exactly to row two of the all responses sheet plus a blank row in row three; but when my script is run from the editor it works without adding a blank row – Roger Pollard Nov 12 '19 at 19:43
Yes I used your script just as written. The first question on the form is actually optional but when I tested it I entered a value on the form – Roger Pollard Nov 12 '19 at 19:44
@RogerPollard That's curious. Would you mind creating a copy of your sheet & form that can be shared? I'm not able to replicate the bug, so am currently at a loss. – Diego Nov 13 '19 at 03:54
Apologies for wasting your time, it turned out to be duplicate scripts. It had been edited in another account that I was not aware of and duplicate triggers set up that i thought had been disabled but not the case. Your script Diego is what i will use in future so thanks for that. I am not sure why the same problem occurred with the new sheet. however all OK now, – Roger Pollard Nov 14 '19 at 08:33
@RogerPollard No problem at all. Glad you were able to resolve it! Please consider marking the answer as accepted. – Diego Nov 14 '19 at 08:41

My google sheets function does the job when run from editor but gives different outcome when trigered by Form submit

1 Answers1