I am a non-programmer and quite new to Office Scripts and I would love some help.
I am currently trying to delete entire rows if the cell in the "Change Flag" column, which happens to be the second column on the Excel sheet, contains the word "Delete" or "Deleted".
I managed to delete rows if they contain the word "Delete" with the following script but could not make the script cells with "Deleted" too. I tried throwing brackets in there but it unfortunately did not work.
Can someone suggest a best practice to handle the deletion of rows based on multiple text matches (delete if x = y OR z)?
function main(workbook: ExcelScript.Workbook) {
// Get the used range on the current sheet.
const currentSheet = workbook.getActiveWorksheet();
let table = workbook.getTables()[0];
// Get the RangeAreas object for each cell with a formula.
const usedRange = currentSheet.getUsedRange();
//get the values of the range
let values = usedRange.getValues();
//get the row count of the range
let rowCount = usedRange.getRowCount();
//starting at the last row, check if the cell in column '1' equals to 'Delete'. If it is, then delete the entire row.
for (let i = rowCount - 1; i >= 0; i--) {
if (values[i][1] == "Delete") {
usedRange.getCell(i, 1).getEntireRow().delete(ExcelScript.DeleteShiftDirection.up)
}
}
}
Your if statement should look like the below:
if (values[i][1] == "Delete" || values[i][1] == "Deleted")
If you are looking for more general tutorials, here is a page I found that explains if statements a little more: https://www.w3schools.com/jsref/jsref_if.asp
You can use the includes() method of the string object to do this. To use that method, you'd have to cast the value to a string using the toString() method like so:
if (values[i][1].toString().includes("Delete"))
Using includes, it will flag for both Delete and Deleted.
The words Delete and Deleted may be spelled in a variety of different ways (e.g. delete and deleted, DELETE and DELETED, etc.). Those different spellings will not be flagged by includes(). If want those different spellings to be evaluated in the same way, you can use a method like toLowercase() to do that. After calling that method, you'd just provide the includes() method with a lowercase version of the spelling like so:
if (values[i][1].toString().toLowerCase().includes("delete"))
This approach will also flag for any other text that includes delete. So if you have text in a cell like "this row should not be deleted" this code will flag that text as well.