How to refresh a sheet's cell value in Google Apps Script [duplicate]

Question

I'm experimenting with Blockspring which provides a Google Sheets add-on which can, for example, run a function which returns data from a webservice e.g.

=BLOCKSPRING("get-stock-current-stats", "ticker", "MSFT")

I want to refresh a cell's data but don't see a 'refresh' call in the docs.

function onOpen() {
  createTimeDrivenTriggers()
}

function createTimeDrivenTriggers() {
  // Trigger every minute
  ScriptApp.newTrigger('myFunction')
      .timeBased()
      .everyMinutes(1)
      .create();

}

function myFunction() {
  Logger.log('I ran'); // can see this in the logs
  SpreadsheetApp.getActiveSheet().getRange('B4').getValue() //presumably returns a value to the script

}

Answer 1

The flush() method can be used. Also see answers provided by others.

Documentation

SpreadsheetApp.flush();

Quote from the documentation:

Applies all pending Spreadsheet changes

If there were no changes to the spreadsheet, but you want to force formulas to recalculate, you will need to make a change, and then use SpreadsheetApp.flush();

For example, you could get the value from cell A1, then set the same value to cell A1. So, there is no chance of losing data because you are getting and setting the same value. The change allows. SpreadsheetApp.flush(); to recalculate all the formulas.

The documentation has some information on optimization: Google Sheets Custom Function Optimization

Answer 2

Instead of using flush, you can tell the spreadsheet which cells the function is dependent on by passing them to the formula at the end of its expected parameters. Its ok to pass more parameters than the function will use. This way the spreadsheet will assume that the value returned by the function might be different if the inputs are different and the normal update process will take care of it.

If you want it to re-evaluate when any cell changes, just pass in a range that includes all the cells.

For Example:

=MyFunction("this", "that", A1:Z10000)

If you take the time to pass in exactly the cells that the function is logically dependent on, it will be more efficient by not re-evaluating when it does not need to.

=MyFunction("this", "that", A10, C5, G6:H9 )

If it's not really dependent on any cell but you want to evaluate the function manually, on demand, make a cell that increments its hidden value when the user clicks on it (see buttons) and then pass that cell address into the function.

=MyFunction("this", "that", A10 )
// make A10 change its value on a button click