Introduction to searching for strings from a worksheet in Office scripts.
Operations
The following code will perform the search. (Red letters change accordingly)
let Search result variables = workbook.getWorksheet("Sheet name").findAll("Search word", { completeMatch: Full match (true) or partial match (false), matchCase: Case-sensitive (true) or case-insensitive (false)});The result is an array of the cell range of the search result.
If the search result does not exist, it is undefined.
This can cause an error and must be addressed.
if(Search result variables === undefined){
// undefined if no search hits
}
else{
// If you get a hit in your search
}
The result is an array of range objects (the same as the result of getRange) with a wide variety of operations.
The most common are as follows
result.getAreaCount(); // Number of hits
result.getAreas(); // Array of hit cells (range)
result.getAreas()[index]; // Hit cell(range)
result.getAreas()[index].getAddress(); // Address of hit cell (range)
result.getAreas()[index].getValue(); // Value of hit cell (range)
result.getAreas()[index].clear(); // Clear the hit cell (range)
result.getAreas()[index].setValue(value); // Update hit cells (range)For those who want to learn Office script effectively
The information on this site is now available in an easy-to-read e-book format.
Or Kindle Unlimited (unlimited reading).
You willl discover how to about basic operations.
By the end of this book, you will be equipped with the knowledge you need to use Excel Office Script to streamline your workflow.