Speeding up Google Sheets GetRange - CopyTo Macro

user_19893452 0
Viewed 24

First off, I'm like super-rookie at any coding related topics, so all criticism is valid I think. I created a Google Sheet which calculates production costs and generates decent looking quotes to be sent out. In order to record every quote I wrote a macro that creates a new row on a specific sheet and copies all the variables (231 to be exact) from the quote to this new row. The problem is that running this macro takes usually around 2-3 minutes, which is oddly slow. What to do to speed it up?

/** @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 macros
1 Answers

Issue:

There are several actions you can make to improve the efficiency:

  • Instead of retrieving an object every time you want to use it (for example, to get your sheets, you use getSheetByName and via getRange('SheetName'!RangeNotation) multiple times), store the object in a variable (in the sheet case, e.g. Captured) and use that from then on.
  • Since you are only copying values from ranges, you can use getValues and setValues to copy data instead of copyTo. This will allow you to get all sheet values at once and work on them in your script, therefore reducing the amount of times you call the Sheets service. Calling this kind of service is what slows down the script, so any modification that reduces the amount of calls you make will reduce the execution time. See Minimize calls to other services.
  • Also, as long as the cells where you want to post your data are continguous with each other, you should try to get all those cells at once in a single range, as shown in the example below with D6:H6. This will also minimize the calls to Sheets.

Code sample:

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);
  // ...
}

Note:

  • I just added the part corresponding to your original script, but it should be easy to adapt the rest of your code to this workflow.
  • I'd suggest you to take a look at Best Practices.
user_106120110
Related