News article social share counter?

How would I build a sheet that lists matching articles from TechCrunch, and then fetch share/link counts from a number of social network platforms?

1 Like

Hi Robert,

Unfortunately, dashdash currently doesn’t offer an integration that can help with this use case.
However, I looked it up and came across this SharedCount API that can. I’ll explain below how you can do so.

First, create a free account on SharedCount and retrieve your personal API key. Their free plan gives you 15,000 calls per month.
Then, create a spreadsheet on dashdash and set it up as follows:

A1: API URL
A2: API key
B1: https://api.sharedcount.com/v1.0/
B2: [YOUR PERSONAL API KEY]

Screenshot 2020-04-08 at 15.44.11

Here, we’re just setting up the API endpoint that we’ll be using in our calls in the next step.
Next, we’ll be setting up the table where we’ll be adding the URLs and retrieving their data.

A4: URL
B4: Get count
A5: [YOUR URL]
B5: =GET($B$1&"?apikey="&$B$2&"&url="&A5)

In B5, the GET function takes the API endpoint and appends to it the API key and the URL to look up. Notice that the API URL and key cells are locked so that when we copy them later, the cell reference doesn’t change.
This will return a data object/the API response that looks like this.

Note that for the null values, the API documentation states that “This endpoint returns null (instead of 0) when a service fails to report a number. It removes values from the responses that SharedCount no longer queries, like Buzz, Reddit, and Delicious.”

You can now display the data you’re interested in by clicking on the "Copy" button next to the data point you want to display, to copy it to the clipboard, and pasting it in a cell to generate the necessary formula.
For example, if we wanted to display the Facebook total count, comment count, share count, and Pinterest metrics, it would look like this.

C5: =PARSE(B5,"[‘Facebook’].[‘total_count’]")
D5: =PARSE(B5,"[‘Facebook’].[‘comment_count’]")
E5: =PARSE(B5,"[‘Facebook’].[‘share_count’]")
F5: =PARSE(B5,"[‘Pinterest’]")

Now that you have everything set up, you can look up a list of URLs all at once. All you have to do is copy and paste the URLs in column A. Then, copy the cells B5:F5 and paste them next to all the URLs. The GET functions will execute automatically and retrieve all the metrics.

Based on a quick test, the API seems to work with different news sources as well as websites in general.
Please let me know if this works for you and if you have any questions.

2 Likes

Hi Hady,
Thanks so much for putting this together. I tried it and it works.
Unfortunately, SharedCount returns null data for most social networks, and the same seems to be true for most services in its competitive set.
I assume Twitter and LinkedIn might have ripped access from them at some point.
Thanks. Hoping this will be the first time I use Dashdash.

2 Likes