DEV Community

Cover image for Pulling your DEV.TO Stats into a Google Sheet
Jeremy Morgan for Pluralsight

Posted on

Pulling your DEV.TO Stats into a Google Sheet

Google Sheets is a pretty awesome spreadsheet program. I use it a lot for work and personal stuff.

I decided I wanted to pull in some of my stats from into a Google sheet and found it to be easy.

Note: Don't want to write out the code? Get it here

So here's how you can do it for your stats.

Step 1: Get your API Key

You can locate this in your profile on Dev.To. Go to your settings.

Dev To Settings

Your API key will be under "Account."

Dev To Settings

Getting Published Articles

The API is simple. You can use something like Postman to test it out, using the URL and your API key:
Enter fullscreen mode Exit fullscreen mode

Dev To API in Postman

If you see this, your URL works! You can change per_page to whatever you'd like.

Create the Spreadsheet

Create a Sheet in Google Sheets that looks like this:

Dev To API in Postman

Here I have a sheet that lists

  • Title
  • Published
  • Total Views
  • Reactions
  • Comments

These will all be auto-populated from the API.

Click on Tools -> Script editor to open up the Script editor.

Dev To API in Postman

Code To Pull Data

In the script editor, you'll see the code.

function myFunction() {

Enter fullscreen mode Exit fullscreen mode

We're going to replace the function with ours. As soon as you change the code, you'll see a prompt asking you to edit the project name. You can put whatever you want here.

Dev To API in Postman

Next, we want to create a function. This function will get the first empty row of the spreadsheet we just created:

function getFirstEmptyRow() {
    var spr = SpreadsheetApp.getActiveSpreadsheet();
    var column = spr.getRange('A:A');
    var values = column.getValues(); // get all data in one call
    var ct = 0;
    while (values[ct][0] != "") {
    return (ct);
Enter fullscreen mode Exit fullscreen mode

Next, we'll create a function that formats the time to look better. The timestamp from the API is useful, but a bit ugly. This fixes that.

function formatDate(timestamp) {
    var year = timestamp.substring(0, 4);
    var month = timestamp.substring(5, 7);
    var day = timestamp.substring(8, 10);
    var newdate = month + "/" + day + "/" + year;
    return newdate;
Enter fullscreen mode Exit fullscreen mode

You'll notice much of this code is JavaScript. I was able to build this without even looking anything up to see if it would work in Google Sheets. I would guess there are a lot of things you can do here with JavaScript.

Finally, let's drop in the main function that will be getting our stats:

function getStats() {

    var options = {
        "access-control-allow-headers": "Content-Type",
        "api-key": "[YOUR API KEY]"
    var header = {
        'headers': options

    try {
        // make the API call
        var response = UrlFetchApp.fetch("", header);
        // parse output as JSON
        var output = JSON.parse(response.getContentText());
        // grab the spreadsheet tab
        var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('[ TAB NAME ]');

        output.forEach(function(row, index) {
            sheet.getRange(index + 2, 1).setValue(row.title);
            sheet.getRange(index + 2, 2).setValue(formatDate(row.published_timestamp));
            sheet.getRange(index + 2, 3).setValue(row.page_views_count);
            sheet.getRange(index + 2, 4).setValue(row.positive_reactions_count);
            sheet.getRange(index + 2, 5).setValue(row.comments_count);

    } catch (err) {
        throw new Error(err);
Enter fullscreen mode Exit fullscreen mode

Make sure to replace [TAB NAME] with the name of the tab the spreadsheet lives in.

What this function does:

  • Makes the API call
  • Parses the JSON that returns
  • Gets the Active spreadsheet by the name
  • loops through each published article
  • sets a value from each article into a row in Google Sheets

Save the file, then select the function (getStats) from the top and run it:

Dev To API in Postman

The first time you run it, it will ask for your permission:

Dev To API in Postman

Authorize it, and the script will run. It should look something like this:

Dev To API in Postman

Congrats! Now you can sort and filter and do cool spreadsheet stuff.

Create a Button

I wanted to create a button for this so I don't have to go into the script editor every time. It's pretty easy.

Go to Insert and select Drawing:

Dev To API in Postman

Draw some sort of button, and save it.

Dev To API in Postman

Now, it will be inserted into your spreadsheet. Click the three dots in the upper right-hand corner and select "Assign script."

Dev To API in Postman

In the next window, type in getStats and save it.

Now you have a button to refresh it any time you want.

You're Finished!!

So in this tutorial we:

  • Grabbed an API key
  • Tested our Dev.TO url
  • Created a spreadsheet
  • Wrote code to populate the spreadsheet with stats
  • Built a button to update it.

There are a lot of cool things you can do here. You can build charts, perform analysis, etc. All kinds of cool stuff.

You can get the Full source code here.

If you use this, or expand on it let me know what you build! I'm curious to see how the authors on Dev.To use this functionality.

Top comments (4)

alexdesousa profile image
Alex de Sousa

Awesome post! I just implemented my own version to gather the statistics in the way I wanted them and it's so easy!

This is not only a tutorial on how to gather statistics from DEV, but actually how to develop useful scripts for Google Sheets.

Thanks for sharing!

Just a comment: the function getFirstEmptyRow() is not being used in the script at all.

p0onage profile image
Joshua Duxbury

This is pretty handy, we have some data in elastic search and it’s been a manual job generating reports from it. Something like this would be great to pull that elastic search data into a document

phm200 profile image
Peter Miller

Thanks for the post, this is a cool idea. For me at least, the gist is missing some code in getStats() between line 38 and line 40. The inside of the forEach is missing.

jeremycmorgan profile image
Jeremy Morgan • Edited

Thank you! Not sure how those got stripped out, but I fixed it.