如何提升Google Apps Script中复制粘贴操作的运行速度？

我的Google Apps Script代码执行超时无法完成，核心为复制粘贴逻辑，问题出在向工作日工作表复制数据的环节。不确定是代码存在问题还是有更高效的实现方式，恳请技术指导。原代码如下：

function myFunction() {
 var week=  SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Production Overview').getRange('b1').getValue();
 var pasteweek= SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Production Overview').getRange('b1').setValue(week);
 var weekdays= SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Production Overview').getRange('c3:i3').getDisplayValues();
 var pasteweekdays= SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Production Overview').getRange('c3:i3').setValues(weekdays);

var rosterclearup=SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Roster').getRange(1,1,1000,13)
rosterclearup.clearContent()
var yushoroster=sheet =SpreadsheetApp.openById("1ILFN5xehSjnRbCWPq20ZJBZF678K6mgn-FerNWv6gCo").getSheetByName('Attendance Sheet').getRange(1,1,1000,13).getDisplayValues()
var pasteroster=SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Roster').getRange(1,1,1000,13).setValues(yushoroster)//
var sortingalphabetically=SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Roster').getRange('A2:M').sort({column: 2, ascending: true,header: true})


let lastrowAM=SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Roster').getRange('P1:P').getLastRow()
let lastrowPM=SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Roster').getRange('O1:O').getLastRow()

let copyAM= SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Roster').getRange(2,16,lastrowAM,).getDisplayValues()
let copyPM= SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Roster').getRange(2,17,lastrowPM,).getDisplayValues()

let pasteAM= SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Thursday').getRange(4,2,lastrowAM).setValues(copyAM)
let pastePM1= SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Thursday').getRange(242,2,lastrowPM).setValues(copyPM)
SpreadsheetApp.getActive().toast("Thrusday Copied")

let pasteAM2= SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Friday').getRange(4,2,lastrowAM).setValues(copyAM)
let pastePM2= SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Friday').getRange(242,2,lastrowPM).setValues(copyPM)
SpreadsheetApp.getActive().toast("Friday Copied")

let pasteAM3= SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Saturday').getRange(4,2,lastrowAM).setValues(copyAM)
let pastePM3= SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Saturday').getRange(242,2,lastrowPM).setValues(copyPM)
SpreadsheetApp.getActive().toast("Saturday Copied")

let pasteAM4= SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sunday').getRange(4,2,lastrowAM).setValues(copyAM) 
let pastePM4= SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sunday').getRange(242,2,lastrowPM).setValues(copyPM)
SpreadsheetApp.getActive().toast("Sunday Copied")

let pasteAM5= SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Monday').getRange(4,2,lastrowAM).setValues(copyAM)
let pastePM5= SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Monday').getRange(242,2,lastrowPM).setValues(copyPM)
SpreadsheetApp.getActive().toast("Monday Copied")

let pasteAM6= SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Tuesday').getRange(4,2,lastrowAM).setValues(copyAM)
let pastePM6= SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Tuesday').getRange(242,2,lastrowPM).setValues(copyPM)
SpreadsheetApp.getActive().toast("Tuesday Copied")


let pasteAM7= SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Wednesday').getRange(4,2,lastrowAM).setValues(copyAM)
let pastePM7= SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Wednesday').getRange(242,2,lastrowPM).setValues(copyPM)
SpreadsheetApp.getActive().toast("Wednesday Copied")
}

核心优化点 #

• 缓存常用对象：提前获取并缓存当前活动表格、Roster表、Production Overview表等对象，避免反复调用服务接口。
• 删除冗余操作：移除getValue()后立刻setValue()相同值、getDisplayValues()后立刻setValues()相同值这类无意义操作。
• 精确获取数据范围：用getLastRow()获取实际有数据的行数，替代固定1000行的范围，减少不必要的数据读取。
• 批量循环处理工作表：将所有工作日工作表存入数组，通过循环完成复制粘贴，减少重复代码并统一执行逻辑。
• 减少Toast提示：将多个Toast合并为一个最终提示，降低UI交互带来的耗时。

优化后的代码 #

function optimizedFunction() {
  // 缓存常用表格对象
  const activeSS = SpreadsheetApp.getActiveSpreadsheet();
  const rosterSheet = activeSS.getSheetByName('Roster');
  const externalSS = SpreadsheetApp.openById("1ILFN5xehSjnRbCWPq20ZJBZF678K6mgn-FerNWv6gCo");
  const attendanceSheet = externalSS.getSheetByName('Attendance Sheet');

  // 清理Roster表内容并复制外部考勤数据
  const rosterLastRow = attendanceSheet.getLastRow();
  const rosterLastCol = attendanceSheet.getLastColumn();
  rosterSheet.getRange(1, 1, rosterLastRow, rosterLastCol).clearContent();
  
  const attendanceData = attendanceSheet.getRange(1, 1, rosterLastRow, rosterLastCol).getDisplayValues();
  rosterSheet.getRange(1, 1, rosterLastRow, rosterLastCol).setValues(attendanceData);
  
  // 对Roster表排序
  rosterSheet.getRange('A2:M').sort({column: 2, ascending: true, header: true});

  // 获取AM/PM数据（修正行数计算，避免空行）
  const lastrowAM = rosterSheet.getRange('P:P').getLastRow();
  const lastrowPM = rosterSheet.getRange('O:O').getLastRow();
  const copyAM = rosterSheet.getRange(2, 16, lastrowAM - 1).getDisplayValues();
  const copyPM = rosterSheet.getRange(2, 17, lastrowPM - 1).getDisplayValues();

  // 批量处理所有工作日工作表
  const weekDays = ['Thursday', 'Friday', 'Saturday', 'Sunday', 'Monday', 'Tuesday', 'Wednesday'];
  weekDays.forEach(day => {
    const daySheet = activeSS.getSheetByName(day);
    daySheet.getRange(4, 2, copyAM.length, 1).setValues(copyAM);
    daySheet.getRange(242, 2, copyPM.length, 1).setValues(copyPM);
  });

  // 统一完成提示
  SpreadsheetApp.getActive().toast("所有工作日数据复制完成");
}

内容的提问来源于stack exchange，提问作者Jessica Sartori