Cannot refresh custom function in Google Sheet

[Tuan Do]

I have a custom function which make an API call, which returns a different value everytime it is called. From the header, 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)

}

````

However, when I click "Refresh all" from the top menu bar, the function `refresh_all_gs` is

called but the cell which has the custom function does not refresh. I wonder what I am doing

wrong here. Thanks a lot

[Tanaike]

Your "refresh_all_gs" use the cell "A10". So how about putting the custom function to the cell "A10"?

[Tuan Do]

No cell "A10" is just a random cell that I choose to replace its value with itself, that cell is empty. I am trying to refresh all the cells which have my customFunction (cell "A10" does not have that customFunction).

[Tanaike]

Thank you for replying. I have to apologize for my poor English skill. I thought that your script "refresh_all_gs" uses only the cell "A10". From your question, I couldn't notice that you wanted to refresh all cells including the custom functions. I deeply apologize for my poor English skill again.

When you want to refresh all custom function "customFunction" in the Spreadsheet, how about this method?

https://stackoverflow.com/a/65756852

[Tuan Do]

Hi, no worry at all, there is no need for apology :). Thanks for sharing that answer. I dont quite understand these 2 lines in your answer:

````

sheet.createTextFinder(`^\\${formula}`).matchFormulaText(true).useRegularExpression(true).replaceAllWith(tempFormula); 

 sheet.createTextFinder(`^\\${tempFormula}`).matchFormulaText(true).useRegularExpression(true).replaceAllWith(formula);

````

Do you mind explaining it a little bit more so that I can adapt it to my situation? Thanks

[Tanaike]

Thank you for replying. I apologize for the inconvenience. In those scripts, at first, the existing formula is replaced with other formula and then, the formula is replaced with the original one. For this, TextFinder is used.

[Tuan Do]

Thanks Tanaike. I don't think my situation is similar to your answer. What I am trying to do is to manually refresh all the cells which have custom function even though there is no change to those cells. In your answer the refresh only happens when there is a change to those cell. In the question in that post, the OP mentioned he used `SpreadsheetApp.flush()` to refresh the spreadsheet. I also use `SpreadsheetApp.flush()` in my code above but I don't know why it does not work

[Tanaike]

Thank you for replying. I deeply apologize that my comment was not useful for your situation.

[Jonathan Butler]

The only other way I can think of this working is to have the custom function code accept an argument(e) or anything of that nature. Then have the custom function refer to a random cell (b21) for example. Finally, write a uuid to that random cell. It should update the custom function because the data it is based on has changed.

On Sat, Nov 27, 2021 at 6:28 PM Tanaike <kanshi...@gmail.com> wrote:

Thank you for replying. I deeply apologize that my comment was not useful for your situation.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/82314c1b-20c5-4f6c-9658-4f2b4f1c204bn%40googlegroups.com.

[Laurie Nason]

Dear Tuan,

What you could do is to have in the cell that calls your custom function a reference to "Now()" and compare it to 0 - which will always be false and then if it's false you call your custom function. 

e.g. =IF(Now()=0,"",mycustomfunction())

Then on the sheet settings (currently on the file menu - though it changes that often... :-)), you can use the "Calculation" tab and set the recalculation to every minute or every hour - unfortunately, only those two choices are available.

The other way is the one that Jonathan describes - reference another cell in your cell formula, that you change using a trigger at a set interval (more scope than the above method) to change that cell, which then forces the re-calculate of the cells containing the custom function.

Hope this helps.

Laurie

On Sun, Nov 28, 2021 at 3:28 AM Tanaike <kanshi...@gmail.com> wrote:

Thank you for replying. I deeply apologize that my comment was not useful for your situation.

--