将Google文档中的范围复制到另一个工作表-Copyrangeingoogledocstoanothersheet

I have Google form that gets filled in by a few users. Works great but has it's limitations.

我有谷歌表格,由少数用户填写。效果很好,但有其局限性。

I'd like to copy the data entered from the active sheet to a new sheet called "work", all information except the first row that is. In the first row I have a few array formulas that populate some cells as new data is entered on the active sheet.

我想将从活动工作表输入的数据复制到名为“work”的新工作表中,除了第一行之外的所有信息。在第一行中,我有一些数组公式,在活动工作表上输入新数据时填充一些单元格。

The second sheet (work) has a header row with all the formatting, data validation, some formulas etc (row 1). This information can not be applied when a new record is added via the form.. so I am told..

第二个工作表(工作)有一个标题行,包含所有格式,数据验证,一些公式等(第1行)。当通过表单添加新记录时,无法应用此信息..所以我被告知..

Thus, once the data has been copied from the active sheet (called active) I'd like the new data to be formatted as per the heading row (row 1) of the "work" sheet with all the formatting, validation, formulas etc being applied to the new data.

因此,一旦从活动工作表复制数据(称为活动),我希望根据“工作”工作表的标题行(第1行)格式化新数据,包括所有格式,验证,公式等被应用于新数据。

Is this doable? I am a noob when it comes to scripting so a complete solution would be highly appreciated.

这可行吗?在编写脚本时我是一个菜鸟,所以我们非常感谢完整的解决方案。

here is a sample form you can play with https://docs.google.com/spreadsheet/ccc?key=0AplugTacg-08dFNRUHROSW82bDhESkxBdjVTV0NOLUE

这是一个示例表单,您可以使用https://docs.google.com/spreadsheet/ccc?key=0AplugTacg-08dFNRUHROSW82bDhESkxBdjVTV0NOLUE

First thing i noticed one can not just copy/paste as the array formulas will bong things up so it has to be a paste special - values only

首先我注意到一个不能只是复制/粘贴,因为数组公式会把事情搞砸,所以它必须是一个特殊的粘贴 - 仅限值

Any help greatly appreciated.

任何帮助非常感谢。

1 个解决方案

function onFormSubmit(e) {
  var sheet = SpreadsheetApp.getActive().getSheetByName('work2');
  var nextRow = sheet.getRange(sheet.getLastRow() + 1, 1, 1, 9); 
  sheet.getRange(2, 1, 1, 9).copyTo(nextRow);
  var sLength = e.values[2].length;
  var huuh = e.values[3] * sLength;
  var pending = e.values[3] * e.values[3] / huuh;
  var nextMOnth= e.values[3] + pending;
  nextRow.setValues([[e.values[0], e.values[1], huuh, e.values[2], sLength, e.values[3], 'Please select action', pending, nextMonth]]);
}