从谷歌电子表格创建一个谷歌表单 [英] Creating a google form from a google spreadsheet

问题描述

我创建了一个Google电子表格来自动转换为Google表单，因此我不必手动输入所有问题到Google表单中。

我我正在写谷歌应用程序脚本，并设法得到所有的问题。我试图根据表单的第一列将表格分成不同的部分。所以如果第一列是1，那么与它相对应的问题应该在第一部分，如果它是2，则它应该创建另一部分。依此类推。

我该怎么做？代码是什么？我在此处附加了Google表格 Google电子表格

  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）; （var i = 0; i< numberRows; i ++）
 
} 
 
 Logger.log（data）; 
 var questionType = data [i] [0]; 
 if（questionType ==''）
 
 {
 continue; 
} 
 //从电子表格的第一列中选​​择问题的类型
 else if（questionType =='1'）
 {
 var rowLength = data [I]。长度; 
 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（'请指明并附上相关文件'）; //在最后一个
 
 form.addPageBreakItem（）。setTitle（'Identity  -  Asset Management'）中添加文本问题。setHelpText（）（）; 
 
 
  

googleSheet

解决方案

在下一节中使用相同的确切格式，您可以使用简单的计数器。我已经写了一个成功的脚本变体，但它取决于你真正想要什么。

我会做的一些更改

$ b $对于（i = 0; i <项目长度; i ++）{
form.deleteItem（项目[i]）
} $ b $，b

  b  

而不是当前的 form.deleteItem（0）; 。否则，我看到你抓住了所有的数据，但是你没有利用它。每次需要这些选项时调用电子表格应用程序会导致运行速度变慢。更多关于的循环：将 Logger.log（data）; 移到循环之外。每次进入下一行数据时，没有理由继续记录完整的数据范围。或者将它更改为 Logger.log（data [i]）; 这会更有意义。 您已经做一个

  if（questionType ==''）{
 continue; 
} 
  

跳过空行，所以不太确定最后一个 else 是为了。无论如何，循环会自动转到下一个选项。

现在您的设置工作的方式是，您在电子表格中的问题必须

强有力。那就是你不能有

• 第1节


• 第2节
  
• 第1部分

因为这会创建3个部分而不是2个。但是，让我们一起移动，假设电子表格只会设置的方式只有像

• 第1部分


• 部分1


• Section 2

在这种情况下，您应该使用通过在循环之前的某处添加一个计数器 var sectionCount = 0 来解决数据和 questionType 然后在你的中为循环，你做一个简单的

  else if （'Section'+ questionType）
 sectionCount ++ 
} 
   
 
 
这将创建该部分（假设列A中的数字总是增加1）。然后在相同的 for 循环中，如果语句不需要更多的并且可以使用
  items = data [i] .slice（2，data [i] .length + 1）
 items = items.filter（chkEmpty） 
 
 form.addMultipleChoiceItem（）。setTitle（data [i] [1]）。setChoiceValues（items）
  
其中
 函数chkEmpty（val）{
 return val！=''
} 
  
 
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
解决方案
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 != ''
}


                        
这篇关于从谷歌电子表格创建一个谷歌表单的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！