Google App Scrip 根据"question text"移动 Google 表格列

Question

我正在寻找一些帮助，如果可能的话，在脚本上自动处理和排序行在一个Google工作表中，把另一个工作表集包含在其中。我对JS相当陌生，所以请原谅我!

的例子:

我有一个应用程序设置填充一个谷歌表格的数据基于调查回应捕获谷歌以外。

A列=1是用户的邮件，B列=2包含各种问题，C列=3包含用户的回复。

每个用户被以简短调查的形式问了6个问题。这会自动从源数据库填充到工作表中，没有任何问题。

我正在旋转齿轮的地方是从每个响应(行)中获取特定问题，并在其自己的工作表中移动行。

function CopyDataToNewFile() {

// How Many Columns over to copy
var columsCopyCount = 6;
// A=1 B=2 C=3 D=4 E=5
// What Column(s) to Monitor
var columnsToMonitor = 2;
// B=2 Monitors Question text
//TARGET SPREAD SHEETS
var questionText1 = "<p>Did you enjoy this course?</p>";
var questionText2 = "<p>How long have you been using the system?</p>";
var questionText3 = "<p>Which alternatives did you consider before purchasing the product?</p>";
var questionText4 = "<p>Thinking about your course, which module did you consider the most valuable to you?</p>";
var questionText5 = "<p>On a scale of 1 to 5, how satisfied are you with your course experience today?</p>";
var questionText6 = "<p>How can we improve your experience</p>";
//SOURCE SPREAD SHEET
var ss = SpreadsheetApp.openById('ID REMOVED');
var sourceSpreadSheetSheetID = ss.getSheetByName("Main Sheet");
var sourceSpreadSheetSheetID1 = ss.getSheetByName(questionText1);
var sourceSpreadSheetSheetID2 = ss.getSheetByName(questionText2);
var sourceSpreadSheetSheetID3 = ss.getSheetByName(questionText3);
var sourceSpreadSheetSheetID4 = ss.getSheetByName(questionText4);
var sourceSpreadSheetSheetID5 = ss.getSheetByName(questionText5);
var sourceSpreadSheetSheetID6 = ss.getSheetByName(questionText6);
var data = sourceSpreadSheetSheetID.getRange(2, 1, sourceSpreadSheetSheetID.getLastRow() - 1, sourceSpreadSheetSheetID.getLastColumn()).getValues();
var questionText1 = [];
var questionText2 = [];
var questionText3 = [];
var questionText4 = [];
var questionText5 = [];
var questionText6 = [];

for (var i = 0; i < data.length; i++) {
var rValue = data[i][6];
if (rValue == questionText1) {
questionText1.push(data[i]);
} else if (rValue == questionText2) {
questionText2.push(data[i]);
} else if (rValue == questionText3) {
questionText3.push(data[i]);
} else if (rValue == questionText4) {
questionText4.push(data[i]);
} else if (rValue == questionText5) {
questionText5.push(data[i]);
} else if (rValue == questionText6) {
questionText6.push(data[i]);
} else { // Fail Safe
questionText5.push(data[i]);
}

}
if (questionText1.length > 0) {
sourceSpreadSheetSheetID1.getRange(sourceSpreadSheetSheetID1.getLastRow() + 1, 1, questionText1.length, questionText1[0].length).setValues(questionText1);
}
if (questionText2.length > 0) {
sourceSpreadSheetSheetID2.getRange(sourceSpreadSheetSheetID2.getLastRow() + 1, 1, questionText2.length, questionText2[0].length).setValues(questionText2);
}
if (questionText3.length > 0) {
sourceSpreadSheetSheetID3.getRange(sourceSpreadSheetSheetID3.getLastRow() + 1, 1, questionText3.length, questionText3[0].length).setValues(questionText3);
}
if (questionText4.length > 0) {
sourceSpreadSheetSheetID4.getRange(sourceSpreadSheetSheetID4.getLastRow() + 1, 1, questionText4.length, questionText4[0].length).setValues(questionText4);
}
if (questionText5.length > 0) {
sourceSpreadSheetSheetID5.getRange(sourceSpreadSheetSheetID5.getLastRow() + 1, 1, questionText5.length, questionText5[0].length).setValues(questionText5);
}
if (questionText6.length > 0) {
sourceSpreadSheetSheetID6.getRange(sourceSpreadSheetSheetID6.getLastRow() + 1, 1, questionText6.length, questionText6[0].length).setValues(questionText6);
}
// Will delete the rows of Main Sheet once the data is copided to other sheets
sourceSpreadSheetSheetID.deleteRows(2, sourceSpreadSheetSheetID.getLastRow() - 1);

// Optional Fail Safe
}else {
var targetRange = sourceSpreadSheetSheetID1.getRange(sourceSpreadSheetSheetID1.getLastRow() + 1, 1);
sourceSpreadSheetSheetID.getRange(i, 1, 1, columsCopyCount).copyTo(targetRange), { contentsOnly: true };
//sourceSpreadSheetSheetID.deleteRow(i);
} ```
The source example I worked from can be found here! 
[stackoverflow thread][1]

[1]: https://stackoverflow.com/questions/46932943/google-app-script-google-spreadsheets-move-row-based-on-cell-value-efficiently
When ran in Google scripts I get the following error: 
>
Error
TypeError: Cannot read property 'getRange' of null
CopyDataToNewFile
@ 
I have tried changing around around the string on the **rValue** as I'm sure this is the culprit (I may be wrong) as I think I should be referencing the text "<p>Did you enjoy this course?</p>";? 
if (rValue == questionText1) {
questionText1.push(data[i]);

Answer 1

你可以这样写

const BASE_COLUMN = 2; // Column with sheet name for distribution
function distribute() {
var sheet = SpreadsheetApp.getActive().getSheetByName("Input");
while(sheet.getLastRow()>1) {
var row = sheet.getRange("A2:F2").getValues()[0];
var outputSheetName = row[BASE_COLUMN-1];  // Column 2 is distribution column
if(outputSheetName != '' ) {
var outputSheet = SpreadsheetApp.getActive().getSheetByName(outputSheetName); // Get destination sheet
outputSheet = outputSheet || SpreadsheetApp.getActive().insertSheet(outputSheetName); // Create if not exists
outputSheet.appendRow(row); // copy row
}
sheet.deleteRow(2); // remove original row
}
}

的例子https://docs.google.com/spreadsheets/d/1vY9FZ4PBCxtI3pmsjYtDqtec_n-EQy-H7j3PS_8EBU4/edit gid = 353094948

脚本嵌入到工作表中。

它获取行并将它们分布到不同的工作表中。表名为BASE_COLUMN(在我的例子中是2)