CODEWITHSUNDEEP
google-sheetsgoogle-docs
DasPete
DasPete

Reputation: 851

Refresh ImportHTML

I have a sheet that is importing data using ImportHTML. The data changes quite frequently, is there any way to have Google Sheets automatically refresh the data?

I tried adding & year(now()) & month(now()) & day(now()) & hour(now()) & minute(now()), to the formula but I get the following error:

This function is not allowed to reference a cell with NOW(), RAND(), or RANDBETWEEN()

Upvotes: 3

Views: 9620

Answers (2)

Yash Karan Singh
Yash Karan Singh

Reputation: 43

Very late to the question. I recently had to do a similar thing. I thought I'd write something up here for others to find useful..hopefully.

My Use Case:
I wanted my google sheet to call IMPORTDATA on a specific url every few minutes because the data stored against that url was being changed by a Timer Trigger and an Http Trigger Azure Function.
Basically, I wanted my google sheet to get refreshed every 5 minutes because I knew the data against the url keeps changing.

What I Tried:
I started out with a similar script to what @greg alluded. (Cheers for that.) I setup a timer trigger for that script to be 5 minutes. All looked hunky-dory.

Issues:
I realised that setting the function as a value on the cell caused the function to run only at the start when the sheet was empty. The second time the trigger hit, the sheet did not get refreshed. So I tried

cell.setValue("");
cell.setValue('=IMPORTDATA("my-url")');

No Luck. The sheet did not get refreshed with the new data. It seemed as if the sheet saw that the value-to-set in the cell is the same as the existing value, so it did not bother re-running the function.

To confirm my hypothesis,
I manually tried:

  1. Cut the IMPORTDATA function from the google sheet cell.
  2. Paste the value back into the cell.

The sheet did not get refreshed.

But when I manually tried:

  1. Remove/Change the url from/in the cell. Hit Enter.
  2. Paste the formula again in the cell. Hit Enter.

This worked. So, it seems that cell.SetValue() or even cell.setFormula() just doesn't re-run the function I was setting because the same function existed in the cell to start with.

My Solution best suited to my use-case:
Since the data against the url, I was pointing to, was already in CSV format, I did something like this:

  var firstSheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
  var text = UrlFetchApp.fetch(url).getContentText();   //gets the data.
  var csv = Utilities.parseCsv(text);                   //parses the data.
  var range = firstSheet.getRange(1, 1, csv.length, 17) //getting the range.
  range.setValues(csv);                                 //setting the data.

I'm basically grabbing the data from the url and putting it into the sheet on timer instead of calling the IMPORTDATA function since that approach didn't quite work out for me.

You could do a similar thing, assuming you want to get some sort of table or chart content and save the values to your sheet.

Upvotes: 2

Greg
Greg

Reputation: 1364

"Any way"? Yes, but as Mogsdad has mentioned 'round these parts before, "Since the retirement of the GOOGLECLOCK function, there is no formula that will trigger automatic recalculation."

Using Google Apps Script and something like...

function getData() {
  var queryString = Utilities.formatDate(new Date(), "GMT", "yyyyMMddHHmmss");
  var cellFunction = '=IMPORTHTML("<your-url>?' + queryString + '","<your-query>",<your-index>)';

  SpreadsheetApp.getActiveSpreadsheet().getSheetByName("<your-sheet>").getRange('<your-cell>').setValue(cellFunction);
}

...should get you there. After saving, naming, and authorizing the script, set up a trigger so it runs/refreshes the data as frequently as you'd like.

Upvotes: 3

Related Questions