how to reduce runtime of google app script that using setValues() and copyto() in for loop

user_13657673 0
Viewed 157

how to reduce runtime of google app script that using setValues() and copyto() in forloop

I find some code and modify it for copy format copy and paste

I don't know why use it setValues() and CopyTo() but it working..

i think problem is some class in forloop. but, I don't know how to seperate setValues(),copyto() from forloop in this case

it work well but take many time by setValues() in forloop How do I seperate it from forloop for speed up

function Duplicate_Format() {
  const SS = SpreadsheetApp.getActiveSpreadsheet();
  const Source_NotationRange = "B6:B47";
  const Target_Sheet = SS.getSheets();
  const Source_Data_sheet = SpreadsheetApp.getActive().getSheetByName('01.01');
  const Source_Data_Range = Source_Data_sheet.getRange(Source_NotationRange);
  var Location_First = 4;
  var Location_Quantity = 366;
  const Location_Last = Location_First + Location_Quantity -1;
  
  for( let j = Location_First ; j <= Location_Last; j++){
   var Target_Sheets = Target_Sheet[j];
   var Target_Range = Target_Sheets.getRange(Source_NotationRange);
   Target_Range.setValues(Source_Data_Range.getValues());
   Source_Data_Range.copyTo(Target_Range, {formatOnly:true});
  }
}
google-apps-script google-sheets
1 Answers

I believe your goal as follows.

  • You want to reduce the process cost of your script.

In order to achieve this, I would like to propose to use Sheets API. In this case, the flow of the modified script is as follows.

  1. Create the request body for the CopyPasteRequest of the method of batchUpdate in Sheets API.
  2. Request to Sheets API using the created request body.

Modified script:

Before you run the script, please enable Sheets API at Advanced Google services.

function Duplicate_Format() {
  const SS = SpreadsheetApp.getActiveSpreadsheet();
  const Source_NotationRange = "B6:B47";
  const Target_Sheet = SS.getSheets();
  const Source_Data_sheet = SpreadsheetApp.getActive().getSheetByName('01.01');
  const Source_Data_Range = Source_Data_sheet.getRange(Source_NotationRange);
  var Location_First = 4;
  var Location_Quantity = 366;
  const Location_Last = Location_First + Location_Quantity -1;

  // I modified below script.
  // 1. Create the request body for the CopyPasteRequest of the method of batchUpdate in Sheets API.
  var requests = [];
  var srcStartRow = Source_Data_Range.getRow() - 1;
  var srcEndRow = srcStartRow + Source_Data_Range.getNumRows();
  var srcStartCol = Source_Data_Range.getColumn() - 1;
  var srcEndCol = srcStartCol + Source_Data_Range.getNumColumns();
  for( let j = Location_First ; j <= Location_Last; j++){
    var Target_Sheets = Target_Sheet[j];
    var Target_Range = Target_Sheets.getRange(Source_NotationRange);
    var dstStartRow = Target_Range.getRow() - 1;
    var dstEndRow = dstStartRow + Target_Range.getNumRows();
    var dstStartCol = Target_Range.getColumn() - 1;
    var dstEndCol = dstStartCol + Target_Range.getNumColumns();
    requests.push({
      copyPaste:{
        source:{sheetId:Source_Data_sheet.getSheetId(),startRowIndex:srcStartRow,endRowIndex:srcEndRow,startColumnIndex:srcStartCol,endColumnIndex:srcEndCol},
        destination:{sheetId:Target_Sheets.getSheetId(),startRowIndex:dstStartRow,endRowIndex:dstEndRow,startColumnIndex:dstStartCol,endColumnIndex:dstEndCol},
        pasteType:"PASTE_NORMAL"
      }
    });
  }

  // 2. Request to Sheets API using the created request body.
  var res = Sheets.Spreadsheets.batchUpdate({requests: requests}, SS.getId());
}

References:

user_71086530
Related