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 DocsPerl and Flickr Stats and Google Docs - Part 2Perl 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

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
#!/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.

1
2
3
4
5
6
7
8
9
[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.

1
2
3
4
5
6
7
8
9
  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.