4
5

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

google-api-ruby-client gem を使ってGoogle DriveやGoogle Spreadsheetを使う

Last updated at Posted at 2019-06-26

gem

ドキュメント

Google Drive API
https://developers.google.com/drive/api/v3/about-files
Google Spreadsheet API
https://developers.google.com/sheets/api/reference/rest/
提供はされているが、repoをcloneしてソース自体を見た方が分かりやすい

https://github.com/googleapis/google-api-ruby-client/blob/master/generated/google/apis/drive_v3.rb
https://github.com/googleapis/google-api-ruby-client/tree/master/generated/google/apis/drive_v3

https://github.com/googleapis/google-api-ruby-client/blob/master/generated/google/apis/sheets_v4.rb
https://github.com/googleapis/google-api-ruby-client/tree/master/generated/google/apis/sheets_v4

とりあえずQuickstartでAPIが使える状態にしておく

https://developers.google.com/drive/api/v3/quickstart/ruby
その他各種言語でも同じようなページあり

Google Drive API ざっくりとした使い方

SCOPEについて

# https://developers.google.com/drive/api/v3/about-auth
SCOPES = [
  Google::Apis::DriveV3::AUTH_DRIVE, # フルアクセス(用途に応じて変更)
]

Google::Auth::UserAuthorizer.new(client_id, SCOPES, token_store)

フォルダを作る

# https://developers.google.com/drive/api/v3/folder
# application/vnd.google-apps.folder というMIMEタイプを使ってファイルを作る
# https://developers.google.com/drive/api/v3/mime-types
file_metadata = {
    name: 'Invoices',
    mime_type: 'application/vnd.google-apps.folder'
}
file = drive_service.create_file(file_metadata, fields: 'id')

ファイルやフォルダを探す

# https://developers.google.com/drive/api/v3/reference/query-ref
# Combine clauses with the conjunctions and or or, and negate the query with not.
# 複数あればandでつなぐ  or、not検索も出来る
drive_service.list_files(q: %|name = '探しているファイルやフォルダの名前' and parents in '特定のフォルダ内だけを探す場合、そのフォルダのID'|)

# Surround with single quotes '. Escape single quotes in queries with \', e.g., 'Valentine\'s Day'.
# 文字列はシングルクオートで囲むが、文字列内にシングルクオートがある時のエスケープがruby的にややこしい
drive_service.list_files(q: %|name = 'abc\'0123'|)

# abc'0123 -> abc\'0123 にしたい場合
# ダメな方法 [\']が後方参照(backreference - $') に解釈されて思い通りにならない
puts "abc'0123".gsub("'", '\'') # => abc'0123

# 方法1
puts "abc'0123".gsub("'", Regexp.escape(%q|\'|)) # => abc\'0123

# 方法2
puts "abc'0123".gsub("'"){ %q|\'| } # => abc\'0123


ファイルなどを移動させる

# remove_parentsをしないと、複数の場所に同じファイルが存在してしまう(シンボリックリンク的な)
file_id = '1sTWaJ_j7PkjzaBWtNc3IzovK5hQf21FbOw9yLeeLPNQ'
folder_id = '0BwwA4oUTeiV1TGRPeTVjaWRDY1E'
# Retrieve the existing parents to remove
file = drive_service.get_file(file_id,
                              fields: 'parents')
previous_parents = file.parents.join(',')
# Move the file to the new folder
file = drive_service.update_file(file_id,
                                 add_parents: folder_id,
                                 remove_parents: previous_parents,
                                 fields: 'id, parents')

「My Drive」から移動させる

# https://developers.google.com/drive/api/v3/folder
# You can use the alias root to refer to the root folder anywhere a file ID is provided
# 通常はサンプルのような流れで問題が無いが、移動させたい元が
# トップレベルとなる「My Drive」の場合、remove_parentsを「root」に
file = drive_service.update_file(file_id,
                                 add_parents: folder_id,
                                 remove_parents: "root",
                                 fields: 'id, parents')

Google Spreadsheet API ざっくりとした使い方

新規作成

# 渡すオブジェクトはこのフォーマット
# https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets#Spreadsheet
spreadsheet = {
  properties: {
    title: 'Sales Report'
  }
}
# または
# spreadsheet = Google::Apis::SheetsV4::Spreadsheet.new
# spreadsheet.properties = {
#   title: 'Sales Report'
# }
spreadsheet = service.create_spreadsheet(spreadsheet,
                                         fields: 'spreadsheetId')
puts "Spreadsheet ID: #{spreadsheet.spreadsheet_id}"

タイトル更新

request = Google::Apis::SheetsV4::Request.new
# https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/request#updatespreadsheetpropertiesrequest
request.update_spreadsheet_properties = {
  properties: { title: "changed" },
  fields: "*",
}
batch_request = Google::Apis::SheetsV4::BatchUpdateSpreadsheetRequest.new
batch_request.requests = [request]
# https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/batchUpdate
service.batch_update_spreadsheet(sheet.spreadsheet_id, batch_request)

セル更新

# https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/update
value_range = Google::Apis::SheetsV4::ValueRange.new
# https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values
# For input, 
# with range=A1:B2,majorDimension=ROWS then 
# [[1,2],[3,4]] will set A1=1,B1=2,A2=3,B2=4. 
# With range=A1:B2,majorDimension=COLUMNS then
# [[1,2],[3,4]] will set A1=1,B1=3,A2=2,B2=4.
# value_range.major_dimension = ROWS # default
# value_range.major_dimension = COLUMNS
value_range.values = [
  # major_dimension = ROWS なので、配列1つが行のデータ
  # major_dimension = COLUMNS だと、列のデータになる
  [
    "A2に入る値",
    "B2に入る値",
    "00001", # RAWなので「1」にならない
    12.34,  # RAWなので数字のセルになる
    "E2に入る値",
  ]
]

range = "Sheet1!A2:E2"

# value_input_option
# https://developers.google.com/sheets/api/reference/rest/v4/ValueInputOption
service.update_spreadsheet_value(
  sheet.spreadsheet_id,
  range,
  value_range,
  value_input_option: "RAW"
)

4
5
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
4
5

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?