perl 连接到google电子表格

我有一个使用Perl创建的Web应用程序，并且在电子表格文档上有一个表，我想将应用程序连接到电子表格，我尝试了文档，但无法获得令牌，我所做的是：

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

我无法让Net::Google::Spreadsheets::V4使用服务帐户并使用Net::Google::DataAPI::Auth::OAuth2来获取访问令牌，但您应该能够从WWW::Google::Cloud::Auth::ServiceAccount获取访问令牌，然后使用该令牌来授权对google drive rest api的请求。以下代码对我有效：

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>

编辑：

要获取具有给定ID的给定工作表的单元格的值，可以使用以下url：https://sheets.googleapis.com/v4/spreadsheets/%s/values/%s，其中第一个%s由工作表的ID替换，第二个%s表示要提取的单元格区域。下面是一个示例：

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>

perl 连接到google电子表格

1条答案

相关问题

热门标签

最新问答