GoogleSpreadSheet.pm

# vim: set cin sw=2:
use  strict;
use  Crypt::OpenSSL::RSA;
use  Data::Dumper;
use  LWP::UserAgent;
use  XML::LibXML;
use  HTTP::Request;
use  FindBin;

package  GoogleSpreadSheet;
use  JSON;
use  MIME::Base64;
use  constant PEM_FILE => 'google-private.pem';
use  constant WS_URI => 'https://spreadsheets.google.com/feeds/worksheets/{key}/private/full';
use  constant CELLS_URI => 'https://spreadsheets.google.com/feeds/cells/{key}/{ws}/private/basic';
use  constant CELL_URI => 'https://spreadsheets.google.com/feeds/cells/{key}/{ws}/private/full/{cell}';
use  constant BATCH_URI => 'https://spreadsheets.google.com/feeds/cells/{key}/{ws}/private/full/batch';
use  constant WS_ADD_URI => 'https://spreadsheets.google.com/feeds/worksheets/{key}/private/full';

sub  new($){
  my $class = shift;
  die 'Must declare your Google certificate id on enviroment ($ENV{GOOGLE_CLIENT_ID} = \'{project}\'' 
    unless $ENV{GOOGLE_CLIENT_ID};
  my $self = bless({ }, $class);
  $self->spreadsheet( shift );
  return $self;
}

sub  spreadsheet(){
  my $self = shift;
  return $self->{spreadsheet} if( scalar(@_) == 0 );
  delete $self->{worksheets};
  delete $self->{_ua};
  my $sh = shift;
  if( $sh =~ /^http/ ){
    $sh =~ /key\=([^\&]+)\&/;
    $sh = $1;
  }
  return $self->{spreadsheet} = $sh;
}

sub  _get_id{
  my $id = shift;
  my @parts = split /\//, $id;
  return $parts[-1];
}

sub  worksheets(){
  return  keys %{shift->_ws()};
}

sub  cell($$;$){
  my( $self, $ws, $cell, $value ) = @_;
  if( scalar( @_ ) == 4 ){
    return $self->_setcell( $ws, $cell, $value );
  }
  if(ref($cell) eq 'HASH' ){
    return $self->_setcellhash( $ws, $cell );
  } else {
    my $c = $self->_cells( $ws );
    return undef unless exists $c->{$cell} ;
    return $c->{$cell}->{value};
  }
}

# Return a hash from each cell from a Worksheet
sub  cells($){
  my  $c = shift->_cells( shift );
  my  %ret = ();
  foreach my $cell( keys %$c ){
    $ret{$cell} = $c->{$cell}->{value};
  }
  return  \%ret;
}

# Return true if the parameter is a valid worksheet;
sub  is_worksheet($){
  my $self = shift;
  my $ws = shift;
  my $sws = $self->_ws();
  return exists $sws->{$ws};
}

# Add a worksheet
#   my $worksheet_id = $spreadsheet->add_worksheet( $title, $rows, $columns );
sub  add_worksheet($$$){
  my ( $self, $title, $rows, $cols ) = @_;

  # Build atom
  my $atom = XML::LibXML::Document->new( '1.0' );
  my $entry = $atom->createElement( 'entry' );
  $entry->setAttribute( 'xmlns', "http://www.w3.org/2005/Atom" );
  $entry->setAttribute( 'xmlns:gs', "http://schemas.google.com/spreadsheets/2006" );
  my $element = $atom->createElement( 'title' );
  $element->appendText( $title );
  $entry->addChild( $element );
  $element = $atom->createElement( 'gs:rowCount' );
  $element->appendText( $rows );
  $entry->addChild( $element );
  $element = $atom->createElement( 'gs:colCount' );
  $element->appendText( $cols );
  $entry->addChild( $element );

  my $ws_uri = WS_ADD_URI;
  my $sh = $self->spreadsheet;
  $ws_uri =~ s/\{key\}/$sh/;
  warn "WS Add uri is $ws_uri" if $ENV{DEBUG};
  warn $entry->toString();

  my $req  = HTTP::Request->new( 'POST' );
  $req->uri( $ws_uri );
  
  $req->header( 'Content-type' => 'application/atom+xml' );
  $req->header( 'Authorization' => 'OAuth ' . $self->_access_token );
  $req->header( 'GData-Version' => '3.0' );
  $req->content( $entry->toString() );
  my $res = LWP::UserAgent->new->request( $req );
  if( $res->status_line =~ /^401/ ){
    $self->_refresh_ua();
    $res = LWP::UserAgent->new->request( $req );
  }
  die 'on add worksheet: ' . $res->status_line unless $res->is_success;

  # Decode
  my $root = XML::LibXML->new()->parse_string( $res->decoded_content() )->getDocumentElement;
  $self->_ws;  # Refresh worksheet if did'nt read
  return $self->_analyze_ws_node( $root )->{ws_id};
}

