CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Andii
Andii

Reputation: 594

Rename appsscript project on duplication of spreadsheet

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

Upvotes: 2

Views: 370

Answers (1)

Tanaike
Tanaike

Reputation: 201643

  • 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.

In this case, how about this answer?

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 v3 at Advanced Google services. And please set the variables of spreadsheetId, newSpreadsheetName, GASProjectId, and newGASProjectName.

function myFunction() {
  var spreadsheetId = "###"; // Please set the source Spreadsheet ID.
  var newSpreadsheetName = "newName of copied Spreadsheet"; // Please set the new Spreadsheet name.

  var GASProjectId = "###";  // Please set the container-bound script ID of the template Spreadsheet.
  var newGASProjectName = "newName of copied container-bound script"; // Please set the new GAS project name.

  // Rtetirve the original filename of the filename of the container-bound script.
  var originalGASProjectName = DriveApp.getFileById(GASProjectId).getName();

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

  //Make a copy of the Spreadsheet and rename it.
  var copy = DriveApp.getFileById(spreadsheetId).makeCopy(newSpreadsheetName);

  // Set permissions
  // If you want to publicly share the copied Spreadsheet, please use the below line.
  copy.setSharing(DriveApp.Access.ANYONE, DriveApp.Permission.EDIT);

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

In the current stage, it seems that the container-bound script can also be renamed with DriveApp. So, the above script can be modified as follows. In this case, Drive API is not required to be enabled.

function myFunction() {
  var spreadsheetId = "###"; // Please set the source Spreadsheet ID.
  var newSpreadsheetName = "newName of copied Spreadsheet"; // Please set the new Spreadsheet name.

  var GASProjectId = "###";  // Please set the container-bound script ID of the template Spreadsheet.
  var newGASProjectName = "newName of copied container-bound script"; // Please set the new GAS project name.

  // Rtetirve the original filename of the filename of the container-bound script.
  var originalGASProjectName = DriveApp.getFileById(GASProjectId).getName();

  // Rename to new project name.
  var orgGASProject = DriveApp.getFileById(GASProjectId);
  orgGASProject.setName(newGASProjectName);

  //Make a copy of the Spreadsheet and rename it.
  var copy = DriveApp.getFileById(spreadsheetId).makeCopy(newSpreadsheetName);

  // Set permissions
  // If you want to publicly share the copied Spreadsheet, please use the below line.
  copy.setSharing(DriveApp.Access.ANYONE, DriveApp.Permission.EDIT);

  // Rename to original project name.
  orgGASProject.setName(originalGASProjectName);
}

Note:

  • Updated on July 10, 2024: The old script was for Drive API v2. In the current stage, when Drive API is enabled at Advanced Google services, Drive API v3 is used. So, I modified it.

References:

Upvotes: 4

Related Questions