-Custom function return does not update

Question

I have been making a script that finds a number (rank) associated with a nickname. I have created a custom function for this. Basically the formula input finds that input (a nickname) in an array of nicknames, it translates the array order into row number by adding 2(+1 because array starts at 0 and +1 because of the index of my column). After doing that it goes to the column where the rank number is located and that is what it returns.

The script works well but when I update the rank number the formula doesn't always return the new value (but if I run the formula again it does) so I suppose that the problem is that it either takes too long to execute it or my spreadsheet is not really recalculating every minute as I have set the settings.

What is the problem and how can I fix it? Thanks!

This is the code of the custom function:

/**
* Finds the rank of the inputed user
*
* @customfunction
*/
function FINDRECRUITRANK(Recruit_Name) {
  var app = SpreadsheetApp;
  var ss = app.getActiveSpreadsheet();
  var display = ss.getSheetByName("DisplaySheet");
  var lastRow = display.getRange("B133").getValue(); //B133 has a counter of total rows used
  var allnicknamesbeta = display.getRange(2, 3, lastRow-1).getValues();
  var allnicknames = allnicknamesbeta.map(function(r){ return r[0]; });
  
  
  var index =  allnicknames.indexOf(Recruit_Name) + 2; // +1 because array starts from 0 and +1 because of the first row index
  
 
  var recruitRank = display.getRange(index, 4).getValue();
  
  return recruitRank;
}

Update:

The first column is a list of names that have a number asigned to it(ranks). The 5th clumn has the recruit_name. What the function does is looks for th recruit_name in the first column, in this case it would be on (2,1) and then it copies the number assigned to it (rank, in this case on cell(2,2)). Thats what the function will return->2. The problem comes when I change that 2 to a 3(or any other number !=2), now cell (2,2) would have a 3 but the function input which is cell (1,5) has not changed that's why the return doesn't get updated.

The return of the formula is not in the picture, but it could be for example in (1,6).

Answer 1

If you want to have a function that updates every time there is a change in the sheet instantly, what you should be looking for is for an onEdit() simple trigger.

In the following implementation, I am setting the value of the Recruit_Name in a allocated cell for that and returning the value of the Apps Script function in a different cell allocated also for that purpose. In this way, all the values will get updated automatically.

/**
* Finds the rank of the inputed user
*
* @customfunction
*/

function onEdit() {
  var app = SpreadsheetApp;
  var ss = app.getActiveSpreadsheet();
  var display = ss.getSheetByName("DisplaySheet");
  
  // Get your parameter you were getting before in the sheet in a specific cell of it
  var Recruit_Name = display.getRange("F1").getValue();
  var lastRow = display.getRange("B133").getValue(); //B133 has a counter of total rows used
  var allnicknamesbeta = display.getRange(2, 3, lastRow-1,1).getValues();
  var allnicknames = allnicknamesbeta.map(function(r){ return r[0]; });
  
  
  var index =  allnicknames.indexOf(Recruit_Name) + 2; // +1 because array starts from 0 and +1 because of the first row index
  
 
  var recruitRank = display.getRange(index, 4).getValue();
  
  // Show the result of all the operations in a specific cell allocated for this function
  display.getRange('F2').setValue(recruitRank);
}

I hope this has helped you :D