Google Sheets to Form – Insert Blank Fields with Answers

google sheetsgoogle-apps-scriptgoogle-forms

I have set up a script to insert existing answers into my Google Form from a Google Sheet (by adapting the code from this answer). I inserted it into the Forms Scripts and it works when all fields in the Sheet are filled. However, the nature of the Form questionnaire is such, that certain sections may be skipped thus leaving some fields in the Sheet empty. I would like to insert such a Sheet, with some of its answer fields blank.

The script works when all of the fields in the Sheet are filled, but doesn't when it encounters a blank field (which means that the first row of answers that has any blank field in it stops the execution of the script).

What I would like to know is how to explain to my script that it should just accept blank answers and insert them as such?

Here is the script:

function ApendResponses() {
  var form = FormApp.openById("1M-mTBlZPRj44jXs_YHj-cNa9yHjU25ItEWBuKtUvKZo");
  var sheet = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1Jc9NVlN-Wcmd87E-FnyocHgl5Vx2WXqOGPTkhvXjCFU/");
  var rows = sheet.getDataRange();
  var numRows = rows.getNumRows();
  var values = rows.getValues();

  for (var x = 0; x < values.length; x++) {

    var formResponse = form.createResponse();
    var items = form.getItems();

    var row = values[x];

    var formItem = items[0.0].asTextItem();   
    var response = formItem.createResponse(row[1]);     
    formResponse.withItemResponse(response);

    var formItem = items[1.0].asTextItem();   
    var response = formItem.createResponse(row[2]);     
    formResponse.withItemResponse(response);

    var formItem = items[2.0].asListItem();   
    var response = formItem.createResponse(row[3]);     
    formResponse.withItemResponse(response);

    var formItem = items[3.0].asPageBreakItem();       
    formResponse.withItemResponse(response);

    var formItem = items[4.0].asListItem();   
    var response = formItem.createResponse(row[4]);     
    formResponse.withItemResponse(response);

    var formItem = items[5.0].asPageBreakItem();       
    formResponse.withItemResponse(response);

    var formItem = items[6.0].asListItem();   
    var response = formItem.createResponse(row[5]);     
    formResponse.withItemResponse(response);

    var formItem = items[7.0].asPageBreakItem();       
    formResponse.withItemResponse(response);

    var formItem = items[8.0].asTextItem();   
    var response = formItem.createResponse(row[6]);     
    formResponse.withItemResponse(response);

    formResponse.submit();
    Utilities.sleep(500);

  }

};

Example Form is here, with its accompanying Sheet here (I left a blank field in the third row for testing purposes).

Best Answer

Here is a solution that works:

function ApendResponses() {
  var form = FormApp.openById("1M-mTBlZPRj44jXs_YHj-cNa9yHjU25ItEWBuKtUvKZo");
  var sheet = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1Jc9NVlN-Wcmd87E-FnyocHgl5Vx2WXqOGPTkhvXjCFU/");
  var rows = sheet.getDataRange();
  var numRows = rows.getNumRows();
  var values = rows.getValues();

  for (var x = 0; x < values.length; x++) {

    var formResponse = form.createResponse();
    var items = form.getItems();

    var row = values[x];

    var formItem = items[0.0].asTextItem();
    if (!isempty(row[1])){
    var response = formItem.createResponse(row[1]);
    formResponse.withItemResponse(response);}

    var formItem = items[1.0].asTextItem();
    if (!isempty(row[2])){
    var response = formItem.createResponse(row[2]);
    formResponse.withItemResponse(response);}

    var formItem = items[2.0].asListItem();
    if (!isempty(row[3])){
    var response = formItem.createResponse(row[3]);
    formResponse.withItemResponse(response);}

    var formItem = items[3.0].asPageBreakItem();                       
    formResponse.withItemResponse(response);

    var formItem = items[4.0].asListItem();
    if (!isempty(row[4])){
    var response = formItem.createResponse(row[4]);
    formResponse.withItemResponse(response);}

    var formItem = items[5.0].asPageBreakItem();
    formResponse.withItemResponse(response);

    var formItem = items[6.0].asListItem();
    if (!isempty(row[5])){
    var response = formItem.createResponse(row[5]);
    formResponse.withItemResponse(response);}

    var formItem = items[7.0].asPageBreakItem();
    formResponse.withItemResponse(response);

    var formItem = items[8.0].asTextItem();
    if (!isempty(row[6])){
    var response = formItem.createResponse(row[6]);
    formResponse.withItemResponse(response);}

    formResponse.submit();
    Utilities.sleep(0.01);

  }

}

function isempty(entry) {
  if (entry == undefined)
  {
    return true;
  }

  if (entry == null){
   return true; 
  }
   var tempstr = entry.toString();

   tempstr = tempstr.replace(/[\r\n\t\s]+$/,"");
   tempstr = tempstr.replace(/^[\r\n\t\s]+/,"");
  if (tempstr.length == 0){

    return true;
  } else {

    return false;
  }

}

This solution has two issues:

  1. It doesn't stop, i.e. after the script processes the last row, it keeps on adding blank rows. This is easily dealt with: the script needs to be stopped after the correct amount of answers got injected, and any surplus answers can be individually deleted from the Forms' "Responses" tab.

  2. There is a 6min time limit on running scripts. Thus this script cannot handle plenty of answers (definitely not more than 500). Workarounds can be made by better scripting (stop, record answers processed, then run again from there until the job's done) or by splitting answers into several sheets and running them one after the other.

Source.

Related Topic