CODEWITHSUNDEEP
google-apps-scriptweb-applications
user45723
user45723

Reputation: 123

Populate Google Form using Google App Script on Form open

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;
}

Upvotes: 2

Views: 3487

Answers (1)

ziganotschka
ziganotschka

Reputation: 26796

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.

Upvotes: 2

Related Questions