Populate Google Form using Google App Script on Form open

1
votes

I have a google form to where I want to populate with some data from a spreadsheet when user is opening the form.

I have a ListItem which I populate it with spreadsheet data. Until now I used the Open(e) function and a trigger, but I just found out that this method is triggered only on form edit not on form open.

Do you have an idea how can I do that?

To have an idea on what I want, I have two files, Code.gs contains the main functions like onOpen and onFormSubmit, and ItemClass where I get my data and create the UI.

I set a console log to Open(e) function, but never triggers.

Code.gs

function onOpen(e) {
  console.log({message: 'onOpen', initialData: e});
  let items = getItems();
  
  let form = FormApp.openById(PARAMS.formID);
  
  form.setTitle('New Form')
  
  createUI(form, items);
}

ItemsClass.gs

function getItems() {
  var email = Session.getActiveUser().getEmail();
  
  var allItems = SpreadsheetApp.openByUrl(PARAMS.sheetURL).getSheetByName("Items Stream").getDataRange().getDisplayValues();
  
  var headers = allItems.shift();
  
  var items = new Array;
  for (var i = 0; i < allItems.length; i++) {
      var first = allItems[i][1]
      var second = allItems[i][2]
      items.push(first + "&" + second)
  }
   return items;
}
1
google-apps-scriptweb-applications
The onOpen trigger for forms works when you as the the form creator open the form editor, not when a user opens a form that he fills out and submits. If you explain more in detial why you want to run the form on onOpen, one could come up with a workaround. Do you want that each time a user fills out a form the content changes? - ziganotschka
Whenever a user opens the form, I want him to see some events taken from spreadsheet and from there he'll have the posibility to modify those events and on submit modify them in the spreadsheet - user45723
If you want the form contents to update every time a user opens a form to fill - this is not possible with Google Forms. You could alternatively crate a custom HTML with web polling- I can post a sample if you are interested! - ziganotschka
yes sure! thank you :) - user45723

1 Answers

1
votes

The on Open trigger Google Forms works only when opening the form editor, not the actual form that the user fills out

To return to the user the updated data whenever he opens the form and allow him to modify the data, you should create a custom HTML form with Web polling.

  • Web Polling with setInterval allows to pull fresh data from the spreadsheet and update it in specified intervals
  • Apps Script WebApps allow you to combine Apps Script and HTML/Javascript which allows you easy interaction between serverside and UI - useful for creation of a custom HTML form
  • Use google.script.run to communicate between the two sides.

Simple sample pulling updated data from column A in a spreadsheet and allowing the user to modify the values:

code.gs:

var sheet = SpreadsheetApp.openById('XXX').getSheetByName("YYY");

function doGet(){
  var html=HtmlService.createTemplateFromFile('index');
  return html.evaluate();
}
function getValues() {
  //get data from the first column
  var data = sheet.getRange(1, 1, sheet.getLastRow(), 1).getValues();
  var table = "";
  for (var i = 0; i < data.length; i++) {
    table +='<tr><td>' + data[i][0] + ' </td><tr>';
  }
  return table;
}
function writeToSheet(newValues) {
  newValues = newValues.split(",");
  var range = sheet.getRange(1, 1, newValues.length, 1);  
  newValues = newValues.map(function(row){return [row]});
  range.setValues(newValues);
}

index.html:

<!DOCTYPE html>
<html>
   <head>
      <base target="_top">
   </head>
   <script>
      function onSuccess(values){
        document.getElementById("data").innerHTML=values;
      }
      function polling(){
//modify the interval of 2000 ms to any desired value
        setInterval( function(){google.script.run.withSuccessHandler(onSuccess).getValues()},2000);
       }
      function updateValues(){
        var newValues= document.getElementById("newValues").value;
        google.script.run.writeToSheet(newValues);        
      }       
   </script>
   <body onload="polling()">
      <div> Values: </div>
      <table id="data">
      </table>
      <div> If you want to modify the values in the spreadsheet, type in new values comma separated: </div>
      <input type="text" id="newValues" ><br><br>
      <input type="button" value="Confirm" onclick="updateValues()">
   </body>
</html>

Deploy this WebApp and described in the documentation and paste the WebApp URL into a browser address bar.