You’ve likely seen RSS feeds on various websites (usually signaled by their orange and white wave icon), meaning that you can subscribe (almost always free) to automatically receive any new articles or other items from that source. Many are from the industry trade publications that alert you to news of business and personnel changes worthwhile for talent sourcing.
Microsoft Outlook and various dedicated feed reader apps exist, and there’s even paid competitive intelligence software, but what if you wanted a free central repository built up over time that could be shared with certain colleagues?
That’s what this article provides: a working Google sheet with Google Apps Script behind it, of which you can save a personal copy from http://bit.ly/compnewsfeeder to your own Google Drive.
Do not edit this version; instead in the top Google sheets menu do the following:
- Select File
- Make a copy
- Click the OK button at the bottom – (ignore the checkboxes above it)
Below explains how to modify it for your content sources. Obviously, your team will need Google sheets access to benefit: if your corporate network doesn’t allow that, they may need to access from a personal computer. Any browser is fine (not just Chrome).
Click the green Share button at the upper right when you’re ready to share your version (you can select individuals or anyone with the link, but you’ll need to give them editing rights in order to use it fully).
When you create a new feedreader (or the first time you use this one), you will be asked to do a one-time authorization to use the script.
- In the “Authorization Required” popup
- Click the green Continue button
- It will then ask you to login: pick any Google account you have (you can even create a new account for things like this)
- Next, it says “This app isn’t verified,” but don’t click the blue “Back to Safety” button
- Instead, click the hyperlinked text “Advanced” to the left
- Then scroll to the bottom of the popup
- Click “Go to CompetitiveNewsFeederScript (unsafe)”
- Finally, scroll to the bottom of the popup again and click the blue
- Allow button at the lower right.
That’s it (you will also receive a Gmail message confirming you allowed this – you can ignore than email).
USAGE
In the top horizontal menu of the Google sheet, you’ll notice after File Edit View Insert Format Data Tools Add-ons Help is a menu choice called Custom Menu. That was generated by the script. To see what’s going on there, you can select Tools → Script Editor, which will open in a new browser tab (don’t mess with anything yet, unless you’ve got a basic understanding of Google Apps Script).
Script lines 4-15 contain the code for that. Anything you include in this section will be visible in the custom menu unless it’s commented out (i.e., any single-line beginning with // or any length block surrounded by /* commented out stuff here */ which you may recognize as JavaScript syntax. That’s because Google Apps Script is mostly JavaScript, with some helpful additions!
So, for example, you’ll notice the cleanCell and convertDates functions are commented out from appearing in the menu, but those functions do exist in the code in case you ever want them. The cleanCell function will take any messy article text (Description) and trim the leading and trailing line returns, and some mid-line line returns you don’t want.
You’ll notice I commented out another method that Andre Bradshaw suggested – but it’s awesome if you want the whole article text to appear as a single line in the cell, so I do use it in the main script (line 169).
Be careful about renaming sheets! In various places, you’ll see the getSheetByName command, such as:
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName(“Archive”);
This is to ensure that the correct sheet is selected, even if new sheets are inserted (you may pull in more sources over time). If you change the feeds being populated, you may want to rename the sheets accordingly. That’s fine *as long as* you change the sheet name between lines 75-95. For example, lines 79-80 are:
var sheet2 = ss.getSheetByName(“SecLenTimes”);
var cellFn2 = ‘=ImportFeed(“http://www.securitieslendingtimes.com/rssfeed.php”)’;
If you want to pull in the feed for, say, Software Development Times at https://sdtimes.com/feed/ instead, then you could change the 2 lines above to:
var sheet2 = ss.getSheetByName(“SDTimes”);
var cellFn2 = ‘=ImportFeed(“https://sdtimes.com/feed/”)’;
But the rest of the script, which references it by the variable name sheet2, can continue to be used as-is. For example, when you get to lines 117-123, this loop should pull in your feed’s articles one per row, and it’s looking for the title in column A, the URL in B, date in C, and description (article text) in D.
Occasionally a source’s feed has another column in between (e.g., Author), which I’m assuming you don’t need. The script only repurposes the aforementioned 4 columns from the feed (manually coded for each feed – there may be a way to auto-detect, but I haven’t figured that out yet).
If you are pulling a different website’s feed, the way to test what columns are generated is by importing the feed. Enter the corresponding formula in a new sheet, or any cell that has no cells populated to the right or below that cell (otherwise, you’ll get a warning that it can’t overwrite existing cells). For example, you could insert a blank sheet and enter in cell A1:
=ImportFeed(“https://sdtimes.com/feed/”)
In this case, we see SD Times does have Author as column B, so URL appears in C, date in D, and description in E. Therefore, the correctly-coded template to use is in lines 126-132 (TradersMagazine’s feed matches the format of SD Times’ feed), so probably easier to use sheet3 throughout rather than sheet2 for that feed source, or just edit yourself: change the URL row getRange part from (i+1,2) to (i+1,3) because the URL is now column C (Google Apps Script and JavaScript reference by the number, 3 = 3rd column), and similarly change the date row’s getRange from (i+1,3) to (i+1,4) and finally from (i+1,4) to (i+1,5) on the description row. Everything else should be fine as is.
Another notable part of the script is lines 179-183, which utilize a script from MomentJS.com to convert the more complex RSS timestamps to a simple YYYY-MM-DD date format. I needed this to make it simpler to sort the articles from newest to oldest (row 204), applied back on the Archive sheet where articles from all sources are consolidated.
Finally, come two other functions. Starting on line 198 is hiringNewsFilters, which, when invoked by selecting “Hiring news” in the Custom Menu, looks for certain keywords in the title of each article (lines 199-200 — yes, you can replace with your own keywords!) to determine what’s likely to be news related to hire or promotion.
That filter is applied to the Archive sheet (line 209), and the matching filtered rows are displayed back on the first sheet, Filters, starting in cell A3 (line 218). Finally, the cursor focus is brought back to cell A4 of the Filters sheet (line 221), so you don’t accidentally mess up the formula in A3 and in case you ran this Custom Menu selection while on another sheet and didn’t realize that the results would display back on the Filters tab.
The function departureNewsFilters (starting line 228) is similar, except when you select “Departure news” in the Custom Menu, it finds articles with the title containing terms indicating layoffs or a particular individual’s departure.
Last but not least, you’ll see two other functions reflected in the Custom Menu as Search TITLE (corresponds to lines 250-262) and Search BODY (264-276). As you can guess, these will prompt you to enter a keyword or phrase and will filter articles with your terms in the title or description, respectively.
To make it easier to follow which column is for what data, as you scroll down, you’ll see I inserted header columns of Title, URL, Date, Description, and froze the top row of the Archive sheet (View → Freeze → 1 row). On the first Filters sheet, which has a 2-row header, I did View → Freeze → 2 rows (but you don’t have to, because every time you select a filter from the menu, that part of the script populates the top 2 rows and freezes them automatically).
The only phase 1 item left on my to-do list is a rows de-duplicator. Over time, this becomes increasingly important because the more often you pull in a feed, the more likely some articles will repeat. In the meantime, I recommend only running the data refresh (“Update All Feeds Data” in the Custom Menu) once per week or less, since most sources usually have at least a week’s worth queued. And yes, it can take a minute or two to finish running that function if you have a lot of sources. (You’ll know when it’s done because the “Running script”message in the box atop your Google sheet will disappear.)
If you have ideas for other functionality to add or a more efficient way to do some of the existing functionality, I welcome your input (particularly from the coders who can actually create what they’re proposing).
While there is nothing dangerous about using this sheet, please note that I am not responsible for any issues arising from the use of this sheet, and I do not have the bandwidth to review your version, but if you encounter an error or other problem, you can go to places like StackOverflow (yes, many items in their extensive Q&A are tagged for Google Apps Script at https://stackoverflow.com/questions/tagged/google-apps-script ) or maybe find a helpful colleague on the Sourcers Who Code group on Facebook (https://www.facebook.com/groups/SourcersWhoCode/).