Among the best Google products I love playing with are Google Docs, especially Google Spreadsheets. They are feature-rich, highly flexible, reliable, and, on top of that, absolutely free.
I've been using Google Spreadsheets for ages... for campaign tracking, reporting, automating, and aggregating some of the most complex search engine optimization (SEO) and social media tasks.
In this article, I'll share the three most useful, yet easy-to-implement, Google spreadsheet tips any online marketer would find useful.
1. Google Spreadsheet Functions and Scripts
The beauty of Google Spreadsheets is its flexibility. You can be as creative as you want (or, at least, as "your geekiness" permits). If you are not ready to spend hours trying to figure out Google functions and Google scripts, here's a quick look at the three most interesting ones:
|
Script* / function |
What does it do? |
Google spreadsheet to demonstrate it |
Courtesy / further instructions |
|
=importxml("URL"; "xPath") |
Works great for scraping any Web data (e.g., search results) |
||
|
=FacebookLikes(URL) |
Counts Facebook "Likes," shares, and comments for any URL. It's great to use in combination with =importxml function to automatically extract social media interaction for the scraped search results. |
||
|
=tweetMemeCount(URL) |
Counts tweets for any URL (based on Tweetmeme). It's great to use in combination with =importxml function to automatically extract social media interaction for the scraped search results |
*Though functions will work as soon as you copy-paste into the cell, a script requires additional effort. To install a new script to your Google spreadsheet, do the following:
- Go to Tools > Script gallery.
- Find the script, and click "Install."
Or, if the script you are using is not in the gallery, do the following:
- Go to Tools > Script manager.
- Click "New."
- Copy-paste the script code there.
- Click "Save."
In both cases, the subsequent steps are the following:
- Go to Tools > Script manager.
- Select the script, and click "Run" to authorize it.
If any of the scripts are not working or show a "too many connections" error message, go to the script manager, click "edit" the script, and "save" in the new window. Resaving a script forces it to rerun the requests.
2. Scraping Anything to Google Spreadsheets Is Easy
To use the Scraper tool, you'll need the Google Chrome browser installed on your computer. (I am pretty sure a Firefox version of Scraper does not exist.) Using Google Chrome, install the Scraper extension.
Scraper is the easiest tool for data mining that I am aware of; it grabs any data from the page and imports it into Google Spreadsheets. The tool is invaluable when you want to analyze and structure (or restructure) text, huge link lists, etc. Scraper works well for importing Google search results.
To use Scraper, just highlight any part of the page you want to import into Google Spreadsheets, and select "Scrape similar" from the context menu:
There you go!
Now, use the scripts and functions to play with the data you have exported.
Note: If you are planning to use Scraper to analyze search results, be mindful that Google Instant shows the default 10 results per page. If you want to export more results, you'll have to disable Google Instant and choose up to 100 results per page via "Search Settings" in the top-right corner of your screen.
Note: Though this method is so much easier than using the =importxml function (see item No. 1), the results from this method won't be updated each time you open the document. (The =importxml function allows you to use the spreadsheet to search!).
3. Aggregating RSS Feeds
Using spreadsheets as an RSS reader provides a few huge benefits:
- Spreadsheets can arrange the news in tables (which saves plenty of time going through the updates).
- Spreadsheets have plenty of cool features built in (e.g., charts, color coding).
- Spreadsheets have plenty of scripts developed that can add some interesting data to your updates (we have already looked at some of them above).
You can get your copy here. Just make a copy of that document (via File > Make a copy), and change the feed URLs in the yellow cells to update the spreadsheet with your feed details.
Here are a few bright ideas on using the aggregator:
- Get focused. Collect the feeds of your very favorite blogs.
- Monitor reputation. Create RSS feeds using Google Alerts, Twitter search, Google News, and unite them here.
- Color-code. Use the "Conditional formatting" option to color-code news by interest, name, etc., and you'll instantly see your favorite topics as the RSS feed loads.
The spreadsheet is courtesy of this helpful tutorial.
* * *
Do you love playing with Google Spreadsheets? Share your tips in the comments!
