This April Only: Save 30% on PRO with code ROCKETSCIENCE »

Real-World Education for Modern Marketers

Join Over 624,000 Marketing Professionals

Start here!
N E X T
Text:  A A

Three Awesome Google Spreadsheet Hacks for Marketers

by   |    |  20,894 views

In this article, you'll learn...

  • Google Spreadsheets tips all marketers should know
  • How to use Functions and Scripts, the Scraper tool, and RSS feeds in Google Spreadsheets

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)

Google's RELATED: search results  (via)

Built-in

=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.

Facebook LIKE Analytics

FB Like Counter

=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

Easy Reputation Management Spreadsheet

PHP Climber

*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!


Join over 624,000 marketing professionals, and gain access to thousands of marketing resources! Don't worry ... it's FREE!

WANT TO READ MORE?
SIGN UP TODAY ... IT'S FREE!

We will never sell or rent your email address to anyone. We value your privacy. (We hate spam as much as you do.) See our privacy policy.

Sign in with one of your preferred accounts below:

Loading...
Ann Smarty is a search and social enthusiast and professional blogger. One of her recent major projects is MyBlogGuest, a free platform connecting blog owners to guest authors for mutual benefit.

Rate this  

Overall rating

  • This has a 5 star rating
  • This has a 5 star rating
  • This has a 5 star rating
  • This has a 5 star rating
  • This has a 5 star rating
2 rating(s)

Add a Comment

Comments

  • by Amy Thu Mar 8, 2012 via web

    I LOVE THIS

  • by Ann Smarty Thu Mar 8, 2012 via web

    Thanks, Amy! I am glad to hear you like it!

  • by stlubahn Thu Mar 8, 2012 via web

    Amazing, was not aware you could do all this with Google Apps!

  • by Corey Mon Mar 19, 2012 via web

    Ann, you are a rock star. Thanks for the post.

  • by Ann Smarty Tue Mar 20, 2012 via web

    Thanks, Corey! I am happy to hear it looks useful!

  • by Bill Thu Feb 20, 2014 via web

    Amazing, Ann. Thanks for sharing. Look forwards to your other helpful sharing stuffs.

MarketingProfs uses single
sign-on with Facebook, Twitter, Google and others to make subscribing and signing in easier for you. That's it, and nothing more! Rest assured that MarketingProfs: Your data is secure with MarketingProfs SocialSafe!