在Google表格应用程序中进行身份验证 [英] Authenticating in a Google sheets application

问题描述

我有一个类似批处理的应用程序，该程序由调度程序定期调用，没有人工参与.它使用Perl Net :: Google :: Spreadsheets 包来更新某些单元格在Google表格电子表格中，通过从数据库中获取的数据进行.

I have a batch-like application which is periodically invoked by a scheduler, no human user involved. It uses the Perl Net::Google::Spreadsheets package to update some cells in a Google-sheets spreadsheet, by data fetched from a database.

很长时间以来，仅通过向软件包的"new"方法提供用户名和密码即可对自身进行身份验证.但是最近，Google要求我们使用OAuth2协议进行身份验证.

For a long time it was simple to authenticate itself by just providing a username and a password to the 'new' method of the package. But as of lately, Google require us to authenticate using the OAuth2 protocol.

JT 提供了

J.T. provided a solution that I am sure is very helpful to many people more knowledgeable than I am. I will appreciate however if somebody could answer some questions to clarify it, as follows:

1. 创建凭据:在Google Developer Console中创建了一个项目并要求创建新的客户端ID后，系统会为您提供三个选项:

1. Creating credentials: once you created a project in the Google Developer Console and you are asking to create a new client ID, you are presented with three options:

• 对于"Web应用程序"(然后要求提供授权的JavaScript起源"和授权的重定向URI".这些与我的情况有关吗?)
• 对于服务帐户"(我怀疑这是我的选择，但是如果无法回答以下问题，我将无法验证.)
• 对于已安装的应用程序"(可以举个例子吗?)

我应该为哪个应用程序选择 ?

Which one should I choose for may application?

我是否需要JSON或P12密钥?

Should I asked for a JSON or a P12 key?

如何处理我得到的各种类型的实体?我在Perl脚本中嵌入了什么?

What do I do with the various types of entities I get? What do I embed in the Perl script?

J.T在第13行评论说:您将需要在此处放置代码并获得令牌".什么样的代码?在做什么?

At line 13, J.T commented that "you will need to put code here and receive a token". What kind of code? Doing what?

由于没有人工用户，因此我需要该应用程序自行执行完整的身份验证过程. J.T.的代码会提示用户输入代码".此代码是凭证"实体之一吗?我该怎么办?

Since there is no human user, I need the application to do the full authentication process by itself. J.T.'s code prompts a user for a 'code'. Is this code one of the "credentials" entities? How do I do it?

换句话说，我需要有人逐步引导我逐步完成整个过程.

In other words, I need somebody to walk me gently through the whole process, step by small step.

谢谢大家！

MeirG

推荐答案

我也必须经历这一过程，而一开始并不了解，所以我很乐意为您解释.这里是答案，但请随时进行澄清.基本上，您首先需要运行需要人工干预的脚本-这样，您便可以从Google获取访问令牌，然后您的批处理脚本就可以反复使用它而无需人工干预.因此，您必须从头开始跳过一些箍，但是一旦完成，就一切准备就绪.所以:

I had to go through this too, without knowing much at the start, so I'm happy to help explain it. Here are the answers, but feel free to ask for clarification. Basically, you need to first run a script that requires manual intervention - this lets you obtain an access token from Google, which your batch-like script can then use over and over without human intervention. So you have to jump through some hoops at the start, but once that's done, you're all set. So:

1. 选择网络应用程序".虽然不直观，但可以使用.

1b.系统将要求您配置同意屏幕".放在这里真的没关系-只需为项目命名即可.

1b. You'll be asked to configure a "consent screen". Doesn't really matter what you put here - just give the project a title.

1c.对于重定向uri"，请删除提供的"example.com".值，然后输入"https://developers.google.com/oauthplayground".

1c. For "redirect uri", delete the provided "example.com" value and enter "https://developers.google.com/oauthplayground".

忽略JSON和P12密钥；它们用于其他类型的应用程序.填写以上信息并单击创建客户端ID"后，您将获得一个页面(暂停后)，其中显示了客户端ID和客户端密码.这是您在下面的代码中需要的两个字符串.

Ignore the JSON and P12 keys; they are for other types of applications. Once you fill in the above info and click "Create Client ID", you'll get a page (after a pause) that displays a client ID and client secret. Those are the two strings you'll need in the code below.

下面的代码本质上与您链接到上面的解决方案相同(我非常依赖它)，但是我对其进行了编辑以更改一些内容，主要是为了提供有关正在发生的事情的更多信息.将客户端ID和客户端密钥添加到下面的代码后，请运行它.然后，您将完成以下步骤:

The code below is essentially the same solution that you linked to above (and I relied heavily on it), but I've edited it to change a few things, primarily to give more information about what's going on. Once you have added your client ID and client secret to the code below, run it. Then you'll go through these steps:

1. 复制脚本打印出的URL，并将其粘贴到浏览器中.
2. 如果要求您登录，请登录Google.然后点击允许访问"在下一页上.
3. 在浏览器的下一页上，将在左侧带有一个标记为授权代码"的框. (例如: https://members.orcid.org/sites/default/files/image06.png ，但是您的身份验证代码会更长.)不要单击代码下方的按钮，但是请复制该字符串，以确保获得完整内容(即可能会超出对话框的视线范围.)
4. 返回运行脚本的终端，然后粘贴复制的代码.

