exceltypescriptexcel-2010office-scriptsms-office-script

Office Scripts Rows not Copy Pasting Correctly


Goal is to find any rows in a table "PO" on sheet that's value is "Cancelled," "Ordered," or "Voided" in column A, copy the row, then paste it onto the end of the table ("PO ARCHIVE") on sheet2. After I figure out how to do this I will be deleting the row from "PO" but that's commented out for now.

The problem is that when I run this code as is, the code pastes a blank row at the end of the table (I have determined this through testing what happens when I replace lastrow+1 with lastrow-1 in the code). Interestingly, it only ever pastes one blank row, even if here are more than one rows that satisfy the conditions.

I have also tried manually changing the row numbers within the setValue statement to single rows that satisfy the conditions, and this results in the script pasting rows that just contain the value of column A in every cell A:O.

Also I know that the way I've set up my i counter in the for loop is probably inefficient but I'm not sure the best way to fix it.

    function main(workbook: ExcelScript.Workbook) {
    let tb = workbook.getTable("PO");
    let tableRows = tb.getRowCount();
    let sheet = workbook.getActiveWorksheet();
    let tb2 = workbook.getTable("PO");
    let tableRows2 = tb.getRowCount();
    let sheet2 = workbook.getWorksheet("PO Archive");
    // Set the range to the column of data to modify
    let lastRowCount = sheet.getTable("PO").getRowCount()
    if (tb) {
        let range = tb.getRangeBetweenHeaderAndTotal();
        let vals = range.getValues();
        for (let i = 3; i < vals.length + 3; i++) {
            let value: string = vals[i - 3][0].toString();
            if (value == "Cancelled" || value == "Ordered" || value == "Voided") {
                let lastRow = sheet2.getUsedRange().getLastCell().getRowIndex() + 1;
                sheet2.getRange(`A${lastRow+1}:O${lastRow+1}`).setValue(sheet.getRange(`A${i+3}:O${i+3}`).getValue());
 
 
                //tb.deleteRowsAt(0, i-3);
            }
        }
    }
}

Solution

  • Modify the script logic as follows:

    function main(workbook: ExcelScript.Workbook) {
        let tbPO = workbook.getTable("PO");
        let sheet = workbook.getWorksheet("PO_Table"); // modify as needed
        let sheet2 = workbook.getWorksheet("PO Archive");
        // Set the range to the column of data to modify
        if (tbPO) {
            const tabFilter = tbPO.getAutoFilter();
            tabFilter.clearCriteria();
            const dataRange = tbPO.getRangeBetweenHeaderAndTotal()
            tabFilter.apply(dataRange,
                0, {
                filterOn: ExcelScript.FilterOn.values,
                values: ["Cancelled", "Ordered", "Voided"]
            });
            const visRng = dataRange.getSpecialCells(ExcelScript.SpecialCellType.visible);
            if (visRng) {
                const desDataRange = sheet2.getUsedRange(true);
                const destCell = sheet2.getCell(desDataRange.getRowCount(), 0);
                destCell.copyFrom(visRng);
                // remove filtered rows from PO
                const visAreas = visRng.getAreas().reverse();
                let visRngRefs: string[] = visAreas.map(x => x.getAddress());
                // remove rows 
                visRngRefs.forEach(x => {
                    sheet.getRange(x).getEntireRow().delete(ExcelScript.DeleteShiftDirection.up);
                })
            }
            tbPO.getAutoFilter().clearCriteria();
        }
    }
    

    enter image description here