Build an Automated Content Curation Tool in Google Sheets
Note: the Google Sheets Add-on is now deprecated, but you can replicate most of its functionality with some code you can copy and paste from here.
As you may already know, we recently joined up with Blockspring to provide access to our Text Analysis API service in their functions library. To showcase what Blockspring and AYLIEN Text Analysis API can do we’ll be sharing some interesting API mashups we’ve built, that we think you’ll find useful.
For this edition we’re going to show you how to build an Automated Content Curation Tool inside a spreadsheet with little or no programming experience.
What you’ll need for this mashup:
- 15 minutes
- An AYLIEN API account
- A Blockspring account
- A couple of topics or keywords relevant to your business that you want to monitor
Step 1. Get set up
Open a blank spreadsheet, start your Blockspring module and sign in using your Blockspring credentials. (It’s located in your add-on section of the tool bar)
Step 2. Gather your articles
In the Blockspring console (sidebar) there will be a list of suggested functions and an option to browse or search functions. The first function we’re going to use is the “Get recent news about a topic” function. This function utilises the Google News API to pull in recent articles based on a query you suggest.
Once you’ve chosen this function select the “Insert into a new sheet button” this will open a new sheet loaded with a sample function. You’ll notice that the function has an example query pre-loaded, replace the sample query with your own personal search query and hit enter.
In this example we’re going to monitor news about Machine Learning, Deep Learning and Text Analysis, using the following query:
“machine learning” OR “deep learning” OR “Text Analysis”
We’re also going to change the number of articles we want to pull from the Google news API. When you load the function it automatically pulls in 5 articles. We’ve changed it to 30 to try and get some more coverage.
So now we have our articles listed in our spreadsheet, but to get through them all it would take some time to read every one, or scan through each URL to decide, what is relevant, what we might share or what for example might be about our competitors that we should be aware of.
Step 3. Summarize the content
To summarize the articles we’re going to analyze each URL with the AYLIEN API. Choose the “Summarize URL with AYLIEN function” in your Blockspring console and hit insert into selected cell.
The function will have an example URL in it, you need to replace this with the target cell you want to analyze. In our case it was B13. Heres what your new function should look like:
=BLOCKSPRING("summarize-url-with-aylien", "url", b13, "max_sentences", 3)
Notice we’ve also changed the number of sentences you want the article summarized to. We’ve decided on 3 sentences but that’s totally up to you.
Once you’ve hit enter, the function will run and populate the results automatically. You can reuse the same function for the rest of the articles by dragging the function down through the remaining cells.
Step 4: Get Hashtag suggestions for each article
We’ve found it very useful when sharing the content we’ve created to include 2 or 3 hashtags to ensure you get maximum exposure. AYLIEN automatically suggests these for you by running the Hashtag Suggestion function.
As before choose “Suggest Hashtags for URL with Aylien” in the Blockspring console and select insert into selected cell. Again, the function will be loaded with a sample URL, replace this with your target cell and hit enter.
You’ll notice that the suggested hashtags are populated vertically through the cells below, which kind of messes up our spreadsheet. Use the “transpose” function to populate them horizontally. Your new formula should look like this:
=TRANSPOSE((BLOCKSPRING("suggest-hashtags-for-url-with-aylien", "url", B13)))
As we’ve done before drag this down through the rest of the cells to get suggestions for every article.
With a little bit of cosmetic work you can clean your sheet and make it look a little prettier. Try wrapping the text for your Summaries and Titles and add some headings to a frozen row to make it a bit easier on the eye.
Every time you open the spreadsheet it will update automatically and curate useful and shareable content for you based on topics you want covered. We find it particularly useful to check it each morning to stay on top of important content. You can also utilize more of the AYLIEN functions like Entity Extraction and Concept Extraction to look for mentions of companies and people for example.
Isn’t that freakin’ cool?
We’d love to hear about some of the mashups your building with AYLIEN on Blockspring. Let us know if you’ve hacked together something useful recently. We might even feature it on our blog!