google sheets launch form from script

Question

I'm trying to launch a form from a script from my script following the code I borrowed from here:

which I did as follows:

function CreateForm() {
  var form = FormApp.create('New Form');
  var item = form.addCheckboxItem();
  item.setTitle('Is this working now?');
  item.setChoices([
          item.createChoice('yes'),
          item.createChoice('no')
  ]);
  form.addMultipleChoiceItem()
          .setTitle('What should I do about it?')
          .setChoiceValues(['Complain', 'Rejoice', 'Keep Trying'])
          .showOtherOption(true);
  Logger.log('Published URL: ' + form.getPublishedUrl());
  Logger.log('Editor URL: ' + form.getEditUrl());  
  Logger.log('Form ID: ' + form.getId());
}


function launchForm() {
  var formTitle = 'Weekly Report Questionnaire';
  var formID = '1NjNO7JTlt9H2IB6L3QowGitR38o2KrnZxpKB3MWoVXE';
  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(formTitle)
  .setWidth(500) 
  .setHeight(450);
  SpreadsheetApp.getActiveSpreadsheet().show(htmlApp);
}

So the problem is, when I execute launchForm from my menu add-on, the form appears, but it doesn't work - I can't check the boxes, enter text, or submit the form, although the cursor changes when I hover over an item. I can only close the form. I want the form to store the values from the input fields in my form as variables for later output in my email html text.

score 0 · Accepted Answer · answered Dec 16 '17 at 02:25

How about this workaround? In this workaround, the form URL is directly used and opened in the iframe.

Modified script :

I modified only launchForm() as follows.

function launchForm() {
  var formTitle = 'Weekly Report Questionnaire';
  var formID = '1NjNO7JTlt9H2IB6L3QowGitR38o2KrnZxpKB3MWoVXE';
  var form = FormApp.openById(formID);
  var formUrl = form.getPublishedUrl();
  var htmlApp = HtmlService
  .createHtmlOutput('<script>location.href = "' + formUrl + '"</script>')
  .setSandboxMode(HtmlService.SandboxMode.IFRAME)
  .setTitle(formTitle)
  .setWidth(500) 
  .setHeight(450);
  SpreadsheetApp.getActiveSpreadsheet().show(htmlApp);
}

If this was not useful for you, I'm sorry.

google sheets launch form from script

1 Answers1

Modified script :

Linked