Convergence Happens: 2015

Saturday, August 29, 2015

Perl and Flickr Stats and Google Docs - Updated for Google OAuth2

In this update article of Perl and Flickr Stats and Google Docs, I'll be covering how to change the final program to use Google OAuth2 for accessing the Google Spreadsheet. Please review the previous posts, especially number 4 in the series as I'll be referencing that code to add in specific changes for OAuth2.

The reason for this update is that some time back around May and June of 2015, Google changed the requirement in using their APIs from just username/password to OAuth2. I did not notice this until recently and finally got time to update the access to Google Docs. The one thing I need to add to this program is some error checking so it can email me if an error occurs and I can deal with future issues sooner.

The previous posts cover connecting to Flickr, pulling the data, authenticating to Google Docs and putting the data into the spreadsheet; Perl and Flickr Stats and Google Docs, Perl and Flickr Stats and Google Docs - Part 2, Perl and Flickr Stats and Google Docs - Part 3 and Perl and Flickr Stats and Google Docs - Updated for Google OAuth2. You will want to review the previous articles as they fully document what is happening and the setup for Google OAuth2 and changes to the main program.

The setup for Google OAuth2 is almost like what you would do for the Flickr API Auth. In this case you need to get an account on developers.google.com, Once in, create an OAuth2 ClientID, I selected other rather than Web Application, and you will get a ClientID and a client secret to use in the program to get and save the authorization code. The base code I used was from http://stackoverflow.com/questions/30735920/authenticating-in-a-google-sheets-application and http://www.stackoverflow.dluat.com/questions/31527911/perl-using-netgooglespreadsheets-gives-error-in-libxml.

Enter this code, update your Client ID and client secert, save the program and run it.

#!/usr/bin/perl

# Code to get a web-based token that can be stored
# and used later to authorize our spreadsheet access.

# Based on code from https://gist.github.com/hexaddikt/6738162

#-------------------------------------------------------------------
# To use this code: 
# 1. Edit the lines below to put in your own 
#    client_id and client_secret from Google.  
# 2. Run this script and follow the directions on
#    the screen, which will give step you
#    through the following steps:
# 3. Copy the URL printed out, and paste
#    the URL in a browser to load the page.
# 4. On the resulting page, click OK (possibly
#    after being asked to log in to your Google
#    account).
# 5. You will be redirected to a page that provides
#    a code that you should copy and paste back into the
#    terminal window, so this script can exchange it for
#    an access token from Google, and store the token.  
#    That will be the the token the other spreadsheet access
#    code can use.


use Net::Google::DataAPI::Auth::OAuth2;
use Net::Google::Spreadsheets;
use Storable; #to save and restore token for future use
use Term::Prompt;

# Provide the filename in which we will store the access
# token.  This file will also need to be readable by the
# other script that accesses the spreadsheet and parses
# the contents.

my $session_filename = ".stored_google_access.session";


# Code for accessing your Google account.  The required client_id
# and client_secret can be found in your Google Developer's console
# page, as described in the detailed instruction document.  This
# block of code will also need to appear in the other script that
# accesses the spreadsheet.

# Be sure to edit the lines below to fill in your correct client
# id and client secret!
my $oauth2 = Net::Google::DataAPI::Auth::OAuth2->new(
client_id => 'CLIENT_ID.apps.googleusercontent.com',
client_secret => 'CLIENT_SECRET',
   scope => ['http://spreadsheets.google.com/feeds/'],
);
# We need to set these parameters this way in order to ensure
# that we get not only an access token, but also a refresh token
# that can be used to update it as needed.
my $url = $oauth2->authorize_url(access_type => 'offline',
approval_prompt => 'force');

# Give the user instructions on what to do:
print <<END

The following URL can be used to obtain an access token from
Google.  

1. Copy the URL and paste it into a browser.  

2.  You may be asked to log into your Google account if you
were not logged in already in that browser.  If so, go
ahead and log in to whatever account you want to have
access to the Google doc.

3. On the next page, click "Accept" when asked to grant access.

4.  You will then be redirected to a page with a box in the
left-hand column labeled  "Authorization code".  
Copy the code in that box and come back here.

Here is the URL to paste in your browser to get the code:

$url

END
;

# Here is where we get the code from the user:
my $code = prompt('x', 'Paste the code obtained at the above URL here: ', '', '');
 
 # Exchange the code for an access token:
 my $token = $oauth2->get_access_token($code) or die;

# If we get to here, it worked!  Report success:
print "nToken obtained successfully!n";
print "Here are the token contents (just FYI):nn";
print $token->to_string, "n";

