Troubleshoot Google Sheet Script: Finding First Empty Column in Row

Question

I have Google script that will create a Google form based on data in a Google sheet. However, the code is not detecting the last empty cell in each row correctly. Instead it writes every cell in the row even if it is blank. So the forms always have questions with fifteen possible choices because that is the range of the sheet. Here is an example of a faulty form. Here is a link to an editable version of the sheet. Thank you.

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var createForm = [ {name: "Create form", functionName: "CreateFormfromSheet"}];
  ss.addMenu("Generate Test", createForm);
}

function CreateFormfromSheet() {
 var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
 var range = ss.getDataRange(); 
 var data = range.getValues();
 var numberRows = range.getNumRows();
 var numberColumns = range.getNumColumns();
 var firstRow = 2;
 var form = FormApp.create(data[0][1]);

 form.setDescription(data[0][4]);

 for(var i=0;i<numberRows;i++){
  var questionType = data[i][0]; 
  if (questionType==''){
     continue;
  }
  else if(questionType=='TEXT'){
   form.addTextItem()
     .setTitle(data[i][1]) 
     .setHelpText(data[i][2])
     .setRequired(true);   
  } 
  else if(questionType=='PARAGRAPH'){
   form.addParagraphTextItem()
     .setTitle(data[i][1]) 
     .setHelpText(data[i][2])
     .setRequired(true);
  }
  else if(questionType=='CHOICE'){
  var rowLength = data[i].length;
  var currentRow = firstRow+i;
  var currentRangeValues = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1').getRange(currentRow,1,1,rowLength).getValues();
  var getSheetRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1').getDataRange();
  var numberOfColumnsSheet = getSheetRange.getNumColumns();
  var numberOfOptionsInCurrentRow = numberOfColumnsSheet;
  var lastColumnInRange = String.fromCharCode(64 + (numberOfOptionsInCurrentRow));
  var range_string = 'E' + currentRow + ":" + lastColumnInRange + currentRow;
  var optionsArray = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1').getRange(range_string).getValues();
  var choicesForQuestion =[];
    for (var j=0;j<optionsArray[0].length;j++){
        choicesForQuestion.push(optionsArray[0][j]);
        }
  form.addMultipleChoiceItem()
    .setTitle(data[i][1]) 
    .setHelpText(data[i][2])
    .setChoiceValues(choicesForQuestion)
    .setRequired(true); 
  }
  else if(questionType=='CHECKBOX'){
  var rowLength = data[i].length;
  var currentRow = firstRow+i;
  var currentRangeValues = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1').getRange(currentRow,1,1,rowLength).getValues();
  var getSheetRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1').getDataRange();
  var numberOfColumnsSheet = getSheetRange.getNumColumns();
  var numberOfOptionsInCurrentRow = numberOfColumnsSheet;
  var lastColumnInRange = String.fromCharCode(64 + (numberOfOptionsInCurrentRow));
  var range_string = 'E' + currentRow + ":" + lastColumnInRange + currentRow;
  var optionsArray = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1').getRange(range_string).getValues();
  var choicesForQuestion =[];
    for (var j=0;j<optionsArray[0].length;j++){
        choicesForQuestion.push(optionsArray[0][j]);
        }
  form.addCheckboxItem()
    .setTitle(data[i][1]) 
    .setHelpText(data[i][2])
    .setChoiceValues(choicesForQuestion)
    .setRequired(true);
  }
  else if(questionType=='LIST'){
  var rowLength = data[i].length;
  var currentRow = firstRow+i;
  var currentRangeValues = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1').getRange(currentRow,1,1,rowLength).getValues();
  var getSheetRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1').getDataRange();
  var numberOfColumnsSheet = getSheetRange.getNumColumns();
  var numberOfOptionsInCurrentRow = numberOfColumnsSheet;
  var lastColumnInRange = String.fromCharCode(64 + (numberOfOptionsInCurrentRow));
  var range_string = 'E' + currentRow + ":" + lastColumnInRange + currentRow;
  var optionsArray = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1').getRange(range_string).getValues();
  var choicesForQuestion =[];
    for (var j=0;j<optionsArray[0].length;j++){
        choicesForQuestion.push(optionsArray[0][j]);
        }
  form.addListItem()
    .setTitle(data[i][1]) 
    .setHelpText(data[i][2])
    .setChoiceValues(choicesForQuestion)
    .setRequired(true);
  }
  else if(questionType=='GRID'){
  var rowLength = data[i].length;
  var currentRow = firstRow+i;
  var currentRangeValues = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1').getRange(currentRow,1,1,rowLength).getValues();
  var getSheetRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1').getDataRange();
  var numberOfColumnsSheet = getSheetRange.getNumColumns();
  var numberOfOptionsInCurrentRow = numberOfColumnsSheet;
  var lastColumnInRange = String.fromCharCode(64 + (numberOfOptionsInCurrentRow));
  var range_string = 'E' + currentRow + ":" + lastColumnInRange + currentRow;
  var optionsArray = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1').getRange(range_string).getValues();
  var rowTitles =[];
    for (var j=0;j<optionsArray[0].length;j++){
        rowTitles.push(optionsArray[0][j]);
        }
  var rowLength = data[i+1].length;
  var currentRow = firstRow+i+1;
  var currentRangeValues = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1').getRange(currentRow,1,1,rowLength).getValues();
  var getSheetRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1').getDataRange();
  var numberOfColumnsSheet = getSheetRange.getNumColumns();
  var numberOfOptionsInCurrentRow = numberOfColumnsSheet;
  var lastColumnInRange = String.fromCharCode(64 + (numberOfOptionsInCurrentRow));
  var range_string = 'E' + currentRow + ":" + lastColumnInRange + currentRow;
  var optionsArray = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1').getRange(range_string).getValues();
  var columnTitles =[];
    for (var j=0;j<optionsArray[0].length;j++){
        columnTitles.push(optionsArray[0][j]);
        }  
  form.addGridItem()
    .setTitle(data[i][1]) 
    .setHelpText(data[i][2])
    .setRows(rowTitles)
    .setColumns(columnTitles)
    .setRequired(true);
  } 
  else if(questionType=='IMAGE'){
   var img = UrlFetchApp.fetch(data[i][3]); 
   form.addImageItem()
     .setTitle(data[i][1]) 
     .setHelpText(data[i][2])
     .setImage(img);
  }
  else if(questionType=='PAGE'){
   form.addPageBreakItem()
     .setTitle(data[i][1]) 
     .setHelpText(data[i][2]);   
  } 
  else if(questionType=='SECTION'){
   form.addSectionHeaderItem()
     .setTitle(data[i][1]) 
     .setHelpText(data[i][2]);   
  }
  else if(questionType=='TIME'){
   form.addTimeItem()
     .setTitle(data[i][1]) 
     .setHelpText(data[i][2]);   
  }
  else{
    continue;
  }
 } 
}

Answer 1

var getSheetRange =         SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1').getDataRange();

This line is getting the range of the whole sheet, then you are using the above Range in the next line:

var numberOfColumnsSheet = getSheetRange.getNumColumns();

which giving the number of columns as 19 because it is including your 2nd row.

Try:

var numberOfColumnSheet = someRowRange.getNumColumns();

Answer 2

Using the block for CHOICE, this is how I would approach it:

else if (questionType=='CHOICE') {
  // we already have data, no need to retrieve it again
  var choicesForQuestion = [];
  for (var j=4; j < numberColumns; j++)
    if (data[i][j] != "") // only push if there's something to push
      choicesForQuestion.push(data[i][j]);
  form.addMultipleChoiceItem()
    .setTitle(data[i][1]) 
    .setHelpText(data[i][2])
    .setChoiceValues(choicesForQuestion)
    .setRequired(true); 
}