# Return hash or worksheets
sub  _ws(){
  my $self = shift;
  return  $self->{worksheets} if exists $self->{worksheets};

  warn 'Getting ' . $self->_ws_uri() if $ENV{DEBUG};
  my $res = $self->_ua()->get( $self->_ws_uri() );
  $res = $self->_refresh_ua()->get( $self->_ws_uri() ) if $res->status_line =~ /^401/;
  die 'on get worksheets: ' . $res->status_line unless $res->is_success;

  my $root = XML::LibXML->new()->parse_string( $res->decoded_content() )->getDocumentElement;

  $self->{worksheets} = {};
  my @nodes = $root->getElementsByTagName( "entry" );
  foreach my $node( @nodes ){
    $self->_analyze_ws_node($node);
  }
  return $self->{worksheets};
}

sub  _analyze_ws_node($){
  my ( $self, $node ) = @_;
  my $title = $node->getElementsByTagName( "title" )->shift->textContent;
  my $id    = $node->getElementsByTagName( "id" )->shift->textContent;
  my $rows  = $node->getElementsByTagName( "gs:rowCount" )->shift->textContent;
  my $cols  = $node->getElementsByTagName( "gs:colCount" )->shift->textContent;
  my $upd   = $node->getElementsByTagName( "updated" )->shift->textContent;
  my $wsid  = _get_id( $id );
  $self->{worksheets}->{$wsid} = { 
    title => $title, id => $id, rows => $rows, cols => $cols, updated => $upd, ws_id => $wsid 
  };
  return  $self->{worksheets}->{_get_id($id)};
}

# Return all cells for a worksheet
sub  _cells($){
  my $self = shift;
  my $ws = shift;
  die 'Worksheet ' . $ws . ' is invalid for this spreadsheet' unless $self->is_worksheet( $ws );
  return  $self->{worksheets}->{$ws}->{cells} if exists $self->{worksheets}->{$ws}->{cells} ;
  my $cell_uri = $self->_cell_uri( $ws );

  warn 'Getting cells at ' . $cell_uri if $ENV{DEBUG};
  my $res = $self->_ua->get( $cell_uri );
  $res = $self->_refresh_ua()->get( $cell_uri ) if $res->status_line =~ /^401/;
  die 'on get cells: ' . $res->status_line unless $res->is_success;
  my $root = XML::LibXML->new()->parse_string( $res->decoded_content() )->getDocumentElement;

  my @nodes = $root->getElementsByTagName( "entry" );
  my %c = ();
  foreach my $node( @nodes ){
    my $title = $node->getElementsByTagName( "title" )->shift->textContent;
    my $content  = $node->getElementsByTagName( "content" )->shift->textContent;
    my $id    = $node->getElementsByTagName( "id" )->shift()->textContent;
    $c{_get_id($id)} = { id => $id, title => $title, value => $content };
  };
  return $self->{worksheets}->{$ws}->{cells} = \%c;
}

