Authentication in the Google Sheets app

Question

I have a batch application that is periodically called by the scheduler, and the human user is not involved. It uses the Perl Net :: Google :: Spreadsheets package to update some cells in the Google Sheets spreadsheet using data retrieved from the database.

For a long time, it was easy to authenticate by simply providing a username and password for the "new" package method. But recently, Google has required us to authenticate using the OAuth2 protocol.

JT provided a solution that I am sure is very useful for many people more knowledgeable than me, however I would appreciate it if anyone could answer some questions to clarify this, namely:

• Creation of credentials: after creating a project in the Google Developer Console and you want to create a new client identifier, you are offered three options:

  • For a “web application” (then it asks for “Authorized JavaScript Sources” and “Authorized Redirect URIs. Do they match my situation?)
  • For a “service account” (I suspect this is a choice for me, but without answering the following questions, I cannot verify this.)
  • For the "Installed application" (Can I give examples to this?)

  Which should I choose for the can application?

• Should I ask for JSON or the P12 key?

• What should I do with the different types of entities that I get? What do I embed in a Perl script?

• In line 13, JT noted that "you will need to enter the code here and get the token." What code? What to do?

• Since the user does not exist, I need the application to complete the full authentication process. JT code asks the user for the "code". Is this code one of the credential objects? How to do it?

In other words, I need someone to carefully walk me through the whole process, step by step.

Thanks guys!

Meirg

Answer 1

I also had to go through this without knowing it from the very beginning, so I am happy to help explain this. Here are the answers, but feel free to ask for clarification. Basically, you need to first run a script that requires manual intervention - this allows you to get an access token from Google, which your batch script can use again and again without human intervention. So you have to jump through some hoops from the start, but once that is done, you are all set. So:

• Select "web application." Not intuitively, but it will work.

  1b. You will be prompted to set up a “consent screen”. It doesn't matter what you do here - just give the project a name.

  1c. For uri redirection, remove the provided value from example.com and enter https://developers.google.com/oauthplayground .

Ignore the JSON and P12 keys; They are designed for other types of applications. After filling in the above information and click "Create Client ID", you will get a page (after a pause) that displays the client ID and client secret. These are the two lines that you will need in the code below.

Below is the same solution that you contacted above (and I relied heavily on it), but I edited it to change a few things, primarily to give more information about what is happening. Once you have added your client ID and client secret in the code below, run it. Then you follow these steps:

• Copy the URL that the script prints and paste it into the browser.
• Sign in to Google if he asks you. Then click "Allow access" on the next page.
• On the next page in the browser, the field to the left of the "Authorization code" will appear. (For example: https://members.orcid.org/sites/default/files/image06.png , but your code will be longer.) Do not click the button below the code, but copy this line, being sure to get it all (which may extend out of sight in the dialog box).
• Go back to the terminal where you ran the script and paste the code you copied.

If all goes well, the script will exchange this code for an access token and save the token to disk. Then your batch script can reuse this token.

Here's the extended code to do it all:

#!/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 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 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 ; 

After you get this job and have a token stored on disk, the beginning of your batch script can configure access to the spreadsheet as follows:

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