Send pre-filled Google Form via email

问题:

I have a script which creates a pre-filled Google Form from a spreadsheet. I can get the pre-filled URL easily enough and it's working as expected. I'm having a hard time sending the pre-filled form via email. I can get the HTML output using HtmlService and appending it to the body of the email, but it isn't interactive. I can fill in text fields, but cannot select multiple choice items or submit the form.

Script

// A form is already built...

var logs = ss.getDataRange().getValues();
var formResponse = form.createResponse();

for(var j=0;j<7; j++) {                        
  formItem = items[j+2].asTextItem();
  response = formItem.createResponse(weekData[i][j]);
  formResponse.withItemResponse(response);
}

var url = formResponse.toPrefilledUrl();

// Mark as sent so it isn't caught in the next trigger      
logs.getRange(i+1, logs.getLastColumn()).setValue('sent');

var response = UrlFetchApp.fetch(url);
var htmlBody = HtmlService.createHtmlOutput(response).getContent();

MailApp.sendEmail({
  to: "[e-mail]",
  subject: "Confirm Intern Hours",
  htmlBody: htmlBody
});

Any ideas on how to send this as a functional form in an email? If worst comes to worst, I can include a shortened link and have them click out to the live form.

回答1:

If you are only looking for a pre-filled multiple choice reply to the form that is submitted from the email body - you can create a pre-filled link for each of the multiple choice items, add them to the html email, and then submit the pre-filled form using the link in the email.

If you also need the input text fields to be filled out by the user from the email, that would probably take a bit more work..but may be possible using replace().

function emailForm () {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var form = FormApp.openByUrl(ss.getFormUrl());
  var items = form.getItems();
  // or wherever your Multiple Choice item is in the list
  var choice_item = items[0].asMultipleChoiceItem();
  var choice_opts = choice_item.getChoices();
  var prefilled_set = [];

  var logs = ss.getDataRange().getValues();
  var formResponse = form.createResponse();

  for(var j=0;j<7; j++) {                        
    formItem = items[j+2].asTextItem();
    response = formItem.createResponse(weekData[i][j]);
    formResponse.withItemResponse(response);
  }

  var url = formResponse.toPrefilledUrl();

  // Create a prefilled link for each multiple choice option
  choice_opts.forEach(function (e) {
    var choice_response = choice_item.createResponse(e.getValue());
    formResponse.withItemResponse(choice_response);

    var prefill_url = formResponse.toPrefilledUrl();
    // Change the URI from view to response and Shorten the URL
    var tiny_url = form.shortenFormUrl(
                       prefill_url.replace("/viewform?", "/formResponse?"));
    // Make HTML Link
    var html_link = '<a href="' + tiny_url + '"> ' + e.getValue() + ' </a>';

    // Push each option link into an array
    prefilled_set.push(html_link);
  });

  var response = UrlFetchApp.fetch(url);

  // Get your HTML Output and Add the Multiple Choice links to it
  var htmlBody = HtmlService.createHtmlOutput(response + prefilled_set.toString())
                            .getContent();

  MailApp.sendEmail({
    to: "[e-mail]",
    subject: "Confirm Intern Hours",
    htmlBody: htmlBody
  });
}

This is the piece that makes the pre-filled links submit the form from email:

 prefill_url.replace("/viewform?", "/formResponse?")

---

Also, will probably want to format the links in some way to make them stand out more. Or even replace response with your own html template