# Save the token for future use:
my $session = $token->session_freeze;
store($session, $session_filename);

print <<END2

Token successfully stored in file $session_filename.
 
 Use that filename in your spreadsheet-access script to
 load the token as needed for access to the spreadsheet data.

END2
   ;

It will produce a URL that you will copy and paste into your browser, taking you to an access approval screen, which you will click Accept on, taking you to a page with a code, which you will copy and enter back to the waiting program and hit return. It will save your authorization information into a file called .stored_google_access.session. You will want to change permissions to 600 and move it to your $HOME directory in Linux so the cron job will have access to it or add the full path into the base program below

Here is the program flickr-google-stats.pl, I'll be walking through the code below.

flickr-google-stats.pl

#!/usr/bin/perl
use strict;
use warnings;
use feature qw(say switch);
use POSIX qw(strftime);
# Note: the order of these three lines is critical
# the Net::Google::Spreadsheets comes after the Flickr
# modules you will get errors but you will get data
# in the spreadsheet
#
# Class::MOP::load_class is deprecated at /usr/local/lib64/perl5/Class/MOP.pm line 69.
# Class::MOP::load_class("Net::Google::Spreadsheets::Spreadsheet") called at /usr/local/share/perl5/Net/Google/DataAPI.pm line 108
# Net::Google::Spreadsheets::spreadsheets(Net::Google::Spreadsheets=HASH(0x22c6868), undef) called at /usr/local/share/perl5/Net/Google/Spreadsheets.pm line 57
# Class::MOP::Class:::around(CODE(0x3e90790), Net::Google::Spreadsheets=HASH(0x22c6868)) called at /usr/local/lib64/perl5/Class/MOP/Method/Wrapped.pm line 158
# Class::MOP::Method::Wrapped::__ANON__(Net::Google::Spreadsheets=HASH(0x22c6868)) called at /usr/local/lib64/perl5/Class/MOP/Method/Wrapped.pm line 87
# Net::Google::Spreadsheets::spreadsheets(Net::Google::Spreadsheets=HASH(0x22c6868)) called at google-input.pl line 16
#

use Net::Google::Spreadsheets;
use Net::Google::DataAPI::Auth::OAuth2;
use Net::OAuth2::AccessToken;
use Storable;

use Flickr::API2;
use Flickr::API2::Request;

my $username = 'Username';
my $apppassword = 'Password';

my $flickrKey = 'FLICKR_KEY';
my $flickrSecretKey = 'FLICKER SECRET KEY';
my $flickrAuthToken = 'FLICKER AUTH TOKEN';

# Ensure you format the Date Column in the spreadsheet for this format
my $date = strftime "%Y-%m-%d", localtime;

print $date . "\n";

my $api = new Flickr::API2({'key'    => $flickrKey,
                             'secret' => $flickrSecretKey,
                             'perms' => 'read'});

my $gtv = $api->execute_method("flickr.stats.gettotalviews",
         {date=>$date,
          auth_token=>$flickrAuthToken}); 

say "Total views is " . $gtv->{stats}->{total}->{views};
my $totalviews = $gtv->{stats}->{total}->{views};

say "Photostream views is " . $gtv->{stats}->{photostream}->{views};
my $photostreamviews = $gtv->{stats}->{photostream}->{views};

say "Photos views is " . $gtv->{stats}->{photos}->{views};
my $photoviews = $gtv->{stats}->{photos}->{views};

say "Sets views is " . $gtv->{stats}->{sets}->{views};
my $setsviews = $gtv->{stats}->{sets}->{views};

say "Collections views is " . $gtv->{stats}->{collections}->{views};
my $collectionsviews = $gtv->{stats}->{collections}->{views};

say "Galleries views is " . $gtv->{stats}->{galleries}->{views};
my $galleriesviews = $gtv->{stats}->{galleries}->{views};

 my $gpp = $api->execute_method("flickr.stats.getpopularphotos",
          {date=>$date,
           auth_token=>$flickrAuthToken});
 
say "Top Photo id is " . $gpp->{photos}->{photo}[0]->{id};
my $topphotoid = $gpp->{photos}->{photo}[0]->{id};
say "Top Photo view is " . $gpp->{photos}->{photo}[0]->{stats}->{views};
my $topphotoviews = $gpp->{photos}->{photo}[0]->{stats}->{views};
say "Top Photo Title is " . $gpp->{photos}->{photo}[0]->{title};
my $topphototitle = $gpp->{photos}->{photo}[0]->{title};
say "https:\/\/www.flickr.com\/photos\/mikebates\/$gpp->{photos}->{photo}[0]->{id}\/";
my $topphotourl = "https:\/\/www.flickr.com\/photos\/mikebates\/$gpp->{photos}->{photo}[0]->{id}\/";

