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.


1
2
3
4
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. 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
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.


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


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