Google Sheets custom function displays "Loading..." forever in mobile app

Question

I have written a custom function for Google Sheets in Apps Script. The goal is to have a sheet which automatically calculates who owes how much money to whom (e.g. to split a bill).

My sheet looks like this:

The first bill (Restaurant) is to be split among all 5 and the second bill is to be split among all 5 except Peter, because there is no 0 in B3.

The input for my Apps Script function will be cells B1 to F3 (thus, values AND names). The function works fine - it calculates the correct results. I open that spreadsheet via browser (sheets.google.com) AND via my phone app (Google Sheets). However, on my phone it often happens that the result cell (with the formula =calc_debt(B1:F3)) only displays "Loading ...". What's the problem?

For the sake of completeness, here is custom function's code:

function calc_debt(input) {
  var credit = [0, 0, 0, 0, 0]; // credit[0] = Peter, credit[1] = Mark ...
  for (var i = 1; i < input.length; i++) { // starting at i = 1 to skip the first row, which is the names!
    // first: calculate how much everybody has to pay
    var sum = 0;
    var people = 0;
    for (var j = 0; j <= 4; j++) {
      if (input[i][j] !== "") {
        sum += input[i][j];
        people += 1;
      }
    }
    var avg_payment = sum / people;
    // second: calculate who has payed too much or too little
    for (var j = 0; j <= 4; j++) {
      if (input[i][j] !== "") {
        credit[j] += input[i][j] - avg_payment;
      }
    }
  }

  // this function is needed later
  function test_zero (value) {
    return value < 0.00001;
  };

  var res = ""; // this variable will contain the result string, something like "Peter to Mark: 13,8 | Katy to ..."

  while (!credit.every(test_zero)) {
    var lowest = credit.indexOf(Math.min.apply(null, credit)); // find the person with the lowest credit balance (will be minus!)
    var highest = credit.indexOf(Math.max.apply(null, credit)); // find the person with the highest credit balance (will be plus!)
    var exchange = Math.min(Math.abs(credit[lowest]), Math.abs(credit[highest])); // find out by how much we can equalize these two against each other
    credit[lowest] += exchange;
    credit[highest] -= exchange;
    res += input[0][lowest] + " to " + input[0][highest] + ": " + exchange.toFixed(2) + "  |  "; // input[0] = the row with the names.
  }
  return res;
}

Answer 1

I'm having a similar issue in the android app that loading a custom formula sometimes just shows 'Loading...', while in the web it always works fine. I've found a workaround to load the formulas in the android app:

Menu - > Export - > Save as - > PDF.

This will take a moment and behind the modal loading indicator you will see that the formulars eventually resolve. You can wait for the export to finish or cancel it as soon as you see your formular was resolved.

Also making the document available offline via the menu toggle could resolve the formulars.

Another thing you could do is using caching in your script. So whenever you use the web version to render more complex formulars the results are being stored and immediately loaded for the mobile app. Unfortunately, the Google cache is limited in time and does invalidate after a few hours. See here for more information: https://developers.google.com/apps-script/reference/cache/

This two things work quite well. However, I'm searching for a better solution. Let me know if you find one.