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

  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
108
109
#!/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


  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
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
#!/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.