Looking for a more efficient script/logic for lookup (loop) OfficeScript

Question

I managed to make a relatively logical and functional script that takes all empty cells in a column, and then one by one compares a list (separate table) for matching words. The problem is that is so unbelievably inefficient/slow and memory intesive, in practice it's not useful.

function main(workbook: ExcelScript.Workbook) {
  let transactionsTable = workbook.getTable("table_transactions");
  let emptyCatCells = transactionsTable.getColumnByName("category").getRange().getSpecialCells(ExcelScript.SpecialCellType.blanks)
  let lookupWords = workbook.getTable("table_autoCatReference").getColumnByName("lookupWord").getRange().getValues()

  if (emptyCatCells) {
    // Loop through areas
    for (let i = 0; i < emptyCatCells.getAreas().length; i++) {
      let areaBlankCategory = emptyCatCells.getAreas()[i];
      // Loop through cells
      for (let j = 0; j < areaBlankCategory.getCellCount(); j++) {
        let descEmptyCat = areaBlankCategory.getCell(j, -1)

        for (let lookupWord of lookupWords) {

          if (descEmptyCat.getText().includes(lookupWord)){
 
            areaBlankCategory.getCell(j, 0).setValue("FOUND")
          } else {

          }
        }

        
      }
    }
  } else {
    console.log("No blank cells")
  }

}

So I have a table_transactions with columns description and category. Also I have a table_autoCatReference with lookupWord and matchedCategory

For every row that has an empty category, I want to lookup lookupWords for a match in the cell for the description of the corresponding empty category. If a match is found, then place the matchedCategory (did not get to implement this yet, for now its a static string) for the lookupWord in the empty cell of category (in the first table)

I sort of got somewhere with my script but in sure it's very questionable. I would be very interested in learning about superior ways of thinking and implementing this idea. Thank you.

Answer 1

There are some changes to improve the efficency

• Load category and description into array
• Load lookupWord into Dictionary
• Check description is in dictionary with desValues[i][0] in dictCate
• Update category col all at once

function main(workbook: ExcelScript.Workbook) {
  let transactionsTable = workbook.getTable("table_transactions");
  let desColumn = transactionsTable.getColumnByName("description").getRangeBetweenHeaderAndTotal();
  let catColumn = transactionsTable.getColumnByName("category").getRangeBetweenHeaderAndTotal();
  let emptyCatCells = catColumn.getSpecialCells(ExcelScript.SpecialCellType.blanks)
  let catValues = catColumn.getValues();
  let desValues = desColumn.getValues();
  let lookupWords = workbook.getTable("table_autoCatReference").getColumnByName("lookupWord").getRangeBetweenHeaderAndTotal().getValues();
  let dictCate: { [key: string]: string } = {}
  // Loop through lookup words and add to dictionary
  for (let i = 0; i < lookupWords.length; i++) {
    dictCate[lookupWords[i][0].toString()] = ""
  }
  if (emptyCatCells) {
    // Loop through all cells in category column
    for (let i = 0; i < catValues.length; i++) {
      // If category is blank
      if (catValues[i][0] === "") {
        // If description is in dictionary
        if (desValues[i][0].toString() in dictCate) {
          // Set category to "FOUND"
          catValues[i][0] = "FOUND"
        }
      }
    }
    // Update category column
    catColumn.setValues(catValues);
  } else {
    console.log("No blank cells")
  }
}