Hi i'm trying to write a custom function that takes a pair of cells, loops through all the worksheets in the spreadsheet to find an identical matching pair of cells, and returns another value from that same row.
Background; sheet 0 is a master sheet of all LOA and ID combinations (essentially location and serial #) which need to have there inspection dates filled in. The people who do these inspections update their personal worksheets with the LOA-ID combination + inspection data on google drive. Im trying to get the master sheet to update automatically whenever this data is added.
The sheets all follow the same format (LOA, ID in 1st & 2nd columns, inspection date in the 14th). This is a custom function im using which does what i intend, but works painfully slow. How do i make this run faster? It takes several seconds PER CELL; i need to run this over 10k+ cells.
function findMatch(LOA,GRID) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheets = ss.getSheets();
var returnDate = "not found"
for (var sheetNum = 1; sheetNum < sheets.length; sheetNum++){
var ws = ss.getSheets()[sheetNum]
for (var count = 1; count<ws.getLastRow(); count++){
if (ws.getRange(count,1,1,1).getValues()==LOA && ws.getRange(count,2,1,1).getValues()==GRID)
{
returnDate = ws.getRange(count,14,1,1).getValue()
break;
}
else
{
}
}
}
Logger.log(returnDate)
return returnDate