Microsoft Office Scripts - Move cell value based on another cell's value

Question

I'm looking to loop through each row and based on a cell value I want to move another cell's value to new cell.

Example: Loop through each row if column "Method" has a value "D" move the cell value in column "Amount" (Column G) to "New Amount" (Column J).

What I have:

    let row = 1;
    let rowCount = table.getRowCount();
    while (row <= rowCount) {
        let text = table.getRange().getCell(row, columnToCheck).getText();
        if (text === "D") {
            selectedSheet.getRange(`J${row}`).setValue(selectedSheet.getRange(`G${row}`).getValue());
            rowCount--;
        } else {
            row++;
        }
    }

taller · Accepted Answer · 2024-12-23 18:42:32Z

0

I recommend using getColumn("Amount").getIndex() to dynamically retrieve the column index instead of hardcoding the column name as Column G. This approach ensures the script remains robust and adaptable, even if the table layout (column order) is modified by the user.

Reading or populating cells directly can be time-consuming. Loading data into an array is a more efficient approach for processing.

Microsoft documentation:

ExcelScript.Table interface getRangeBetweenHeaderAndTotal()

ExcelScript.TableColumn interface getIndex()

function main(workbook: ExcelScript.Workbook) {
    const table = workbook.getActiveWorksheet().getTables()[0];
    const methodId = table.getColumn("Method").getIndex();
    const amtId = table.getColumn("Amount").getIndex();
    const newamtId = table.getColumn("NewAmount").getIndex();
    const tableRng = table.getRangeBetweenHeaderAndTotal();
    const dataArr = tableRng.getTexts();
    dataArr.forEach(row => {
        if (row[methodId] === "D") {
            row[newamtId] = row[amtId]
            row[amtId] = ""
        }
    }
    );
    tableRng.setValues(dataArr);
}

edited Dec 23, 2024 at 18:42

answered Dec 23, 2024 at 17:46

taller

19.7k2 gold badges9 silver badges24 bronze badges

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

1 Comment

Morgan.Batt Dec 23, 2024 at 18:33

WORKED. Thank you so much! What's the easiest way to delete the "Amount" cell after it's copied to "New Amount" column?

Collectives™ on Stack Overflow

Microsoft Office Scripts - Move cell value based on another cell's value

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related