Sharing same google form across multiple spreadsheets

Question

I have created a Feedback form and want to open it from and share it across multiple spreadsheets. I have used a revised version of the code in the following post Single Google Form for multiple Sheets.

The form opens as expected as an IFRAME but does not allow me (or any user) to populate the fields and create a response. What am I missing?

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  // Or DocumentApp or FormApp.
  ui.createMenu('Data Architecture')
      .addItem('Create File Note', 'menuItem1')
      .addSeparator()
      .addSubMenu(ui.createMenu('Feedback & Actions')
          .addItem('Provide  Feedback', 'provideFeedback')
          .addItem('Create and Action', 'createAction'))        
      .addToUi();
}

function provideFeedback() {
  
  var documentName = SpreadsheetApp.getActive().getName();
  
  var dataentityName = SpreadsheetApp.getActiveRange().getCell(1, 1).getDisplayValue();
  
  launchForm();
  
  
}

function createAction() {
  
  var documentName = SpreadsheetApp.getActive().getName()
  
  var documentID = SpreadsheetApp.getActive().getId()
  
  var dataentityName = SpreadsheetApp.getActiveRange().getCell(1, 1).getDisplayValue()
  
  launchForm();
  
}

function launchForm(){

  var formID='1pmH3AWiMUczat5uIaZ5zaT--cmDjq9v3W9pePPjwGF0';
  var form = FormApp.openById(formID);
  var formURL = form.getPublishedUrl();
   
  var response = UrlFetchApp.fetch(formURL);
  var formHtml = response.getContentText();
  
  var htmlApp = HtmlService
  .createHtmlOutput(formHtml)
  .setSandboxMode(HtmlService.SandboxMode.IFRAME)
  .setTitle('Feedback and action form')
  .setWidth(800)
  .setHeight(800);
  
  SpreadsheetApp.getActiveSpreadsheet().show(htmlApp);
}

It would be useful to share the part of the code you use to display the form. — Jack Brown, May 09 '17 at 13:40

Jack Brown · Accepted Answer · 2017-05-10T16:47:32.683

Instead of accessing the web page and generating the HTML content and serve it again, you can use iframe tag with src to your URL to do it for you.

function launchForm(){

  var formID='1pmH3AWiMUczat5uIaZ5zaT--cmDjq9v3W9pePPjwGF0';
  var form = FormApp.openById(formID);
  var formURL = form.getPublishedUrl()+ "?embedded=true" 

  var htmlApp = HtmlService
  .createHtmlOutput('<iframe src="'+formURL+'" width="500" height="400" frameborder="0" marginheight="0" marginwidth="0"> </iframe>')
  .setTitle('Feedback and action form')
  .setWidth(400)
  .setHeight(400);

  SpreadsheetApp.getActiveSpreadsheet().show(htmlApp);
}

To have pre-filled form to be used in your script:
1) Get a prefilled Form, by following the steps here
2) For a form like this:

the prefilled url will look like this:

var formURL = "https://docs.google.com/forms/d/e/{Publish ID}/viewform?
usp=pp_url&
entry.1739076204=Jack+Brown&
entry.1869878870=2017-05-12&
entry.799903038=Incorrect&
entry.1996064114=I+am+the+best&
entry.607023772=Select+Me"

I have separated out the individual options so that it is easier to compare with an entry in the form. Now to modify the entry all you have to do is modify the value after '=' sign for each entry. So to change the name you would do:

var name = "Chuck Norris"
entry.1739076204='+name+'

Similarly, you can modify each enter by modifying the URL. The below code modifies the entry for a specific form I have hosted. You will have to modify if as per your needs

 function launchForm(){

  var publishedURl = "https://docs.google.com/forms/d/e/1FAIpQLSdsHM53jTWJ0Eqn8VSxE5bWHjnD9KXsVBrqLsBwtyJIIsjpnA/viewform"
  var name = "Chuck Not Norris"
  var dt = Utilities.formatDate(new Date(), "GMT-05:00", "yyyy-MM-dd")
  var options1 = ["Correct","Incorrect"]
  var options2 = "Chuck Norris is google Apps Scripts"
  var options3 = ["Select Me","Don't select me"]
  var preEditUrl = publishedURl + '?usp=pp_url&entry.1739076204='+name
                   +'&entry.1869878870='+dt
                   +'&entry.799903038='+options1[0]
                   +'&entry.1996064114='+options2
                   +'&entry.607023772='+options3[0]

  var htmlApp = HtmlService
  .createHtmlOutput('<iframe src="'+preEditUrl+'" width="500" height="400" frameborder="0" marginheight="0" marginwidth="0"> </iframe>')
  .setTitle('Feedback and action form')
  .setWidth(400)
  .setHeight(400);

  SpreadsheetApp.getActiveSpreadsheet().show(htmlApp);
}

Hopefully, this will get you started in the right direction.

This works, thank you very much; much appreciated. The only remaining question is how to pre-populate certain fields when the form opens in the IFRAME, such as the email address of the user creating the response, defaulting a date field to the current date, and 3 parameters passed from the caller. — M Vega, May 10 '17 at 10:46
@MVega Updated the answer to explain how to pre-populate the field — Jack Brown, May 10 '17 at 16:47

Sharing same google form across multiple spreadsheets

1 Answers1