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

Question

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.

Just wanted to add that I ran "Make a Copy" from Google Drive context menu and took the same amount of time. So I'm guessing there's no way to do this without reducing file size. — Dexter, May 25 '22 at 23:43

score 2 · Accepted Answer · answered May 26 '22 at 01:03

2

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.

answered May 26 '22 at 01:03

Century Tuna

1,378
1
6
13

1

This worked great for me, thanks. Took me a minute to read through the documentation, but there was a drastic difference in speed. – Dexter May 26 '22 at 21:15

Cooper · Answer 2 · 2022-05-26T01:45:30.490

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

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

2 Answers2

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

Linked