Forum Discussion
Find row in Cloud Excel table matching text
I have 2 identifiers in rows in Excel and a block of text. I want get the row of data in Excel that has the identifiers in the text. For example, if I have text that says, "I love peach cobbler and ...
Is there a way to do that in Typescript, not Excel formulas? I have multiple calls running at the same time. I don't want to populate temporary cells or tables because that will slow things down considerably.
Sorry, I'm not very familiar with the ExcelScript. As a minimum you may
function main(workbook: ExcelScript.Workbook) {
let selectedSheet = workbook.getActiveWorksheet();
let formula = "=REDUCE($C$1, SEQUENCE(ROWS(data)), LAMBDA(a,v, IF(SUM(--ISNUMBER(SEARCH( INDEX( data, v, {1,2} ), $A1))) = 2, a & INDEX(data, v, 3),a)))";
selectedSheet.getRange("D1").setFormulaLocal(formula);
}I took C1 as blank cell, don't know how to add empty string within text.
Not sure which temporary cells do you mean, if where the text is it could be wrapped by another lambda with text as parameter.
- To minimize reads and writes, which slow down the script the most, I read the table and traverse it in Typescript as a 2D array. I can read each row and calculate based on that. I traverse the rows until I find a match on the first criteria, then I filter on that result and search for the second criteria. If there was some way that I could match both criteria at the same time, that would be better.