Excel Office Scripts - Replace "blank cells" with the text "null"-CodePudding

I'm trying to replace blank cells in column B (column name= Status) with the text "null". I have two different approaches that get me halfway there, however I can't figure out how to fully solution this. Any help would be greatly appreciated

Here's the excel table showing column B and a few cells in that column with blank cells

Here's two different scripts I created trying to solution this

Script 1= I can identify the blank cells, but cannot figure out how to set the values for the cells.

function main(workbook: ExcelScript.Workbook) {
let source = workbook.getWorksheet("Source");
let sourceTable = workbook.getTable("Source");
const statusVisibleRange = source.getUsedRange().getColumn(0);
const statusVisibleRangeShifted = statusVisibleRange.getOffsetRange(1,1);
const StatusFilter = "Status";
const StatusFilterValues = '';
let stringValue = "null";

let blankCells = 
statusVisibleRangeShifted.getUsedRange().getSpecialCells(ExcelScript.SpecialCellType.blanks);
console.log(blankCells.getAddress());
}

Script 2=Script works if there are blank cells in column B (column name = Status), however if there are not any blank cells...it's filtering to all values and updating the statuses of everything in this column to "null".

function main(workbook: ExcelScript.Workbook) {
let source = workbook.getWorksheet("Source");
let sourceTable = workbook.getTable("Source");
const statusVisibleRange = source.getUsedRange().getColumn(0);
const statusVisibleRangeShifted = statusVisibleRange.getOffsetRange(1,1);
const StatusFilter = "Status";
const StatusFilterValues = '';
let stringValue = "null";

const statusFilter = sourceTable.getColumnByName(StatusFilter);
statusFilter.getFilter().applyValuesFilter([StatusFilterValues]);

statusVisibleRangeShifted.getUsedRange().setValue(stringValue);

statusFilter.getFilter().clear();

CodePudding user response：

This worked for me ...

function main(workbook: ExcelScript.Workbook)
{
  let worksheet = workbook.getWorksheet("Source");
  let table = worksheet.getTable("Source");

  let statusColumn = table.getColumnByName("Status");
  let statusColumnRange = statusColumn.getRangeBetweenHeaderAndTotal();

  let emptyStatusCells = statusColumnRange.getSpecialCells(ExcelScript.SpecialCellType.blanks);

  if (emptyStatusCells != undefined) {
    let rangeAreas = emptyStatusCells.getAreas();

    rangeAreas.forEach(range => {
      let values = range.getValues();

      values.forEach(cellValue => {
        cellValue[0] = "null";
      })

      range.setValues(values);
    })
  }

  // Add additional logic here once the blank cells are dealt with.
}