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' );
}
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>