How to loop through cell in table column and check if Regex pattern has match?

Question

i am trying to write Office Script to loop through table column range and check if Regex PAttern has match. If yes just mark red color:

function main(workbook: ExcelScript.Workbook) {

    let worksheet = workbook.getWorksheet("Workstreams");
    let usedColumn = worksheet.getTables()[0].getColumn("Last Update Time");
    let regexPattern = "^(0[1-9]|1[0-2])\/(0[1-9]|1\d|2\d|3[01])\/(23) ([1-9]|0[1-9]|1[0-2]):[0-5][0-9] ([AaPp][Mm])$"

}

how to

loop through column?
mark cell with red

Thank you for help, Michal

EDIT 02.02.2023: Code to try to get value from cell:

function main(workbook: ExcelScript.Workbook) {
  let worksheet = workbook.getWorksheet("Workstreams");
  let usedColumn = worksheet.getTables()[0].getColumn("Last Update Time");
  let dataRange = usedColumn.getRangeBetweenHeaderAndTotal();
  //const values = dataRange.getValues();
  let regex = /^(0[1-9]|1[0-2])\/(0[1-9]|1\d|2\d|3[01])\/(23) ([1-9]|0[1-9]|1[0-2]):[0-5][0-9] ([AaPp][Mm])$/;

  for (let i = 0; i < dataRange.getValues().length; i++) {
    let cRow: number = i;
    let rowCell: ExcelScript.Range = dataRange.getCell(cRow, 0);
    let rowValue: string = rowCell.getValue() as string;
    console.log(rowValue);
  } 

}

and output is like: 44641.25 44713.21875 44587.1361111111 test

for these values in column: 3/21/22 6:00 AM 6/1/22 5:15 AM 1/26/22 3:16 AM test

so the issue is that JS is converting string to number somehow... Best, Michal

You can try changing `getValue()` to `.getText()`. That should fix the formatting issue. — Brian Gonzalez, Jan 02 '23 at 16:24

score 1 · Answer 1 · answered Dec 31 '22 at 01:03

I think your question is at the intersection of javascript and Office Scripts api. You can test if a value matches a regex using string's .match function, for example:

let x = "hello".match(/^he.*/);

Within x you can get some details (eg: what part of the input matched), but if you just care that you got A match, you can test the return for null

if ("hello".match(/^he$/) === null) { 
   ... no match found ... 
} else {
   ... match found ... 
}

You can get the values from your table's column by grabbing the range with data, then the values, like so:

const dataRange = usedColumn.getRangeBetweenHeaderAndTotal();
const values = dataRange.getValues();

values will be a 2D array of type number | string | boolean. This is a quirk of getRange functions returning 2D areas. Since your column is vertical, the data in it will look like [[row1Data], [row2Data], ... ]

Now you can run a forEach over the values and test the regex. Where the regex fails, you can apply your formatting:

values.forEach((rowValues, index) =>  {
   if ((rowValues[0] as string).match(yourRegex) === null) {
      // apply the formatting
      dataRange.getCell(index, 0).getFormat().getFill().setColor('#FF0000');
   }
}

But the only issue that column is formatted as Time and this read format as number: 44641.25 from Time cell: "3/21/22 6:00 AM" so only thing is to convert it and keep as displayed value. — Michał Mańkowski, Jan 02 '23 at 11:03
https://ibb.co/Z8H2fN6 like here this is format which is not answering for Regex — Michał Mańkowski, Jan 02 '23 at 11:06

Brian Gonzalez · Answer 2 · 2023-01-15T02:20:49.033

You can try the code below:

    function main(workbook: ExcelScript.Workbook) {
      let worksheet = workbook.getWorksheet("Workstreams");
      let usedColumn = worksheet.getTables()[0].getColumn("Last Update Time");
      let rowCount = usedColumn.getRangeBetweenHeaderAndTotal().getRowCount();
      let colVals = usedColumn.getRangeBetweenHeaderAndTotal().getValues() as string[][];
      let regex = /^(0[1-9]|1[0-2])\/(0[1-9]|1\d|2\d|3[01])\/(23) ([1-9]|0[1-9]|1[0-2]):[0-5][0-9] ([AaPp][Mm])$/;
    
      for (let i = 0; i < rowCount; i++) {
          if (regex.test(colVals[i][0])) {
            usedColumn.getRangeBetweenHeaderAndTotal().getCell(i, 0).getFormat().getFill().setColor("red");
          }
      } 
    }

This code iterates through the values in the table column. It also created a regular expression object from the pattern. Using the RE object, it tests the values from the column it's iterating through. If there's a match, it sets the cell color for that value to red.

I updated topic where i explained that still have issue with code. — Michał Mańkowski, Jan 02 '23 at 11:21

How to loop through cell in table column and check if Regex pattern has match?

2 Answers2