I have several spreadsheets from which i would like to be able to run the same code. So i created a .gs library. One of the things that the library does is create a modal in the active sheet using htmlService. The HTML code is also part of the library. On each bounded script, i add a menu item to the sheet that allows the user to call the library function. However, i am running into issues when trying to access the active sheet's data from the HTML using google.script.run. I have tried several things:
Test 1
First i tried having the HTML call a fn in the library gs, assuming it would be within scope. The code looked like this:
bounded.gs
function onOpen(e) {
var ui = SpreadsheetApp.getUi();
ui.createMenu('myMenu')
.addItem('Run Custom Function', 'myLibrary.createMyModal')
}
myLibrary.gs
function createMyModal() {
var widget = HtmlService.createHtmlOutputFromFile('modalHTMLfile');
SpreadsheetApp.getUi().showModalDialog(widget, "title");
}
function helloWorld() {
var ui = SpreadsheetApp.getUi();
ui.alert('hello, world!');
}
modalHTMLfile.html
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<script>
function onClickFn() {
google.script.run.helloWorld();
}
</script>
</head>
<body>
<button onclick='onClickFn();'>click me!</button>
</body>
</html>
The modal generates just fine but when i click the button, it generates an error in the console: "Uncaught TypeError: google.script.run.helloWorld is not a function"
So i figured that there was an issue of scope, and i should put helloWorld in the bounded .gs:
Test 2
I moved the fn helloWorld into the bounded .gs as follows:
bounded.gs
function onOpen(e) {
var ui = SpreadsheetApp.getUi();
ui.createMenu('myMenu')
.addItem('Run Custom Function', 'myLibrary.createMyModal')
}
function helloWorld() {
var ui = SpreadsheetApp.getUi();
ui.alert('hello, world!');
}
myLibrary.gs
function createMyModal() {
var widget = HtmlService.createHtmlOutputFromFile('modalHTMLfile');
SpreadsheetApp.getUi().showModalDialog(widget, "title");
}
modalHTMLfile.html
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<script>
function onClickFn() {
google.script.run.helloWorld();
}
</script>
</head>
<body>
<button onclick='onClickFn();'>click me!</button>
</body>
</html>
This time, when i clicked the button, something happened on the server side (see image below), but another error message appeared. This time, "Uncaught ScriptError: We're sorry, a server error occurred" See this image. Which led me to test #3:
Test 3
Soooo i did some questing around the interwebs and found some suggestions on stack exchange including to have two fns with the same name, an empty one in the bounded script and the real function in the library gs. So i tried that...here's the code:
bounded.gs
function onOpen(e) {
var ui = SpreadsheetApp.getUi();
ui.createMenu('myMenu')
.addItem('Run Custom Function', 'myLibrary.createMyModal')
}
function helloWorld() {
//placeholder function to "make things work"
}
myLibrary.gs
function createMyModal() {
var widget = HtmlService.createHtmlOutputFromFile('modalHTMLfile');
SpreadsheetApp.getUi().showModalDialog(widget, "title");
}
function helloWorld() {
var ui = SpreadsheetApp.getUi();
ui.alert('hello, world!');
}
modalHTMLfile.html
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<script>
function onClickFn() {
google.script.run.helloWorld();
}
</script>
</head>
<body>
<button onclick='onClickFn();'>click me!</button>
</body>
</html>
This third test yielded the same error as Test 2.
Test 4
For all three of the previous tests I included myLibrary in bounded.gs using Development Mode and i read something online suggesting that running in Development Mode can cause this issue. So i tried using the most up to date deployment of the library.
This is what my library deployment looks like (v5)
And when i click the button, i get an error message showing that the library is not recognized:
--
I'm metaphorically pulling my hair out over here, does anyone have any suggestions? Is this even possible? I couuuuuuld put all the code in the bounded.gs file but i have 8 spreadsheets and i'd really love to avoid having 8 identical yet separate code bases to keep updated (that's the point of the library in the first place XD).
Thank you!!!
