Need Help With Google Sheets and API Refresh — Guild Wars 2 Forums

Need Help With Google Sheets and API Refresh

Hi,

Okay, so I'm totally pulling my hair out over this. Way back in August/September, I created a lovely little Google Spreadsheet that was a simple sheet that called the API to get an item name, an item sell price, and an item buy price. I used various basic math formulas from there, and all was well. To get Google to refresh the cells, all I had to do was close down or refresh the page and everything would update.

Fast forward to now after a long break from the game, and the sheet no longer updates on its own. I have to delete or cut the info out of the cell, and then put it back into the cell in order to get the latest information. I have 80 + cells, I cannot do this every day, let alone every hour or however long it is that I last used it and need to use it again. Google research has been completely dry, the only things I can find are features that only affect Google's own integrated calls but not any custom ones. Can anybody offer a script solution or another way to get Google Sheets to simply refresh the cells?

Comments

  • I'm sure Google changed how it works a while ago. The results of custom formulas are now persistently cached. I'm sure it didn't use to persist between sessions some time ago. But it works as follows: as long as the parameters don't change, the result won't be updated. Unfortunately, I don't have a solution on hand.

  • Dash.3179Dash.3179 Member ✭✭
    edited June 11, 2018

    Sorry for the delayed response on a necro thread, but I'll post this just incase you never found your answer (or if anyone else comes looking).

    The issue you're dealing with is called memoisation, and occurs because of how google sheets caches data like Archomeda said... here's how I get around it:
    1. create a cell with a 0 in it and use the 'named ranges' feature to name it "dummyValueToForceUpdate"
    2. use the script editor to create a script:
    function incrementDummyValueToForceUpdate() {
    var cell = SpreadsheetApp.getActive().getRange("dummyValueToForceUpdate");
    cell.setValue(cell.getValue() + 1);
    }
    3. set a timer for that function to determine your refresh rate (note gw2 api has a 5min cache, so I recommend putting this at 10mins or greater)
    4. in spreadsheet settings make sure you have "recalculation on change" enabled
    5. For ImportJSON (assuming you're using that for API import), go back to script editor and change:
    "function ImportJSON(url, query) {"
    to
    function ImportJSON(url, query,dummyValue) {"
    6. and finally in the data import cell change:
    =ImportJSON("url","parameters","settings")
    to
    =ImportJSON("url","parameters","settings",dummyValueToForceUpdate)
    if you aren't using the optional parameters, still keep in the extra commas so that ImportJSON doesn't throw an error when trying to parse the dummy value.

    Note that this Dummy Cell can then be used to update any custom formula... all you need to do is include the dummy cell in the formula without using it in the result.

    This makes makes sheets factor in the dummy value when recalculating (which has changed due to the incrementation), making it not use the previously cached answer & kicking in a proper refresh.
    For me dummyValueToForceUpdate = 31066. I realise this is kinda a silly solution to a stupid problem, but it works.

©2010–2018 ArenaNet, LLC. All rights reserved. Guild Wars, Guild Wars 2, Heart of Thorns, Guild Wars 2: Path of Fire, ArenaNet, NCSOFT, the Interlocking NC Logo, and all associated logos and designs are trademarks or registered trademarks of NCSOFT Corporation. All other trademarks are the property of their respective owners.