Auto Refresh on ImportXML on Google Sheets


#1

I have a few web address’s for =ImportHTML and I was following a guide from here to achieve an auto update Apps Script.

The website I’m referencing is: https://support.geckoboard.com/hc/en-us/articles/206260188-Using-Google-Sheets-IMPORTHTML-function-to-display-data-in-Geckoboard

This is an example of one of the imports I’m using:
A1=importxml(“http://www.nfl.com/liveupdate/scorestrip/ss.xml","//g/@q” )
When I paste just that into google sheets, it does work.

Step 5: Replace with the table position of the page. The web address I’m using doesn’t have a table reference. “//G/@Q” . I’m not sure how to adjust it to work to my address?

The error messages I’ve gotten in sheets:
Formula Parse Error, Function IMPORTHTML parameter 3 expects number values. But ‘//g/@q’ is a text and cannot be coerced to a number.

I’d appreciate the help


#2

Hello :wave:

As that page is purely an XML file, you can import it into Google Sheets using:

=IMPORTDATA(“http://www.nfl.com/liveupdate/scorestrip/ss.xml”)


The auto-update script would be simlar to the importHTML() or importXML() methods. We have a guide + example for importDATA() as well.

Can you elaborate a bit on what you’re trying to achieve - the end result? Perhaps we could explore other options.

Hari


#3

I tried using the importdata and it didn’t populate the way importxml did.

Basically I need NFL scores to automatically populate in a spreadsheet and I’d prefer it to auto update as scores change frequently.


#4

Hi again!

You should be able to achieve it by slightly tweaking the importXML query. For example, for fetching the “vnn” values into a Google Sheets column, you can use:

=importxml(“http://www.nfl.com/liveupdate/scorestrip/ss.xml","//@vnn”)


I’ve created a sample Google Sheet for your reference:


You can similarly fetch each element you want.

Note: You can then use the script mentioned in our importXML article here to automate the import. You’ll have to tweak the script to include all your required values.


I hope this helps! Are you looking to display the result on Geckoboard?

Cheers,
Hari


#5

Okay so I have the Google sheet just as you do, but I’m very clueless when
it comes to changing the code. Can you just give me an example of how you
would change the first part according to the google spreadsheet you made.

var cellFunction1 = ‘=IMPORTXML("’ +
SpreadsheetApp.getActiveSheet().getRange(‘A1’).getValue() + ‘?’ +
queryString + ‘","’+
SpreadsheetApp.getActiveSheet().getRange(‘A2’).getValue() + ‘")’;
SpreadsheetApp.getActiveSheet().getRange(‘C1’).setValue(cellFunction1);

A1 - Should reflect WHERE your first URL is in your google sheet. I assume
I wouldn’t replace anything because first code is in A1.
A2- Should reflect first XPath Query is written in your google sheet - This
I’m so unsure of what that is or what i’m supposed to be replacing in the
code
C1- Where I want to display the results of IMPORTXML - So I want it to
display in A20, what do I replace?

If you could maybe write the first Cellfunction1 out with how it is in
accordance to your google sheet you sampled. Then I’d be able to follow
suite on the other Import’s.

I know this is asking for alot, but I appreciate the help thus far and
taking the time to explain fully.


#6

Hello :wave:

Could you share how you plan to display the result on Geckoboard — that is, which visualization would you use and how would you consume your dashboards (via TV screens, on desktop/laptop, our iPhone app?)


#7

Right now I was trying to use the code to just display in google sheets and
follow the guide for it to auto update.


#8

If you get it working, would you be willing to share the widget? NFL scores would be awesome.


#9

Not the most optimised script, but this should do the trick:

function getData() {
  SpreadsheetApp.getActiveSpreadsheet().getRange('A1').setValue('=importxml("http://www.nfl.com/liveupdate/scorestrip/ss.xml","//@eid")')
  SpreadsheetApp.getActiveSpreadsheet().getRange('B1').setValue('=importxml("http://www.nfl.com/liveupdate/scorestrip/ss.xml","//@gsis")')
  SpreadsheetApp.getActiveSpreadsheet().getRange('C1').setValue('=importxml("http://www.nfl.com/liveupdate/scorestrip/ss.xml","//@d")')
  SpreadsheetApp.getActiveSpreadsheet().getRange('D1').setValue('=importxml("http://www.nfl.com/liveupdate/scorestrip/ss.xml","//@q")')
  SpreadsheetApp.getActiveSpreadsheet().getRange('E1').setValue('=importxml("http://www.nfl.com/liveupdate/scorestrip/ss.xml","//@h")')
  SpreadsheetApp.getActiveSpreadsheet().getRange('F1').setValue('=importxml("http://www.nfl.com/liveupdate/scorestrip/ss.xml","//@hnn")')
  SpreadsheetApp.getActiveSpreadsheet().getRange('G1').setValue('=importxml("http://www.nfl.com/liveupdate/scorestrip/ss.xml","//@hs")')
  SpreadsheetApp.getActiveSpreadsheet().getRange('H1').setValue('=importxml("http://www.nfl.com/liveupdate/scorestrip/ss.xml","//@v")')
  SpreadsheetApp.getActiveSpreadsheet().getRange('I1').setValue('=importxml("http://www.nfl.com/liveupdate/scorestrip/ss.xml","//@vnn")')
  SpreadsheetApp.getActiveSpreadsheet().getRange('J1').setValue('=importxml("http://www.nfl.com/liveupdate/scorestrip/ss.xml","//@vs")')
  SpreadsheetApp.getActiveSpreadsheet().getRange('K1').setValue('=importxml("http://www.nfl.com/liveupdate/scorestrip/ss.xml","//@rz")')
  SpreadsheetApp.getActiveSpreadsheet().getRange('L1').setValue('=importxml("http://www.nfl.com/liveupdate/scorestrip/ss.xml","//@gt")')
}

#10

Hey I appreciate the help. There are some games tomorrow, so I’m going to
see how this works and let you know.

Again thanks for taking the time


#11

I tried this, updated the triggers as well and it still didn’t auto update
the page every minute or even every 5 minutes. What else could it be?


#12

Not sure. I’m using the script to update this spreadsheet (https://docs.google.com/spreadsheets/d/1nyhvuVtc-YCefYnMkG8MYe7iSi8Ms_n4FFfeHd31LbE/edit#gid=1300120159) every minute. I added a =NOW() on M1 to make sure it is updating (and it’s working like a charm).

Have you checked that the endpoint is updating? Perhaps the trigger is not correctly set or your quotas for G Suite don’t allow a refresh every minute (I’m using a business account for the above, but off the top of my head consumer accounts are limited to refresh every 10 minutes -or more-)