# Get the token that we saved previously in order to authenticate:
my $session_filename = "/home/username/.stored_google_access.session";

# Be sure to edit the lines below to fill in your correct client
# id and client secret!
my $oauth2 = Net::Google::DataAPI::Auth::OAuth2->new(
 client_id => 'CLIENT_ID.apps.googleusercontent.com',
 client_secret => 'CLIENT SECRET',
    scope => ['http://spreadsheets.google.com/feeds/'],
    );

# Deserialize the file so we can thaw the session and reuse the refresh token
my $session = retrieve($session_filename);
 
my $restored_token = Net::OAuth2::AccessToken->session_thaw($session,
 auto_refresh => 1,
 profile => $oauth2->oauth2_webserver,
 );

$oauth2->access_token($restored_token);
# Now we can use this token to access the spreadsheets
# # in our account:
my $service = Net::Google::Spreadsheets->new( 
 auth => $oauth2);

my @spreadsheet = $service->spreadsheets();

# find a spreadsheet by title
  my $spreadsheet = $service->spreadsheet(
     { title => 'Flickr Activity - Start 2015'
     });

# find a worksheet by title
my $worksheet = $spreadsheet->worksheet(
   {title => 'RawStats'
    });

# add a row
   my $new_row = $worksheet->add_row(
       {
        date => $date,
        totalviews => $totalviews,
        photostreamviews => $photostreamviews,
        photoviews => $photoviews,
        setsviews => $setsviews,
        collectionsviews => $collectionsviews,
        galleriesviews => $galleriesviews,
        topphotoid => $topphotoid,
        topphotoviews => $topphotoviews,
        topphototitle => $topphototitle,
        topphotourl => $topphotourl,
      }
    );

Key lines added or changed,

Add lines 20, 21, and 22. They setup up the OAuth2 libraries and the Storable library.
Add lines 78 through 101, they setup access to the saved authorization file, and the connection to Google Drive and the spreadsheet.

Line 100 is the changed line 75 in the original program. If you have everything in place to start adding Flickr data to your Google Spreadsheet.

Let me know if there is anything I missed or could improve upon here.

Saturday, February 21, 2015

Geocaching with the iPhone 5s

Well this weekend, February 14, 2015, I went out Geocaching with an iPhone 5s and the Geocaching app from geocaching.com and here is an assessment of my experience. It was pretty cool and it works with some effort.

My geocaching experience level would be considered novice and I wanted to do something along with my going out to take pictures (something to get me outside even more.) So my first day out I found 5 of 9 that I searched for and second day 7 of 8, though 3 of these were ones I did not find the first day. Like I said, novice level. I had no clue how cache hiders really hid caches and learned the hard way by myself.

Using the Geocaching app was very easy, I select local "easy" to find caches and there were lots of them. The first cache I found ended up being in the knot of a tree and the second a magnetic key-holder on a lamppost. Both of these were relatively easy.

The iPhone 5s as a GPS worked fairly well. I found it would take me directly to the area of the cache and would actually try to take me straight to the cache. I wondered if would take me directly to the point of the cache and it would not. Best I could do was one foot and even then it was off a bit. I did validate the phone would not take me to a cache if there was no service which is what I expected. The approach that finally worked was to try and approach the cache from multiple directions and it would narrow down where I needed to go. Experimenting worked out pretty well.

For the failures I had, well chock that up to inexperience. I had no clue about guard rail caches and with some tips I got from a friend and a bit of research via Google and the geocaching.com site, day 2 was much better with a higher success rate.

One of the things I have learned is that the iPhone GPS can be a bit jittery. It is impacted by metal buildings and overhead power lines. I found using a regular GPS that these things were not a big a factor in reception.

Beautiful day. Cache I found in Longview, WA - GC4RPG8

Huge cache - Longview, WA - GC4RPG8

More things to learn though. Next thing to figure out is how to download GPS information on caches to my GPS and use that instead of the preloaded caches.

You want to get outdoors, geocaching, photography and bird watching will get you there. In the Pacific Northwest you have assume bad weather, rain, fog, mist,and wind.

Thoughts, please comment.

Saturday, January 31, 2015

Perl and Flickr Stats and Google Docs - Part 4

In this final article of Perl and Flickr Stats and Google Docs, I'll be covering the final program that pulls data from Flickr and places that data into a Google Spreadsheet.

