Importhtml not refreshing

[E J E]

Hi.

Ive tried various scripts and variations but none update:

this my scr:

function mynewFunction() {

var sh = SpreadsheetApp.getActiveSheet();

var r = "C7";

var insertDataOption = 'overwrite';

var f = '=ImportHTML("https://uk.investing.com/commodities/real-time-futures";"table";1)';

var range = sh.getRange(r);

range.clear();

SpreadsheetApp.flush();

range.setFormula(f);

}

does not refresh even if i have the timer set up on 1min. only when i click run does it run.

otherwise stays stagnant.

thank you for your help.

[Laurie Nason]

According to google - the importhtml function refreshes once every hour when the spreadsheet is open.

The document also says that if you delete and re-add cess or overwrite the cells with the same formula, this will trigger a refresh - which is probably why you are running the function in the first place.

However, like you said - your function doesn't seem to update the table.

After playing around with the function I came up with one that does seem to work.

function updateformula() {

//Formula is located on Sheet7, in cell A2

var myRange=SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet7').getRange('A2');

var myformula='=ImportHTML("https://uk.investing.com/commodities/real-time-futures";"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;

}

If you set up a timed trigger to run every minute, then this function will do what you want it to - I think the trick is the first .flush() after removing the formula from the cell

Laurie

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/132795f6-8b1b-4c89-b29e-67da987927c6n%40googlegroups.com.

[E J E]

thank you Laurie.

So its refreshing but not updating. im sure its some glitch somewhere maybe related to the date as it seems to stay on the same time eventhough the E1 cell is updating time. 

let me add images:

[E J E]

so it basically does refresh but does not update with the desired link. 

[Laurie Nason]

That's strange - I just checked and my sheet is still updating - including the time. 

I take it that if your tab in your sheet name was different - you changed the script to point to the right tab - in both locations?

Maybe try changing the cell that the date writes to  - see if that makes a difference?

Also, I noticed that you had some failures on the script - if you look at the log associated with the script is there an error mentioned?

[E J E]

You r right it is strange. In this specific workbook it does not updat, but if i open a new workbook it does work.

I dont understand why this is happening with this specific workbook, maybe something conflicting o something else. driving me nuts. thank you for your concern and help.

[eje]

Brett  if i may ask, does importhtml update even if the workbook is closed? or must it remain opened for it to function? thank u

On Tue, Mar 28, 2023 at 8:35 AM eje <ejenc...@gmail.com> wrote:

in the google sheet or in the app script, or both? thank u

On Tue, Mar 28, 2023 at 5:37 AM Brett Grear <bgrea...@stvps.co.uk> wrote:

Put a random # after the end of the url such as 'https://uk.investing.com/commodities/real-time-futures#123456789'

Sheets will ignore it and since it is a "different" url it will trigger a refresh.

I usually use new Date().getTime() to generate a random int. Then you can just set up a apps script trigger that finds the url and changes the end number whenever you want a refresh.

You received this message because you are subscribed to a topic in the Google Groups "Google Apps Script Community" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-apps-script-community/HlLg9JLyLl4/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/a8208068-03e4-4b81-9de1-cc68387dd51fn%40googlegroups.com.

[eje]

in the google sheet or in the app script, or both? thank u

On Tue, Mar 28, 2023 at 5:37 AM Brett Grear <bgrea...@stvps.co.uk> wrote:

Put a random # after the end of the url such as 'https://uk.investing.com/commodities/real-time-futures#123456789'

Sheets will ignore it and since it is a "different" url it will trigger a refresh.

I usually use new Date().getTime() to generate a random int. Then you can just set up a apps script trigger that finds the url and changes the end number whenever you want a refresh.

On Thursday, 23 March 2023 at 15:05:45 UTC E J E wrote:

--
You received this message because you are subscribed to a topic in the Google Groups "Google Apps Script Community" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-apps-script-community/HlLg9JLyLl4/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/a8208068-03e4-4b81-9de1-cc68387dd51fn%40googlegroups.com.