在Google Sheets脚本中对大型非线性单元格范围使用setValue

Question

我有一个Google Sheets脚本，它在特定单元格的onEdit上运行，它设置了100多个不在线性范围内的单元格的值。我为每个单元格使用了一个单独的setValue命令，这导致函数运行非常缓慢(每个单元格只需要不到一秒钟的时间来填充！(。

我想还有一种更有效的方法可以实现这一点，我很乐意听到任何建议。

以下是我的代码摘录(我只包含了一小部分100多个setValue命令(：

var ss = SpreadsheetApp.getActiveSpreadsheet ();
var dateselected = ss.getRange ("Data Sheet!A2").getValue();
var daterow = getdaterow(dateselected); //the row of the selected date
ss.getRange("Reconciliation!G3").setValue(ss.getRange("Data Sheet!D" + daterow).getValue());
ss.getRange("Reconciliation!K3").setValue(ss.getRange("Data Sheet!E" + daterow).getValue());
ss.getRange("Reconciliation!E7").setValue(ss.getRange("Data Sheet!F" + daterow).getValue());
ss.getRange("Reconciliation!G7").setValue(ss.getRange("Data Sheet!G" + daterow).getValue());
ss.getRange("Reconciliation!E8").setValue(ss.getRange("Data Sheet!H" + daterow).getValue());
ss.getRange("Reconciliation!G8").setValue(ss.getRange("Data Sheet!I" + daterow).getValue());

对于上下文：指定的日期存储在数据表中！A2，在函数getdaterow中用于确定在设置值时要使用的行号。

提前谢谢！

Answer 1

• 您希望使用Google Apps脚本将一个单元格的值复制到一个单元格
• 在你的情况下有很多可以复制的范围
• 您希望降低脚本的处理成本

如果我的理解是正确的，这个答案怎么样？请将此视为几种可能的答案之一。

在这个答案中，我在Sheets API中使用了spreadsheets.values.batchGet和spreadsheets.values.batchUpdate两种方法来降低工艺成本。在这种情况下，您的目标可以通过2个API调用来实现。

修改的脚本：

请按如下方式修改您的脚本。在这种情况下，问题中的当前脚本将被修改。因此，当您在实际情况下使用此选项时，请根据您的情况修改object。此外，在运行脚本之前，请在Advanced Google服务中启用Sheets API

发件人：

var ss = SpreadsheetApp.getActiveSpreadsheet ();
var dateselected = ss.getRange ("Data Sheet!A2").getValue();
var daterow = getdaterow(dateselected); //the row of the selected date
ss.getRange("Reconciliation!G3").setValue(ss.getRange("Data Sheet!D" + daterow).getValue());
ss.getRange("Reconciliation!K3").setValue(ss.getRange("Data Sheet!E" + daterow).getValue());
ss.getRange("Reconciliation!E7").setValue(ss.getRange("Data Sheet!F" + daterow).getValue());
ss.getRange("Reconciliation!G7").setValue(ss.getRange("Data Sheet!G" + daterow).getValue());
ss.getRange("Reconciliation!E8").setValue(ss.getRange("Data Sheet!H" + daterow).getValue());
ss.getRange("Reconciliation!G8").setValue(ss.getRange("Data Sheet!I" + daterow).getValue());

收件人：

var ss = SpreadsheetApp.getActiveSpreadsheet ();
var dateselected = ss.getRange ("Data Sheet!A2").getValue();
var daterow = getdaterow(dateselected); //the row of the selected date
// I modified below script.
var object = [
{srcRange: "Data Sheet!D" + daterow, dstRange: "Reconciliation!G3"},
{srcRange: "Data Sheet!E" + daterow, dstRange: "Reconciliation!K3"},
{srcRange: "Data Sheet!F" + daterow, dstRange: "Reconciliation!E7"},
{srcRange: "Data Sheet!G" + daterow, dstRange: "Reconciliation!G7"},
{srcRange: "Data Sheet!H" + daterow, dstRange: "Reconciliation!E8"},
{srcRange: "Data Sheet!I" + daterow, dstRange: "Reconciliation!G8"},
];
var spreadsheetId = ss.getId();
var srcRanges = {ranges: object.map(function(e) {return e.srcRange})};
var srcValues = Sheets.Spreadsheets.Values.batchGet(spreadsheetId, srcRanges);
var resourceForBatchUpdate = {valueInputOption: "USER_ENTERED", data: object.map(function(e, i) {return {range: e.dstRange, values: srcValues.valueRanges[i].values}})};
Sheets.Spreadsheets.Values.batchUpdate(resourceForBatchUpdate, spreadsheetId);

• 在object中，源范围和目标范围被设置为作为对象的数组。例如，ss.getRange("Reconciliation!G3").setValue(ss.getRange("Data Sheet!D" + daterow).getValue());就是{srcRange: "Data Sheet!D" + daterow, dstRange: "Reconciliation!G3"}。
  • 请根据您的实际情况修改object
• 这个修改后的脚本假定daterow返回行号

参考文献：

• 高级谷歌服务
• 方法：spreadsheets.values.batchGet
• 方法：spreadsheets.values.batchUpdate

如果我误解了你的问题，而这不是你想要的方向，我道歉。