I am facing the problem of sharing a script with a bunch of functions designed to help in manipulation of different copies of the same original google spreadsheet (each user has a personal copy). Each spreadsheet copy has its own bounded script copy, so if I improve any function I have to update every single copy of the script.
To overcome this problem, I saw it is possible to call a library from your script project, and put all shared functions in that library. Some references I read:
How can I invoke my standalone script in my spreadsheet? ; How to use a google apps script in multiple documents ; How to pass bounded script to another user so he can add it to his own spreadsheets? ; Distribute Google Apps Script and push updates ; https://issuetracker.google.com/issues/36755072
There you can see several problems of using that approach:
- To propagate your library updates (dinamically, not having to touch the user sheets again), it has to be included in the sheet-bounded scripts using development mode and giving your users edit access to your library script (if they only have view access, they use a static version of your library).
- You need to redesign your original sheet-bounded code, so the functions are invoqued preceded by the name used to designate your library: libraryname.functionName() -might not be a problem, but I mention it just in case somebody else is getting errors because of this, like I did-.
- From my tests, I have the feeling that code runs much more slowly when using the library, compared to the same code pasted in the sheet-bounded script.
So I wonder if it is possible to include the code in some other way, and I found some comments about using eval() to include javascript code from a external url:
importing external javascript to google apps script ; inject external javascript file in google app script(Google Docs) ; https://ctrlq.org/code/20380-load-external-javascript-with-eval
Basically, this is how they say to do it:
eval(UrlFetchApp.fetch('http://path.to/external/javascript.js').getContentText());
My questions:
I do not fully understand how eval() should work in the above examples. Is it intended to be run from a script which runs in the google app server? (so my included file will still be calling SpreadsheetApp. or DriveApp. functions)
If the code inside eval() defines a bunch of functions, are they still available after eval() finishes? Can we call them from user driven events, like clicking menu options?
And if we can call them, should be invoqued in a special way? I wasn't able to do it
I could just load a new menu (defined in remote.js) using eval(remote.js). BUT the other functions defined in remote.js are not available to menu calls anymore (clicking on menu raises errors like Script function not found: f1).
- Should I perhaps load all the functions someway before the menu is defined? I tried it defining function f1() at the begin but it doesn't make a difference with respect to f2(), which is at the end. I also tried 2 separate calls, 1st eval(remote_functions.js), 2nd eval(remote_menuLoader.js), but this didn't make a difference either (so my code below shows just a single eval call, which loads a single remote.js with definitions of both functions and menu which will call them).
My code:
Google spreadsheet bounded-script:
var app = SpreadsheetApp.getActiveSpreadsheet();
function onOpen() {
app.addMenu("LOAD remote menu", [
{name: "eval(remote.js) to load new menu", functionName: "evalRemote"}
]);
}
function evalRemote() {
var remotejs_url="https://drive.google.com/uc?authuser=0&id=1wCjewWFTPdH-Luw53zWh_KHX-RhRhFIn&export=view";
eval(UrlFetchApp.fetch(remotejs_url).getContentText());
var message=remote_onOpen(app);
Browser.msgBox(message);
}
remote.js file:
function f1() {
Browser.msgBox("you called f1");
}
function remote_onOpen(app) {
var menuName="REMOTE-loaded menu";
app.addMenu(menuName, [
{name: "FUNCTION 1", functionName: "f1"}
,{name: "FUNCTION 2", functionName: "f2"}
]);
return("remote_onOpen(app) function was called;\\n "
+ "A reference to '" + app.getName() + "' was passed \\n"
+ "in order to create this new menu on it:\\n"
+ "'" + menuName + "'\\n\\n"
+ "You should now have 2 new functions available.");
}
function f2() {
Browser.msgBox("you called f2");
}