Include Parent Folder Name in Spreadsheet onOpen

Question

I would like a spreadsheet to automatically include the name of its parent folder in cell A1 of the active sheet onOpen. I have a script that successfully achieves this if I run it manually, yet I am not able to accomplish this onOpen. As recommended below, I have tried using an installable onOpen trigger but that is not a solution as I want to make copies of the spreadsheet and have the folder name included onOpen. The trigger will not copy with the spreadsheet so I am back to square one. The issue is with permissions, I believe? Any workaround?

Although I'm not sure about your script, how about installing the function as the OnOpen event trigger? Because I thought that the script might use the methods that the authorization is required. At that time, before install the trigger, please rename the function name from onOpen() to others because of preventing duplicate run. developers.google.com/apps-script/guides/triggers/installable If this was not the direct solution, I apologize. — Tanaike
Great idea. Thanks for the input. The only problem is that I will make multiple duplicates of the spreadsheet and the installable trigger will not automatically copy to the new copies. Therefore, I am back where I started with needing to manually run the function. — jasond

Cooper Cooper · Accepted Answer · 2019-05-07T23:34:19

You can use something like this to create an installable onOpen trigger if one is not already in the project.

function createOpenTrigger(funcname) {
  if(funcname) {
    if(!isTrigger(funcname)) {
      ScriptApp.newTrigger(functionName).forSpreadsheet(SpreadsheetApp.getActive()).onOpen().create();
    }
  }
}

function isTrigger(funcName){
  var r=false;
  if(funcName){
    var allTriggers=ScriptApp.getProjectTriggers();
    for(var i=0;i<allTriggers.length;i++){
      if(funcName==allTriggers[i].getHandlerFunction()){
        r=true;
        break;
      }
    }
  }
  return r;
}

Of course, the user will have to approve it first.

Include Parent Folder Name in Spreadsheet onOpen

2 Answers