Updated for 2017!
Google Sheets has a powerful and versatile set of IMPORT formulas that can import data from other websites.
This article looks at importing data from popular social media channels into a Google sheet, for social network analysis or social media management. If you manage a lot of different channels then you could use these techniques to set up a master view (dashboard) to display all your metrics in one place.
The formulas below are generally set up to return the number of followers (or page likes) for a given channel, but you could adapt them to return other metrics (such as follows) with a little extra work.
Caveats: the websites these formulas retrieve data from frequently change, which means the formulas stop working. I will try to keep this post updated with working versions.
Example workbooks: Each example has a link to an associated Google Sheet workbook, so feel free to make your own copy:
File > Make a copy....
Continue reading The Import Cookbook: How to import social media data into Google Sheets with formulas
Built entirely in Google Sheets, this real-time dashboard extracts social media metrics data for each team, and then ranks each team based on number of followers for that channel. The overall position in the table is based on cumulative scores of rankings.
See the live version on the web here.
Note: I could not find an official YouTube channel for Manchester United, which harmed their position overall. They would likely be in 1st place overall if their YouTube subscriber rank was not dead last.
The data is imported using Google Sheet’s IMPORTXML and IMPORTHTML functions. I’ve written in detail on how to use them to import social media data here.
Interested in learning how this was put together?
Keep your eyes peeled for a how-to blog post coming soon and, hear hear, this will feature as part of the online course I’m currently creating! More details coming soon 🙂
Have you ever wanted to extract a specific piece of information from a website, so you can analyze it in your Google spreadsheet? Turns out, basic web scraping is possible right in your Google spreadsheet.
If you want to import social media data into your Google Sheets, you might also be interested in my follow up post to this one – using the same formulas as below to import popular social media network stats into your Google Sheets.
For example, recently I needed to find out the authors for a long list of blog posts from a Google Analytics report, to identify the star authors pulling in the page views. It would have been extremely tedious to open each link and manually enter each author’s name. Thankfully, there are some techniques available in Google Sheets to do this for us.
Continue reading Using Google Sheets as a basic web scraper