How to select last row in Office Scripts?

Question

Good day,

I do not have any Java script knowledge, so what I am trying to solve will probably be very easy.

What I am trying to do in Excel, is to automate copying rows from a table from one area in a worksheet and insert it into another table in a different area in the same worksheet.

To do this, I have recorded the steps to automate this process using Office Scripts. Below is the script from the recording:

But what I would like to do, is to select the last row that contains data instead of a fixed row that might or might not have data.

I would appreciate any help.

Answer 1

Append data rows at the end

function main(workbook: ExcelScript.Workbook) {
    let selectedSheet = workbook.getActiveWorksheet();
    // modify source and dest. table name as needed
    let tableSrc = workbook.getTable("Table1");
    let tableDes = workbook.getTable("Table2");
    // locate the first blank cell in the first col of dest table
    let anchorCell = tableDes.getRange().getColumn(0).getLastCell().getOffsetRange(1,0);
    // copy data rows from source table to dest table
    anchorCell.copyFrom(tableSrc.getRangeBetweenHeaderAndTotal(), ExcelScript.RangeCopyType.all, false, false);
}

---

Insert data rows at the beginning

function main(workbook: ExcelScript.Workbook) {
    let selectedSheet = workbook.getActiveWorksheet();
    // modify source and dest. table name as needed
    let tableSrc = workbook.getTable("Table1");
    let rowCount = tableSrc.getRowCount();
    let tableDes = workbook.getTable("Table2");
    // Insert row ainto dest table
    for (let i = 0; i < rowCount; i++) {
        tableDes.addRow(0);
    }
    // locate the first cell in the first col
    let anchorCell = tableDes.getRangeBetweenHeaderAndTotal().getCell(0,0);
    // copy data rows from source table to dest table
    anchorCell.copyFrom(tableSrc.getRangeBetweenHeaderAndTotal(), ExcelScript.RangeCopyType.all, false, false);
}

---

Update

Question: However empty rows in the table are also copied across. How do you select only the rows in the source table that contain data?

function main(workbook: ExcelScript.Workbook) {
    let selectedSheet = workbook.getActiveWorksheet();
    // modify source and dest. table name as needed
    let tableSrc = workbook.getTable("Table1");
    let lastCell = tableSrc.getRange().getColumn(0).getLastCell();
    if (lastCell.getText().length === 0) {
        lastCell = lastCell.getRangeEdge(ExcelScript.KeyboardDirection.up);
    }
    let endRow = lastCell.getRowIndex();
    let startRow = tableSrc.getRange().getRowIndex() + 1;
    if (endRow >= startRow) {
        let colCount = tableSrc.getRange().getColumnCount();
        let rowCount = endRow - startRow + 1;
        console.log(rowCount)
        let srcRange = tableSrc.getRangeBetweenHeaderAndTotal().getAbsoluteResizedRange(rowCount, colCount);
        let tableDes = workbook.getTable("Table2");
        // Insert row ainto dest table
        for (let i = 0; i < rowCount; i++) {
            tableDes.addRow(0);
        }
        // locate the first cell in the first col
        let anchorCell = tableDes.getRangeBetweenHeaderAndTotal().getCell(0, 0);
        // copy data rows from source table to dest table
        anchorCell.copyFrom(srcRange, ExcelScript.RangeCopyType.all, false, false);
    }
}