Refresh data retrieved using custom function in Google Sheet

Question

I wrote my own Google Apps script, which will receive an id and receive information from a web service (price).

I use this script in a spreadsheet and it works just fine. My problem is that these prices are changing and my table is not updating.

How can I make it restart the script and update the cells (without manually traversing each cell)?

Answer 1

Well, it looks like my problem was that google behaves in a weird way - it does not restart the script, as long as the script parameters are similar, it uses the cached results of previous runs. Therefore, it does not reconnect to the API and doesn’t retrieve the price; it simply returns the previous result of the script that was cached.

See here for more details: https://code.google.com/p/google-apps-script-issues/issues/detail?id=888

and here: Script to summarize data not updated

My solution was to add another parameter to my script, which I don't even use. Now, when you call a function with a parameter different from previous calls, it will have to restart the script, because the result for these parameters will not be in the cache.

Therefore, whenever I call a function, for an additional parameter, I pass "$ A $ 1". I also created a refresh menu item, and when I ran it, it puts the current date and time in A1, so all calls in the script with $ A $ 1 as the second parameter will have to be recounted. Here is some code from my script:

 function onOpen() { var sheet = SpreadsheetApp.getActiveSpreadsheet(); var entries = [{ name : "Refresh", functionName : "refreshLastUpdate" }]; sheet.addMenu("Refresh", entries); }; function refreshLastUpdate() { SpreadsheetApp.getActiveSpreadsheet().getRange('A1').setValue(new Date().toTimeString()); } function getPrice(itemId, datetime) { var headers = { "method" : "get", "contentType" : "application/json", headers : {'Cache-Control' : 'max-age=0'} }; var jsonResponse = UrlFetchApp.fetch("http://someURL?item_id=" + itemId, headers); var jsonObj = eval( '(' + jsonResponse + ')' ); return jsonObj.Price; SpreadsheetApp.flush(); } 

And when I want to put the price of the element with id 5 in the cell, I use the following formula:

 =getPrice(5, $A$1) 

When I want to update prices, I just click on the menu item "Refresh" → "Refresh". Remember that you need to reload the table after changing the onOpen() script.

Answer 2

I know this is an old question. But this method does not require any user action other than making changes.

What I did was like tbkn23.

The function I want to overestimate has an additional unused parameter $ A $ 1. So the function call

 =myFunction(firstParam, $A$1) 

But in the code the function signature

 function myFunction(firstParam) 

Instead of the update function, I used the onEdit (e) function similar to this

 function onEdit(e) { SpreadsheetApp.getActiveSheet().getRange('A1').setValue(Math.random()); } 

This function is launched whenever any cell in the spreadsheet is edited. So, now you are editing a cell, a random number is placed in A1, this updates the parameter list, as suggested by tbkn23, as a result of which the user-defined function will be reevaluated.

Answer 3

It's very late, and I don’t know that it would be useful, but in fact there are settings that you can do automatically NOW()

Answer 4

If you are setting up a function inside a specific column, just order a table in that column.

Ordering forces you to update data that calls your custom function for all rows in this column at once.

Answer 5

Each time you deploy your web application, it uses the same version. That is why it is not updated.

After making changes, go to File-> Version Control and save the new version. A new version appears. Then, when deploying, select the version you want to run

Answer 6

It may be a super-old thread, but it may be useful for those who are trying to do this, as I was now.

Disabling the Lexi script as-is does not seem to work with current sheets anymore, but if I add a dummy variable to my function as a parameter (there is no need to actually use it inside the function), this will really cause Google sheets to refresh the page again.

So, a declaration like: function myFunction (firstParam, dummy), and then calling it will be as suggested. It worked for me.

In addition, if all your sheets that you edit appear to be in trouble for a random variable, it is easy to eliminate the restriction on one sheet:

 function onEdit(e) { e.source.getSheetByName('THESHEETNAME').getRange('J1').setValue(Math.random()); } 

Answer 7

If you wrote a user-defined function and used it in your spreadsheet as a formula, then every time you open a spreadsheet or any reference cell, it changes, the formula is recalculated.

If you just want to keep track of the table and change its values, consider adding a trigger with a timer that will update the cells. Read more about triggers here.