CODEWITHSUNDEEP
google-sheetsweb-scrapinggoogle-sheets-formula
Leison Maharjan
Leison Maharjan

Reputation: 1

Importxml() returned "empty cells" or "formula parse error"

I tried Importhtml ("https://nepsealpha.com/investment-calandar/dividend","table",) and then Importxml("https://nepsealpha.com/investment-calandar/dividend",xpath). I found out xpath from "selectorgadget" extension of googlechrome, but still couldn't import it. It shows either "empty content" or formula parse error".

Upvotes: 0

Views: 167

Answers (2)

Mike Steelson
Mike Steelson

Reputation: 15318

You can retrieve quite all the informations this way

=importxml(url,"//div/@data-page")

and then parse the json.

By script : =getData("https://nepsealpha.com/investment-calandar/dividend")

function getData(url) {
  var from='data-page="'
  var to='"></div></body>' 
  var jsonString = UrlFetchApp.fetch(url).getContentText().split(from)[1].split(to)[0].replace(/&quot;/g,'"')
  var json = JSON.parse(jsonString).props.today_prices_summary.top_volume
  var headers = Object.keys(json[0]);
  return  ([headers, ...json.map(obj => headers.map(header => obj[header]))]);
}

edit

to update periodically, add this script

function update(){
  var chk = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0].getRange('A1')
  chk.setValue(!chk.getValue())
}

put a trigger as you wish on the update function and change as follows

=getData("https://nepsealpha.com/investment-calandar/dividend",$A$1)

enter image description here

Upvotes: 1

Krzysztof Dołęgowski
Krzysztof Dołęgowski

Reputation: 2660

I know that's not the answer you want to see. It's impossible to get any content from this website using IMPORTXML or other tools included in Google Sheets.

It's generated using Javascript. Once Javascript is disabled no content is displayed: enter image description here

It's done on purpose. Financial companies pay for live stock data and they don't want to share it with us for free. So the site is protected against tools like importxml.

Upvotes: 0

Related Questions