Creating a google form from a google spreadsheet

Question

I have created a google spreadsheet to automatically convert into a google form, so i don't have to manually enter all the questions into the google form.

I am writing google app script and managed to get all the questions.I am trying to divide the form in to sections depending on the first column of the sheet. So if the first column is "1" questions corresponding to it should be on the first section and if it is "2" it should create another section.And so on.

How can i do that? what will be the code? I have attached the google sheet as here Google spreadsheet

function myFunction() 
{
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
  var range = ss.getDataRange(); 
  var data = range.getValues();
  var numberRows = range.getNumRows();
  var numberColumns = range.getNumColumns();
  var firstRow = 1;
  var form = FormApp.openById('1hIQCLT_JGLcvjz44vXTvP5ziia6NnwCqWBxYT4h2uCk');


  var items = form.getItems();
  var ilength = items.length;
  for (var i=0; i<items.length; i++)

  {

    form.deleteItem(0);

  }

  for(var i=0;i<numberRows;i++)
  {
    Logger.log(data);
    var questionType = data[i][0]; 
    if (questionType=='')

    {
      continue;
    }
    //choose the type of question from the first column of the spreadsheet
    else if(questionType=='1')
    {
      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 = 'C' + 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("").setChoiceValues(choicesForQuestion).setRequired(true); 

    }

    else
    {
      continue;
    }

  } 

  form.addParagraphTextItem()
  .setTitle('Please specify and attach relevant documents');          // add the text question at the last

  form.addPageBreakItem().setTitle('Identity - Asset Management').setHelpText("")();

}

googleSheet

I have also attached my google spreadsheet at the bottom "googleSheet" — Ishika Goyal
It would help if you formatted your code to make it more readable in the StackOverflow reader. Otherwise, this is a reasonable question to ask for being your first question! — NoseKnowsAll
Thanks @NoseKnowsAll !! Is there anything wrong with my code formatting. I mean in terms of reading it? — Ishika Goyal
Yes there is some poor formatting. For instance, your brackets {} should all line up vertically so that it's visually easy to tell what's in the same code blocks. This also means everything inside the code blocks should be tabbed (well, 4 spaces). Those two fixes would make this question much more readable. — NoseKnowsAll

Vytautas Vytautas · Accepted Answer · 2016-12-13T09:02:52

If you want to use the same exact format for the next section you can get away with a simple counter. I have written a successful script variant, but it depends on what you really want.

Some of the changes I would do

for (i = 0; i < items.length; i++) { 
  form.deleteItem(items[i])
}

instead of the current form.deleteItem(0);. Otherwise I see that you grab all the data, however you do not utilize it. Calling the spreadsheet app each time you want the options causes it to run a lot slower. More on that for loop: move the Logger.log(data); outside of the loop. There is no reason for you to keep logging the full data range each time you go to the next row of the data. Or change it to Logger.log(data[i]); which would make more sense.

You already do a

if (questionType=='') {
  continue;
}

to skip over the empty lines, so not really sure what that last else is meant for. The loop will fall through to the next option on its own anyway.

Now the way your set up would work is that your questions in the spreadsheet must be in order. That is you cannot have

Section 1
Section 2
Section 1

as that will create 3 sections instead of 2. However let's move along with the assumption that the spreadsheet would only be set up in a way where you will only have a sequence like

Section 1
Section 1
Section 2

In that case you should utilize your data and questionType by adding a counter var sectionCount = 0 somewhere before the loop. Then inside of your for loop you do a simple

else if (questionType != sectionCount) {
  form.addSectionHeaderItem().setTitle('Section ' + questionType)
  sectionCount++
}

this will create the section (provided that the numbers are always increasing by 1 in Column A). Then in the same for loop you do not need any more if statements and can just use

items = data[i].slice(2, data[i].length + 1)
items = items.filter(chkEmpty)

form.addMultipleChoiceItem().setTitle(data[i][1]).setChoiceValues(items)

where

function chkEmpty(val){
  return val != ''
}

Creating a google form from a google spreadsheet

2 Answers