Office Scripts cannot read properties of undefined

Question

I am writing a program that sorts my table then splits the strings in column E of the table, cutting the values in column E down to the first 10 chars and putting the remainder in column F.

function main(workbook: ExcelScript.Workbook) {
    let masterA = workbook.getTable("MasterA");
    // Sort on table: masterA column index: '8'
    masterA.getSort().apply([{key: 8, ascending: true}]);
    // Sort on table: masterA column index: '2'
    masterA.getSort().apply([{key: 2, ascending: true}]);
    let tableRows = masterA.getRowCount();
    let sheet = workbook.getActiveWorksheet();
    // Set the range to the column of data to modify
    let lastRowCount = sheet.getTable("MasterA").getRowCount()
    if (lastRowCount > 0) {// Adjust the range to include all rows in the column
    let range = sheet.getRangeByIndexes(1, 0, lastRowCount, 10);
    let vals = range.getValues();

    for (let i=1; i< tableRows; i++) {
        let value: string = range.getValue[i][5]
        let len: number = value.length
        if (len > 10) {
            vals[i][6] = value.substring(10)
            vals[i][5] = value.substring(0,9)
        }
    }}
}

I am getting this error when I try to run: "Line 16: Cannot read properties of undefined (reading '5')" in reference to let value: string = range.getValue[i][5]

Any ideas? This is my first attempt at Office Scripts and I may be missing something obvious.

Answer 1

Use vals[i][5] to get the value and toString() converts it to a string.

vals is a zero-base index array, so i should be start from 0.

        for (let i = 0; i < tableRows; i++) {
            let value: string = vals[i][5].toString();
            let len: number = value.length
            if (len > 10) {
                vals[i][6] = value.substring(10)
                vals[i][5] = value.substring(0, 9)
            }

---

There is a easier way to get the table range.

Microsoft documentation:

ExcelScript.Table interface getRangeBetweenHeaderAndTotal()

 
    if (masterA) {
        let range = masterA.getRangeBetweenHeaderAndTotal();
        let vals = range.getValues();
        for (let i = 0; i < vals.length; i++) {
            let value: string = vals[i][5].toString();
            let len: number = value.length
            if (len > 10) {
                vals[i][6] = value.substring(10)
                vals[i][5] = value.substring(0, 9)
            }
        }
    }