search

joseballester / muFunds

µFunds lets you import your asset data from Morningstar and other sources to your Google Sheets spreadsheet by using a simple formula.
https://mufunds.com
81 stars 11 forks source link

Missing recalculation when using time-triggered Google Script Function #18

Open davidalvarezm opened 4 years ago

davidalvarezm commented 4 years ago

While following the muFund guide to save history of mutual funds, I had not got values for muFund formulas when the script was triggered automatically. I finally got it solved by adding to suggested code flush function at script initiation, which reclaculates all values within the Spreadsheet: SpreadsheetApp.flush(). I just recommend to include this in the guide: https://mufunds.com/history.html

davidalvarezm commented 4 years ago

Fail! Although it did work the first time I have been testing it few days without success. Now I am not able to automatically run the muFund function with the time-trigger. Any clue?

davidalvarezm commented 4 years ago

I have tried by playing with the google sheet permissions (edit anyone with link, public...), and activating the sheet where muFunds are applied before using flush() function, but nothing. Script still gives #NAME errors. The worst thing is that the error is not consistent. Sometimes it works fine. Any idea? @joseballester have you ever find this error?

franry commented 4 years ago

To your initial question. I have been playing around with muFunds for several weeks now. Trying to get the spreadsheet to update by running a script. It looks like this is not possible by design. I ended up with a workaround. Make a Macro that makes a copy - delete and re-insert of the ISIN's for the funds you want to update. Find the Macro in macros.gs. Do some changes so the macro is running in the sheet that it is supposed to (var sheet1 = "Yoursheetname";). And call it form a trigger.

I have also looked at this solution: https://github.com/sheets-market-data/refresh-tool

This guy has a script that looks thru all formulas in the sheet. Delete them and re-inserts them. It works, but I could not find a proper way to control the thing, so I gave up on him. I have been programming in several languages, but I am not a programmer. So it was a little to heavy for me JS is also new for me.

FYI I have also made a script that copies the NAVs to history, it can be shared if you want.