Custom functions in new Google Sheets occasionally show persistent 'Loading...' message

Other Questions Google Sheets
1

I’ve been working with custom functions in the latest version of Google Sheets. Sometimes these functions get stuck showing a ‘Loading…’ message in a red box around the cell. It’s really frustrating.

I know Google says to try reloading the page or renaming the function. But that doesn’t always work. Has anyone found a reliable way to fix this?

I’m especially interested in solutions for more complex functions. But even simple ones can have this problem sometimes.

Here’s a basic example of a custom function that might get stuck:

function MY_CUSTOM_SUM(a, b) {
  Utilities.sleep(2000);  // Simulate some processing
  return a + b;
}

Any tips or tricks would be really helpful. It’s making it hard to use custom functions effectively in my spreadsheets.

2

I’ve dealt with this frustrating issue too. One approach that’s helped me is implementing a retry mechanism in my custom functions. Here’s an example:

function MY_CUSTOM_SUM(a, b, maxRetries = 3) {
  for (let i = 0; i < maxRetries; i++) {
    try {
      Utilities.sleep(2000);
      return a + b;
    } catch (e) {
      if (i === maxRetries - 1) throw e;
      Utilities.sleep(1000 * Math.pow(2, i));
    }
  }
}

This function will attempt to execute up to 3 times, with exponential backoff between retries. It’s not perfect, but it’s reduced the frequency of ‘Loading…’ messages for me.

Another tip: try to avoid circular references in your custom functions. I’ve found that these can sometimes cause the persistent loading issue. Always double-check your function dependencies to ensure they’re not calling each other in a loop.

3

I’ve encountered this issue too, and it can be really frustrating. One trick that’s worked for me is to add a timestamp parameter to the function. This forces Google Sheets to recalculate the function each time:

function MY_CUSTOM_SUM(a, b) {
  var timestamp = new Date().getTime();
  Utilities.sleep(2000);
  return a + b;
}

Then in your sheet, call it like: =MY_CUSTOM_SUM(A1, B1, NOW())

This isn’t foolproof, but it’s helped reduce the ‘Loading…’ problem for me. Also, try to minimize API calls and heavy computations in your functions. Sometimes, splitting complex functions into smaller, more manageable pieces can help mitigate the issue.

4

yo, i feel ur pain. those loading msgs are a real bummer. one thing that’s worked for me is clearing the browser cache and cookies. sometimes google sheets gets stuck and needs a fresh start. also, try breaking ur function into smaller chunks. less complex = less chance of hanging. good luck!