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

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 个解决方案

#1

I'm struggling a bit with the logic behind what you're doing, but I've attempted a solution in sheet 'work2' in this copy of your spreadsheet. Perhaps have a play and report back what's not right or missing.

我正在努力解决你正在做的事情背后的逻辑,但是我已经尝试在电子表格的这个副本中的表'work2'中找到解决方案。也许有一个游戏,并报告什么是不对或缺少。

The script is this:

脚本是这样的:

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]]);
}

and has an "on form submit" trigger attached to it.

并附有一个“on form submit”触发器。

#1