CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-drive-api
jasond
jasond

Reputation: 17

Include Parent Folder Name in Spreadsheet onOpen

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?

Upvotes: 0

Views: 130

Answers (2)

Wicket
Wicket

Reputation: 38131

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?

No, the issue isn't with permissions, the issue is related to other concepts. I'll try to make a summary including the minimal of them that I think are required for this question to help you learn the basic terms so you could scan this site and others to find the stuff you will need or that will help you to make more specific questions:

  • There are two kinds of script projects, bounded and standalone.
  • Scripts that will make certain actions should be authorized before they execute. Please keep reading.
  • There are two kinds of triggers, simple an installable
  • Installable triggers could be created manually or by a script. Creating a a trigger by a script requires authorization.
  • Installable triggers only could execute functions that the user that is creating the trigger either manually or through a script had been authorized. If you create an installable trigger but later makes changes to the script, perhaps this will make that your script should be authorized again.
  • Installable triggers could be created for spreadsheets by using bounded and standalone project scripts. This kind of triggers require authorization to access non public spreadsheets
  • To get the parent folder of a spreadsheet your script should use the Drive Service or the Drive Advanced Service. This requires authorization.

Upvotes: 0

Cooper
Cooper

Reputation: 64032

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.

Upvotes: 1

Related Questions