Populate a Google Form responses from a Google Spreadsheet

Question

I am trying (and not succeeding at the moment) to populate a Google Form from a Google Spreadsheet using the items that I have picked up from this website and this extremely useful answer provided by Mogsdad here.

Ideally, I'm looking for:

1. The Logger.log (URL) logs URL's for all the data stored in the spreadsheet, is it possible to just log the last entry and use this to generate the URL?

2. Is it then possible for the pre-filled URL to auto submit once populated with the data? I have found this useful article here which suggests that this can be done?

3. The data that is stored in the Google Spreadsheet is data captured from another Google Form. This is due to the need of using Excel (lack of Internet connectivity) with a concatenate formula to merge all cells with data into one. This is then submitted on the other Google Form which has this script to split the data out by column ready to answer the questions with. Will this impact the trigger needed to auto submit when a submission is made?

May I add that I have a rather limited understanding on this so please go easy if this seems rather easy to do!

Answer 1

is it possible to just log the last entry and use this to generate the URL?

I'm not sure I follow you here - in that other answer, the Logger.log() statement was just to demonstrate that you could generate the correct URL, if you wanted to distribute it. Instead of logging it, you'd just use the content of the variable url.

But let's move on, because I think this is a little off your path.

Is it then possible for the pre-filled URL to auto submit once populated with the data?

There's a better starting point. Can we programmatically submit information from a spreadsheet into a google form? Sure! See Use App Scripts to open form and make a selection. It's a more reliable way to do the job than what you see in the "URL Tricks" post.

In the case of "auto submit", or simulating a form submission, you don't need to worry about the pre-filled URL at all. That's a shortcut for pesky humans. What you want is to put together the payload for a POST request, instead, and have a computer bypass the form UI altogether.

Something about Excel... Will this impact the trigger needed to auto submit when a submission is made?

(Sounds like...) You are using Form1 to get data into Spreadsheet1, then expecting to react to the (human?) submission of Form1 by having the machine submit Form2 after breaking apart the data from Form1.

Yeah, you'll need to be careful that the column split is done before you try to read the information to submit Form2.

I suggest that you would be best served with a form-submission trigger function for Spreadsheet1 that splits the string received from Form1 then immediately sends the POST to Form2. I'd then record the fact that this action has occurred, using the technique from Spreadsheet Email Trigger.

Answer 2

2. Is it then possible for the pre-filled URL to auto submit once populated with the data? I have found this useful article here which suggests that this can be done?

As Mogsdad said

There's a better starting point. Can we programmatically submit information from a spreadsheet into a google form? Sure!

According to an edit to Use App Scripts to open form and make a selection, the payload/post method is now obsolete. The alternative is to use the Form App service. Below is an adaptation that I made to evenBetterBuilURLs¹ by Mogsdad to submit responses instead of create pre-filled URLs and to be used in a stand-alone Google Script Project File.

The original code lines that were changed are commented out. Also I inserted some breaklines to avoid the horizontal scroll bar.

/**
 * Use Form API to populate form
 * 
 * Addapted from https://stackoverflow.com/a/26395487/1677912
 */
function populateFormResponses() {
  //var ss = SpreadsheetApp.getActive();
  var id = '11KDxp1C6jAZaTMNlGHke8zEzQ7aZrFSFGABdwUHEV80';
  var ss = SpreadsheetApp.openById(id);
  var sheet = ss.getSheetByName("Form Responses 1");
  var data = ss.getDataRange().getValues();  // Data for pre-fill
  var headers = data[0];                     // Sheet headers == form titles (questions)

  var formUrl = ss.getFormUrl();             // Use form attached to sheet
  var form = FormApp.openByUrl(formUrl);
  var items = form.getItems();
  //var urlCol = headers.indexOf("Prefilled URL");   // If there is a column labeled this
                                                     // way, we'll update it

  // Skip headers, then build URLs for each row in Sheet1.
  for (var row = 1; row < data.length; row++ ) {
    //Logger.log("Generating pre-filled URL from spreadsheet for row="+row);
    Logger.log("Generating response from spreadsheet for row="+row);
    // build a response from spreadsheet info.
    var response = form.createResponse();
    for (var i=0; i<items.length; i++) {
      var ques = items[i].getTitle();           // Get text of question for item
      var quesCol = headers.indexOf(ques);      // Get col index that contains this 
                                                // question
      var resp = ques ? data[row][quesCol] : "";
      var type = items[i].getType().toString();
      Logger.log("Question='"+ques+"', resp='"+resp+"' type:"+type);
      // Need to treat every type of answer as its specific type.
      switch (items[i].getType()) {
        case FormApp.ItemType.TEXT:
          var item = items[i].asTextItem();
          break;
        case FormApp.ItemType.PARAGRAPH_TEXT: 
          item = items[i].asParagraphTextItem();
          break;
        case FormApp.ItemType.LIST:
          item = items[i].asListItem();
          break;
        case FormApp.ItemType.MULTIPLE_CHOICE:
          item = items[i].asMultipleChoiceItem();
          break;
        case FormApp.ItemType.CHECKBOX:
          item = items[i].asCheckboxItem();
          // In a form submission event, resp is an array, containing CSV strings. Join 
          // into 1 string.
          // In spreadsheet, just CSV string. Convert to array of separate choices, ready 
          // for createResponse().
          if (typeof resp !== 'string')
            resp = resp.join(',');      // Convert array to CSV
          resp = resp.split(/ *, */);   // Convert CSV to array
          break;
        case FormApp.ItemType.DATE:
          item = items[i].asDateItem();
          resp = new Date( resp );
          break;
        case FormApp.ItemType.DATETIME:
          item = items[i].asDateTimeItem();
          resp = new Date( resp );
          break;
        default:
          item = null;  // Not handling DURATION, GRID, IMAGE, PAGE_BREAK, SCALE, 
                        // SECTION_HEADER, TIME
          break;
      }
      // Add this answer to our pre-filled URL
      if (item) {
        var respItem = item.createResponse(resp);
        response.withItemResponse(respItem);
      }
      // else if we have any other type of response, we'll skip it
      else Logger.log("Skipping i="+i+", question="+ques+" type:"+type);
    }

    // Submit response
    response.submit();

    // Generate the pre-filled URL for this row
    //var editResponseUrl = response.toPrefilledUrl();

    // If there is a "Prefilled URL" column, update it
    //if (urlCol >= 0) {
    //  var urlRange = sheet.getRange(row+1,urlCol+1).setValue(editResponseUrl);
    //}
  }
};