Sheets Contained App Script Custom Function - results keep disappearing after several hours

Question

I hope you can assist me here.

disclaimer: I'm new to Google Sheets and JavaScript, but have many years of programming experience.

I've got a pretty simple Custom Function in a sheet (Contained) that works absolutely fine if the Sheet is open in the browser. It basically takes in 5 arrays from the sheet and outputs 5 arrays from the cell from which it was called.

The sheet provides data to several other sheets that calculate from that data and then in turn share that to a further 'public' sheet that is then shared to a WordPress site.

ThisData -> CalculationSheet -> PublicViewSheet -> WordPress site

The problem kicks in after an hour or two after I close the Google Sheet in my browser, and I notice in the WordPress site that the data that was nicely displaying the leaderboard now outputs no athletes or their scores.

If I then:

1. merely open ThisData sheet and do nothing else, the calculated data is there and then all the athletes scores appears in the WordPress site 5 mins later due to caching.
2. instead open CalculationSheet or PublicViewSheet the 'QUERY( IMPORTRANGE(' that retrieves the data from ThisData retrieves no data (merely just the static headers).

I thought it might be the Google Sheets Custom Function caching problem and I've tried the trick of setting a cell to a Date/Time (see 'updateLastEditDate' in code below) and then referencing that cell through the score calculation (CalculateScores) as a dummy variable, and then setting up a trigger every hour to change the date which forces a recalculation... but I'm still getting the same disappearing results issues.

I've got a logger running to take a look at the output every time the CustomFunction runs, and when reviewing the logs, I can see that the output data is correct, but it seems that it's not getting written to the sheet. Or it might be getting written, but it's not showing further down the line ???

I guess I could rather change the CustomFunction to a function that is triggered onEdit as well as once a day (via triggers) and directly read the data from the sheet and then write it back... but a CustomFunction seems a neater solution, and allows me to move data ranges, add columns, etc without having to edit and hardwire the CalculateScores function.

I'm just scratching my head on why it's causing the above behavior... any ideas would be most welcome.

See code below: (for all it's worth... as it produces the expected output)

/** @OnlyCurrentDoc */

/**
 * Calculates Precision Rifle scores..
 *
 * @param {lMatchNames}.
 * @return {array} a full array of calculated scores.
 * @customfunction
 */

function CalculateScores(lMatchNames, lAthleteIDs, lAthleteDivisions, lAthleteScores, lTieBreakScores, lTieBreakTimes, lDate ){

  if(lMatchNames==undefined || lAthleteIDs==undefined || lAthleteDivisions==undefined || lAthleteScores==undefined || lTieBreakScores==undefined || lTieBreakTimes==undefined){
    console.log("Setting null parameters");
    var lMatchNames = [[]];
    var lAthleteIDs = [[]];
    var lAthleteDivisions = [[]];
    var lAthleteScores = [[]];
    var lTieBreakScores = [[]];
    var lTieBreakTimes = [[]];
  }else{
  }

console.log(lNumMatches);

  var lNumMatches = lMatchNames.length;

  var lMatchData = [];
  for (var i = 1; i < lNumMatches; i++) {
    //Find Match Object
    var lMatchName = lMatchNames[i][0];
    var lAthleteID = lAthleteIDs[i][0];
    var lAthleteDivision = lAthleteDivisions[i][0];
    var lAthleteScore = lAthleteScores[i][0];
    var lMatchObject = lMatchData.find(item => item.name == lMatchName);
    if (lMatchObject === undefined) {
      // Setup new match object in Array
      lMatchObject = {
        name:lMatchName,
        combinedScores:[],
        tieBreakScores:[],
        };
      lMatchData.push(lMatchObject);
    }
    // Add info to Match Object
        if(lAthleteID!="0"){
          addAndSort(lMatchObject.combinedScores, lAthleteScore);
          var lTieBreakScore = (lAthleteScore * 100000000) + (lTieBreakScores[i][0] * 100000) - lTieBreakTimes[i][0]
          addAndSort(lMatchObject.tieBreakScores, lTieBreakScore);
          let lKeys = Object.keys(lMatchObject);
          if(lKeys.indexOf(lAthleteDivision)==-1){
            lMatchObject[lAthleteDivision]=[];
            var lAthleteDivisionTieBreak = lAthleteDivision + "TieBreak";
            lMatchObject[lAthleteDivisionTieBreak]=[];
          }
          addAndSort(lMatchObject[lAthleteDivision], lAthleteScore);
          addAndSort(lMatchObject[lAthleteDivisionTieBreak], lTieBreakScore);
        }
  }

//Loop back through the data and calculate outputs
var lOutputArray = [];
lOutputArray.push(["Overall%", "OverallRank", "Division%", "DivisionRank", "RankTotal"]);
for (var i = 1; i < lNumMatches; i++) {
  var lMatchName = lMatchNames[i][0];
  if (lMatchName != ""){
    var lAthleteID = lAthleteIDs[i][0];
    var lAthleteDivision = lAthleteDivisions[i][0];
    var lAthleteDivisionTieBreak = lAthleteDivision + "TieBreak";
    var lAthleteScore = lAthleteScores[i][0];
    var lTieBreakScore = (lAthleteScore * 100000000) + (lTieBreakScores[i][0] * 100000) - lTieBreakTimes[i][0]
    if(lAthleteID=="0"){
      var lOverallPercentage = "";
      var lOverallRank = "";
      var lDivisionPercentage = "";
      var lDivisionRank = "";
      var lTieBreakScore = "";
    }else{
      var lMatchObject = lMatchData.find(item => item.name == lMatchName);
      var lOverallPercentage = lAthleteScore / lMatchObject.combinedScores[lMatchObject.combinedScores.length-1];
      lOverallPercentage = Math.round(lOverallPercentage * 100000) / 100000;
      var lOverallRank = lMatchObject.tieBreakScores.length - lMatchObject.tieBreakScores.lastIndexOf(lTieBreakScore);
      var lDivisionPercentage = lAthleteScore / lMatchObject[lAthleteDivision][lMatchObject[lAthleteDivision].length-1];
      lDivisionPercentage = Math.round(lDivisionPercentage * 100000) / 100000;
      var lDivisionRank = lMatchObject[lAthleteDivisionTieBreak].length - lMatchObject[lAthleteDivisionTieBreak].lastIndexOf(lTieBreakScore);
    }
    lOutputArray.push([lOverallPercentage, lOverallRank, lDivisionPercentage, lDivisionRank, lTieBreakScore]);
  }
}
console.log(lOutputArray);
return lOutputArray;
SpreadsheetApp.flush();

}

function addAndSort(arr, val) {
    arr.push(val);
    i = arr.length - 1;
    item = arr[i];
    while (i > 0 && item < arr[i-1]) {
        arr[i] = arr[i-1];
        i -= 1;
    }
    arr[i] = item;
    return arr;
}

function onEdit() {
  updateLastEditDate();
};

function updateLastEditDate() {
  SpreadsheetApp.getActiveSpreadsheet().getRange('BC1').setValue(new Date().toTimeString());
};