Google Forms: Is it possible to update a title of a form onLoad?

Question

I'm looking for a way to update a header of an element of a google form with specific data from a cell of google spreadsheet. As the data in the spreadsheet is changing, so should the header of the form element.

Here is my code I'm using.

function readTheSheet() {
  var ss = SpreadsheetApp.openById("SpreadsheetKey");
  var active = SpreadsheetApp.setActiveSpreadsheet(ss);
  var sheet = SpreadsheetApp.setActiveSheet(ss.getSheets()[4]);
  var data = ss.getDataRange().getValues();  // Data for pre-fill

  var formUrl = ss.getFormUrl();
  var form = FormApp.openByUrl(formUrl);
  var items = form.getItems();
  var formItem = items[0].asTextItem();

  formItem.setTitle('Aktueller Clankrieg: ' + data[1][0]);

  Logger.log(data[1][0]);
};

This code is running fine, exept that it's not updating when someone is responding to the form. As per Google Dev the onOpen() trigger runs only when a user opens a form, not when responding to one.

Does anyone know if and how this could be done?

If yes, any help or direction will be GREATLY appreciated.

Thank you in advance! Smite

@Zig: Thanks for your comment. It's about the headline of an element, so the update must be there BEFORE someone adds or change something. The headline contains information what to add into the field of the form. — Die 7. Legion Smite
you can have a webapp that dinamically generates the link to access to form which includes url parameters for the prefilled fields. users would first enter the webapp, then enter the form. — Zig Mandel
As far as I know, you can't update onLoad when a user is entering to submit. Probably because of the following: suppose a user enters and the form sets some fields based on some criteria. Then while that user is logged, another user enters the same form and the fields are changed because the criteria reacts different. When the first user presses the "submit" button, the data will be inconsistent, the elements that he's seeing probably no longer exist in the source code of the form file. — marianomdq
OnOpen() only triggers when someone opens a form to edit it. :( — Rodger

Rodger Rodger · Accepted Answer · 2016-06-30T12:41:36

OnOpen() only triggers when someone opens a form to edit it. :(

To get around this, I set the sheet up to edit the form whenever the relevant columns/rows were changed using onEdit() (onChange() would work also) triggers for those sections.

That was a problem though if someone updated part of the data and then went to lunch/home for the day/etc and someone else used the form in between. If you have a single column/row that matters or if it doesn't matter if it gets piecemeal updates or if you have users that will follow instructions that should work for you.

I ended up changing it to a manually triggered event. The users kept forgetting to use the menu item though so I stuck a "Big Red Button" jpg on the sheet with the data that mattered and associated the script to that. Seems to have done the trick. Whenever they change the part of the sheet that deals with the form they remember to hit the button and the form updates.

Basically grab your updated data from your sheet and then use a combination of:

//code to get the value to update into the form
var Form = FormApp.openById('form id') //to open the form
//more code to get the list of form items and select the right one if you are updating more than one thing
item.getTitle() == 'Item name that you want to update' //That is literally what you typed in that section on the form

Hollar if you need more specifics on logic/syntax.

Google Forms: Is it possible to update a title of a form onLoad?

1 Answers