The previous posts cover connecting to Flickr, pulling the data, authenticating to Google Docs and putting the data into the spreadsheet; Perl and Flickr Stats and Google Docs, Perl and Flickr Stats and Google Docs - Part 2, Perl and Flickr Stats and Google Docs - Part 3 and Perl and Flickr Stats and Google Docs - Part 4. You will want to review the previous articles as they fully document what is happening below.

Here is the program flickr-google-stats.pl, I'll be walking through the code below.

flickr-google-stats.pl

#!/usr/bin/perl

use strict;
use warnings;
use feature qw(say switch);
use POSIX qw(strftime);
# Note: the order of these three lines is critical
# the Net::Google::Spreadsheets comes after the Flickr
# modules you will get errors but you will get data
# in the spreadsheet
#
# Class::MOP::load_class is deprecated at /usr/local/lib64/perl5/Class/MOP.pm line 69.
# Class::MOP::load_class("Net::Google::Spreadsheets::Spreadsheet") called at /usr/local/share/perl5/Net/Google/DataAPI.pm line 108
# Net::Google::Spreadsheets::spreadsheets(Net::Google::Spreadsheets=HASH(0x22c6868), undef) called at /usr/local/share/perl5/Net/Google/Spreadsheets.pm line 57
# Class::MOP::Class:::around(CODE(0x3e90790), Net::Google::Spreadsheets=HASH(0x22c6868)) called at /usr/local/lib64/perl5/Class/MOP/Method/Wrapped.pm line 158
# Class::MOP::Method::Wrapped::__ANON__(Net::Google::Spreadsheets=HASH(0x22c6868)) called at /usr/local/lib64/perl5/Class/MOP/Method/Wrapped.pm line 87
# Net::Google::Spreadsheets::spreadsheets(Net::Google::Spreadsheets=HASH(0x22c6868)) called at google-input.pl line 16
#

use Net::Google::Spreadsheets;
use Flickr::API2;
use Flickr::API2::Request;

my $username = 'GoogleUsername';
my $apppassword = 'GoogleAPIPassword';

my $flickrKey = 'FlickrAPIKey';
my $flickrSecretKey = 'FlickrSecretKey';
my $flickrAuthToken = 'FlickrAuthToken';

# Ensure you format the Date Column in the spreadsheet for this format
my $date = strftime "%Y-%m-%d", localtime;

print $date . "\n";

my $api = new Flickr::API2({'key'    => $flickrKey,
                             'secret' => $flickrSecretKey,
                             'perms' => 'read'});

my $gtv = $api->execute_method("flickr.stats.gettotalviews",
         {date=>$date,
          auth_token=>$flickrAuthToken}); 

say "Total views is " . $gtv->{stats}->{total}->{views};
my $totalviews = $gtv->{stats}->{total}->{views};

say "Photostream views is " . $gtv->{stats}->{photostream}->{views};
my $photostreamviews = $gtv->{stats}->{photostream}->{views};

say "Photos views is " . $gtv->{stats}->{photos}->{views};
my $photoviews = $gtv->{stats}->{photos}->{views};

say "Sets views is " . $gtv->{stats}->{sets}->{views};
my $setsviews = $gtv->{stats}->{sets}->{views};

say "Collections views is " . $gtv->{stats}->{collections}->{views};
my $collectionsviews = $gtv->{stats}->{collections}->{views};

say "Galleries views is " . $gtv->{stats}->{galleries}->{views};
my $galleriesviews = $gtv->{stats}->{galleries}->{views};

 my $gpp = $api->execute_method("flickr.stats.getpopularphotos",
          {date=>$date,
           auth_token=>$flickrAuthToken});
 
say "Top Photo id is " . $gpp->{photos}->{photo}[0]->{id};
my $topphotoid = $gpp->{photos}->{photo}[0]->{id};
say "Top Photo view is " . $gpp->{photos}->{photo}[0]->{stats}->{views};
my $topphotoviews = $gpp->{photos}->{photo}[0]->{stats}->{views};
say "Top Photo Title is " . $gpp->{photos}->{photo}[0]->{title};
my $topphototitle = $gpp->{photos}->{photo}[0]->{title};
say "https:\/\/www.flickr.com\/photos\/mikebates\/$gpp->{photos}->{photo}[0]->{id}\/";
my $topphotourl = "https:\/\/www.flickr.com\/photos\/mikebates\/$gpp->{photos}->{photo}[0]->{id}\/";

my $service = Net::Google::Spreadsheets->new(
  username => $username,
  password => $apppassword
);

my @spreadsheet = $service->spreadsheets();

# find a spreadsheet by title
  my $spreadsheet = $service->spreadsheet(
     { title => 'Flickr Activity - Start 2015'
     });

