Office script does not process all data

Question

I have created an office script (cannot use VBA, because it has to work in Excel online), which transforms data and converts it to table. However, it only works only if I have no more that 4 rows of data, if there are more rows the rest does not get processed fully, seems like the script runs out of time and runs last lines of code or something.

Source data table (in sheet2) looks like this:

Code	Column1	Column2	Column3	Column4	Column5	Column6	Column7	Column8	Column9	Column10	Column11	PowerAppsId
a	1	2	3	4	5	6	7	8	9	10	11	12
Dia = 88.90Thk = 6.30Len = 12000Qua = S460MHHea = 315155Pcs = 19Mtr = 228.00Cor = Sor = Btc = 21KE020558												1d4dfa2c-ed94-4152-adcd-6700e208608f
Dia = 88.90Thk = 6.30Len = 12000Qua = S460MHHea = 36829EPcs = 19Mtr = 228Cor = Sor = Btc = Z000470271												c7a27d77-36bd-407e-8131-7180d753b79f
Dia = 219,1Thk = 12,5Len = 12000Qua = S460MHHea = 032111Pcs = 4Mtr = 48Sor = STOCKCor = Btc = 21C1010406												ded5157e-f82f-46b1-897a-1f2874f40900
Dia = 88.90Thk = 6.30Len = 12000Qua = S460MHHea = 36829EPcs = 19Mtr = 228Cor = Sor = Btc = Z000470273												5fd82d95-a416-4d1f-ab0f-bee896a5d5e2
Dia = 219,1Thk = 12,5Len = 12000Qua = S460MHHea = 032111Pcs = 4Mtr = 48Sor = STOCKCor = Btc = 21C1010406												743fd8c3-8e79-4d72-86af-f46f50a44644
Dia = 88.90Thk = 6.30Len = 12000Qua = S460MHHea = 36829EPcs = 19Mtr = 228Cor = Sor = Btc = Z000470271												8bb45032-cab4-452d-a044-c189461967a7
Dia = 88.90Thk = 6.30Len = 12000Qua = S460MHHea = 315155Pcs = 19Mtr = 228.00Cor = Sor = Btc = 21KE020558												e7e3cddd-0ae2-40a1-bbc0-1191817f8f02
Dia = 88.90Thk = 6.30Len = 12000Qua = S460MHHea = 36829EPcs = 19Mtr = 228Cor = Sor = Btc = Z000470271												25347ead-8ccf-43e4-a8f0-b34517dc8526
Dia = 219,1Thk = 12,5Len = 12000Qua = S460MHHea = 032111Pcs = 4Mtr = 48Sor = STOCKCor = Btc = 21C1010406												852ff0b5-41ef-42ba-afa0-18cb7b83bf80
Dia = 88.90Thk = 6.30Len = 12000Qua = S460MHHea = 36829EPcs = 19Mtr = 228Cor = Sor = Btc = Z000470273												a45eada1-b926-48bf-872b-2f5a8150a5b1

Then I run the script:

