onOpen trigger not being activated on Google Form

问题:

The onOpen event on my Google Form is being activated only when I open the form for edition, but not when I open it to answer (/viewform). Actually, I just want it to run when someone opens the form to answer, because my script updates a combobox with current information from a table. I'm not sure if that is the best way to do it, but I'm open to suggestions.

I tried both creating a function onOpen() and also creating a doIt() function and manually adding a trigger to onOpen on the Resources > Add Trigger menu. The Trigger is listed correctly and seems to be working as opening the form for edition updates it properly. Probably I got the wrong trigger handler then, but there are no others for what I want (just onEdit, that should do what onOpen is doing, and onInstall).

The docs for this are incredibly succinct, and absolutely focused on Spreadsheets scripts. They mention custom hooks, however, called Installable Triggers, and one of them is actually:

When a spreadsheet or the form editor is opened. (Note that this trigger does not activate when a user opens a form to respond, but rather when an editor opens the form to modify it.) Unlike the simple trigger onOpen, the installable trigger can act as the user who installed the trigger.

Which suggests that onOpen should do exactly what I want... So now I am very confused.

Also, I'm not sure how the permissions will work. For now, as far as I can see, it seems that anyone to whom I share the form is able to edit it, and everyone can answer it, as long as the person has the URL. Is there a way to whitelist people to answer it? I don't quite like the URL being the only barrier stopping outsiders to see the data I'm loading into the combobox, and, most important, update this data by submitting the form... It seems brute-force could easily unravel a lot of 'secret' forms this way... Or is it ok, from a security standpoint, and I am just mad?

回答1:

The trigger you need to install is not an onOpen but an onFormSubmit. is shows up in the script editor/ressources/current script trigger

see illustration below

As you have read (but aparently not understood), the onOpen trigger fires only when someone opens the form editor, not when someone fills in (submits) the form.

About the second point, there is no way to whitelist users that would be allowed to fill a form, all you can do is to select the answers based on used names and eventually reject answers that you don't allow.

If this access control is really vital for you you could achieve something quite efficient using UiApp or HTML service but that would be a bit more complex.

Concerning your last point about permissions, things are very simple : installable triggers run as the user who created the trigger. If you did then the script will run as if you ran it manually, using your priviledges and access rights.

回答2:

I think that the trick to this is to setup the your script to trigger on the edit of the spreadsheet where the data that populates the form exists. You would probably need to check in the onEdit(e) event to see if you actually need to act such as checking the event object for what was edited. This way, as soon as you update the source data, the form us updated for you (vs when the form is opened).