EDIT: To better illustrate my difficulty, I have included an example of my data that showcases the desired outcome:
I hope this clarifies my request.
I am seeking assistance with a complex issue: I am working on creating a summary sheet for multiple data sheets. Each data sheet (e.g., "Sheet1," "Sheet2," "Sheet3") contains an ID variable used to categorize the data within each sheet.
My goal is to iterate through all data sheets, extract and categorize the data based on the ID variable so that I have separate sections for As, Bs, and Cs from each sheet, and then merge these sections together WITH EMPTY ROWS in the summary sheet.
The progress I've made so far includes the following steps:
function main() {
// SETUP.
var app = SpreadsheetApp;
var workbook = app.getActiveSpreadsheet();
var activeSheet = workbook.getActiveSheet();
// CREATE NEW SUMMARY SHEET.
var targetSheet = workbook.getSheetByName("Summary");
if (!targetSheet) {
workbook.insertSheet("Summary",1);
}
// ARRAY OF SHEETS USED IN MY LOOP.
var tabs = ["Sheet 1",
"Sheet 2",
"Sheet 3"];
// LOOP FOR ALL SHEETS.
for (var i = 0; i < tabs.length; i++) {
var sheet = workbook.getSheetByName(tabs[i]);
// GRAB THE ORIGINAL DATA.
var originalData = sheet.getRange(5, 1, sheet.getLastRow()-5, sheet.getLastColumn()).getValues();
// SELECT ID AND SORT BY UNIQUE IDs.
var range = sheet.getRange(5,2,sheet.getLastRow()-5,1).getValues();
var range = [].concat.apply([], range);
let uniqueValues = range.filter((v, i, a) => a.indexOf(v) === i);
// GRAB THE UNIQUE DATA PIECES IN EACH SHEET.
for (var t = 0; t < uniqueValues.length; t++) {
var filterText = uniqueValues[t];
var newData = originalData.filter(function(item) {
return item[1] === filterText;
});
// TODO: DEFINE NEXT STEPS
// e.g., remove rows below a specific threshold.
// WRITE DATA PIECES BACK TO SUMMARY SHEET.
workbook.getSheetByName("Summary").getRange(???).setValues(newData);
}
}
}
The current code successfully segments the data across different data sheets. However, merging the segments poses a challenge as each iteration overwrites the previous one.
I need to devise a method to gather these segmented data pieces based on IDs and concatenate them appropriately as shown in the example data provided above.
I seem to be struggling to maintain control between loops and temporary data storage within the loops.