Office Script that replaces blank cells with "null" text, ignoring merged cells

Question

I am trying to write an Office Script that replaces blank cells with "null" text that I can run in Excel Online. The script I have right now is replacing merged cells with "null". I would like to write it so that if a cell is blank and not merged, then "null".

function main(workbook: ExcelScript.Workbook) {

  let sheet = workbook.getWorksheet("Sheet1");
  
  let range = sheet.getUsedRange();
  
  range.getValues().forEach((row, rowIndex) => {
    row.forEach((cell, colIndex) => {
      if(cell === "") {
        range.getCell(rowIndex, colIndex).setValue("null");
      }
    });
  });
}

I have tried using getMergedRanges() without success so far.

The end goal is to prepare the file for a PowerQuery. Because PowerQuery doesn't handle merged cells well, I need to identify all of the non-merged cells first.

score 0 · Answer 1 · answered Apr 25 '23 at 21:48

This is a function I use to get details of merged cells in a worksheet.

Once you have that, you can add a check during your loop to see if the cell is part of a merged area.

async function Get_Merged_Areas_Arr_Of_Objs(context, rng) {
    //var mergedAreas = ws.getUsedRange(true).getMergedAreasOrNullObject();
    var mergedAreas = rng.getMergedAreasOrNullObject();
    mergedAreas.load(["areas"]);
    await context.sync()

    var Merged_Areas_Arr_Of_Objs = []
    if (mergedAreas.isNull != true) {
        var arrlen = mergedAreas.areas.items.length
        for (var ai = 0; ai < arrlen; ai += 1) {
            var obj = mergedAreas.areas.items[ai]
            var rng_obj = {}
            rng_obj['worksheet'] = obj['address'].split("!")[0]
            rng_obj['rowIndex'] = obj['rowIndex']
            rng_obj['columnIndex'] = obj['columnIndex']
            rng_obj['rowCount'] = obj['rowCount']
            rng_obj['columnCount'] = obj['columnCount']
            rng_obj['end_rowIndex'] = obj['rowIndex'] + obj['rowCount'] - 1
            rng_obj['value'] = obj['values'][0].toString()
            Merged_Areas_Arr_Of_Objs.push(rng_obj)
        }
    }
    return Merged_Areas_Arr_Of_Objs
}

This is probably due to my inexperience with js, but I'm having trouble applying this function to my script. — Austin, Apr 26 '23 at 14:52
The loop will be a bit complicated, you'll have to loop through the `Merged_Areas_Arr_Of_Objs` likely and check if the `rowIndex` is inbetween/part of a `Merged_Areas_Arr_Of_Objs` objs `rowIndex` and `end_rowIndex` — FreeSoftwareServers, Apr 26 '23 at 22:16
You could likely skip some iterations/improve performance by increaseing the loop index by the `rowCount` once a `mergedArea` has been found. But that would require using `++` format of a loop, not `forEach`. — FreeSoftwareServers, Apr 26 '23 at 22:17

Office Script that replaces blank cells with "null" text, ignoring merged cells

1 Answers1