# find a worksheet by title
my $worksheet = $spreadsheet->worksheet(
   {title => 'RawStats'
    });

# add a row
   my $new_row = $worksheet->add_row(
       {
        date => $date,
        totalviews => $totalviews,
        photostreamviews => $photostreamviews,
        photoviews => $photoviews,
        setsviews => $setsviews,
        collectionsviews => $collectionsviews,
        galleriesviews => $galleriesviews,
        topphotoid => $topphotoid,
        topphotoviews => $topphotoviews,
        topphototitle => $topphototitle,
        topphotourl => $topphotourl,
      }
    );

Lines 1 through 22, - Line 1, is enables the program to find Linux, the other lines setup the specific modules to use. Note the comments mention an error you will get if you have the last three modules (lines 20-22) loaded out of order.

Lines 23 through 29, are configurable for your Google Docs credentials and Flickr API keys and authentication token.

Lines 31 through 34, grab the current local date and uses it to populate the date column in the spreadsheet. Review previous articles on how this section works.

Lines 36 through 73, further authenticate to retrieve the data you want and pulls that information into program variables to be used to populate the spreadsheet.

Lines 75 through 107, authenticate with Google Docs, connects to the specific spreadsheet and workbook inside the spreadsheet and then adds a row at the end of the workbook and populates with the data. The data goes into a workbook for the raw data (RawStats) and the primary workbook uses VLOOKUP to retrieve the information by date to fill in the columns there.

Here is the data in the RawStats workbook:

You see here the data filled into the primary workbook:

Section of workbook with data loaded from RawStats workbook using VLOOKUP

Here is the VLOOKUP command I used:

=iferror(vlookup(A30,RawStats!$A$2:$K$1005,3,false),0)

The iferror basically enables me to set the value to 0 if there is no data for the date.

Configure flickr-google-stats.pl to run via Cron
Setting the run of the program in cron on a Linux system. This is fairly easy is you know vi. First copy the program from your development directory to your bin directory /home/username/bin directory and set the permissions on the file to be executable. I use chmod 700 to set permissions so that only the user can run the program not other people.

[username@murdock flickr]$ 
[username@murdock flickr]$ cp flickr-
flickr-auth.pl              flickr-popular.pl           flickr-test.pl
flickr-google-stats-nop.pl  flickr-stats.pl             
flickr-google-stats.pl      flickr-test2.pl             
[username@murdock flickr]$ cp flickr-google-stats.pl /home/username/bin/
[username@murdock flickr]$ chmod 700 /home/username/bin/flickr-google-stats.pl 
[username@murdock flickr]$ ls -l /home/username/bin/flickr-google-stats.pl 
-rwx------. 1 username username 4096 Jan 31 12:40 /home/username/bin/flickr-google-stats.pl

Once this is done you can execute the command crontab -e as the user to edit the user's crontab file. (This assumes you have setup the user to use cron, which is outside the scope of this article and there are several good articles if you Google "cron linux setup".

1	[username@murdock flickr]$ crontab -e

Now enter the line 1 below into the crontab file. Note the /dev/null can be changed to something like /home/username/logs/flickr-google-stats.log, ensure you have the logs directory created. I recommend once you know the program is executing changing this back to /dev/null which will dump the output to the bit bucket.

  15    17    *     *     * /home/username/bin/flickr-google-stats.pl >/dev/null 2>&1
# *     *     *     *     *  command to be executed
# -     -     -     -     -
# |     |     |     |     |
# |     |     |     |     +----- day of week (0 - 6) (Sunday=0)
# |     |     |     +------- month (1 - 12)
# |     |     +--------- day of month (1 - 31)
# |     +----------- hour (0 - 23)
# +------------- min (0 - 59)

Hope you enjoyed this set of articles and enables you to use Perl, Flickr and Google Docs.

Your thoughts and comments appreciated.

Saturday, January 24, 2015

Perl and Flickr Stats and Google Docs - Part 3

In this part of Perl and Flickr Stats and Google Docs, I'll be covering creating and connecting to a Google Spread so we can put the data gathered in the previous posts Perl and Flickr Stats and Google Docs and Perl and Flickr Stats and Google Docs - Part 2.

In part 2, I used the program flickr-stats.pl to gather the statistics from Flickr that I wanted to store in a Google Spreadsheet to do this I used the Perl Module (Net-Google-Spreadsheets).

