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

Answer 1

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