Excel Office Script to Replace #N/A Values in a Column with Corresponding Values from Another Column

Question

I'm working on an Excel Script using Office Scripts in Excel and facing an issue with replacing #N/A values in one column with corresponding values from another column. I have a table named "Table1" with columns "MRGAC Date" and "Day of Original Date."

Here's what I have so far,

function main(workbook: ExcelScript.Workbook) {
    // Your code here
    let tableName = "Table1";
    
    let mrgacColumnName = "MRGAC Date";
    let table = workbook.getTable(tableName);
    let mrgacColumn = table.getColumnByName(mrgacColumnName);
    mrgacColumn.getFilter().applyValuesFilter(["#N/A"]);
    let filterVal=mrgacColumn.getRange().getValues();
    let srcRange = table.getColumn("Day of Original Date").getRangeBetweenHeaderAndTotal();
    let targetCell = table.getColumn("MRGAC Date").getRangeBetweenHeaderAndTotal().getRow(0);
    targetCell.copyFrom(srcRange, ExcelScript.RangeCopyType.all, false, false);
    mrgacColumn.getFilter().clear();


}

The script correctly filters rows where "MRGAC Date" is #N/A and copies values from "Day of Original Date" to "MRGAC Date." However, it currently replaces all values in "MRGAC Date" column, and I want it to replace only the #N/A values.

Any suggestions on how to modify the script to achieve this specific replacement only for #N/A values?

Answer 1

Excel cannot paste non-continuous ranges directly. It is recommended to load the data into an array, process it in memory, and then write the results back to the worksheet.

function main(workbook: ExcelScript.Workbook) {
    const tableName = "Table1";
    const mrgacColumnName = "MRGAC Date";
    const dayColumnName = "Day of Original Date";
    const searchKey = "#N/A";
    let table = workbook.getTable(tableName);
    let dayValues = table.getColumnByName(dayColumnName).getRangeBetweenHeaderAndTotal().getValues();
    let mrgacColumn = table.getColumnByName(mrgacColumnName).getRangeBetweenHeaderAndTotal() ;
    let mrgaValues = mrgacColumn.getValues();
    for (let i = 0; i < mrgaValues.length; i++){
        if (mrgaValues[i][0] === searchKey){
            mrgaValues[i][0] = dayValues[i][0]
        }
    }
    mrgacColumn.setValues(mrgaValues);
}