Based on the article Write to a Google Spreadsheet with a Perl Script I learned that you need to pre-create the spreadsheet before using the program. The Perl module cannot create a spreadsheet but it can create workbooks within a spreadsheet if you need that functionality.
>Create a spreadsheet called FlickrStats and enter column names of the stats and information you want to gather. Here I want the date, total views, photostream views, photo views, sets views, collections views, galleries views, the top photo id, top photo views, top photo title and the top photo URL. This will help to add rows into the spreadsheet each day the program is run.

Snapshot of pre-created spreadsheet with headers.

Next, we need to be able to connect and authenticate to Google Docs to access a spreadsheet. Since I use the Google 2-step verification, I could not just use my Google account password, I had to use a Google Apps Password. If you have 2-step verification turned on to get a Google Apps password, sign into your Google Accounts setting page, click on Account and scroll down the App Passwords. You can use this Google Help article to get to your App Passwords - Sign in using App Passwords.

Code used to connect and authenticate to Google.

my $service = Net::Google::Spreadsheets->new(
  username => 'googleID',
  password => 'GoogleAppsPassword'
);

Next you will want access your spreadsheet and get to the specific workbook where you want to put your data.

my @spreadsheet = $service->spreadsheets();

# find a spreadsheet by title
   my $spreadsheet = $service->spreadsheet(
       {
         title => 'FlickrStats'
       }
   );

# find a worksheet by title
   my $worksheet = $spreadsheet->worksheet(
       {
          title => 'Sheet1'
       }
    );

Line 1 creates the access identifier to the spreadsheet. Lines 4-8, finds and opens your spreadsheet. and lines 11-15 provide the access to your worksheet.

One thing I noticed was that the process of finding/opening your spreadsheet that it does not care what folder in your Google Drive the spreadsheet resides in. In this case, I had created a folder called "Murdock" (which is the machine where I am running the Perl programs and I want its output in one place rather than the root folder of Google Drive) and it finds the spreadsheet each time without an issue.

Here is the code that writes the data out to the workbook. This adds a row to the end of the workbook.

my $date = '2015-01-24';
my $totalviews = 100;
my $photostreamviews = 10;
my $photoviews = 3;
my $setsviews = 33;
my $collectionsviews = 3;
my $galleriesviews = 15;
my $topphotoid = 8758252700;
my $topphotoviews = 9;
my $topphototitle = '20130501-2138-0168-untitled_HDR';
my $topphotourl = 'https://www.flickr.com/photos/mikebates/8758252700/';

# add a row
   my $new_row = $worksheet->add_row(
       {
        date => $date,
        totalviews => $totalviews,
        photostreamviews => $photostreamviews,
        photoviews => $photoviews,
        setsviews => $setsviews,
        collectionsviews => $setsviews,
        galleriesviews => $galleriesviews,
        topphotoid => $topphotoid,
        topphotoviews => $topphotoviews,
        topphototitle => $topphototitle,
        topphotourl => $topphotourl,
      }
    );

Here is a snapshot of the spreadsheet with the row added to it.

FlickrStats spreadsheet with data.

And here is the complete code for connecting to the FlickrStats spreadsheet and adding data to the workbook.

google-input.pl

use strict;
use warnings;
use Net::Google::Spreadsheets;
#use Net::Google::AuthSub;
use Data::Dumper;

my $username='Google Account';
my $apppassword='Google App Password';

my $service = Net::Google::Spreadsheets->new(
  username => $username,
  password => $apppassword
);

my @spreadsheet = $service->spreadsheets();

# find a spreadsheet by title
   my $spreadsheet = $service->spreadsheet(
       {
         title => 'FlickrStats'
       }
   );

# find a worksheet by title
   my $worksheet = $spreadsheet->worksheet(
       {
          title => 'Sheet1'
       }
    );

my $date = '2015-01-24';
my $totalviews = 100;
my $photostreamviews = 10;
my $photoviews = 3;
my $setsviews = 33;
my $collectionsviews = 3;
my $galleriesviews = 15;
my $topphotoid = 8758252700;
my $topphotoviews = 9;
my $topphototitle = '20130501-2138-0168-untitled_HDR';
my $topphotourl = 'https://www.flickr.com/photos/mikebates/8758252700/';

# add a row
   my $new_row = $worksheet->add_row(
       {
        date => $date,
        totalviews => $totalviews,
        photostreamviews => $photostreamviews,
        photoviews => $photoviews,
        setsviews => $setsviews,
        collectionsviews => $setsviews,
        galleriesviews => $galleriesviews,
        topphotoid => $topphotoid,
        topphotoviews => $topphotoviews,
        topphototitle => $topphototitle,
        topphotourl => $topphotourl,
      }
    );

The next and final article in the series will bring everything together accessing Flickr and adding the data to the FlickrStat Spreadsheet. I'll also be streamlining the code a bit and using variables to hold authentication information to make the code more flexible.

