Vertical stacking of columns from 365 pairs of rows

Link to example sheet with faux data: So, here's a formula that works, but iterating the pattern is way too long to type by hand. I tried using chatGPT to simply iterate the pattern but it basically breaks. It either doesn't work, messes up the pattern or, the last time, it reset the conversation and remembers none of it. Yikes.

=FILTER(VSTACK(E6:F,G6:H,I6:J,K6:L), (VSTACK(E6:E,G6:G,I6:I,K6:K) <> "") + (VSTACK(F6:F,H6:H,J6:J,L6:L) <> "") )

To cover all 365 days, I need this to iterate out to Column ABH.

I've tried an apps script (though Id rather it was a formula). It works until column 370 then it jumps back to column A for some reason and I cannot understand this weird issue. The code is as follows:

function stackColumns() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lastColumn = 736; // Adjust to your actual last column
  var lastRow = 100; // Adjust to your actual last row
  
  // Define constants for batch processing
  var startColumn = 5; // Starting column index (E = column 5)
  var batchSize = 100; // Number of columns to process in each batch

  var stackedData = [];

  // Loop through batches of columns
  for (var start = startColumn; start <= lastColumn; start += batchSize) {
    var end = start + batchSize - 1;
    if (end > lastColumn) {
      end = lastColumn;
    }

    // Get data range for the current batch of columns
    var dataRange = sheet.getRange(6, start, lastRow - 5 + 1, end - start + 1);
    var values = dataRange.getValues();
    var numRows = values.length;
    var numCols = values[0].length;

    // Process each column pair in the current batch
    for (var i = 0; i < numCols - 1; i += 2) { // Increment by 2 for column pairs
      var currentStartColumn = start + i;
      var currentEndColumn = start + i + 1;

      // Process each row in the current column pair
      for (var row = 0; row < numRows; row++) {
        var startValue = (currentStartColumn <= numCols) ? values[row][currentStartColumn - start] : "";
        var endValue = (currentEndColumn <= numCols) ? values[row][currentEndColumn - start] : "";

        // Skip processing if both values are empty
        if (startValue === "" && endValue === "") {
          continue;
        }

        // Push the data pair to stackedData
        var rowData = [
          startValue,
          endValue
        ];
        stackedData.push(rowData);
      }
    }
  }

  // Clear existing content and paste the stacked data starting from a specific cell
  var outputRange = sheet.getRange(7, 739, stackedData.length, 2); // Adjust the number of columns (2 for each pair)
  outputRange.clearContent();
  outputRange.setValues(stackedData);
}

I'm at a total loss here. This shouldn't be that difficult but it's defeated me.

Solution

You want a script (though preferably a formula) to create stacked columns of Roster data. You have a script but it is not working to your satisfaction.

This answer is based on the script included in the question. But rather than try and trouble shoot that script, the answer is a complete re-write. The answer consists of two functions:

buildRosterStack - enables the user to declare the number of days to process.
- includes a test that the daysToProcess is not greater than the Calendar data
stackRosters - the main script based.

The time to process is less than 2 seconds.

/*
//  
//  NOTES
//  Each day consists of 2 columns
//  Blank rows are not reported
//  Number of days to process is a variable, set in subroutine
//  Sub-routine also tests that the days to process is not more than the Calendar
//
*/

function buildRosterStack(){

  // enter the number of days to process
  var daysToProcess = 335 // 30 Nov 2024 (extent of available data)

  // test the number of days
  var maxYearDays = 366 // days in 2024
  if (daysToProcess>maxYearDays){
    // request exceeds calendar data, stop processing
    console.log("days to Process is greater than Calendar days. Processing stopped")
  }

  stackRosters(daysToProcess)
}



function stackRosters(daysToProcess) {

  var days2Process = daysToProcess //Number of days to process

  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var sheetName = "Master Calendar OLD"
  var sheet = ss.getSheetByName(sheetName)

  // get the dimensions  
  var lastRow = sheet.getLastRow()
  // Logger.log("DEBUG: the last row = "+lastRow)
  
  // Define constants for batch processing
  var startRow = 6
  var startColumn = 5; // Starting column index (E = column 5)
  var stackedData = [] // declare a temporary array
  // Logger.log("DEBUG: the start row = "+startRow+", start column = "+startColumn+", number of days to process = "+days2Process)

  // get the data
  var range = sheet.getRange(startRow,startColumn,lastRow-startRow+1,days2Process*2)
  // Logger.log("DEBUG: the data range = "+range.getA1Notation())
  var values = range.getValues()

  var rowsOfData = values.length
  // Logger.log("DEBUG: rows of data = "+rowsOfData)

    

  // loop through the data
  for (var j=0;j<(days2Process*2);j += 2){ // days
    for (var i=0;i<rowsOfData;i++){ // rows
    
      var dayData1 = values[i][j]
      var dayData2 = values[i][j+1]
      // Logger.log("DEBUG: i="+i+", j="+j+", data1 = "+dayData1+", length:"+dayData1.length+", data 2 = "+dayData2+", length:"+dayData2.length)
      
      // test for BOTH cells as blank
      // if yes, move to next row
      // if no, push BOTH cells onto temporary array
      if (dayData1.length ==0 && dayData2.length==0)
      {
        continue
      }
      else{
        stackedData.push([dayData1,dayData2])
      }
      
    }
  }

  // set output sheet and range and then set the Values
  var targetSheet = ss.getSheetByName("output")
  targetSheet.getRange(1,1,stackedData.length,2).setValues(stackedData)
  console.log("Done")

}

SAMPLE OUTPUT