Google Spreadsheet Live Data – Not auto updating

This is an issue with Google Sheets & IMPORTDATA. It pulls the data an caches it. It will refresh eventually, but you don’t have any control over when and how frequently it does.

So, in short, nothing can be done about that?

Not that I know of. You might search for more information about IMPORTDATA.

I’m having the same issue… Is this also true if using CFDBDATA to import into Google Sheets?

It is the same. This is Google issue. Google decides when to refresh the data and doesn’t give an API to control it.

Hey guys,

I managed to find a work around you might be interested in:

• To the far right of my data I added some extra info (you can put this info anywhere on the spreadsheet, as long as it will not interfere with the data being imported). For me, column “P” is that column.
• In “P1” I added the current date by using the forumla “=TIMESTAMP()”
• In “P2” I added the URL from the formula given in CFDB Google Spreadsheet Live Data export. i.e. if it was =IMPORTDATA(“https://mydomain.com/abcdefg”), then you would add “https://mydomain.com/abcdefg” to “P2”
• In “P3” I added the formula “=P2&”&”&P1”
• To import the data I added “=IMPORTDATA(P3)” to cell “A1”.
• Now to make this all work & auto update I added some code and changed a setting. Go to Tools > Code Editor. This opens up a script page. Add the following code:

  function timestamp() {
  return new Date().getTime();
  }
  function autoUpdate(){
  SpreadsheetApp.getActiveSheet().getRange('P1').setValue('0');
  SpreadsheetApp.getActiveSheet().getRange('P1').setFormula('=TIMESTAMP()');
  }

• Whilst still in the script editor, go to Resources > Current project’s triggers. Under “Run” add the function “autoUpdate”, and then under “events” change to “time-driven”, and select your preferred update frequency.
• Hit Save
• It then all worked. Obviously you’ll need to adjust the code to suit your situation, but this has worked perfectly for me ??

Have fun ??

Thank you so much, conorseed! I’m a newbie to Google Sheets and I can’t believe that worked, but it did! Thank you so much!

Hello conorseed,

Bingoo!!! your solution works and make my life easier. Thankyou soo much for the solution. One thing what I need is when I fill my form sheet auto update on live spreadsheet but the problem is that new record at on row 1. I want to add new entry at the bottom because when I add my Comments for each entry and when I add new entry old record moves down and new record at row one so the comment of last added entry will be shown beside the new entry and so on. Simple I want to add new entry at the bottom how can I achieve this Please help.

Thank you in advance!

Mufaddal
Develper53

Reverse the sort order. Regenerate the export link from the Shortcode builder page with “orderby” set to “Submitted ASC”

Hello Michael Simpson,

Thanks for the reply. I have checked there is no option of submitted ASC in order by drop down. Can you please show me the steps of to do this ?

Waiting for your positive reply thank you ??

I did it ?? I found the way how to do it

Well thanks for the tip ??

I can’t seem to get this running.

What should the output of P3 look like?

mine looks like:

https://domain.com/testing.csv&1469127272410

This throws up an error.

Any help would be appreciated.

Same here, pretty much as anrky. Can’t get it work ??

Hi,

Although I set the trigger in “every minute”, Impordata was not working healty.
When I cleared imported cell content manually, the script brought the updated data to the spreadsheet. Then I tried to clear content automatically using another scipt and set the trigger in every 5 minute.

Use that function to import data:

function myFunction() {
  SpreadsheetApp.getActive().getRange('A2').setValue('=importdata("https://www.abcd.com.data.csv")')
}

Use that function to clear content automatically:

function clearRange() {
   var data = SpreadsheetApp.getActive().getSheetByName('data');
  data.getRange('A2').clearContent();}

Hope this helps.

Yusuf

I’m not aware of a way to force the Google Sheet to update on a schedule we define. It seems to update when it decides to do so.