Hello,
Ive been trying to understand and find a solution to a problem ive been having since a few days:
Ive added an =importhtml to my google sheet to have updated quotes from a specific website. By using this function, with an app script and a trigger, the imported html does not update though i does refresh. IF i try this same procedure on a new workbook, it does work.
I am wondering if something conflicts when connected to glide. or if anyone else has had this issue when using an app script?
thank you very much for your help.
So do you mean the IMPORTHTML content does not refresh on the Google Sheets side?
I would suggest adding an unharmful param to your URL so it won’t be “cached”. Something like:
https://example.com/query?t=20230326
With 20230326 crafted from a “today” value. Once it updates, your IMPORTHTML will be forced to update as well.
Thank you Thinh. I will try this parameter.
So if i undertand you well i should add ‘/query?t=20230326’ meaning if my function is: '=ImportHTML(“https://uk.investing.com/commodities/real-time-futures”,“table”,1)
I should do the following: '=ImportHTML(“https://uk.investing.com/commodities/real-time-futures/query?t=20230326”,“table”,1) this in the app script?
To use the suggestion from @ThinhDinh, you would have:
=ImportHTML(“https://uk.investing.com/commodities/real-time-futures?t=20230326”,“table”,1)
1 Like
Yes, but you should have a dynamic variable so that the "t’ value changes.
How can i set the ‘t’ variable to dynamic? Thank you
Hola!
You need to add a line to your code to get a dynamic value in t, something like…
var value= (Math.random()*10000000).toFixed();
var myFormula='=ImportHTML(“https://uk.investing.com/commodities/real-time-futures?t=${value}”,“table”,1)'
t will range from 1-9,999,999 each time your code is executed and avoid using script’s cache
thank you Valero, will try adding that to the script. 
IT HAS BEEN SOLVED: Thank you all!!
So i leave this here just in case anyone has the same problem:
to copy and paste:
function updateformula() {
//Formula is located on Sheet7, in cell A2
var myRange=SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘Sheet7’).getRange(‘B5’);
var value= (Math.random()*10000000).toFixed();
var myformula=‘=ImportHTML(“https://uk.investing.com/commodities/real-time-futures/?t=${value}”;“table”;1)’
myRange.setFormula(‘’);
SpreadsheetApp.flush();
myRange.setFormula(myformula);
//to show when the last time that this information was updated - add in the date/time to cell E1
SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘Sheet7’).getRange(‘E1’).setValue(new Date());
SpreadsheetApp.flush();
return;
}
(dont forget to add the trigger on timer)
on GLIDE side you just work with google sheet using the following formula:
=ImportHTML(“https://uk.investing.com/commodities/real-time-futures/?t=${value}”,“table”,1)
Make sure to use the right sheet name on the script, in this case its sheet7.
2 Likes