# Set cell value
sub  _setcell($$$){
  my ( $self, $ws, $cell, $value ) = @_;
  die 'Worksheet ' . $ws . ' is invalid for this spreadsheet' unless $self->is_worksheet( $ws );

  my $sh = $self->spreadsheet( );
  $cell =~ /^R(\d+)C(\d+)$/;
  my $row = $1;
  my $col = $2;
  die 'Invalid form of cell' unless $row && $col;

  # Build atom
  my $atom = XML::LibXML::Document->new( '1.0' );
  my $entry = $atom->createElement( 'entry' );
  $entry->setAttribute( 'xmlns', "http://www.w3.org/2005/Atom" );
  $entry->setAttribute( 'xmlns:gs', "http://schemas.google.com/spreadsheets/2006" );
  
  my $id = $atom->createElement( 'id' );
  $id->appendText( "https://spreadsheets.google.com/feeds/cells/$sh/$ws/private/full/$cell" );
  
  my $link = $atom->createElement( 'link' );
  $link->setAttribute( 'rel', 'edit' );
  $link->setAttribute( 'type', "application/atom+xml" );
  $link->setAttribute( 'href', "https://spreadsheets.google.com/feeds/cells/$sh/$ws/private/full/$cell" );
  
  my $gscell = $atom->createElement( 'gs:cell' );
  $gscell->setAttribute( 'row', $row );
  $gscell->setAttribute( 'col', $col );
  $gscell->setAttribute( 'inputValue', $value );
  
  $entry->addChild( $id );
  $entry->addChild( $link );
  $entry->addChild( $gscell );

  my $cell_uri = CELL_URI;
  $cell_uri =~ s/\{key\}/$sh/;
  $cell_uri =~ s/\{ws\}/$ws/;
  $cell_uri =~ s/\{cell\}/$cell/;
  warn "Cell uri is $cell_uri" if $ENV{DEBUG};

  my $req  = HTTP::Request->new( 'PUT' );
  $req->uri( $cell_uri );
  
  $req->header( 'Content-type' => 'application/atom+xml' );
  $req->header( 'Authorization' => 'OAuth ' . $self->_access_token );
  $req->header( 'If-Match' => '*' );
  $req->header( 'GData-Version' => '3.0' );
  $req->content( $entry->toString() );

  my $res = $self->_ua->request( $req );
  $res = $self->_refresh_ua()->request( $req ) if $res->status_line =~ /^401/;
  die 'on set cell: ' . $res->status_line unless $res->is_success;

  if( exists $self->{worksheets}->{$ws}->{cells} ){
    my $node = XML::LibXML->new()->parse_string( $res->decoded_content() )->getDocumentElement;
    my $content  = $node->getElementsByTagName( "content" )->shift->textContent;
    my $title    = $node->getElementsByTagName( 'title' )->shift->textContent;
    my $id       = $node->getElementsByTagName( 'id' )->shift->textContent;
    $self->{worksheets}->{$ws}->{cells}->{_get_id($id)} = { id => $id, value => $content, title => $title };
  }
  return  $value;
}

