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

Does this answer your question : https://stackoverflow.com/a/64438079/12835757 ? — Mateo Randwolf, Jan 29 '21 at 08:26
@Mateo, thank you for your suggestion! The posted code is working perfectly as it is and I understood the code concept. But for my lack of HTML knowledge, my first challenge is I couldn't decipher where "()/g)" used in the "const tables" came from inspecting the Yahoo web page. In my example to pull a table data from FinViz.com, what should it be for the HTML table? I would greatly appreciate if you can help. — Newbie, Jan 29 '21 at 17:43

score 0 · Accepted Answer · answered Feb 01 '21 at 15:44

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;
}

References

UrlFetchApp.fetch() to get the HTTP response data.
getContentText() to return the HTTP response enconded as a String.
Javascript search which returns the index index of the first match between the regular expression and the given string, or -1 if no match was found.
Javascript substring which returns a new string containing the specified part of the given string starting from the first index and finishing with the second index.

@ Mateo, so sweet! It's working perfectly. I understood your such concise codes completely and what the index means in your code. Now I could expand your code scheme to grab more data from FinViz. I highly appreciate your help. — Newbie, Feb 01 '21 at 20:42

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

1 Answers1

References