CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Dana
Dana

Reputation: 713

Is it possible to invoke SpreadsheetApp.getUi() via onFormSubmit in google app script?

With my spreadsheet bound script, this code works fine when invoked from onOpen. When invoked from onFormSubmit I get this error: "Cannot call SpreadsheetApp.getUi() from this context."

        function showSidebar(issue,row) {
        var html = HtmlService
            .createTemplateFromFile('MySidebar');
        html.issue = issue;
        html.row = row;        
        SpreadsheetApp.getUi()
            .showSidebar(html.evaluate().setTitle('Alert'));
}

Upvotes: 4

Views: 3065

Answers (2)

Evan Strat
Evan Strat

Reputation: 43

You can't directly access the spreadsheet via SpreadsheetApp from a trigger like onFormSubmit() because at the time the onFormSubmit() is called, the spreadsheet is out of context - onFormSubmit() outside both the form it is for and the spreadsheet (if any) the script is linked to. Unfortunately, there's not really a simple workaround for this.

Additionally, you can't call getUi() if the function calling getUi() isn't doing it from a user interface like the spreadsheet itself - since triggers run in the background, there won't necessarily be a UI to get, so to speak.

Before you try the solution below, consider this: if you are using a form you made yourself, rather than one that you wrote code to create, consider using the data validation option under Advanced settings in the Forms editor. For text questions, you can require responses to be integers within a range, for example, or you could require responses to match a regular expression. If that won't work, try the following:

First, we need to open the spreadsheet (programmatically) before we can work with it. I've found that the easiest way to do this is to store the spreadsheet ID or URL somewhere accessible by the spreadsheet and its server-side functions. There are multiple ways to do this. For example, you could use the Properties Service (via another server-side function, because onFormSubmit() still does not "know" of any spreadsheets at this point). I've found that the other server-side functions that access the spreadsheet work just fine even when called from a function like onFormSubmit that was called by a trigger.

You can save the spreadsheet ID or URL in the onInstall function to make sure it gets saved. (This works for future users, but if you test calling onFormSubmit() via a trigger at this point, it won't work because the ID will not have been saved anywhere yet. To get around this, add the same code you use to save the ID or URL in the onInstall function to the onOpen function, and then refresh/open your spreadsheet. You can then remove the ID- or URL-saving code from the onOpen function (but leave it in the onInstall function!). Then, use a different function in your Code.gs file (or whichever file has your server-side functions in it) to retrieve that spreadsheet ID or URL and call SpreadsheetApp.openByID(id) or SpreadsheetApp.openByUrl(url) to open the spreadsheet.

After that, you should be able to use getActiveSheet or similar if you need to.

Unfortunately, we still can't call getUi() from this context. When you think about it, this makes sense because there's no way to know if the user has the spreadsheet open, and you wouldn't want your trigger code reaching the execution time limit because there was no response to the alert/prompt.

What I would do instead is the following:

  • When you get a new form submission, your onFormSubmit (or equivalent) function will be called.
  • From there, you can check the form responses for validity. However, instead of showing an alert to the user, send them an email (just make sure you don't send too many). This way, the user can be notified of the problem and take care of it on their own time, and they won't miss invalid submissions if they don't have the spreadsheet open. You can also include a URL that will let the user edit their response using the instructions in the next bullet point.
  • You can get a URL to edit the response by
    1. Getting the form (e.g., FormApp.openByUrl(url)) by its ID or URL using a method similar to the one I described above [onFormSubmit is not within the form's document, so you have to "open" the form]
    2. Getting an array of all the responses calling getResponses() on your form
    3. Either finding the latest response by timestamp or just taking the latest response by getting the last element of the array of responses. There's one caveat with that second method, and that's for instances when you are processing one submission and in the time between submission and the code finishing its processing of the submission, another submission is received, and the last response in the array from getResponses changes. I can't say whether that'll be an issue for you since I don't know how frequently you're expecting responses, but it's something to consider. If you're looking at timestamps (the first method), its conceivably possible in rare instances for two responses to have the same timestamp. (Alternatively, rather than focusing on just the latest response in onFormSubmit, you could check for rows in the Form Responses sheet that do not have a certain value you add (after processing a row), and then process all the rows that don't have that value (and so haven't been processed yet). But you shouldn't need to do that unless responses in very close succession are a concern.)

Upvotes: 4

Alan Wells
Alan Wells

Reputation: 31300

It's not going to work because, technically, the spreadsheet isn't the "current instance" of the "user interface".

Google Documentation - Custom Sidebars

Even though your script is bound to the spreadsheet, and getUi works with whatever file it's bound to, it's being called from another file.

You can even run that code "manually" from the script editor, and it will add a custom dialog, but if you call it from the form being submitted, it won't work.

Upvotes: 1

Related Questions