1. Copy the URL that the script prints out, and paste it in a browser.
2. Log into Google if it asks you to. Then click "allow access" on the next page.
3. On the following page in the browser, there will be a box toward the left labeled "Authorization code". (Like this: https://members.orcid.org/sites/default/files/image06.png but your auth code will be longer.) Don't click the button below the code, but do copy that string, being sure to get the whole thing (which may stretch out of sight in the dialog box).
4. Go back to the terminal where you ran the script, and paste in the code you've copied.

如果一切顺利，脚本将使用该代码交换访问令牌，并将该令牌保存在磁盘上.然后您的批处理脚本可以重复使用该令牌.

If all goes well, the script will exchange that code for an access token, and save the token on disk. Then your batch script can use that token repeatedly.

这是完成所有这些操作的扩展代码:

Here's the expanded code to do all of this:

#!/usr/bin/perl

# Code to get a web-based token that can be stored 
# and used later to authorize our spreadsheet access. 

# Based on code from https://gist.github.com/hexaddikt/6738162

#-------------------------------------------------------------------

# To use this code:

# 1. Edit the lines below to put in your own
#    client_id and client_secret from Google. 
# 2. Run this script and follow the directions on 
#    the screen, which will give step you 
#    through the following steps:
# 3. Copy the URL printed out, and paste 
#    the URL in a browser to load the page. 
# 4. On the resulting page, click OK (possibly
#    after being asked to log in to your Google 
#    account). 
# 5. You will be redirected to a page that provides 
#    a code that you should copy and paste back into the 
#    terminal window, so this script can exchange it for
#    an access token from Google, and store the token.  
#    That will be the token the other spreadsheet access
#    code can use. 


use Net::Google::DataAPI::Auth::OAuth2;
use Net::Google::Spreadsheets;
use Storable; #to save and restore token for future use
use Term::Prompt;

# Provide the filename in which we will store the access 
# token.  This file will also need to be readable by the 
# other script that accesses the spreadsheet and parses
# the contents. 

my $session_filename = "stored_google_access.session";


# Code for accessing your Google account.  The required client_id
# and client_secret can be found in your Google Developer's console 
# page, as described in the detailed instruction document.  This 
# block of code will also need to appear in the other script that
# accesses the spreadsheet. 

# Be sure to edit the lines below to fill in your correct client 
# id and client secret!
my $oauth2 = Net::Google::DataAPI::Auth::OAuth2->new(
    client_id => 'your_client_id.apps.googleusercontent.com',
    client_secret => 'your_client_secret',
    scope => ['http://spreadsheets.google.com/feeds/'],
    redirect_uri => 'https://developers.google.com/oauthplayground',
                             );
# We need to set these parameters this way in order to ensure 
# that we get not only an access token, but also a refresh token
# that can be used to update it as needed. 
my $url = $oauth2->authorize_url(access_type => 'offline',
                 approval_prompt => 'force');

# Give the user instructions on what to do:
print <<END

The following URL can be used to obtain an access token from
Google.  

1. Copy the URL and paste it into a browser.  

2.  You may be asked to log into your Google account if you 
were not logged in already in that browser.  If so, go 
ahead and log in to whatever account you want to have 
access to the Google doc. 

3. On the next page, click "Accept" when asked to grant access. 

4.  You will then be redirected to a page with a box in the 
left-hand column labeled  "Authorization code".  
Copy the code in that box and come back here. 

Here is the URL to paste in your browser to get the code:

$url

END
    ;

# Here is where we get the code from the user:
my $code = prompt('x', 'Paste the code obtained at the above URL here: ', '', ''); 

# Exchange the code for an access token:
my $token = $oauth2->get_access_token($code) or die;

# If we get to here, it worked!  Report success: 
print "\nToken obtained successfully!\n";
print "Here are the token contents (just FYI):\n\n";
print $token->to_string, "\n";

# Save the token for future use:
my $session = $token->session_freeze;
store($session, $session_filename);

print <<END2

Token successfully stored in file $session_filename.

Use that filename in your spreadsheet-access script to 
load the token as needed for access to the spreadsheet data. 

END2
    ;

一旦可以正常工作并将令牌存储在磁盘上，那么批处理脚本的开头就可以像这样设置电子表格访问权限:

Once you've gotten that working and have the token stored on disk, then the beginning of your batch script can set up the spreadsheet access like this:

use Net::Google::Spreadsheets;
use Net::Google::DataAPI::Auth::OAuth2;
use Net::OAuth2::AccessToken;
use Storable;

# Authentication code based on example from gist at 
#  https://gist.github.com/hexaddikt/6738247

# Get the token that we saved previously in order to authenticate:
my $session_filename = "stored_google_access.session";


# Be sure to edit the lines below to fill in your correct client 
# id and client secret!
my $oauth2 = Net::Google::DataAPI::Auth::OAuth2->new(
    client_id => 'your_client_id.apps.googleusercontent.com',
    client_secret => 'your_client_secret',
    scope => ['http://spreadsheets.google.com/feeds/'],
    redirect_uri => 'https://developers.google.com/oauthplayground',
                             );

# Deserialize the file so we can thaw the session and reuse the refresh token
my $session = retrieve($sessionfile);

my $restored_token = Net::OAuth2::AccessToken->session_thaw($session,
                                auto_refresh => 1,
                                profile => $oauth2->oauth2_webserver,
                                );

$oauth2->access_token($restored_token);
# Now we can use this token to access the spreadsheets 
# in our account:
my $service = Net::Google::Spreadsheets->new(
                         auth => $oauth2);
    

这篇关于在Google表格应用程序中进行身份验证的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！