# Set cell value
sub  _setcellhash($$$){
  my ( $self, $ws, $cells ) = @_;
  die 'Worksheet ' . $ws . ' is invalid for this spreadsheet' unless $self->is_worksheet( $ws );

  my $sh = $self->spreadsheet( );

  # Build atom
  my $atom = XML::LibXML::Document->new( '1.0' );
  my $feed = $atom->createElement( 'feed' );
  $feed->setAttribute( 'xmlns', "http://www.w3.org/2005/Atom" );
  $feed->setAttribute( 'xmlns:batch', "http://schemas.google.com/gdata/batch" );
  $feed->setAttribute( 'xmlns:gs', "http://schemas.google.com/spreadsheets/2006" );
  my $id = $atom->createElement( 'id' );
  $id->appendText( "https://spreadsheets.google.com/feeds/cells/$sh/$ws/private/full" );

  foreach my $cell( keys %$cells ){
    $cell =~ /^R(\d+)C(\d+)$/;
    my $row = $1;
    my $col = $2;
    die 'Invalid form of cell ' . $cell unless $row && $col;

    my $entry = $atom->createElement( 'entry' );
    my $batchid = $atom->createElement( 'batch:id' );
    $batchid->appendText( $cell );
    my $batchop = $atom->createElement( 'batch:operation' );
    $batchop->setAttribute( 'type', 'update' );
    my $id = $atom->createElement( 'id' );
    $id->appendText( "https://spreadsheets.google.com/feeds/cells/$sh/$ws/private/full/$cell" );

    my $link = $atom->createElement( 'link' );
    $link->setAttribute( 'rel', 'edit' );
    $link->setAttribute( 'type', "application/atom+xml" );
    $link->setAttribute( 'href', "https://spreadsheets.google.com/feeds/cells/$sh/$ws/private/full/$cell" );
    
    my $gscell = $atom->createElement( 'gs:cell' );
    $gscell->setAttribute( 'row', $row );
    $gscell->setAttribute( 'col', $col );
    $gscell->setAttribute( 'inputValue', $cells->{$cell} );
  
    $entry->addChild( $batchid );
    $entry->addChild( $batchop );
    $entry->addChild( $id );
    $entry->addChild( $link );
    $entry->addChild( $gscell );

    $feed->addChild( $entry );
  }

  my $cell_uri = BATCH_URI;
  $cell_uri =~ s/\{key\}/$sh/;
  $cell_uri =~ s/\{ws\}/$ws/;
  warn "Cell batch uri is $cell_uri" if $ENV{DEBUG};

  my $req  = HTTP::Request->new( 'POST' );
  $req->uri( $cell_uri );
  
  $req->header( 'Content-type' => 'application/atom+xml' );
  $req->header( 'Authorization' => 'OAuth ' . $self->_access_token );
  $req->header( 'If-Match' => '*' );
  $req->header( 'GData-Version' => '3.0' );
  $atom->addChild( $feed );
  $req->content( $atom->toString() );

  my $res = $self->_ua->request( $req );
  $res = $self->_refresh_ua()->request( $req ) if $res->status_line =~ /^401/;
  die 'on set cell: ' . $res->status_line . $res->decoded_content unless $res->is_success;

  if( exists $self->{worksheets}->{$ws}->{cells} ){
    my $root = XML::LibXML->new()->parse_string( $res->decoded_content() )->getDocumentElement;
    my @nodes = $root->getElementsByTagName( "entry" );
    foreach my $node( @nodes ){
      my $content  = $node->getElementsByTagName( "content" )->shift->textContent;
      my $title    = $node->getElementsByTagName( 'title' )->shift->textContent;
      my $id       = $node->getElementsByTagName( 'id' )->shift->textContent;
      $self->{worksheets}->{$ws}->{cells}->{_get_id($id)} = { id => $id, value => $content, title => $title };
    };
  }
  return  1;
}


sub  _ws_uri(){
  my $self = shift;
  my $s = WS_URI;
  my $sh = $self->spreadsheet();
  $s =~ s/\{key\}/$sh/;
  return $s;
}

sub  _cell_uri($){
  my $self = shift;
  my $ws   = shift;
  my $s = CELLS_URI;
  my $sh = $self->spreadsheet();
  $s =~ s/\{key\}/$sh/;
  $s =~ s/\{ws\}/$ws/;
  return $s;
}



# For generate certificate, you must download private certificate from
# https://code.google.com/apis/console/ convert to .pem file
# openssl pkcs12 -in google-privatekey.p12 -nodes -out google-private.pem
# Remember that password is 'notasecret'
sub  _get_certificate_file(){
  my $filename;
  if( -e './' . PEM_FILE ){
    $filename = './' . PEM_FILE;
  } elsif( -e $FindBin::Bin . '/' . PEM_FILE ){
    $filename = $FindBin::Bin . '/' . PEM_FILE;
  } elsif( -e $ENV{HOME} . '/' . PEM_FILE ){
    $filename = $ENV{HOME} . '/' . PEM_FILE;
  } else {
    die 'Can not retrieve ' . PEM_FILE . '. Please generate on your local dir or your home';
  };
  warn "pem file locate at " . $filename if $ENV{DEBUG};
  return $filename;
}

