How can I use OnSubmit trigger to run function on a google spreadsheet?

Question

I will try to explain the situation so you can better understand my problem. I have a spreadsheet that contains 3 sheets: the first one contains the answers from a form, the second one contains the answers from a second form and the third one is a sheet that collects data from the first and the second sheets by ARRAYFORMULA and it's ok. I've used the "OnSubmit" trigger to allow the spreadsheet to take the data from the third sheet and put them in some specific cell on a fourth sheet and then to allow me to download the fourth sheet as a PDF. The problem is that the script works only when I try it from the editor but when I submit the form it doesn't work. The "OnSubmit" trigger report this error "Exception: impossible to call SpreadsheetApp.getUi() from this context". Is there a way to solve ? Thanks

function downloadSheetAsPDF() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheetId = ss.getSheetByName("DDT").getSheetId();
var url = "https://docs.google.com/a/mydomain.org/spreadsheets/d/" + ss.getId() + "/export?exportFormat=pdf&gid=" + sheetId + "&access_token=" + ScriptApp.getOAuthToken();
var str = '<input type="button" value="Download" onClick="location.href=\'' + url + '\'" >';
var html = HtmlService.createHtmlOutput(str);
SpreadsheetApp.getUi().showModalDialog(html, "Download PDF");
} ```

Without seeing code, I can't understand why you are using .getUi() for this? It likely needs to be removed. — Stykes
When a trigger runs a function the spreadsheet doesn't open up a user interface. It simply opens open the spreadsheet on the server so no user interfaces are available and there it's not allowed for you to use them in that context. — Cooper
You could probably create the pdf and send it as an attachment in an email — Cooper

Unknown Unknown · Accepted Answer · 2020-05-15T08:22:08

As Cooper mentioned in the comments, it's not possible to Trigger opening a UI window in Sheets, as it requires user interaction. It's also not possible to do an authomatic download.

More information about Trigger's restrictions:

https://developers.google.com/apps-script/guides/triggers#restrictions

How can I use OnSubmit trigger to run function on a google spreadsheet?

1 Answers