USING:
Google Sheets (new)
AIM:
In a spreadsheet, show a modal dialog created from HTML, then run a function, then close that HTML prompt automatically.
The dialog stays until the function finishes executing, then automatically disappears. This process has to be repeated every 3 hours, and the script needs to run as me (as I have edit permissions that other users do not) so simple triggers probably won't work (I've read that you must create an installable trigger if you want the function to run as you and not whoever the current user is at the given time)
WHAT I HAVE:
- A .gs function Magic_Telling, that creates a modal dialog by using an HTML file
- An HTML file, Prompt_Styling, that contains the css / html styling for the prompt. This HTML file then calls a .gs function All_In that processes the rows
MY CODE
This is my first-ever script, so please don't be too disgusted!
Magic_Telling
Creates the modal dialog from HTML file
function Magic_Telling() {
var UI = SpreadsheetApp.getUi();
var newline = '\n'
// Display a modal dialog box with custom HtmlService content.
var htmlOutput = HtmlService.createHtmlOutputFromFile('PromptStyling')
.setSandboxMode(HtmlService.SandboxMode.IFRAME)
.setWidth(300)
.setHeight(100);
UI.showModalDialog(htmlOutput, ' ');
}
Prompt_Styling
HTML file for styling prompt + script that runs the function All_In that will process rows
<html>
<head>
// some irrelevant stuff here
</head>
<script>
window.onload = function() {
google.script.run
.withSuccessHandler(closeDialog)
.All_In();
};
window.closeDialog = function() {
google.script.host.close();
};
</script>
</html>
All_In
Function to process rows
function All_In() {
UnlockRowBlocks();
UnhideRowBlocks();
LockRowBlocks();
HideRowBlocks();
}
When I run MagicTelling from the script editor, it works beautifully. The entire sequence executes (prompt shown, All_In executed, prompt disappeared). Perfect.
I then created an installable trigger by going to Script Editor > Resources > Current project's triggers and added a trigger to run Magic_Telling every 3 hours. (I presume this is an "installable trigger")
The problem: I get an error message...
Cannot call SpreadsheetApp.getUi() from this context.
...when the function reaches the first line of Magic_Telling
What should I do to get around this? PLEASE HELP, and thanks in advance for your generosity. I'm happy to provide as much detail as you need. Really very grateful for any help, completely lost here.