Google Sheets Import XML text value will not display

1
votes

I'm trying to import some energy data into a Google Sheet, but can't seem to get the value to appear since it is just text in the HTML line, and shows up as --,--- in the output. I have tried adding /text() to the XPath but that is still not working either.

Website with data to import:

http://www.caiso.com/TodaysOutlook/Pages/default.aspx

Formula in Google Sheets:

=IMPORTXML("http://www.caiso.com/TodaysOutlook/Pages/default.aspx","//div[@class='overview-large-number']")

Also tried the follwoing Xpath with the same --,--- being displayed.

/html/body/div[2]/div[3]/div[4]/div/div/div[2]/div/div[1]/div[1]/div[1]
/html/body/div[2]/div[3]/div[4]/div/div/div[2]/div/div[1]/div[1]/div[1]/text()

Screen Shot of Webpage:

Caiso Webage with Data

HTML Element with Data:

<div class="overview-large-number">41,946 <span>MW</span></div>

Screen Shote of Google Sheets current Output:

Google Sheets Screenshot

Thank you in advanced for any insight.

1
htmlxpathweb-scrapinggoogle-sheetsgoogle-sheets-importxml
So you just want the 6 MW values? - Calculuswhiz
@Calculuswhiz Correct. - Cory

1 Answers

3
votes

The data is populated after the page loads, so you can't use ImportXML(). The data is requested via HTTP Get on the URL http://www.caiso.com/outlook/SP/stats.txt and is returned in JSON format.

  1. Get the ImportJSON script from here and add it to your sheet (Tools > Script Editor).
  2. This should be enough to get you started:
=Transpose(ArrayFormula(HLOOKUP(
{"AvailableCapacity","CurrentSystemDemand","todayForecastPeakDemand","histDemand","TodaysPeakDemand","tomorrowsForecastPeakDemand"},
ImportJSON("http://www.caiso.com/outlook/SP/stats.txt"),2,0)))

Note that the Historical Peak value is already a string, so you may have to do some extra work to get the number out.