sub  _cert(){
  my $self = shift;
  return $self->{_cert} if exists $self->{_cert};
  my $file = $self->_get_certificate_file();
  open( my $fh, "<$file" ) || die 'Can not open certificate file';
  my $keystring = "";
  while( <$fh> ){
    $keystring .= $_;
  }
  close $fh;
  my $private_key = Crypt::OpenSSL::RSA->new_private_key($keystring);
  $private_key->use_sha256_hash(); # use_sha1_hash is the default
  return $self->{_cert} = $private_key;
}

sub  _urlencode_base64{
  my $str = shift;
  $str = encode_base64( $str, '' );
  $str =~ s/\=$//g;
  $str =~ s/\+/\-/g;
  $str =~ s/\//\_/g;
  return $str;
}


sub  _gen_assertion(){
  my $self = shift;

  my %claim = (
    iss => $ENV{GOOGLE_CLIENT_ID} . '@developer.gserviceaccount.com',
    scope => 'https://spreadsheets.google.com/feeds',
    aud => 'https://accounts.google.com/o/oauth2/token',
    exp => time + 30 * 60,
    iat => time );
  
  my %header = ( alg => "RS256", typ => "JWT" );
  
  my $claim_encoded = _urlencode_base64( encode_json( \%claim ) );
  my $header_encoded = _urlencode_base64( encode_json( \%header ) );
  
  my $signature = $self->_cert()->sign($header_encoded . '.' . $claim_encoded);
  my $assertion = $header_encoded . '.' . $claim_encoded . "." . _urlencode_base64( $signature );
  warn "curl 'https://accounts.google.com/o/oauth2/token' -d 'grant_type=urn:ietf:params:oauth:grant-type:jwt-bearer' -d 'assertion=$assertion'" if $ENV{DEBUG};
  return $assertion;
}

sub  _refresh_ua(){
  my $self = shift;
  warn "Refreshing User Agent (and token)" if $ENV{DEBUG};
  unlink '/tmp/gs-' . $ENV{GOOGLE_CLIENT_ID};
  delete $self->{_ua};
  return  $self->_ua();
}

sub  _access_token(){
  my $self = shift;
  my $access_token;
  if( -e '/tmp/gs-' . $ENV{GOOGLE_CLIENT_ID} ){
    open( my $fh, '<', '/tmp/gs-' . $ENV{GOOGLE_CLIENT_ID} ) or die 'Can\'t open /tmp/gs-' . $ENV{GOOGLE_CLIENT_ID};
    $access_token = <$fh>;
    close $fh;
  } else {
    my $res = $self->_ua()->post( 'https://accounts.google.com/o/oauth2/token', 
      {
        grant_type => 'urn:ietf:params:oauth:grant-type:jwt-bearer',
        assertion => $self->_gen_assertion(),
      } );
    my $data = decode_json( $res->decoded_content );
    $access_token = $data->{'access_token'} ;
    open( my $fh, '>', '/tmp/gs-' . $ENV{GOOGLE_CLIENT_ID} ) or die 'Can\'t open /tmp/gs-' . $ENV{GOOGLE_CLIENT_ID};
    print  $fh $access_token . "\n";
    close $fh;
  }
  warn 'Auth header is ' . $access_token if $ENV{DEBUG}; 
  return  $access_token;
}


sub  _ua(){
  my $self = shift;
  return $self->{_ua} if exists $self->{_ua};
  my $ua = LWP::UserAgent->new;
  $self->{_ua} = $ua;
  my $access_token = $self->_access_token();
 
  $ua->default_header( 'Authorization' => 'OAuth ' . $access_token );
  $ua->default_header( 'If-Match' => '*' );
  $ua->default_header( 'GData-Version' => '3.0' );

  return  $ua;
}




1;