Google Sheets with Google App Script: How to write a 'status' message to the cell before returning the final result?

I have a function that can take a while to return the output. Is there a way to have it print a message in the cell, before overwriting the message with the output a short time later? The function can take 30 seconds to run and it may be used in 20-30 cells, hence it would be nice to see which cell is still calculating and which is done.

function do_calc() {
  print("Now doing the calculations...")
    
  // ... (bunch of code here that does the calculations)
    
  return output;
}

I tried to use setvalue() but it says I don't have permission to do that in a custom function:

Here's my code:

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var active_range = sheet.getActiveRange();
  sheet.getRange(active_range.getRowIndex(), 10).setValue('Running...');
  Utilities.sleep(10000);
  return 'Finished';
}

Issues:

Like I said in the comment you can't return twice because the first return statement will cancel out the code that comes after that.

• Also set methods (like setValue) are not allowed in custom function as clearly stated in the official documentation.

Solution:

The solution would be to incorporate the built in google sheets formula IFERROR.

=iferror(myFunction(),"Running...")

where myFunction is:

function myFunction() {
  try{
    // some code that delays
    Utilities.sleep(10000);
  }
  catch(e){
    return e.message;
  } 
  return 'Finished';
}

I added a try...catch to make sure you return the error messages that are related to the script. Otherwise, iferror will hide them.

Be careful!

A custom function call must return within 30 seconds. If this time is surpassed then the custom function will return the error:

Exceeded maximum execution time

which will not be shown because you have used iferror which will cover the error.