Office Script to format raw data and export to csv

Question

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

	A	B	C	D	E	F	G	H	I	J	K	L
1	RECTYPE	ORDUNIQ	CUSTOMER	SHIPTO	TYPE	ORDDATE	EXPDATE	PONUMBER	LOCATION	REQUESDATE
2	RECTYPE	ORDUNIQ	LINENUM	LINETYPE	ITEM	_MISCCHARG	_PRICELIST	_LOCATION	QTYORDERED	_PRIUNTPRC	_ORDUNIT	_EXTINVMIS
3	RECTYPE	ORDUNIQ	LINENUM	SERIALNUMF	DETAILNUM	MOVED	QTY	QTYMOVED
4	RECTYPE	ORDUNIQ	LINENUM	LOTNUMF	DETAILNUM	EXPIRYDATE	STKQTY	QTY	STKQTYMOVE	QTYMOVED
5	RECTYPE	ORDUNIQ	PAYMENT	DISCBASE	DISCDATE	DISCPER	DISCAMT	DUEBASE	DUEDATE	DUEPER	DUEAMT
6	RECTYPE	ORDUNIQ	UNIQUIFIER	DETAILNUM	COINTYPE	COIN
7	RECTYPE	ORDUNIQ	OPTFIELD	VALUE
8	RECTYPE	ORDUNIQ	LINENUM	OPTFIELD
9	1	1	WAFHOM	88888	1	20240610	20240801	2462460	USA	20240806
10	2	1	1	1	ANT1				2	0
11	2	1	1	1	ANT2				4	0
12	2	1	1	1	ANT3				1	0
13	1	1	WAFHOM	999999	1	20240610	20240801	2462460	CAN	20240806
14	2	1	1	1	PINA1				4	0

Here is the output sheet

	A	B	C	D	E	F	G
1	20240610	20240801	2462460	USA	20240806	ANT1	2
2	20240610	20240801	2462460	USA	20240807	ANT2	4
3	20240610	20240801	2462460	USA	20240808	ANT3	1
4	20240610	20240801	2462460	CAN	20240806	PINA1	4

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
    }
}

And what is your question? Does the code work? What happens or doesn't happen? — Tim Roberts
– Tim Roberts, Commented Jun 15, 2024 at 18:51
Sorry for not being clearer. No, this code does not work. the code does not go inside the if conditions, and I tried to test with console but couldn't get the .getValue() to show on console.log — Jampong
– Jampong, Commented Jun 15, 2024 at 18:56

taller · Accepted Answer · 2024-06-16 00:42:21Z

0

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

edited Jun 16, 2024 at 0:42

answered Jun 15, 2024 at 23:45

taller

19.7k2 gold badges9 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Jampong Over a year ago

This looks awesome. I'll take time to digest this. too bad office scripting does not have debug tools like VBA yet. But Thank you. I will try this out.

Jampong Over a year ago

This works. Thank you so much. Where do I learn Office Scripting other than Youtube and Microsoft Documentation? I am looking to do API with Office Scripts also.

taller Over a year ago

The samples are pretty good. Microsoft documentation: > Office Scripts samples and scenarios

Collectives™ on Stack Overflow

Office Script to format raw data and export to csv

1 Answer 1

Note:

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Note:

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related