Rename appsscript project on duplication of spreadsheet

user_10523006 0
Viewed 208

I'm duplicating spreadsheets based on a template file with attached appsscript project. Below you can see the basic code. This works perfectly for the spreadsheets, but the name of the appsscript project remains the same as the template file. Which is a problem, as I can't distinguish them anymore. I will have hundreds of duplicates in the end.

Is there a way to set the appsscript project name on duplication?

Thank you in advance!

function copyTemplatev2(filename, sheetID) {

  var ss = SpreadsheetApp.openById(sheetID);

   //Make a copy of the template file
  var copy = DriveApp.getFileById(sheetID).makeCopy()
  var documentId = copy.getId();

  // Set permissions
  copy.setSharing(DriveApp.Access.ANYONE, DriveApp.Permission.EDIT)

  //Rename the copied file
  DriveApp.getFileById(documentId).setName(filename);
}
google-apps-script google-sheets
1 Answers
  • attached appsscript project of a template file with attached appsscript project is the container-bound script of Spreadsheet.
  • You want to rename the GAS project name of the container-bound script of Spreadsheet which was copied.
  • The Spreadsheet is used as the template, and the container-bound script is included in the Spreadsheet.

If my understanding is correct, how about this answer? Please think of this as just one of several possible answers.

Issue and workaround:

  • The container-bound script of Google Docs cannot be retrieved by the methods of Files: list and Files: get in Drive API. This has already been reported to issue tracker.
  • The metadata of container-bound script of Google Docs can be updated by the method of Files: update in Drive API.
  • In your case, the GAS project ID (the script ID) is not changed because it is included in the template Spreadsheet. I think that this can be used for achieving your issue.

From above situation, I would like to propose the following flow.

Flow:

  1. Set the variables of the container-bound script ID of the template Spreadsheet and the original project name of container-bound script ID of the template Spreadsheet.
  2. Rename of the GAS project of the template Spreadsheet to the new project name.
  3. Copy the template Spreadsheet. At this time, the GAS project is also copied as the new project name.
  4. Rename of the GAS project of the template Spreadsheet to the original project name.

By above flow, the GAS project name of container-bound script in the copied Spreadsheet can be renamed.

When above workaround is reflected to your script, it becomes as follows.

Modified script:

Before you run the script, please enable Drive API at Advanced Google services. And please set the variables of GASProjectId, originalGASProjectName and newGASProjectName.

function copyTemplatev2(filename, sheetID) {
  var GASProjectId = "###";  // Please set the container-bound script ID of the template Spreadsheet.
  var originalGASProjectName = "originalName";  // Please set the original project name of container-bound script ID of the template Spreadsheet.
  var newGASProjectName = "newName"; // Please set the new GAS project name.


  // Rename to new project name.
  Drive.Files.update({title: newGASProjectName}, GASProjectId);

  var ss = SpreadsheetApp.openById(sheetID);

  //Make a copy of the template file
  var copy = DriveApp.getFileById(sheetID).makeCopy()
  var documentId = copy.getId();

  // Set permissions
  copy.setSharing(DriveApp.Access.ANYONE, DriveApp.Permission.EDIT)

  //Rename the copied file
  DriveApp.getFileById(documentId).setName(filename);

  // Rename to original project name.
  Drive.Files.update({title: originalGASProjectName}, GASProjectId);
}

References:

If I misunderstood your question and this was not the direction you want, I apologize.

user_71086530
Related