0

I have a custom function which make an API call, which returns a different value everytime it is called. From the header menu, I would like refresh/rerun that API call to get a different results. My code is below

function customFunction() {
  var response = UrlFetchApp.fetch("http://numbersapi.com/random/math");
  return response.getContentText()
}

function onOpen(e) {
  SpreadsheetApp.getUi()
      .createAddonMenu()
      .addItem("Refresh all", 'refresh_all_gs')
      .addToUi();
}

function refresh_all_gs(){
  var cell = SpreadsheetApp.getActiveSheet().getRange('A10')
  var formula = cell.getFormula()
  cell.setFormula('=1')
  SpreadsheetApp.flush();
  cell.setFormula(formula)
}

The formula in cell "A1" is: =customFunction()

Cell "A10" is just a random cell that I choose to replace its value with itself, that cell is empty. I am trying to manually refresh cell A1. I follow this answer which says we can use SpreadsheetApp.flush().

However, when I click "Refresh all" from the top menu bar, the function refresh_all_gs is called but the cell A1 does not refresh. I wonder what I am doing wrong here. Thanks a lot

TDo
  • 606
  • 2
  • 9
  • 19
  • You need to add a parameter programmatically(The linked questions show different ways) or at the very least replace the function that's to be refreshed, i.e, change `A10` to `A1` – TheMaster Nov 27 '21 at 14:47

0 Answers0