文档建议反馈控制台
首页
学习
活动
专区
圈层
工具
MCP广场
文章/答案/技术大牛
发布
社区首页 >问答首页 >加速Google单张GetRange - CopyTo宏

问加速Google单张GetRange - CopyTo宏
EN

Stack Overflow用户
提问于 2022-09-01 07:22:38
回答 1查看 35关注 0票数 1

首先，我就像超级新手在任何编码相关的话题，所以所有的批评是有效的，我认为。我创建了一个谷歌表，它计算生产成本，并生成体面的报价发送出去。为了记录每一个引号，我编写了一个脚本，它在特定的工作表上创建一个新行，并将所有变量(确切地说是231)从引号复制到这个新行。问题是，运行这个脚本通常需要2-3分钟，这是奇怪的慢。该怎么做才能加快速度？

代码语言：javascript
复制
/** @OnlyCurrentDoc */


function RecordTest() {

  var spreadsheet = SpreadsheetApp.getActive();
 var Captured = spreadsheet.getSheetByName("Captured data");


// General from quote sheet

  spreadsheet.getSheetByName("Captured data").insertRowsBefore(spreadsheet.getRange('\'Captured Data\'!6:6').getRow(), 1);
  spreadsheet.getRange('\'Captured Data\'!C6').setFormula('=C7+1');

  spreadsheet.getRange('\'Quote generator\'!F10').copyTo(spreadsheet.getRange('\'Captured Data\'!D6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false); //Quote number
  spreadsheet.getRange('\'Quote generator\'!C10').copyTo(spreadsheet.getRange('\'Captured Data\'!E6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false); //Quote Date
  spreadsheet.getRange('\'Quote generator\'!B13').copyTo(spreadsheet.getRange('\'Captured Data\'!F6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false); //Customer 
  spreadsheet.getRange('\'Quote generator\'!G45').copyTo(spreadsheet.getRange('\'Captured Data\'!G6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false); //Total
  spreadsheet.getRange('\'Quote generator\'!B20').copyTo(spreadsheet.getRange('\'Captured Data\'!H6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false); //Contact person
  spreadsheet.getRange('\'Quote generator\'!J38').copyTo(spreadsheet.getRange('\'Captured Data\'!X6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false); //Comment
  
//Include services 1-3
 spreadsheet.getRange('\'Quote generator\'!J27').copyTo(spreadsheet.getRange('\'Captured Data\'!IA6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false); //Include service 1
 spreadsheet.getRange('\'Quote generator\'!J28').copyTo(spreadsheet.getRange('\'Captured Data\'!IB6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false); //Include service 2
 spreadsheet.getRange('\'Quote generator\'!J29').copyTo(spreadsheet.getRange('\'Captured Data\'!IC6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false); //Include service 3


  spreadsheet.getRange('\'Quote generator\'!B37:C37').copyTo(spreadsheet.getRange('\'Captured Data\'!I6:J6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false); //Service no1 and description
  spreadsheet.getRange('\'Quote generator\'!E37:G37').copyTo(spreadsheet.getRange('\'Captured Data\'!K6:M6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false); //QTY, unit price and total
  spreadsheet.getRange('\'Quote generator\'!B38:C38').copyTo(spreadsheet.getRange('\'Captured Data\'!N6:O6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false); //Service no1 and description
  spreadsheet.getRange('\'Quote generator\'!E38:G38').copyTo(spreadsheet.getRange('\'Captured Data\'!P6:R6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false); //QTY, unit price and total
  spreadsheet.getRange('\'Quote generator\'!B39:C39').copyTo(spreadsheet.getRange('\'Captured Data\'!S6:T6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false); //Service no1 and description
  spreadsheet.getRange('\'Quote generator\'!E39:G39').copyTo(spreadsheet.getRange('\'Captured Data\'!U6:W6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false); //QTY, unit price and total

  spreadsheet.getRange('\'Quote generator\'!J17').copyTo(spreadsheet.getRange('\'Captured Data\'!Y6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);  //Include PN 1 
  spreadsheet.getRange('\'Quote generator\'!J18').copyTo(spreadsheet.getRange('\'Captured Data\'!Z6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);  //Include PN 2
  spreadsheet.getRange('\'Quote generator\'!J19').copyTo(spreadsheet.getRange('\'Captured Data\'!AA6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false); //Include PN 3 
  spreadsheet.getRange('\'Quote generator\'!J20').copyTo(spreadsheet.getRange('\'Captured Data\'!AB6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false); //Include PN 4 
  spreadsheet.getRange('\'Quote generator\'!J21').copyTo(spreadsheet.getRange('\'Captured Data\'!AC6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false); //Include PN 5 
  spreadsheet.getRange('\'Quote generator\'!J22').copyTo(spreadsheet.getRange('\'Captured Data\'!AD6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false); //Include PN 6 

 //Part 1

  spreadsheet.getRange('\'Part 1\'!C7').copyTo(spreadsheet.getRange('\'Captured Data\'!AE6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  spreadsheet.getRange('\'Part 1\'!C8').copyTo(spreadsheet.getRange('\'Captured Data\'!AF6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  spreadsheet.getRange('\'Part 1\'!C4').copyTo(spreadsheet.getRange('\'Captured Data\'!AG6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  spreadsheet.getRange('\'Part 1\'!C5').copyTo(spreadsheet.getRange('\'Captured Data\'!AH6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  spreadsheet.getRange('\'Part 1\'!C11').copyTo(spreadsheet.getRange('\'Captured Data\'!AI6'),SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  spreadsheet.getRange('\'Part 1\'!C12').copyTo(spreadsheet.getRange('\'Captured Data\'!AJ6'),SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  spreadsheet.getRange('\'Part 1\'!C13:D13').copyTo(spreadsheet.getRange('\'Captured Data\'!AK6:AL6'),SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  spreadsheet.getRange('\'Part 1\'!D14').copyTo(spreadsheet.getRange('\'Captured Data\'!AM6'),SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  spreadsheet.getRange('\'Part 1\'!C15:D15').copyTo(spreadsheet.getRange('\'Captured Data\'!AN6:AO6'),SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  spreadsheet.getRange('\'Part 1\'!C16').copyTo(spreadsheet.getRange('\'Captured Data\'!AP6'),SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  spreadsheet.getRange('\'Part 1\'!D17').copyTo(spreadsheet.getRange('\'Captured Data\'!AQ6'),SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  spreadsheet.getRange('\'Part 1\'!C23').copyTo(spreadsheet.getRange('\'Captured Data\'!AR6'),SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  spreadsheet.getRange('\'Part 1\'!D24').copyTo(spreadsheet.getRange('\'Captured Data\'!AS6'),SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  spreadsheet.getRange('\'Part 1\'!C30').copyTo(spreadsheet.getRange('\'Captured Data\'!AT6'),SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  spreadsheet.getRange('\'Part 1\'!C31').copyTo(spreadsheet.getRange('\'Captured Data\'!AU6'),SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  spreadsheet.getRange('\'Part 1\'!C32').copyTo(spreadsheet.getRange('\'Captured Data\'!AV6'),SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  spreadsheet.getRange('\'Part 1\'!C33').copyTo(spreadsheet.getRange('\'Captured Data\'!AW6'),SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  spreadsheet.getRange('\'Part 1\'!C34:D34').copyTo(spreadsheet.getRange('\'Captured Data\'!AX6:AY6'),SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  spreadsheet.getRange('\'Part 1\'!C35').copyTo(spreadsheet.getRange('\'Captured Data\'!AZ6'),SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  spreadsheet.getRange('\'Part 1\'!C36').copyTo(spreadsheet.getRange('\'Captured Data\'!BA6'),SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  spreadsheet.getRange('\'Part 1\'!C37').copyTo(spreadsheet.getRange('\'Captured Data\'!BB6'),SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  spreadsheet.getRange('\'Part 1\'!C38').copyTo(spreadsheet.getRange('\'Captured Data\'!BC6'),SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  spreadsheet.getRange('\'Part 1\'!C45').copyTo(spreadsheet.getRange('\'Captured Data\'!BD6'),SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  spreadsheet.getRange('\'Part 1\'!C46').copyTo(spreadsheet.getRange('\'Captured Data\'!BE6'),SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  spreadsheet.getRange('\'Part 1\'!C47').copyTo(spreadsheet.getRange('\'Captured Data\'!BF6'),SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  spreadsheet.getRange('\'Part 1\'!H3').copyTo(spreadsheet.getRange('\'Captured Data\'!BG6'),SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  spreadsheet.getRange('\'Part 1\'!H4').copyTo(spreadsheet.getRange('\'Captured Data\'!BH6'),SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  spreadsheet.getRange('\'Part 1\'!H5').copyTo(spreadsheet.getRange('\'Captured Data\'!BI6'),SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  spreadsheet.getRange('\'Part 1\'!H7').copyTo(spreadsheet.getRange('\'Captured Data\'!BJ6'),SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  spreadsheet.getRange('\'Part 1\'!H8').copyTo(spreadsheet.getRange('\'Captured Data\'!BK6'),SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  spreadsheet.getRange('\'Part 1\'!H9').copyTo(spreadsheet.getRange('\'Captured Data\'!BL6'),SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);


 
 //Part 2

spreadsheet.getRange('\'Part 2\'!C7').copyTo(spreadsheet.getRange('\'Captured Data\'!BM6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 2\'!C8').copyTo(spreadsheet.getRange('\'Captured Data\'!BN6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 2\'!C4').copyTo(spreadsheet.getRange('\'Captured Data\'!BO6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 2\'!C5').copyTo(spreadsheet.getRange('\'Captured Data\'!BP6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 2\'!C11').copyTo(spreadsheet.getRange('\'Captured Data\'!BQ6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 2\'!C12').copyTo(spreadsheet.getRange('\'Captured Data\'!BR6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 2\'!C13:D13').copyTo(spreadsheet.getRange('\'Captured Data\'!BS6:BT6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 2\'!D14').copyTo(spreadsheet.getRange('\'Captured Data\'!BU6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 2\'!C15:D15').copyTo(spreadsheet.getRange('\'Captured Data\'!BV6:BW6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 2\'!C16').copyTo(spreadsheet.getRange('\'Captured Data\'!BX6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 2\'!D17').copyTo(spreadsheet.getRange('\'Captured Data\'!BY6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 2\'!C23').copyTo(spreadsheet.getRange('\'Captured Data\'!BZ6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 2\'!D24').copyTo(spreadsheet.getRange('\'Captured Data\'!CA6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 2\'!C30').copyTo(spreadsheet.getRange('\'Captured Data\'!CB6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 2\'!C31').copyTo(spreadsheet.getRange('\'Captured Data\'!CC6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 2\'!C32').copyTo(spreadsheet.getRange('\'Captured Data\'!CD6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 2\'!C33').copyTo(spreadsheet.getRange('\'Captured Data\'!CE6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 2\'!C34:D34').copyTo(spreadsheet.getRange('\'Captured Data\'!CF6:CG6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 2\'!C35').copyTo(spreadsheet.getRange('\'Captured Data\'!CH6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 2\'!C36').copyTo(spreadsheet.getRange('\'Captured Data\'!CI6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 2\'!C37').copyTo(spreadsheet.getRange('\'Captured Data\'!CJ6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 2\'!C38').copyTo(spreadsheet.getRange('\'Captured Data\'!CK6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 2\'!C45').copyTo(spreadsheet.getRange('\'Captured Data\'!CL6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 2\'!C46').copyTo(spreadsheet.getRange('\'Captured Data\'!CM6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 2\'!C47').copyTo(spreadsheet.getRange('\'Captured Data\'!CN6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 2\'!H3').copyTo(spreadsheet.getRange('\'Captured Data\'!CO6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 2\'!H4').copyTo(spreadsheet.getRange('\'Captured Data\'!CP6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 2\'!H5').copyTo(spreadsheet.getRange('\'Captured Data\'!CQ6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 2\'!H7').copyTo(spreadsheet.getRange('\'Captured Data\'!CR6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 2\'!H8').copyTo(spreadsheet.getRange('\'Captured Data\'!CS6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 2\'!H9').copyTo(spreadsheet.getRange('\'Captured Data\'!CT6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);

//Part3

spreadsheet.getRange('\'Part 3\'!C7').copyTo(spreadsheet.getRange('\'Captured Data\'!CU6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 3\'!C8').copyTo(spreadsheet.getRange('\'Captured Data\'!CV6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 3\'!C4').copyTo(spreadsheet.getRange('\'Captured Data\'!CW6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 3\'!C5').copyTo(spreadsheet.getRange('\'Captured Data\'!CX6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 3\'!C11').copyTo(spreadsheet.getRange('\'Captured Data\'!CY6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 3\'!C12').copyTo(spreadsheet.getRange('\'Captured Data\'!CZ6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 3\'!C13:D13').copyTo(spreadsheet.getRange('\'Captured Data\'!DA6:DB6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 3\'!D14').copyTo(spreadsheet.getRange('\'Captured Data\'!DC6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 3\'!C15:D15').copyTo(spreadsheet.getRange('\'Captured Data\'!DD6:DE6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 3\'!C16').copyTo(spreadsheet.getRange('\'Captured Data\'!DF6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 3\'!D17').copyTo(spreadsheet.getRange('\'Captured Data\'!DG6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 3\'!C23').copyTo(spreadsheet.getRange('\'Captured Data\'!DH6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 3\'!D24').copyTo(spreadsheet.getRange('\'Captured Data\'!DI6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 3\'!C30').copyTo(spreadsheet.getRange('\'Captured Data\'!DJ6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 3\'!C31').copyTo(spreadsheet.getRange('\'Captured Data\'!DK6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 3\'!C32').copyTo(spreadsheet.getRange('\'Captured Data\'!DL6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 3\'!C33').copyTo(spreadsheet.getRange('\'Captured Data\'!DM6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 3\'!C34:D34').copyTo(spreadsheet.getRange('\'Captured Data\'!DN6:DO6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 3\'!C35').copyTo(spreadsheet.getRange('\'Captured Data\'!DP6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 3\'!C36').copyTo(spreadsheet.getRange('\'Captured Data\'!DQ6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 3\'!C37').copyTo(spreadsheet.getRange('\'Captured Data\'!DR6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 3\'!C38').copyTo(spreadsheet.getRange('\'Captured Data\'!DS6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 3\'!C45').copyTo(spreadsheet.getRange('\'Captured Data\'!DT6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 3\'!C46').copyTo(spreadsheet.getRange('\'Captured Data\'!DU6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 3\'!C47').copyTo(spreadsheet.getRange('\'Captured Data\'!DV6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 3\'!H3').copyTo(spreadsheet.getRange('\'Captured Data\'!DW6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 3\'!H4').copyTo(spreadsheet.getRange('\'Captured Data\'!DX6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 3\'!H5').copyTo(spreadsheet.getRange('\'Captured Data\'!DY6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 3\'!H7').copyTo(spreadsheet.getRange('\'Captured Data\'!DZ6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 3\'!H8').copyTo(spreadsheet.getRange('\'Captured Data\'!EA6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 3\'!H9').copyTo(spreadsheet.getRange('\'Captured Data\'!EB6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);

//Part 4

spreadsheet.getRange('\'Part 4\'!C7').copyTo(spreadsheet.getRange('\'Captured Data\'!EC6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 4\'!C8').copyTo(spreadsheet.getRange('\'Captured Data\'!ED6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 4\'!C4').copyTo(spreadsheet.getRange('\'Captured Data\'!EE6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 4\'!C5').copyTo(spreadsheet.getRange('\'Captured Data\'!EF6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 4\'!C11').copyTo(spreadsheet.getRange('\'Captured Data\'!EG6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 4\'!C12').copyTo(spreadsheet.getRange('\'Captured Data\'!EH6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 4\'!C13:D13').copyTo(spreadsheet.getRange('\'Captured Data\'!EI6:EJ6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 4\'!D14').copyTo(spreadsheet.getRange('\'Captured Data\'!EK6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 4\'!C15:D15').copyTo(spreadsheet.getRange('\'Captured Data\'!EL6:EM6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 4\'!C16').copyTo(spreadsheet.getRange('\'Captured Data\'!EN6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 4\'!D17').copyTo(spreadsheet.getRange('\'Captured Data\'!EO6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 4\'!C23').copyTo(spreadsheet.getRange('\'Captured Data\'!EP6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 4\'!D24').copyTo(spreadsheet.getRange('\'Captured Data\'!EQ6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 4\'!C30').copyTo(spreadsheet.getRange('\'Captured Data\'!ER6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 4\'!C31').copyTo(spreadsheet.getRange('\'Captured Data\'!ES6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 4\'!C32').copyTo(spreadsheet.getRange('\'Captured Data\'!ET6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 4\'!C33').copyTo(spreadsheet.getRange('\'Captured Data\'!EU6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 4\'!C34:D34').copyTo(spreadsheet.getRange('\'Captured Data\'!EV6:EW6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 4\'!C35').copyTo(spreadsheet.getRange('\'Captured Data\'!EX6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 4\'!C36').copyTo(spreadsheet.getRange('\'Captured Data\'!EY6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 4\'!C37').copyTo(spreadsheet.getRange('\'Captured Data\'!EZ6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 4\'!C38').copyTo(spreadsheet.getRange('\'Captured Data\'!FA6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 4\'!C45').copyTo(spreadsheet.getRange('\'Captured Data\'!FB6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 4\'!C46').copyTo(spreadsheet.getRange('\'Captured Data\'!FC6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 4\'!C47').copyTo(spreadsheet.getRange('\'Captured Data\'!FD6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 4\'!H3').copyTo(spreadsheet.getRange('\'Captured Data\'!FE6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 4\'!H4').copyTo(spreadsheet.getRange('\'Captured Data\'!FF6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 4\'!H5').copyTo(spreadsheet.getRange('\'Captured Data\'!FG6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 4\'!H7').copyTo(spreadsheet.getRange('\'Captured Data\'!FH6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 4\'!H8').copyTo(spreadsheet.getRange('\'Captured Data\'!FI6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 4\'!H9').copyTo(spreadsheet.getRange('\'Captured Data\'!FJ6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);

//Part 5

spreadsheet.getRange('\'Part 5\'!C7').copyTo(spreadsheet.getRange('\'Captured Data\'!FK6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 5\'!C8').copyTo(spreadsheet.getRange('\'Captured Data\'!FL6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 5\'!C4').copyTo(spreadsheet.getRange('\'Captured Data\'!FM6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 5\'!C5').copyTo(spreadsheet.getRange('\'Captured Data\'!FN6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 5\'!C11').copyTo(spreadsheet.getRange('\'Captured Data\'!FO6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 5\'!C12').copyTo(spreadsheet.getRange('\'Captured Data\'!FP6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 5\'!C13:D13').copyTo(spreadsheet.getRange('\'Captured Data\'!FQ6:FR6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 5\'!D14').copyTo(spreadsheet.getRange('\'Captured Data\'!FS6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 5\'!C15:D15').copyTo(spreadsheet.getRange('\'Captured Data\'!FT6:FU6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 5\'!C16').copyTo(spreadsheet.getRange('\'Captured Data\'!FV6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 5\'!D17').copyTo(spreadsheet.getRange('\'Captured Data\'!FW6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 5\'!C23').copyTo(spreadsheet.getRange('\'Captured Data\'!FX6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 5\'!D24').copyTo(spreadsheet.getRange('\'Captured Data\'!FY6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 5\'!C30').copyTo(spreadsheet.getRange('\'Captured Data\'!FZ6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 5\'!C31').copyTo(spreadsheet.getRange('\'Captured Data\'!GA6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 5\'!C32').copyTo(spreadsheet.getRange('\'Captured Data\'!GB6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 5\'!C33').copyTo(spreadsheet.getRange('\'Captured Data\'!GC6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 5\'!C34:D34').copyTo(spreadsheet.getRange('\'Captured Data\'!GD6:GE6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 5\'!C35').copyTo(spreadsheet.getRange('\'Captured Data\'!GF6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 5\'!C36').copyTo(spreadsheet.getRange('\'Captured Data\'!GG6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 5\'!C37').copyTo(spreadsheet.getRange('\'Captured Data\'!GH6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 5\'!C38').copyTo(spreadsheet.getRange('\'Captured Data\'!GI6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 5\'!C45').copyTo(spreadsheet.getRange('\'Captured Data\'!GJ6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 5\'!C46').copyTo(spreadsheet.getRange('\'Captured Data\'!GK6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 5\'!C47').copyTo(spreadsheet.getRange('\'Captured Data\'!GL6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 5\'!H3').copyTo(spreadsheet.getRange('\'Captured Data\'!GM6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 5\'!H4').copyTo(spreadsheet.getRange('\'Captured Data\'!GN6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 5\'!H5').copyTo(spreadsheet.getRange('\'Captured Data\'!GO6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 5\'!H7').copyTo(spreadsheet.getRange('\'Captured Data\'!GP6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 5\'!H8').copyTo(spreadsheet.getRange('\'Captured Data\'!GQ6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('\'Part 5\'!H9').copyTo(spreadsheet.getRange('\'Captured Data\'!GR6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);


  };
google-apps-script
google-sheets
EN

回答 1

Stack Overflow用户

发布于 2022-09-01 09:52:00

问题：

您可以采取以下几项措施来提高效率：

  • 不要每次想要使用对象时都检索它(例如，为了获取工作表，您可以多次使用getSheetByName并通过getRange('SheetName'!RangeNotation) )，将对象存储在一个变量中(在工作表中，例如Captured)，并从那时起使用它。
  • 因为您只是从范围复制值，所以可以使用getValues和setValues来复制数据，而不是copyTo。这将允许您一次获得所有工作表值，并在脚本中处理它们，从而减少调用sheet服务的次数。调用这种服务会减慢脚本的速度，因此任何减少调用量的修改都会减少执行时间。见尽量减少对其他服务的呼叫。
  • 此外，只要您希望发布数据的单元格彼此之间是相互关联的，您就应该尝试在一个范围内同时获取所有这些单元格，如下面使用D6:H6的示例所示。这也将最小化对工作表的调用。

代码示例：

代码语言：javascript
复制
function RecordTest() {
  var spreadsheet = SpreadsheetApp.getActive();
  var Captured = spreadsheet.getSheetByName("Captured data");
  Captured.insertRowsBefore(6, 1);
  Captured.getRange('C6').setFormula('=C7+1');
  var QuoteGenerator = spreadsheet.getSheetByName("Quote generator");
  var sourceData = QuoteGenerator.getDataRange().getValues();
  var f10 = sourceData[9][5];
  var c10 = sourceData[9][2];
  var b13 = sourceData[12][1];
  var g45 = sourceData[44][6];
  var b20 = sourceData[19][1];
  var j38 = sourceData[37][9];
  Captured.getRange('D6:H6').setValues([[f10,c10,b13,g45,b20]]);
  Captured.getRange('X6').setValue(j38);
  // ...
}

注意：

  • 我刚刚添加了与原始脚本相对应的部分，但是应该很容易将您的其余代码调整到这个工作流中。
  • 我建议你看看最佳实践。
票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：

https://stackoverflow.com/questions/73565559

复制
相关文章

相似问题

领券

腾讯云开发者

扫码关注腾讯云开发者

扫码关注腾讯云开发者

领取腾讯云代金券

热门产品

热门推荐

更多推荐

Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 粤公网安备44030502008569号

腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号

问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档