I'm looking for some assistance if possible on a script to automatically process and sort rows in a Google Sheets to another set of sheets within contained within the main. I'm fairly new to JS so please bear with me!
Example:
I have an app set up to populate a Google sheet with data based on survey responses captured outside of Google.
Column A=1 is the user email, Column B=2 contains a variety of questions, Column C=3 contains the users responses.
Each user is asked 6 questions in the form of a short survey. This automatically populates into the sheet from the source database with no issue.
Where I am spinning gears is taking the specific question from each response (row) and moving row within its own sheet.
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]);
CodePudding user response：
You can use something like this
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
}
}
Script is embed in sheet.
It takes rows and distributes them among different sheets. Row for sheet name is BASE_COLUMN (2 in my example)