function main(workbook: ExcelScript.Workbook) {
    let selectedSheet = workbook.getWorksheet("Sheet2");
    // Replace all "Dia = " with "" on extended range obtained by extending down from range A3 on selectedSheet
    selectedSheet.getRange("A3").getExtendedRange(ExcelScript.KeyboardDirection.down).replaceAll("Dia = ", "", {completeMatch: false, matchCase: false});
    // Replace all "Thk =" with "" on extended range obtained by extending down from range A3 on selectedSheet
    selectedSheet.getRange("A3").getExtendedRange(ExcelScript.KeyboardDirection.down).replaceAll("Thk =", "", {completeMatch: false, matchCase: false});
    // Replace all "Len =" with "" on extended range obtained by extending down from range A3 on selectedSheet
    selectedSheet.getRange("A3").getExtendedRange(ExcelScript.KeyboardDirection.down).replaceAll("Len =", "", {completeMatch: false, matchCase: false});
    // Replace all "Qua =" with "" on extended range obtained by extending down from range A3 on selectedSheet
    selectedSheet.getRange("A3").getExtendedRange(ExcelScript.KeyboardDirection.down).replaceAll("Qua =", "", {completeMatch: false, matchCase: false});
    // Replace all "Hea =" with "" on extended range obtained by extending down from range A3 on selectedSheet
    selectedSheet.getRange("A3").getExtendedRange(ExcelScript.KeyboardDirection.down).replaceAll("Hea =", "", {completeMatch: false, matchCase: false});
    // Replace all "Pcs =" with "" on extended range obtained by extending down from range A3 on selectedSheet
    selectedSheet.getRange("A3").getExtendedRange(ExcelScript.KeyboardDirection.down).replaceAll("Pcs =", "", {completeMatch: false, matchCase: false});
    // Replace all "Sor = " with " SOR" on range A3:A6 on selectedSheet
    selectedSheet.getRange("A3:A6").replaceAll("Sor = ", " SOR", {completeMatch: false, matchCase: false});
    // Replace all "Cor = " with "" on range A3:A6 on selectedSheet
    selectedSheet.getRange("A3:A6").replaceAll("Cor = ", "", {completeMatch: false, matchCase: false});
    // Replace all "Btc =" with "" on range A3:A6 on selectedSheet
    selectedSheet.getRange("A3:A6").replaceAll("Btc =", "", {completeMatch: false, matchCase: false});
    // Replace all "Mtr =" with "" on range A3:A6 on selectedSheet
    selectedSheet.getRange("A3:A6").replaceAll("Mtr =", "", {completeMatch: false, matchCase: false});
    // Text to columns on range A3:A6 on selectedSheet
    for (let row = 0; row < selectedSheet.getRange("A3:A6").getRowCount() ; row++) {
        let sourceRange = selectedSheet.getRange("A3:A6");
        let destinationRange = selectedSheet.getRange("A3");
        let sourceRangeValues = sourceRange.getRow(row).getValues()[0][0].toString().split(/[ ]/)
        destinationRange.getOffsetRange(row, 0).getResizedRange(0, sourceRangeValues.length - 1).setValues([sourceRangeValues]);
    }
    let sheet1 = workbook.getWorksheet("Sheet1");
    // Delete extended range obtained by extending down from range 2:2 on selectedSheet
    sheet1.getRange("2:2").getExtendedRange(ExcelScript.KeyboardDirection.down).delete(ExcelScript.DeleteShiftDirection.up);
    let table1 = workbook.getTable("Table1");
    // Paste to table table1 cell in row 0 on column Dia from extended range obtained by extending down, then right from range A3 on selectedSheet
    table1.getColumn("Dia").getRangeBetweenHeaderAndTotal().getRow(0).copyFrom(selectedSheet.getRange("A3").getExtendedRange(ExcelScript.KeyboardDirection.down).getExtendedRange(ExcelScript.KeyboardDirection.right), ExcelScript.RangeCopyType.all, false, false);
    selectedSheet.getRange("3:3").getExtendedRange(ExcelScript.KeyboardDirection.down).delete(ExcelScript.DeleteShiftDirection.up);
}

but it outputs table like this (to sheet1):

Dia	Thk	Len	Qua	Hea	Pcs	Mtr	Cor	Btc
88.90	6.30	12000	S460MH	315155	19	228.00	SOR	21KE020558
88.90	6.30	12000	S460MH	36829E	19	228	SOR	Z000470271
219,1	12,5	12000	S460MH	032111	4	48	SORSTOCK	21C1010406
88.90	6.30	12000	S460MH	36829E	19	228	SOR	Z000470273
219,1 12,5 12000 S460MH 032111 4Mtr = 48Sor = STOCKCor = Btc = 21C1010406
88.90 6.30 12000 S460MH 36829E 19Mtr = 228Cor = Sor = Btc = Z000470271
88.90 6.30 12000 S460MH 315155 19Mtr = 228.00Cor = Sor = Btc = 21KE0205583
88.90 6.30 12000 S460MH 36829E 19Mtr = 228Cor = Sor = Btc = Z000470271
219,1 12,5 12000 S460MH 032111 4Mtr = 48Sor = STOCKCor = Btc = 21C1010406
88.90 6.30 12000 S460MH 36829E 19Mtr = 228Cor = Sor = Btc = Z000470273

As you can see, first four rows are processed correctly, but the rest only partially. What can be causing this? Script shows no errors

I have tried running the same script locally - saved excel online file as a local copy, but results are the same. Script works normally only if I have up to four rows of data

I haven't analysed it detail but your code has A3 to A6 in a few spots, that's exactly four rows, could that be your problem? — Skin, Jan 30 '23 at 10:12
Somehow, i completely missed that. Will try to edit that soon and see if that fixes script — Lukas Nickus, Jan 30 '23 at 10:42

score 0 · Answer 1 · answered Jan 30 '23 at 11:38

0

Range was not defined correctly in part of the script

answered Jan 30 '23 at 11:38

Lukas Nickus

21
3

Instead of simply providing the answer directly, try writing a detailed comment that explains the solution, as long as the explanation is not too lengthy. – DSDmark Feb 03 '23 at 16:00

Office script does not process all data

1 Answers1