Office Script to format raw data and export to csv

I am new with Office Scripts. Here is what I am trying to accomplish

• If A9 is 1, copy Raw_Data sheet F9 to J9 to temp sheet A1 to E1
• If A10 is 2, copy Raw_Data E10 and I10 to temp sheet F1 and G1
• If A11 is 2, copy Raw_Data sheet F9 to J9 to temp sheet A2 to E2 and copy Raw_Data E11 and I11 to temp sheet F2 and G2
• If A12 is 2, copy Raw_Data sheet F9 to J9 to temp sheet A3 to E3 and copy Raw_Data E12 and I12 to temp sheet F3 and G3
• If A13 is 1, copy Raw_Data sheet F13 to J13 to temp sheet A4 to E4
• If A14 is 2, copy Raw_Data E14 and I14 to temp sheet F4 and G4
• If A15 is 2, copy Raw_Data sheet F15 to J15 to temp sheet A5 to E5 and copy Raw_Data E15 and I15 to temp sheet F5 and G5

Here is Raw_Data sheet

Here is the output sheet

Here is what I've tried to come up with which is not much

function main(workbook: ExcelScript.Workbook) {
    let test_row = 9;
    let test_column = 1;
    let test_empty = 0;
    
    const sheet_temp = workbook.getWorksheet("Temp");
    const sheet_raw = workbook.getWorksheet("Raw_Data");
    const sheet_Upload = workbook.getWorksheet("Upload_SO_Xorosoft");

    while (test_empty == 0) {
        if (sheet_temp.getCell(test_row, test_column).getValue() == 1) {
            //this to test if A9=1
            ;
        }
        else if (sheet_temp.getCell(test_row, test_column).getValue() == 2) {
            //this to test if A9=2
            ;
        }
        else {
            //this to test if A9 is blank
            //complete do while condition
            test_empty=1;
        }
         //export to csv
    }
}

The data extraction logic can be simplified as follows:

• If a cell in Column A is 1, then retrieve 5 columns (Columns A to E in the output table).
• If a cell in Column B is 2, then retrieve 2 columns (Columns F and G in the output table).

• Load data into array dataValues
• Populate output array resRows
• Write output to sheet

function main(workbook: ExcelScript.Workbook) {
    const startRow = 9;
    const colCnt = 10;
    const sheet_temp = workbook.getWorksheet("Temp");
    const sheet_raw = workbook.getWorksheet("Raw_Data");
    const lastRow = sheet_raw.getRange("A:A").getLastCell().getRangeEdge(ExcelScript.KeyboardDirection.up).getRowIndex();
    const dataValues = sheet_raw.getRangeByIndexes(startRow - 1, 0, lastRow - startRow + 2, colCnt).getValues();
    let resRows: [] = [];
    let dataRow: [] = [];
    for (let i = 0; i < dataValues.length; i++) {
        if (dataValues[i][0] == "1") {
            dataRow = [];
            for (let j = 5; j < dataValues[0].length; j++) {
                dataRow.push(dataValues[i][j]);
            }
        }
        else if (dataValues[i][0] == "2") {
            let newRow: [] = [...dataRow];
            newRow.push(dataValues[i][4]);
            newRow.push(dataValues[i][8]);
            // console.log(newRow)
            resRows.push(newRow)
        }
    }
    let useRange = sheet_temp.getUsedRange();
    if(useRange){ useRange.clear(ExcelScript.ClearApplyTo.all);}
    sheet_temp.getRangeByIndexes(0, 0, resRows.length, resRows[0].length).setValues(resRows);
}

Note:

• OfficeScripts cannot export a sheet to CSV like VBA can. However, you can accomplish this using Power Automate.

Save XLSX Excel all worksheets as csv