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

1. loop through column?
2. mark cell with red

Thank you for help, Jacek

CodePudding user response：

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');
   }
}

CodePudding user response：

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.