Delete a specific value from one column and Rename another Value in Excel using office script

Question

I need help on an ExcelScript where I need the Column J filter then

Delete the entire Row in column J with the value "Canceled" Renamed any value in column J with "In Progress" to "Assigned" Renamed any value in column J with "Pending" to Assigned Renamed any value in column J with "Closed" to Resolved

Can anyone assist me on this?

Thanks

I tried filtering then deleted based on Range

your textDelete range 40:90 on selectedSheet selectedSheet.getRange("40:90").delete(ExcelScript.DeleteShiftDirection.up);

your text Clear auto filter on selectedSheet selectedSheet.getAutoFilter().clearCriteria();

your textSet range J2 on selectedSheet selectedSheet.getRange("J2").setValue("Resolved");

your text Auto fill range selectedSheet.getRange("J2").autoFill("J2:J103", ExcelScript.AutoFillType.fillDefault);

Answer 1

Load data into an array, write output to sheet after replacing.

Microsoft documentation:

ExcelScript.Range interface getResizedRange()

ExcelScript.Range interface getUsedRange()

function main(workbook: ExcelScript.Workbook) {
    const dataSheet = workbook.getActiveWorksheet();
    const dataRange = dataSheet.getUsedRange(true);
    // load data
    const dataVals = dataRange.getTexts();
    let outVals: [] = [];
    const colIndex = 9; // Col J
    let delRowsCnt = 0; // rows count
    // loop through rows
    dataVals.forEach(row =>{
        switch (row[colIndex]){
            // replace the values
            case "In Progress":
                row[colIndex] = "Assigned";
                break;
            case "Pending":
                row[colIndex] = "Assigned";
                break;
            case "Closed":
                row[colIndex] = "Resolved";
                break;              
            case "Canceled":
                row[colIndex] = "$DEL$";
                break;
        }
        if (row[colIndex] === "$DEL$") {
            delRowsCnt--; // remove "Canceled"
        } else {
            outVals.push(row); // store the output row
        } 
    })
    // console.log(outVals);
    // clear table
    dataRange.clear(ExcelScript.ClearApplyTo.contents);
    // write output to sheet
    dataRange.getResizedRange(delRowsCnt,0).setValues(outVals);
}

---

Update:

Question: I used the above code and it filtered for column J but for some reason my formulas in column E: H changed any reason that could have happened

Answer: setValues overwrites formulas in the table.

The script has been updated as follows:

• First, remove the rows marked as Canceled.
• Apply the replacement rules only to Column J to ensure formulas in the rest of the table remain intact.

function main(workbook: ExcelScript.Workbook) {
    const dataSheet = workbook.getActiveWorksheet();
    const dataRange = dataSheet.getUsedRange(true);
    const delTag = "Canceled"
    const colIndex = 9; // Col J
    // remove Cancelled rows
    dataSheet.getAutoFilter().apply(dataSheet.getCell(0, colIndex));
    dataSheet.getAutoFilter().apply(dataSheet.getAutoFilter().getRange(),
        colIndex, {
        filterOn: ExcelScript.FilterOn.values, values: [delTag]
    });
    const visRng = dataRange.getOffsetRange(1, 0).getResizedRange(-1, 0).getSpecialCells(ExcelScript.SpecialCellType.visible);
    if (visRng) {
        const visAreas = visRng.getAreas().reverse();
        let visRngRefs: string[] = [];
        visAreas.forEach(x => {
            visRngRefs.push(x.getAddress());
        })
        visRngRefs.forEach(x => {
            dataSheet.getRange(x).getEntireRow().delete(ExcelScript.DeleteShiftDirection.up);
        })
    }
    dataSheet.getAutoFilter().clearCriteria();
    // load data of Col J
    const dataVals = dataRange.getColumn(colIndex).getTexts();
    // loop through rows
    dataVals.forEach(row => {
        switch (row[0]) {
            // replace the values
            case "In Progress":
                row[0] = "Assigned";
                break;
            case "Pending":
                row[0] = "Assigned";
                break;
            case "Closed":
                row[0] = "Resolved";
                break;
        }
    })
    // write output to sheet
    dataRange.getColumn(colIndex).setValues(dataVals);
}