Access local variables while using Google Apps Script Libraries

Question

I am working with different Google spreadsheets which all use the same sheets and structure. All of them do also use an identical set of functions which are executed by using custom menu items.

To maintain the functions only once for all spreadsheets, one serves as a Library for all others. Now here is the problem: All spreadsheets have a set of individual properties which need to be passed to Library function calls. That's why I am currently doing something like this:

Sample function in the library which is made available as lib as well as the menu construction for all sheets:

function onOpen() {
   var ui = SpreadsheetApp.getUi();
   ui.createMenu('Actions')
             .addItem("My Library Function", "trackingHelper")
             .addItem("My 2nd Library Function", "anotherFunction")
             .addToUi();
}

function trackingHelper(sheetProperties) {
   do something and use the sheetProperties
}

And now, in the "child" sheets I am adding something like this:

var sheetProperties = {key: value, ...}

function onOpen() {
   lib.onOpen();
}

function trackingHelper() {
   lib.trackingHelper(sheetProperties);
}

function anotherFunction() {
   lib.anotherFunction(sheetProperties);
}

The problem is, that I always need to edit all sheets if I add new functions. That's why I'd like to do something like this with the menu in the library spreadsheet:

function onOpen() {
   var ui = SpreadsheetApp.getUi();
   ui.createMenu('Actions')
             .addItem("My Library Function", "lib.trackingHelper")
             .addItem("My 2nd Library Function", "lib.anotherFunction")
             .addToUi();
}

So I only want to add one onOpen with this menu to all child sheets to spare the need for adding all functions individually. But how can I pass my child properties now? I tried to use the PropertiesService but executing a script from a child sheet always yielded the properties scope of the library sheet.

Is there any possibility to avoid the need for remapping all local functions to library functions in order to pass some sheet-specific variables? Thanks a lot.

Unfortunately, calling a library function seems to use the Properties scope of the library script/ document and not the child properties. I just tried and the DocumentProperties always refer to the library script/ document instead of using the calling child script. — AdmPicard
stackoverflow.com/questions/22818320/… may help. Alternately, set UserProperties with a key of the fileId and the appropriate config info — tehhowch
Thanks for the suggestion! I am afraid that multiple users are using the scripts, too. The easiest solution so far seems to be storing the values directly in the child sheets and retrieving them via getRange().getValues() as this approach always references the calling sheet. It's not elegant or fast but seems to work. However, maybe there is yet another way. — AdmPicard

CalamitousCode CalamitousCode · Accepted Answer · 2018-12-05T03:43:38

EDIT: I have the same problem as you. I still have to remap all library functions in the child script, BUT I have figured out how to manage individual properties as below.

--

In the library, you can manage the variables for each child:

PROJECT_NAME1 = {
    name: 'abc',
    visibleOverride: false
};

PROJECT_NAME2 = {
    name: 'xyz',
    visibleOverride: true
};

In the child script, you can have a global variable like so:

PROJECT = lib.PROJECT_NAME1;

In the child script, you pass the global PROJECT variable to the library functions:

function hideSheet() {
    lib.Group.toggleSheetVisibility(PROJECT, false);
}

function showSheet() {
    lib.Group.toggleSheetVisibility(PROJECT, true);
}

In the library, you can access the individual settings as customised by the originating child script:

Group = {

    toggleSheetVisibility: function (PROJECT, visible) {

        var sheet = SpreadsheetApp
            .getActive()
            .getActiveRange()
            .getSheet();

        if (!PROJECT.visibleOverride) {

            visible
                ? sheet.showSheet()
                : sheet.hideSheet();
        }
    }
}

Access local variables while using Google Apps Script Libraries

2 Answers