Your thoughts and comments appreciated.

Friday, January 23, 2015

Perl and Flickr Stats and Google Docs - Part 2

In this part of Perl and Flickr Stats and Google Docs, I'll be covering the code at the end of part 1 of Perl and Flickr Stats and Google Docs.

Update 2015-01-24: It should be noted the date that Flickr uses is based on UTC time. So the new Flickr day in the State of Washington is either 4pm or 5pm depending whether you are in Daylight Savings or Standard time. Dealing with this situation is fairly easy, set the program to run at 5:05pm and use the date for your local time zone (this works pretty well for time zones west of UTC) and it will still be the date of the day your want statistics. It might be better to use UTC time which is not impacted by Daylight Savings time and can work regardless of time zone.

flickr-stats.pl

use feature qw(say switch);
use Data::Dumper;
use POSIX qw(strftime);
use Flickr::API2;
use Flickr::API2::Request;
use Net::Google::Spreadsheets;

my $date = strftime "%Y-%m-%d", localtime;
print $date . "\n";

  my $api = new Flickr::API2({'key'    => '<<Flickr API Key>>',
                             'secret' => '<<Flickr Secret Key>>',
                             'perms' => 'read'});

  my $gtv = $api->execute_method("flickr.stats.gettotalviews",
         {date=>$date,
          auth_token=>'<<Flickr Auth Token>>'}); 

#print Dumper($gtv);

say "Total views is " . $gtv->{stats}->{total}->{views};
say "Photostream views is " . $gtv->{stats}->{photostream}->{views};
say "Photos views is " . $gtv->{stats}->{photos}->{views};
say "Sets views is " . $gtv->{stats}->{sets}->{views};
say "Collections views is " . $gtv->{stats}->{collections}->{views};
say "Galleries views is " . $gtv->{stats}->{galleries}->{views};

 my $gpp = $api->execute_method("flickr.stats.getpopularphotos",
          {date=>$date,
           auth_token=>'<<Flickr Auth Token>>'});
 
# print Dumper($gpp);
 
say "Top Photo id is " . $gpp->{photos}->{photo}[0]->{id};
say "Top Photo view is " . $gpp->{photos}->{photo}[0]->{stats}->{views};
say "Top Photo Title is " . $gpp->{photos}->{photo}[0]->{title};
say "https:\/\/www.flickr.com\/photos\/mikebates\/$gpp->{photos}->{photo}[0]->{id}\/";

Lines 1-6: Setup the use of Perl libraries and features. I had to download the Flickr::API2 and Net::Google::Spreadsheets libraries from CPAN.

Lines 8-9: Grabs the date from localtime formatted in my preferred format of YYYY-MM-DD.

Lines 11-13: Connect and authenticate to Flickr using your API Key and Secret Key.

Lines 15-17: Use the Flickr::API2 method execute-method flickr.stats.getotalviews using the date and your Auth Token from part one of this series.

Line 19: I used to find the format of the hash and what the values looked like so I could print them out in lines 21-26.

Lines 21-26: Prints out the data I want to load into my Flickr Stats spreadsheet on Google Drive.

Lines 28-30: Use the Flickr::API2 method execute-method flickr.stats.getpopularphotos using the current date and the Auth Token again. What I want to get from this method is the first photo of the photos viewed that day.

Lines 34-37: Prints out the data I want to load as well into the spreadsheet.

Output:
[flickr]$ perl flickr-stats.pl

2015-01-17
Total views is 243
Photostream views is 22
Photos views is 219
Sets views is 2
Collections views is 0
Galleries views is 0
Top Photo id is 15635946094
Top Photo view is 4
Top Photo Title is 20150111-1338-00028-Island Drive Walkabout-2
https://www.flickr.com/photos/mikebates/15635946094/

Next I'll work on connecting to Google Docs and authenticating and then adding rows to the spreadsheet. These will be coming in the next two weeks in parts 3 and 4 of the series.

Your thoughts and comments appreciated.

Saturday, January 17, 2015

Perl and Flickr Stats and Google Docs

I have a Google Doc spreadsheet that I use to track my Flickr stats every day. The Flickr stats only stay online for 30 days so I have wanted to what my stats look like over a longer period of time. So I figured that it would be nice to automate getting the statistics from Flickr. Sample of the types of statistics I capture below.

I decided on using Perl after I found it had both a Flickr (Flickr-API2) and Google Spreadsheet (Net-Google-Spreadsheets) module. I am assuming you know how to download and install Perl modules.

