当前位置： 文江博客话题详情

连接到Google电子表格

发布于 2025-02-12 23:09:29 字数 936 浏览 0 评论 0原文

我有一个使用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' );
}
原文

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

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（1）

×眷恋的温暖 2025-02-19 23:09:29

我无法制作 net :: Google :: Google :: google :: vendereets :: v4 < /a>使用服务帐户并使用 net :: google :: dataapi :: dataapi :: auth :: auth :: auth :: oauth2 获取访问令牌。但是您应该能够从 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/电子表格/％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>

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>
回复 原文
~没有更多了~

关于作者

听闻余生

暂无简介

文章
评论
27 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

夢野间

文章 0 评论 0

百度③文鱼

文章 0 评论 0

小草泠泠

文章 0 评论 0

zhuwenyan

文章 0 评论 0

weirdo

文章 0 评论 0

坚持沉默

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文