Split column into rows using excel office script

Based on your description I made an attempt at a solution with Office Scripts. It takes a table like this:

and outputs a new table on a new worksheet, like this:

For better or worse I attempted to keep the logic in the workbook via formulas derived from the first table and output into the second. This formula logic would need to be rewritten if there is more than one activity per day.

I'm not a developer but I can already see areas in this Office Script that need improvement:

function main(workbook: ExcelScript.Workbook) {
  // delete new worksheet if it already exists so the rest of the script can run effectively
  // should you need to retain data simply rename worksheet before running
  if (workbook.getWorksheet("My New Sheet") != undefined) {
    workbook.getWorksheet("My New Sheet").delete()
  }
  // assumes your original data is in a table
  let myTable = workbook.getTable("Table1");
  let tableData = myTable.getRangeBetweenHeaderAndTotal().getValues();
  
  // extract the dates as excel serial numbers
  let allDates:number[] = [];
  for (let i = 0; i < tableData.length; i++) {
    allDates.push(tableData[i][2], tableData[i][3]);
  }
  let oldestDate = Math.min(...allDates);
  let newestDate = Math.max(...allDates);
  let calendarSpread = newestDate-oldestDate+2;

  // construct formula from the tableData
  // first add a new 'column' to tableData to represent the days of the week (as numbers) on which the activity is planned. this will be an array added to each 'row'.
  for (let r = 0; r < tableData.length; r++) {
    tableData[r].push(findDay(tableData[r][1]));
  }
  // start a near blank formula string
  let formulaText:string = '=';
  // use the following cell reference
  let cellRef = 'C2';
  // construct the formual for each row in the data and with each day of the week for the row
  let rowCount:number;
  for (let r = 0; r < tableData.length; r++) {
    if (tableData[r][4].length > 1) {
      formulaText += 'IF(AND(OR(';
    } else {
      formulaText += 'IF(AND(';
    }
    for (let a=0; a < tableData[r][4].length; a++) {
      formulaText += 'WEEKDAY(' + cellRef + ')=' + tableData[r][4][a].toString();
      if (a == tableData[r][4].length - 1 && tableData[r][4].length > 1) {
        formulaText += '),';
      } else {
        formulaText += ', ';
      }
    }
    formulaText += cellRef + '>=' + tableData[r][2] + ', ' + cellRef + '<=' + tableData[r][3] + '), "' + tableData[r][0] + '", ';
    rowCount = r+1;
  }
  formulaText += '"-"';
  for (let p=0; p<rowCount; p++) {
    formulaText += ')';
  }

  // create a new sheet
  let newSheet = workbook.addWorksheet("My New Sheet");
  // add the header row
  let header = newSheet.getRange("A1:C1").setValues([["Activity", "Day", "Date"]])
  // insert the oldest date into the first row, then add formula to adjacent cells in row
  let firstDate = newSheet.getRange("C2")
  firstDate.setValue(oldestDate);
  firstDate.setNumberFormatLocal("m/d/yyyy");
  firstDate.getOffsetRange(0, -1).setFormula("=C2");
  firstDate.getOffsetRange(0, -1).setNumberFormatLocal("ddd");
  firstDate.getOffsetRange(0, -2).setFormula(formulaText);
  // use autofill to copy results down until the last day in the sequence
  let autoFillRange = "A2:C" + (calendarSpread).toString();
  firstDate.getResizedRange(0, -2).autoFill(autoFillRange, ExcelScript.AutoFillType.fillDefault);
  // convert the the range to a table and format the columns
  let outputTable = newSheet.addTable(newSheet.getUsedRange(), true);
  outputTable.getRange().getFormat().autofitColumns();
  //navigate to the new sheet
  newSheet.activate();
}

// function to return days (as a number) for each day of week found in a string 
function findDay(foo: string) {
  // start with a list of days to search for
  let daysOfWeek:string[] = ["Sun", "Mon", "Tue", "Wed", "Thur", "Fri", "Sat"];
  //create empty arrays
  let searchResults:number[] = [];
  let daysFound:number[] = [];
  // search for each day of the week, this will create an array for each day of the week where the value is -1 if the day is not found or write the position where the day is found
  for (let d of daysOfWeek) {
    searchResults.push(foo.search(d));
  }
  // now take the search results array and if the number contained is greater than -1 add it's position+1 to a days found array. this should end up being a list of numbered days of the week found in a string/cell
  for (let i = 0; i < searchResults.length; i++) {
    if (searchResults[i] > -1) {
      daysFound.push(i + 1);
    }
  }
  return daysFound
}