urlfetch - How to write a Google Apps Script to pull a part of table data in FinViz.com with UrlFetchApp?

Question

I like to learn how to write up an Google Apps Script to import a part of table data in FinViz.com into a Google sheet, but using UrlFetchApp. I used to use IMPORTHTML. It worked well if just a couple of stock sticker searches. But if I loop the function for many stock stickers, I faced a lot of empty import despite such data available in FinViz. I see from googling that UrlFetchApp is the way to go. Can anyone help write up a script using UrlFetchApp? For a simple example, I have a list of stock tickers in Column A in Google sheet and import their Forward P/E data from FinViz and then write them in Column B in the sheet. I share a Google sheet here. Thank you so much!!

https://docs.google.com/spreadsheets/d/1JmdBKqNVoj2wpJ_st22C8FchgTUss8Edo4q_gMmAgKE/edit?usp=sharing

question from:https://stackoverflow.com/questions/65943901/how-to-write-a-google-apps-script-to-pull-a-part-of-table-data-in-finviz-com-wit

Answer 1

After doing some tests with the site you provided and trying different methods I think it will be easier for you to simply retrieve the HTML static site text and then do a Javascript search on the site to return the values you are looking for. The code from the answer I shared is failing because the website has some HTML compatibillity issues as you are trying to interpret that data with XML instead. Moreover, the table you are interested in is within a div element so you would need to first get to the div element and then to the table.

The following piece of code with self-explanatory comments returns and logs the Forward P/E values from the website you provided:

function ALTERNATIVE(){
  // Get all the static HTML text of the website
  const res = UrlFetchApp.fetch('https://finviz.com/quote.ashx?t=AAPL', {muteHttpExceptions: true}).getContentText();
  // Find the index of the string of the parameter we are searching for 
  index = res.search("Forward P/E");
  // create a substring to only get the right number values ignoring all the HTML tags and classes
  sub = res.substring(index+68,index+73);

  Logger.log(sub);
  return sub;
}