IMPORTHTML randomly breaks when triggered by script
IMPORTDATA is breaking in a very annoying way in my google sheet. I am tracking prices from an API, and logging them with dates for graphing trends. To force IMPORTDATA to refresh at timed intervals and grab the latest prices, I am using a script. As you may know, IMPORTDATA won't actually refresh at any frequent interval if google thinks you're calling the same API too often, so to get it to do that, there's a trick where I use math.random to insert a random number into the URL, which the API ignores, but sheets sees as a new URL. Every time the script run,s it changes this number which makes IMPORTHTML check the API and get a new price. I have it running every 15 minutes. It has been working flawlessly for maybe 5-7 years, aside from the occasional slowdown due to extensive datapoints. When this happens I simply screenshot the graph for posterity, delete the data, and let it start over. This year, I will simply get the error "Could not fetch URL" where IMPORTHTML should be running. Entering the URL with the random number, however, will work in a new spreadsheet. I also noticed that if I turn off the script, then after hours or days, of its own accord, IMPORTHTML will import prices again out of nowhere. Of course it does this at its own interval, and the script will not be there to log them to the record, so this doesn't help me. If I reinstate the script with time trigger then, it will work for 3-5 days or so, before it breaks again in the exact same way. I get the feeling I'm exceeding some kind of quota with this script, but it must be a new one because I haven't added anything to my google drive. In fact I have disabled all other scripts associated with my account and it hasn't helped. I found some forums with similar issues, but google claims those are fixed, and that folks should migrate their formulae to a new document if they're not. That's the first thing I tried. Worked for about a week. Is there something obvious I'm missing?
EDIT: Changed IMPORTHTML to IMPORTDATA. Forgot which function I was using.
Perhaps the scripts subreddit is a better place to post, but it didn't seem very active so I'll try here first.
Posted Apr 26, 2022, 2:34 PM UTC · edited
Script has qoutas as well. How often do you invoke? You could try to get the value via apps script as well and paste as value... Leave behind the hole importhtml
Used to be every 5 minutes, now it's every 15. Hasn't seemed to make a difference, and as I mentioned this exact script (with the 5 minute interval) was running for years. . .
I updated my post to reflect IMPORTDATA, the actual function that's being used. This function is being called by the script, entered into a cell by it with a random number, and then that cell is populating an adjacent range with price data. Is there another way to fetch this data with a script?
Depends on the url...