IMPORTHTML Could not fetch URL

Question

I'm trying to fetch yahoo finance statistical information into google sheets. Used this function but getting error couldnt fetch url..

=IMPORTHTML("https://finance.yahoo.com/quote/AAPL/key-statistics?p=AAPL","TABLE",3)

any idea where I'm getting wrong? Basically I want to retrieve profit margin, operating margin etc into separate columns for particular stock

I answered a few very similar questions with long explanations. https://stackoverflow.com/a/67056410/10445017 and https://stackoverflow.com/a/66544556/10445017 — iansedano, Jun 09 '21 at 10:17

Mike Steelson · Answer 1 · 2021-06-09T09:50:16.870

You can't use IMPORTXML or IMPORTHTML function because the page is built on customer side by javascript and not on server side. Howerver, all datas are available in a big json that you can catch using :

  var source = UrlFetchApp.fetch(url).getContentText()
  var jsonString = source.match(/(?<=root.App.main = ).*(?=}}}})/g) + '}}}}'
  var data = JSON.parse(jsonString)

and then you can navigate through the informations, and choose the ones you need. To get all datas from the json, use :

//mike steelson
let resultat = []; 
function getDataFromYahoo(url) {
  var source = UrlFetchApp.fetch(url).getContentText()
  var jsonString = source.match(/(?<=root.App.main = ).*(?=}}}})/g) + '}}}}'
  var data = JSON.parse(jsonString)
  getAllData(1,eval(data),'data')
  return resultat
}
function getAllData(niv,obj,id) {
  const regex = new RegExp('[^0-9]+');
  for (let p in obj) {
    var newid = (regex.test(p)) ? id + '.' + p : id + '[' + p + ']';
    if (obj[p]!=null){
      if (typeof obj[p] != 'object' && typeof obj[p] != 'function'){
        resultat.push([niv, newid, obj[p]]);
      }
      if (typeof obj[p] == 'object') {
        if (obj[p].length){
          resultat.push([niv, newid, '']);
        }else{
          resultat.push([niv, newid, '']);
        }
        niv+=1;
        getAllData(niv, obj[p], newid );
        niv-=1
      }
    }
  }
}

https://docs.google.com/spreadsheets/d/1EKu4MbuwZ6OTWKvyIJrMfnXf7gXfU8TWU3jwV4XEztU/copy

Thanks Mike; but I dont know Json; Basically I want to extract statistical info into sheets column wise for set of stocks — SRI, Jun 09 '21 at 10:35
What I can do through the spreadsheet that I mentioned is extract all the information (> 15,000) from the web page ... but now it's a big job to detect the information you need, I don't have a magical way to do it unless you try to find them in a 'haystack'. — Mike Steelson, Jun 09 '21 at 11:53

IMPORTHTML Could not fetch URL

1 Answers1