Copy last row to another spreadsheet upon form sub

问题:

I have a Google spreadsheet with an Add-on that takes data from a form and runs on form submission. I also have another Add-on that pushes the data from this spreadsheet to another spreadsheet - let's call it spreadheet2 here. In spreadsheet2 I have my own script with a function copyLastRow() that copies the last row from this spreadsheet to another spreadsheet - let's call it spreadsheet3. My script is supposed to append a new row from spreadsheet2 to spreadsheet3. It runs OK when I run it manually, but it is not running via the project trigger - which I installed for Script editor's Resources - I tried both on Edit and on Change triggers, but they are simply not firing up when data is pushed from spreadsheet2. The script is working when I actually edit spreadsheet2. However, this is not good for what I need - I really need the script to work without manual intervention. Can you, please, help?

function copyLastRow() {  
var target = SpreadsheetApp.openById('xxxxxxxxx').getSheetByName('Sheet1');
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
var lastrow = sheet.getLastRow();
var sourceData = sheet.getRange(lastrow, 1, 1, 53).getValues();
target.appendRow(sourceData[0]);

EDIT: I updated the code - I realized I left the previous version of code here.

回答1:

You're right to worry about whether this function will be effective when multiple users are submitting forms... it won't be. But it's easily improved.

What's the problem? When copyLastRow() runs, it assumes that the last row of the source spreadsheet contains the response that also triggered the function. However, before it gets around to reading that row, another user might submit a form. (Eventually, the function will be triggered by that submission as well, and could process the same row a second time.)

The simplest improvement in this situation is to take advantage of the event object that is provided to the trigger function as a parameter. See Google Sheet Events for some background details.

The newly submitted responses are in event.values, which is an array - exactly what is needed for .appendRow(). Here's how we can update your copyLastRow function:

function copyLastRow(event) {  
  var target = SpreadsheetApp.openById('xxxxxxxxx').getSheetByName('Sheet1');
  target.appendRow(event.values);
}

Now it doesn't matter how many users submit forms - each will be handled uniquely by this function.