Both Flickr and Google Docs require authentication to access and I found that to be the toughest part to configure. Also, for Flickr you will need to get an API key as well as work through the authentication process. For the Flickr authentication process I used the article Creative Commons, Flickr and presentations: A bit of tinkering | The Weblog of (a) David Jones with some modifications.

Once you have your API Key and Secret Key from Flickr you need to get a "frob" so can use all the features of Flickr-API2.

Here is the code I used to get the "frob" and grant permissions to the app.

flickr-auth.pl

use feature qw(say switch);
use Data::Dumper;
use Flickr::API2;

my $api = new Flickr::API2({'key'    => '<<your FLICKR API KEY HERE>>',
                            'secret' => '<<your FLICKR SECRET KEY HERE>>'});

# Get the Frob
my $result = $api->execute_method( 'flickr.auth.getFrob' );
my $frob = $result->{frob}->{_content};

# Get a special URL to tell Flickr to authorize the script
my $url = $api->raw->request_auth_url( 'delete', $frob );
print Dumper( $url );
# wait until I visit the URL and hit enter (change URL to https)
<stdin>;


 my $res = $api->execute_method( 'flickr.auth.getToken', { 'frob' => $frob} );
 print Dumper( $res )

When you run this from the command line, you will get something like the following:

[flickr]$ perl flickr-auth.pl
$VAR1 = bless( do{\(my $o = 'http://api.flickr.com/services/auth/?api_sig=<<your-flickr-api-sig>>&frob=<<flick-your-fob>>0&perms=delete&api_key=<<you-flick-key>>')}, 'URI::http' );

Note The program stops here waiting for a response. Do not hit return here until you have gone through the next couple of steps.

Copy out the http line and place this in your browser, before hitting return in your webpage, change the http to https. You should get a page that looks like this, if you wish to continue, press the "OK, I'll Authorize it" button.

And you should get the response below:

At this point go back to your Perl session and hit return.

$VAR1 = {
          'auth' => {
                      'user' => {
                                  'fullname' => '<<Your Flickr Name>>',
                                  'nsid' => '<<Flicr User ID>>',
                                  'username' => '<<Flickr User Name>>'
                                },
                      'perms' => {
                                   '_content' => 'delete'
                                 },
                      'token' => {
                                   '_content' => '<<Flickr Auth Token>>'
                                 }
                    },
          'stat' => 'ok'
        };

You will need the Flickr Auith Token for your Perl program. Remember to keep the Auth Token and you Secret Key secure.

Below is a sample of using the API once you have gotten past the authentication process. I'll cover this in the next post.

flickr-stats.pl

use feature qw(say switch);
use Data::Dumper;
use POSIX qw(strftime);
use Flickr::API2;
use Flickr::API2::Request;
use Net::Google::Spreadsheets;

my $date = strftime "%Y-%m-%d", localtime;
print $date . "\n";

  my $api = new Flickr::API2({'key'    => '<<Flickr API Key>>',
                             'secret' => '<<Flickr Secret Key>>',
                             'perms' => 'read'});

  my $gtv = $api->execute_method("flickr.stats.gettotalviews",
         {date=>$date,
          auth_token=>'<<Flickr Auth Token>>'}); 

#print Dumper($gtv);

say "Total views is " . $gtv->{stats}->{total}->{views};
say "Photostream views is " . $gtv->{stats}->{photostream}->{views};
say "Photos views is " . $gtv->{stats}->{photos}->{views};
say "Sets views is " . $gtv->{stats}->{sets}->{views};
say "Collections views is " . $gtv->{stats}->{collections}->{views};
say "Galleries views is " . $gtv->{stats}->{galleries}->{views};

 my $gpp = $api->execute_method("flickr.stats.getpopularphotos",
          {date=>$date,
           auth_token=>'<<Flickr Auth Token>>'});
 
# print Dumper($gpp);
 
say "Top Photo id is " . $gpp->{photos}->{photo}[0]->{id};
say "Top Photo view is " . $gpp->{photos}->{photo}[0]->{stats}->{views};
say "Top Photo Title is " . $gpp->{photos}->{photo}[0]->{title};
say "https:\/\/www.flickr.com\/photos\/mikebates\/$gpp->{photos}->{photo}[0]->{id}\/";

Output:
[flickr]$ perl flickr-stats.pl

2015-01-17
Total views is 243
Photostream views is 22
Photos views is 219
Sets views is 2
Collections views is 0
Galleries views is 0
Top Photo id is 15635946094
Top Photo view is 4
Top Photo Title is 20150111-1338-00028-Island Drive Walkabout-2
https://www.flickr.com/photos/mikebates/15635946094/