Home > Net >  Is there any way to reduce computation time of Spreadsheet.makeCopy() in this setting?
Is there any way to reduce computation time of Spreadsheet.makeCopy() in this setting?

Time:05-26

I have a working script that contains the following code in a method of a custom class:

destination = this
  ._source
  .makeCopy(
    this._name,
    this._folder
  )
  .setStarred(true);

This script is taking approximately 15 seconds to complete. this._source is an instance of DriveApp.File (DriveApp.getFileById('xxxxxxxxxxxx')) of the template file the user is working with. this._name is simply a string created by tacking the year onto the title. 'this._folder' is an instance of DriveApp.Folder (DriveApp.getFolderById('xxxxxxxxxxxx')). All of these except for destination are instantiated when the class is instantiated in my On Open trigger, long before the user begins making the copy, while destination is instantiated at the beginning of this method (though I measured that instantiation to be pretty fast).

I measured 15380 milliseconds before and after this statement. I isolated DriveApp.File.setStarred() and measured 100 milliseconds, so the problem is down to DriveApp.File.makeCopy(). Is there anything I can do to get a faster result? I tried the various versions of .makeCopy, and they all seemed to take a while. I think the problem is the size of my actual file (178 KB, which I believe is fairly large for a Sheets file). It has 13 Sheets, one for the entire year's revenue numbers, and twelve months of daily reconciliation for cash drawers, the safe, and bank deposits/withdrawals, so it has a myriad of formulas in it.

CodePudding user response:

Not sure if it's faster all of the time but it seems faster to me

function makeCopy() {
  var folderid = "dfolderid";
  var fileid = "fileid";
  var folder = Drive.Files.get(folderid, { "supportsAllDrives": true });
  var newFile = { "fileId": fileid, "parents": [folder] };
  var args = { "resource": { "parents": [folder], "title": "new Title" }, "supportsAllDrives": true };
  Drive.Files.copy(newFile, fileid, args);
}

CodePudding user response:

SUGGESTION:

You could try Drive API V2 Files:copy method. Initially tested this on a 280kb sheets file which resulted in 11000 milliseconds in computation time using the API Explorer.

  • Related