如果可能的話，我正在尋求一些幫助，以自動處理Google 表格中的行并將其排序到包含在主表格中的另一組表格中。我對JS相當陌生，所以請多多包涵！
例子：
我有一個應用程式設定為使用基于在 Google 之外捕獲的調查回應的資料填充 Google 作業表。
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]);
uj5u.com熱心網友回復：
你可以使用這樣的東西
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）
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/480306.html