Using AppleScript to automate the exporting of Google Analytics information

Date: Nov 01, 2006
Title: AppleScript is AppleRiffic
Comments: 2
Return to article listing

AppleScript is AppleRific

Finally, Safari becomes useful.

Six months after making the switch to Apple, a lonely Cari.net web guy discovers AppleScript to automate a tedious task: Logging into Google Analytics and exporting a specific report, then emailing it, every day.

So I’m still thrilled with my recent discovery of AppleScript. I’m even more thrilled that with very poor programming skills, I was able to accomplish something that will give the appearance that I never, ever forget to send our daily web stats for the daily sales reporting.

The order that came down from our VP of Sales & Marketing, Chris Orlando, was that we needed to email some Google Analytics info, and that this email needed to happen every day, for ever and ever and ever.

In Safari: Open the login form (not the main login page, but the form itself, which resides in an iframe on the main login page) to log into Google Analytics.
1. Enter the user and pass.
2. Submit the form to login.
3. Click on the right link to go to the cari.net stats.
4. Determine today’s date.
5. Determine yesterday’s date.
6. Use that info to select yesterday’s date in the analytics calendar.
7. Export a CSV of yesterday’s stats.
In Finder: Find the exported file.
In Mail.app: Attach and email the new file to jim.
In Finder: Move the file from the desktop to a backup directory.

(Steps 2-4 were actually done in Automator. It was just easier and faster, and it works.)

So after I got the script working (which took a bit, as I’d never used AppleScript before), there was just the matter of putting it in the crontab. As a lifelong Windows user, cron stuff isn’t a strength, so Cronnix came to the rescue. (Make sure you call the script like this: “osascript /path/to/your/script.scpt”.)

The End Result

AppleScript, which triggers an Automator workflow (saved as an application), set to run every morning at 3:30am. Absolutely beautiful.

The Code

Disclaimer: Some of this code was taken from forums

tell application "Safari"
    -- go to appropriate URL to login
    open location "https://www.google.com/accounts/ServiceLoginBox?service=analytics&nui=1&hl=&continue=http://www.google.com/analytics/home/%3Fet%3Dreset%26hl%3D"
    delay 1

    -- fill in the form with User ID and password
    do JavaScript "document.forms[0].Email.value = \"" & "email@domain.tld\"" in document 1
    do JavaScript "document.forms[0].Passwd.value = \"" & "password\"" in document 1

    -- now click the Login button
    do JavaScript "document.forms[0].submit()" in document 1
    delay 6

    -- go to the appropriate page
    set URL of document 1 to "PUT THE LINK TO YOUR SITE HERE"
    delay 6

    -- set the current date, and figure out yesterday's date
    set yesterdayTemp to (current date) - (60 * 60 * 24)
    set yesterdayDate to day of yesterdayTemp
    do JavaScript "ucalDayToggle(" & yesterdayDate & ",0);" in document 1
    delay 3

    -- download the CSV file
    do JavaScript "javascript:urExport(3);" in document 1

end tell

tell application "Safari"
    -- close the tab
    activate

    tell application "System Events"
        click menu item ?
            "Close Tab" of ((process "Safari")'s (menu bar 1)'s ?
            (menu bar item "File")'s (menu "File"))
    end tell
end tell

tell application "Finder"
    open file "Chuck Norris:path:to:script:Mail the Google Analytics to Jim.app"
end tell

“Chuck Norris” is the name of my hard drive, in case anybody’s curious.

Again, steps 2-4 above are in Automator. The Automator workflow is:

Find Finder Items
New Mail Message
Send Outgoing Messages
Find Finder Items
Move Finder Items
Move to Trash

Known Issues

The only item that I’ve had a little trouble with is telling Safari to close the top tab after it’s done. Sometimes it works, but sometimes the download box pops up and nothing gets closed. Not a huge issue.

Also, make sure you have “Enable Access for Assistive Devices” checked in your Universal Access system prefs, or the “click menu item” command won’t work.

And that’s all I know.
Over and out.
-Kelley, Web Developer for Cari.net

UPDATE:
If your computer is not always on, use Anacron to make sure your cron jobs don’t get skipped when sleeping. Anacron for OS X