connect to google spreadsheet-CodePudding

i have a web app created using perl, and i have a table on a spreadsheet document, i want to connect my app to the spreadsheet, i tried the documentation but i can't get the token here is what i did:

sub authenticate {
    my $oauth2 = Net::Google::DataAPI::Auth::OAuth2->new(
        client_id     => 'my client id',
        client_secret => 'my secret code',
        scope         => ['http://spreadsheets.google.com/feeds/'],
    );

    my $url = $oauth2->authorize_url(access_type => 'offline', approval_prompt => 'force');
    use Data::Dumper;
    Core::Global::Logger::debug(Dumper($url));
    
    #you will need to put code here and receive token
    print "OAuth URL, get code: \n$url\n";
   
    use Term::Prompt;
    my $code = prompt('x', 'my code', '', ''); 
    my $token = $oauth2->get_access_token($code) or die;

    #save token for future use
    my $session = $token->session_freeze;
    store( $session, 'google_spreadsheet.session' );
}

CodePudding user response：

I was not able to make Net::Google::Spreadsheets::V4 work with a service account and using Net::Google::DataAPI::Auth::OAuth2 to get the access token. But you should be able to get the access token from WWW::Google::Cloud::Auth::ServiceAccount instead, and then use that token to authorize requests to the google drive rest api. The following worked for me:

use feature qw(say);
use strict;
use warnings;
use WWW::Google::Cloud::Auth::ServiceAccount;
use LWP::UserAgent;
use URI;
use HTTP::Request;
use Mojolicious::Lite;
use Data::Dumper;
use JSON;
use experimental qw(declared_refs refaliasing signatures);

{ 
    my @scopes = ('https://www.googleapis.com/auth/spreadsheets',
                  'https://www.googleapis.com/auth/drive');
    my $auth =  WWW::Google::Cloud::Auth::ServiceAccount->new(
        credentials_path => 'credentials.json',
        scope            => join ' ', @scopes 
    );

    get '/' => sub {
        my $c = shift;
        my $token = $auth->get_token();
        my ($return_code, $files) = get_all_spreadsheets($token);
        $c->render(template => 'index', return_code => $return_code);
    };
    app->start;
}

sub get_all_spreadsheets($token) {
    my $url = 'https://www.googleapis.com/drive/v3/files';
    my $query = 'mimeType="application/vnd.google-apps.spreadsheet"';
    my $params = {
        "q"                         => $query,
        "pageSize"                  => 1000,
        "supportsAllDrives"         => 1,
        "includeItemsFromAllDrives" => 1,
        "fields"                    => "kind,nextPageToken,"
                                       . "files(id,name,createdTime,modifiedTime)",
    };
    my $more_pages = 1;
    my $page_token = '';
    my $status_line;
    my @files;
    while ($more_pages) {
        $params->{pageToken} = $page_token if $page_token;
        my $result = send_google_drive_get_request($url, $params, $token);
        $status_line = $result->status_line;
        if (!$result->is_success) {
            return $status_line;
        }
        my $hash = decode_json($result->content);
        push @files, $hash->{files};
        if (exists $hash->{nextPageToken}) {
            $page_token = $hash->{nextPageToken};
        }
        else {
            $more_pages = 0;
        }
    }
    return $status_line, \@files;
}

sub send_google_drive_get_request( $url, $params, $token ) {
    my $uri = URI->new( $url );
    $uri->query_form($params);
    my $str = $uri->as_string();
    my @headers = get_headers($token);
    my $req = HTTP::Request->new(
        'GET',
        $uri->as_string(),
        \@headers,
    );
    my $ua = LWP::UserAgent->new();
    my $res = $ua->request($req);
    return $res;
}

sub get_headers($token) {
    return 'Accept-Encoding' => 'gzip, deflate',
        'Accept'          => '*/*',
        'Connection'      => 'keep-alive',
        "Authorization"   => "Bearer $token";
}

__DATA__

@@ index.html.ep
<!DOCTYPE html>
<html>
  <head><title>Testing access to google sheets...</title></head>
  <body>
    <h1>Return code = <%= $return_code %></h1>
  </body>
</html>

Edit:

To get the value of a cell for a given sheet with a given id, you can use the following url: https://sheets.googleapis.com/v4/spreadsheets/%s/values/%s where the first %s is replaced by the id of the sheet and the second %s represents the cell range to extract. Here is an example:

use feature qw(say);
use strict;
use warnings;
use WWW::Google::Cloud::Auth::ServiceAccount;
use LWP::UserAgent;
use URI;
use URI::Encode;
use HTTP::Request;
use Mojolicious::Lite;
use Data::Dumper;
use JSON;
use experimental qw(declared_refs refaliasing signatures);

{
    my @scopes = ('https://www.googleapis.com/auth/spreadsheets',
                  'https://www.googleapis.com/auth/drive');
    my $auth =  WWW::Google::Cloud::Auth::ServiceAccount->new(
        credentials_path => 'credentials.json',
        scope => join " ", @scopes
    );

    get '/' => sub {
        my $c = shift;
        my $token = $auth->get_token();
        my $sheet_id = '1FPyDuIPPzwUeLNpLbdI-RzfouKcm-2duOJ9Jio-Z-Qw';
        my $sheet_cell = 'B1';
        my ($return_code, $cell_value) = 
            read_spreadsheet_cell($token, $sheet_id, $sheet_cell);
        app->log->debug(app->dumper( { cell_value => $cell_value } ));
        $c->render(template => 'index', return_code => $return_code);
    };
    app->start;
}

sub read_spreadsheet_cell($token, $id, $cell) {
    my $encoder = URI::Encode->new();
    my $value_range = $encoder->encode(sprintf "'Sheet1'!%s", $cell);
    my $url = sprintf 'https://sheets.googleapis.com/v4/spreadsheets/%s/values/%s', 
         $id, $value_range;
    my $result = send_google_drive_get_request($url, $token);
    my $status_line = $result->status_line;
    if (!$result->is_success) {
        return $status_line;
    }
    my $result_hash = decode_json( $result->content );
    #app->log->debug(app->dumper( $result_hash ));
    return $status_line, $result_hash->{values}[0][0];
}

sub send_google_drive_get_request( $url, $token ) {
    my @headers = get_headers($token);
    my $req = HTTP::Request->new('GET', $url, \@headers);
    my $ua = LWP::UserAgent->new();
    my $res = $ua->request($req);
    return $res;
}

sub get_headers($token) {
    return
        'User-Agent'      => 'Mozilla/8.0',
        'Accept-Encoding' => 'gzip, deflate',
        'Accept'          => '*/*',
        'Connection'      => 'keep-alive',
        "Authorization"   => "Bearer $token";
}

__DATA__

@@ index.html.ep
<!DOCTYPE html>
<html>
  <head><title>Testing access to google sheets...</title></head>
  <body>
    <h1>Return code = <%= $return_code %></h1>
  </body>
</html>