0

The remade questions end up appearing as new columns on the form response sheet even though the question titles remain the same, which becomes messy for analyis. I sincerely request step by step explanation/assistance as to how to fix it as I am very much beginner.

How to Edit existing google form item (question) using google Apps Script I have seen this and seems like it should help me but I am not sure how to incorporate it.

This is what I have so far

function FormCreator() {
  
 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('12z4pe1XM1qhUOdhL198w8ut_hW0Mwh_rZnZGjVBIbTQ');

  var items = form.getItems();
  while(items.length > 0){
    form.deleteItem(items.pop());
  }
  
 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)
    .showOtherOption(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)
    .showOtherOption(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)
    .showOtherOption(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)
    .showOtherOption(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;
  }
 } 
}
  • See my answer at: [https://stackoverflow.com/a/33324671/2946873](https://stackoverflow.com/a/33324671/2946873) – Alan Wells Jun 06 '21 at 14:48
  • You just have to get the item and use `setTitle` on it. The problem I'm seeing here is how to identify which item corresponds to which spreadsheet row of data. Do you know how to identify that? Do you keep the old description, and know which old description refers to each new one? – Iamblichus Jun 07 '21 at 07:35

0 Answers0