Office Script that removes Row based on part of Text

Question

I am making an Office Script that should delete a row based on part of the text in a cell in the first column. The trigger word should be "Applied" but it is often more text after the first word, i.e. "Applied formula" or "Applied number".

The Script:

function main(workbook: ExcelScript.Workbook) {

  //getting the used range in the worksheet
  let usedRange = workbook.getActiveWorksheet().getUsedRange();

  //getting the row count of the used range
  let lastRow = usedRange.getRowCount();

  usedRange = workbook.getActiveWorksheet().getRangeByIndexes(0, 0, (usedRange.getRowIndex() + lastRow), 19)

  //getting the values of the range
  let values = usedRange.getValues();

  //getting the row count of the range
  let rowCount = usedRange.getRowCount();

  //for every row starting at the last row, checking if the cell in column 'A' contains part of text "Applied". If it is, then delete the entire row. 
  for (let i = rowCount - 1; i >= 0; i--) {
    if (values[i][0] == "Applied*") {
      usedRange.getCell(i, 0).getEntireRow().delete(ExcelScript.DeleteShiftDirection.up)
    }
  }

}

I can not get it to work, should there be something else instead of the "*" after Applied in the last part of the script?

Thank you in advance

Skin · Accepted Answer · 2022-03-22T11:42:19.943

I think you need to turn your values into strings and then use the search function.

Example, I have these values in cells A1:A5 ...

this string doesn't have it
this is test a string
this one has Test
another one without
nup, not this one

You'll need to adapt this but I created a script that looks like this ...

function main(workbook: ExcelScript.Workbook)
{
  let activeSheet = workbook.getActiveWorksheet();
  let range = activeSheet.getRange("A1:A5");
  let rangeValues = range.getValues();
  
  rangeValues.forEach(cellValue => {
    console.log(cellValue[0].toString().toLowerCase().search('test'));
  });
}

... and the output of to the console is this ...

So as to avoid case, I set the string to lower case before searching but it's up to you if you want to honour case.

You can also use this approach ...

cellValue[0].toString().search(/test/i)

... to ignore case.

Bottom line, use toString() and then the search() function and it will return the position of the substring within your main string.

Anything greater than -1 means the string exists.

If you want to test to see if the string starts with Applied then you'll need to check for all occurrences where the result of the search equals 0.

score 1 · Answer 2 · answered Mar 22 '22 at 14:46

One option you can do is cast the values as an array of strings. So you would update a line like this:

//getting the values of the range
let values = usedRange.getValues();

To a line like this

//getting the values of the range
let values = usedRange.getValues() as string[][];

This may be faster than calling toString() on every element in the array.

After you did that you'd just have to update your conditional If line from this:

if (values[i][0] == "Applied*") {

To something like this:

if (values[i][0].toLowerCase().includes("applied")) {

Office Script that removes Row based on part of Text

2 Answers2