Monday, January 26, 2009

Export Google Trends to Google Docs Script

Feeling inspired from the success of the script that I wrote to export from Google Analytics to Google Docs , I decided to see if I could reuse most of the code for other Google CSV exports.  My first sub-project was to tackle Google Trends.

I made the necessary front end changes to put in the new links and collect the correct export URLs.  Generally everything worked on the backend as well after only a few minor tweaks.  However, I kept receiving a lot of junk in the export and the content was not legible.

There seems to be some issue with the format of the CSV file being delivered by Google Trends.  It opens fine is a pure text editor like Notepad++ , but the browser recognized thousands of "invalid" characters in the export.  Basically, the CSV contains a null value in between every other valid character and it included rare form feed characters at the beginning and end of the file.  These invisible characters were blowing up the import to Google Docs so that while I would receive a "success" message for the upload, everything came out as gobbledegoop .

After multiple attempts to try to request a different file format, I finally decided to work a little string magic using RegEx.  I stripped out all of the junk characters before sending it up to Google Docs and voila! Everything works fine now.

Unfortunately, Google Trends only allows exports for keyword comparisons at the moment, but not for website data.  In the meantime though, for anyone doing SEO Keyword research, this could be of some valuable use to you.

Here are the links:
Download GM script directly
Greasemonkey Script Page

Enjoy.

3 comments:

  1. Hi! I'm staring an unofficial Google Trends API documentation Wiki, and
    I just wanted to let you know I am including your greasemonkey script as part of it. Just letting you know in case you want to add any documentation.

    It is located here:

    http://zoastertech.com/googletrends/

    And the reference to your script is here:

    http://zoastertech.com/googletrends/?page=Third+party+API+Tools

    Thanks!

    ReplyDelete
  2. Hi,
    I tried to use the script but got error 403 forbidden when uploading to Google docs. Then I got a window to fill in password. I filled in the password and hit the retry button but got the same error again.
    I have now tried several times and uninstalled the script a couple of times also.
    Is this a known problem ?

    ReplyDelete
    Replies
    1. I have not continued to update the script. I suppose Google has changed their API for uploading or their method for authentication. Feel free to modify the script.

      Delete