Office Scripts Rows not Copy Pasting Correctly

Question

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

Does the table "PO Archive" have exactly same headers as "PO"? — taller
– taller, Commented Oct 31, 2024 at 18:05

taller · Accepted Answer · 2024-10-31 23:22:13Z

0

Modify the script logic as follows:

Filter the source table by the desired status (column A).
Copy the filtered table to the second sheet (it's more efficient than to copy rows individually).
Remove the filtered rows from the source table.

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();
    }
}

edited Oct 31, 2024 at 23:22

answered Oct 31, 2024 at 18:50

taller

19.7k2 gold badges9 silver badges24 bronze badges

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

2 Comments

Kat Brown Over a year ago

Thanks! I had to make a few minor adjustments to this code ( explicitly defining the variable types of the desDataRange and destCell and adding 1 to the row count in destCell) to make it work but it solved my problem!

taller Over a year ago

explicitly defining the variable types is good coding habit. adding 1 to the row count in destCell -- getCell() is zero-base index. Does adding 1 leave a blank row on the 2nd sheet?

Kat Brown · Accepted Answer · 2024-10-31 20:33:23Z

taller's answer was effective in solving my problem but I needed to make a few minor adjustments. Here is the final working version of the code for anyone interested.

function main(workbook: ExcelScript.Workbook) {
let tbPO = workbook.getTable("Portland");
let sheet = workbook.getWorksheet("Portland Customers");
let sheet2 = workbook.getWorksheet("Portland Archive");
if (tbPO) {
const tabFilter = tbPO.getAutoFilter();
tabFilter.clearCriteria();
const dataRange = tbPO.getRangeBetweenHeaderAndTotal()
tabFilter.apply(dataRange,
    0, {
    filterOn: ExcelScript.FilterOn.values,
        values: ["Cancelled", "Order Bombed/Job Jacket Sent", "Voided"]
}); const visRng = dataRange.getSpecialCells(ExcelScript.SpecialCellType.visible);
if (visRng) {
    const desDataRange: ExcelScript.Range = sheet2.getUsedRange(true);
    const destCell: ExcelScript.Range = sheet2.getCell(desDataRange.getRowCount()+1, 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(); }}

Collectives™ on Stack Overflow

Office Scripts Rows not Copy Pasting Correctly

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related