Google App Script - Save the sheet file Id created from my sheet file template-CodePudding

thank you for the time you will take to resolve my issue !

I am not sure that Google app script allows to do what I need. Could you please tell me if it is possible? If yes, do you have already a script code to do it?

I have created a file which I have shared it with others colleagues (in a shared drive), and it is used as a "template".

When a colleague creates a copy of it, I would like that the script to give me the new Google sheet id created from the model and saved this id in my Google sheet dashboard?

Is it possible with appscript?

Thanks a lot and have a good day !

CodePudding user response：

Copy Spreadsheet and Save Id

function copySpreadsheetAndSaveId() {
  const fileId = "fileid";
  const ss = SpreadsheetApp.getActive():
  const sh = ss.getSheetByName("Dashboard");
  sh.getRange(sh.getLastRow()   1, 1).setValue(DriveApp.getFileById(fileId).makeCopy().getId());//appends the id to the bottom of column one in the sheeet named Dashboard.
}

CodePudding user response：

Sorry Cooper. Yes your script proposition runs correctly. I've modified a little bit the content in order to write the id copy file in another file than the model, as hereafter:

function copySpreadsheetAndSaveId() {
  const modelId = "id model";

  const ssDashboard = SpreadsheetApp.openById("id dashboard");
  const shDashboard = ssDashboard.getSheetByName("Dashboard");
  shDashboard.getRange(shDashboard.getLastRow()   1, 1).setValue(DriveApp.getFileById(modelId).makeCopy().getId());//appends the id to the bottom of column one in the sheet named Dashboard.
}

Now, I would like to modify the content of this script procedure, in order that the file could be copied only with the action of the user via the google sheet menu "File/copy File" and after, via a trigger as "onEdit", the script stored in the model sheet file, copied the id of the copy file in the dashboard file. I hope that my explanations are clear ! And thanks again for your help