Procedure for operating google spreadsheet with API (ruby)

What is this?

It is a procedure to operate google spreadsheet with API using ruby.

Basically, I'm just doing https://developers.google.com/sheets/api/quickstart/ruby, so if you don't have any problem with the explanation in English, please see the official one.

Target person

It is intended for people who can create the execution environment of ruby ​​by themselves or who already exist.

procedure

https://developers.google.com/sheets/api/quickstart/ruby Click the Enable the Google Sheets API button.

Enter the Project name on the next screen. There is no problem if you leave [Quickstart] as it is, so click [NEXT] after entering it.

On the next screen, select the OAuth client to create. Select Desktop app and click the CREATE button.

On the next screen, the [DOWNLOAD CLIENT CONFIGURATION] button will be displayed. Click it to download the credentials.json file.

Next, we will create a ruby ​​program. First, create a directory to store the program. This time, I created the "sample_google_spreadsheet_api" directory. (Any directory name is fine)

cd ~
mkdir sample_google_spreadsheet_api

Then install the gem.

Although it is described as above on Google's Quickstart page, this time we will create a Gemfile.

cd sample_google_spreadsheet_api
bundle init

A Gemfile will be created, so edit it as follows.

Gemfile

# frozen_string_literal: true

source "https://rubygems.org"

git_source(:github) {|repo_name| "https://github.com/#{repo_name}" }

--- # gem "rails"
+++ gem 'google-api-client'

bundle install.

bundle config set --local path 'vendor/bundle'
bundle install

Place the downloaded credentials.json below.

#Create a file directory and credentials.Store json
mkdir file

sample_google_spreadsheet_api/file/credentials.json

Then create sheets/quickstart/quickstart.rb.

mkdir sheets
cd sheets
mkdir quickstart
cd quickstart
touch quickstart.rb 

Write the following program in quickstart.rb.

This is what you can find in Google's Quickstart.

require "bundler/setup"
require "google/apis/sheets_v4"
require "googleauth"
require "googleauth/stores/file_token_store"
require "fileutils"
OOB_URI = "urn:ietf:wg:oauth:2.0:oob".freeze
APPLICATION_NAME = "Google Sheets API Ruby Quickstart".freeze
CREDENTIALS_PATH = "./file/credentials.json".freeze
# The file token.yaml stores the user's access and refresh tokens, and is
# created automatically when the authorization flow completes for the first
# time.
TOKEN_PATH = "token.yaml".freeze
SCOPE = Google::Apis::SheetsV4::AUTH_SPREADSHEETS_READONLY
##
# Ensure valid credentials, either by restoring from the saved credentials
# files or intitiating an OAuth2 authorization. If authorization is required,
# the user's default browser will be launched to approve the request.
#
# @return [Google::Auth::UserRefreshCredentials] OAuth2 credentials
def authorize
  client_id = Google::Auth::ClientId.from_file CREDENTIALS_PATH
  token_store = Google::Auth::Stores::FileTokenStore.new file: TOKEN_PATH
  authorizer = Google::Auth::UserAuthorizer.new client_id, SCOPE, token_store
  user_id = "default"
  credentials = authorizer.get_credentials user_id
  if credentials.nil?
    url = authorizer.get_authorization_url base_url: OOB_URI
    puts "Open the following URL in the browser and enter the " \
		     "resulting code after authorization:\n" + url
    code = gets
		credentials = authorizer.get_and_store_credentials_from_code(
			user_id: user_id, code: code, base_url: OOB_URI
		)
	end
	credentials
end

# Initialize the API
service = Google::Apis::SheetsV4::SheetsService.new
service.client_options.application_name = APPLICATION_NAME
service.authorization = authorize

# Prints the names and majors of students in a sample spreadsheet:
# https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit
spreadsheet_id = "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms"
range = "Class Data!A2:E"
response = service.get_spreadsheet_values spreadsheet_id, range
puts "Name, Major:"
puts "No data found." if response.values.empty?
response.values.each do |row|
	# Print columns A and E, which correspond to indices 0 and 4.
	puts "#{row[0]}, #{row[4]}"
end

As the content Get the authorization code based on credentials.json and get the access token from the authorization code https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit The contents of are output to the command line. The following is the execution command.

ruby sheets/quickstart/quickstart.rb

Execution result

> ruby sheets/quickstart/quickstart.rb

Open the following URL in the browser and enter the resulting code after authorization:
https://accounts.google.com/o/oauth2/auth?access_type=offline&approval_prompt=force&client_id=[client_id]&include_granted_scopes=true&redirect_uri=urn:ietf:wg:oauth:2.0:oob&response_type=code&scope=https://www.googleapis.com/auth/spreadsheets.readonly

The URL will be displayed the first time you run it, so access it.

1: Select your google account.

2: The authorization screen will be displayed. Click "Allow".

3: The authorization code is displayed, so paste it on the execution screen Return

Paste it into the execution result and Return

> ruby sheets/quickstart/quickstart.rb 

Open the following URL in the browser and enter the resulting code after authorization:
https://accounts.google.com/o/oauth2/auth?access_type=offline&approval_prompt=force&client_id=[client_id]&include_granted_scopes=true&redirect_uri=urn:ietf:wg:oauth:2.0:oob&response_type=code&scope=https://www.googleapis.com/auth/spreadsheets.readonly
#Paste the authorization code obtained above to ↓ Return
[Authorization code]

#The result obtained from Google Spread Sheet is displayed.
Name, Major:
Alexandra, English
Andrew, Math
Anna, English
Becky, Art
Benjamin, English
Carl, Art
Carrie, English
Dorothy, Math
Dylan, Math
Edward, English
Ellen, Physics
Fiona, Art
John, Physics
Jonathan, Math
Joseph, English
Josephine, Math
Karen, English
Kevin, Physics
Lisa, Art
Mary, Physics
Maureen, Physics
Nick, Art
Olivia, Physics
Pamela, Math
Patrick, Art
Robert, English
Sean, Physics
Stacy, Math
Thomas, Art
Will, Math

If you can see the contents of the sample Google Sheets, you are successful.