Welcome to SetYourSites.net, Your source for premium and free website templates.
How To Build A Google Sites RSS News Aggregator Website
In this blog I will show you how I built a Google Sites news aggregator site using Google Sheets
While probably not the ideal platform and tools for hosting and creating a news aggregator site, Google Sites, in combination with Google Sheets,
can be used to create an old style news aggregator somewhat reminiscent of PopURLS (now defunct) or AllTop.com. While such a site built using Google Sites will undoubtedly not load as fast on other hosting using other tools (I notice a difference in load times compared to AllTop and other news aggregators), the load time is not necessarily a deal breaker and such a project still might serve the purpose of providing a place to have your own news aggregator site without spending too much money on hosting.
I'm not all that up on the coding terminology or all the possible features Google Sheets allows for RSS feeds and such so this is going to be a quick and dirty tutorial ala "Just Do This and This" to get your RSS feeds working and installed on your Google Site quickly. If you want to experiment and figure out what else can be done beyond the basic steps I show you here, that's up to you. Let's get to it then.
Go the Google Sheets and open a blank spread sheet. Next, look for the "fx" box right above column "A" in the upper left of the sheet. (Before pasting, click on column "A" to ensure the code is pasted correctly). Right click the box and paste the following code (without formatting) to the right of the"fx" and hit enter
=ARRAYFORMULA(IMPORTFEED("https://www.wired.com/feed/category/ideas/latest/rss", "items", ROW(A3:A9), {8}))
Notice the wired.com link in green between the quotes. That's an RSS feed link. You can replace that RSS link with any RSS feed link you want.
After hitting enter, you might get a warning that says "Warning: Some formulas are tying to send and recieve data from external parties".
Click "Allow Access".
If you did everything right, you should see something like in the following image (right click the image and view in a new tab if it's hard to see):
If you don't see an error or REF! warning, your RSS feed should be working at this point. To double check, you can hover your mouse cursor over one of the hyperlinks in the URL column. If a picture in a small box pops up, that's a good sign your feed is working properly.
If there is a problem, one thing you can check is the ARRAY FORMULA and make sure it is referencing the correct columns. Click on the "I" column and look at the code in the "fx" box. After where is says "HYPERLINK" the two letters (in this case "C:C") should be the letters of the column where the URL starts in the spread sheet. If the URL starts in column "B" or any other column, you would need to change the "C:C" to that column letter to get the feed to work.
2. Specifying The Number Of Data Rows Of The Feed
To set the number of headlines you'd like to appear in your feed, click on column A to access the IMPORTFEED code again. Notice the number "8" at the end of the code in the image below. That represents the number of data fields or headlines that will appear in your RSS feed. You can change it to whatever number you want but I recommend you keep it between a manageable 8-20. 10 is probably ideal.
3. Title Your Feed and Delete Excess Rows and Columns
As we are building a news aggregator, you are going to probably add more than one RSS news feed to your site so you'll want to name each one after the source of the feed. In this particular case, it's Wired.com. You can update the title of the sheet in the upper left-hand corner as indicated in the image below.
At this point, you will also want to delete any excess empty spread sheet cells that are not in use beyond the RSS data cells. This can be accomplished by clicking an empty row or column just after or below the cells occupied by the feed, scrolling to the end of the spread sheet, then, while holding the shift key, clicking the last row or column to highlight all the cells you will be deleting. You then simply right click and select delete for the cells you highlighted.
4. Hide The Rows And Columns You Don't Want To Appear On Your Site
We are building a text link news aggregator like AllTop or PopURLs so we only want the headline text links to appear. That means we want to hide columns A through H and only allow the columns holding the text links to be viewable. To do this we simply highlight columns A through H simultaneously and then right click. A box will appear allowing us to select "Hide Columns A-H". Select that and your spread sheet should look like the one in the image below:
In the above image you might notice the "Title" text link in row "1". It's a dead link but it might confuse people if they see it. I couldn't figure out how to remove it without breaking the feed since that cell apparently holds the code necessary for the RSS feed to work so what I did was just hide it using black text and a black background for that cell.
Now you are ready to add the RSS feed to your Google Site. Open another browser tab and open your Google Site in the editor. At this point, we could use the "Insert" option to add the Google Sheet to your page but I don't recommend that because, depending on how you position the feed on your site, that method may leave your RSS feed with too large of a right-hand border. Instead, I recommend copying the following code and, using the Google Site "Embed" option, embed the code below on your page.
<iframe src="https://docs.google.com/spreadsheets/d/e/2PACX-1vQHyLabY2RhcO9tIRAP9zllnPRet-opPCR3u_XxnN7b438wrwa0yg0SQs1f9UlaK6aJF8eMxvKRSUil/pubhtml?widget=true&headers=false" width="520" height="252" sandbox="allow-same-origin allow-scripts allow-popups"></iframe>
The RSS feed should now be rendered in your page editor and you can click on it and adjust the size to your liking. (Hint: Make sure you don't adjust the size too small that it prevents the full feed from being seen).
Once you've adjusted the size of the embedded feed to your liking, you can now use Copy & Paste to duplicate as many RSS feeds as you expect to put on that page.
You can follow steps 1-4 and create in Google Sheets as many different RSS feeds from different sources as you want to place on your Google Site. Make sure to name them accordingly so you don't mix them up.
Before your RSS feeds can be viewable to your site visitors, you have to publish them to the web. To do this, you go back to Google Sheets and select the feed you wish to share with the web in the editor. Inside the editor, click on "File", "Share" and then select "Publish To Web". A popup entitled "Publish To Web" will appear. Click on the "embed" tab. You will see a box with an iframe code. Do NOT copy the full code. Select and copy ONLY the https:// link between the double quotes (" ").
Now go back to your Google Site page with the embedded RSS feed(s) and edit the news feed source by replacing the old https:// link in the iframe code with the link to the new RSS feed you just copied.
Repeat this for every different RSS feed you've made in Google Sheets for every RSS feed you wish to add to your Google Site.
Once you've added all the RSS feeds you want on your page, you can move them around in Google Site's CMS editor and name them using the text insert option.
To view a working example of a Google Site news aggregator someone created using my instructions, click here.
If you have any questions, get stuck or if there is a step I left out that is confusion, you can contact me for free at info@setyoursites.net
Despite the obvious and well-known drawbacks of using Google Sites, there are still some major advantages. One advantage is that Google will be more welcome to indexing a site created in its web editor in its search results. Another is fee and unlimited hosting. This means you can run such a news aggregator website for as low as $12 a year, if you want to add a custom domain name.
Should you want to forego the domain name, you're talking about a totally free automatically updating news aggregator website, with Google's search indexing and Google's secure url addresses included.
Not a bad deal at all.