表单提交后，将最后一行复制到另一个电子表格 [英] Copy last row to another spreadsheet upon form submission

问题描述

我有一个带有附加组件的Google电子表格，该附加组件可从表单中提取数据并在表单提交上运行。我还有另一个插件，它将数据从这个电子表格推送到另一个电子表格 - 我们在这里称其为spreadsheet2。在spreadsheet2中，我有自己的脚本，它带有一个函数copyLastRow（），用于将此电子表格的最后一行复制到另一个电子表格 - 我们称之为spreadsheet3。我的脚本应该从电子表格2到电子表格3添加一个新行。它在我手动运行时运行正常，但它不通过项目触发器运行 - 我为脚本编辑器的资源安装了该脚本 - 我尝试了编辑和更改触发器，但它们在数据从电子表格2中被推送时根本没有启动。当我实际编辑电子表格2时，该脚本正在工作。但是，这对我所需要的并不好 - 我真的需要脚本才能在没有人工干预的情况下工作。你可以帮忙吗？

  function copyLastRow（）{
 var target = SpreadsheetApp.openById（'xxxxxxxxx' ）.getSheetByName（ 'Sheet 1中'）; 
 var sheet = SpreadsheetApp.getActiveSpreadsheet（）。getSheetByName（'Sheet1'）; 
 var lastrow = sheet.getLastRow（）; 
 var sourceData = sheet.getRange（lastrow，1，1，53）.getValues（）; 
 target.appendRow（sourceData [0]）; 
  

编辑：我更新了代码 - 我意识到我在这里留下了以前版本的代码。

解决方案

您担心这个函数在多个用户提交表单时是否有效......它不会。但它很容易改进。

有什么问题？当 copyLastRow（）运行时，它假定源电子表格的最后一行包含也触发该函数的响应。但是，在阅读该行之前，其他用户可能会提交表单。 （最终，该函数也会被提交触发，并且可以再次处理同一行）。

这种情况最简单的改进就是利用提供给触发函数的事件对象作为参数。有关背景详情，请参阅 Google表单活动。

新提交的回复是 event.values ，这是一个数组 - 正是 .appendRow（）。以下是我们如何更新 copyLastRow 函数的方法：

 函数copyLastRow（event ）{
 var target = SpreadsheetApp.openById（'xxxxxxxxx'）。getSheetByName（'Sheet1'）; 
 target.appendRow（event.values）; 
} 
  

现在有多少用户提交表单并不重要 - 每个表单都会被处理这个功能是唯一的。

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.

解决方案

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.

这篇关于表单提交后，将最后一行复制到另一个电子表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！