GoogleSheet Formula to Bring Back GoogleNews

Question

Looking for a GoogleSheet formula that will bring back

1. UNIQUE HEADLINES, off GoogleNews specifically by keyword "Tesla"

2. with Publication Date sorted by ASC

3. Limit 20 headlines

4. Then, I want to group headlines by dates.

Thanks in advance!

Answer 1

You can use the =IMPORTFEED() function in Google sheets, and use sort and unique to make sure that you do not have duplicate titles and that they are sorted by date.

First, you need to construct the Feed URL of Google News using this format:

https://news.google.com/rss/search?q=<KEYWORD>

For this case, I used:

https://news.google.com/rss/search?q=Tesla

After that you need to construct the formula:

=IMPORTFEED("https://news.google.com/rss/search?q=Tesla")

Note: By default, you will get the first 20, so the basic =Importfeed() formula will work

To sort the information, since the format of the date is odd, it cannot be sort directly. So I use this formula in the E1 cell:

=INDEX(IF(C1:C20="",,REGEXREPLACE(C1:C20," GMT","")*1))

Lastly, I create a new sheet (name the last one "raw") to sort the date on this one:

=SORT(raw!A1:E20,5,true)

It will look like this:

Reference:

• ImportFeed fuction.
• Sort Function.
• Unique Fuction.