みなさんこんにちは。zak-papaです。
前回は、「Google スプレッドシート」を操作する「gspread」の「ワークブック」「ワークシート」の操作方法についてお伝えしました。
【Python】「gspread」で「 Googleスプレッドシート」のワークブック、ワークシートを操作してみよう
3ステップのうちの2つ目までですね。
- ワークブックの取得（指定）
- ワークシートの取得（指定）
- セル範囲の取得（指定）
操作したいワークブックとワークシートは取得できたので、今回は「3つ目」の「セル」に対して値を「取得」したり「入力」したりしていきます。
順に見ていきましょう。
目次
「セル」を操作する準備をしよう
事前準備（前回の復習）
前回と同じ内容になります。
私の動作環境は次の通りです。ご自身の使い慣れているものをお使いいただければと思います。
Windowsでも同じように動作すると思います。
| OS | macOS Catalina 10.15.3 | Version | Python 3.7.4 |
| エディタ | Visual Studio Code | ブラウザ | Google Chrome |
| ディレクトリ | Desktop/gspread | ファイル | gspread_practice.py |
ワークブックは「スプレッドシートキー」で取得、ワークシートは「sheet1」で一番左のシート（前回の続きなので「update title」）を取得しています。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
import gspread from oauth2client.service_account import ServiceAccountCredentials scope = ['https://spreadsheets.google.com/feeds','https://www.googleapis.com/auth/drive'] # 秘密鍵（JSONファイル）のファイル名を入力 credentials = ServiceAccountCredentials.from_json_keyfile_name('spreadsheet-sample.json', scope) gc = gspread.authorize(credentials) # ↑ここまでは毎回同じ内容 # ↓ここからは毎回違う内容（ここから記述） # 「キー」でワークブックを取得 SPREADSHEET_KEY = '対象のスプレッドシートキー' wb = gc.open_by_key(SPREADSHEET_KEY) # 「sheet1」で一番左のシートを取得 ws = wb.sheet1 |
「セル」を操作してみよう
「1つずつ」セルに値を「入力」する
まずは、1つずつセルに値を「入力」してみます。
セルに1つずつ値を入力するには、「update_acell()」の引数に「セルの場所」と「値」を指定して記載します（A1表記）。入力というか単語の意味的には「更新」って感じですね。
では、「A1」から「A4」のセルに値を入力してみましょう。「A4」セルには「関数」も「クォーテーション」で囲んで入力します。
※ 一番左の「update title」シートを取得。次回以降最初の「3行」は割愛します。
|
1 2 3 4 5 6 7 8 9 10 |
# 「キー」でワークブックを取得 SPREADSHEET_KEY = '対象のスプレッドシートキー' wb = gc.open_by_key(SPREADSHEET_KEY) ws = wb.sheet1 # A1表記で入力 ws.update_acell('A1', 1) ws.update_acell('A2', 2) ws.update_acell('A3', 3) ws.update_acell('A4', '=SUM(A1:A3)') # 関数も入力可能 |
上記を実行すると、スプレッドシートにリアルタイムに反映されているのが分かると思います。なんか気持ち良くて好きなんですよね。笑
また、「行」と「列」を指定する「R1C1表記」でもセルに値を入力することができ、次のように「update_cell()」の引数に「行数」「列数」「値」を指定します。
では、「B1〜B4」のセルに入力してみましょう。
|
1 2 3 4 5 |
# R1C1表記で入力 ws.update_cell(1, 2, 4) # B1 ws.update_cell(2, 2, 5) # B2 ws.update_cell(3, 2, 6) # B3 ws.update_cell(4, 2, '=SUM(B1:B3)') # B4（関数も入力可能） |
こちらも「B1〜B4」のセルに値を入力することができましたね。
A1表記の「update_acell()」とR1C1表記の「update_cell()」。
「a」があるかないかですがどちらも使えるようにしておきましょう。
「1つずつ」セルの値を「取得」する
続いて、1つずつセルの値を「取得」してみます。
セルの値を取得する場合も上記同様「A1表記（acell）」と「R1C1表記（cell）」どちらでも取得することができます。
では、次の「A1」と「B1」のセルの値を取得してみましょう。
コードはこちら。「value」属性を付けて取得します。
|
1 2 3 4 5 6 7 |
# A1表記で取得 a1 = ws.acell('A1').value # A1（値:1） print(a1) # R1C1表記で取得 r1c1 = ws.cell(1, 2).value # B1（値:4） print(r1c1) |
「A1」の「1」と「B1」の「4」が取得できましたね。
「複数のセル」を操作してみよう
「複数」のセルに値を「入力」する
続いて、「複数」のセルに一括で値を「入力」する方法を見ていきましょう。
「append_row()」を使って「行ごと」に値を入力することができます。「append_row」を使うと自動的にデータの「最終行」の次の行から入力してくれるため、特にセルの範囲を指定する必要はありません。
次のコードは、「append_row」の引数に「リスト型」のデータを渡して、データのある最終行の次の行（5行目）に入力するコードです。「複数行」を追加する場合は「for文」でループさせます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
# 複数のセルに値を入力（1行のみ） datas = ["A5追加", "B5追加"] ws.append_row(datas) # 複数のセルに値を入力（複数行） datas = [ ["A6追加", "B6追加"], ["A7追加", "B7追加"], ] for row_data in datas: ws.append_row(row_data) |
5行目から7行目（A5〜B7）にデータが入力されましたね。
「複数」のセルの値を「行ごと取得」する
今度は、「複数」のセルの値を「取得」してみましょう。
「行ごと」「列ごと」「すべて」の3パターンの取得方法について見ていきます。
まずは、「行ごと」にデータを取得します。先ほどのスプレッドシートの「2行目」を取得してみましょう。
「行ごと」のデータを取得するには、「row_values()」の引数に「行数」を指定して取得します。
では、次の3パターンを出力してみます。
①行のデータを「リスト」で出力、②「リスト」から1つの値を出力、③リストの全ての値を出力
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
# ①2行目をリストで取得 row_list = ws.row_values(2) print("--- 2行目をリストで出力 ---") print(row_list) # ②2行目の2つ目（B2）の値を出力 print("--- 2行目の2つ目（B2）の値を出力 ---") print(row_list[1]) # ③2行目の値を1つずつ出力 print("--- 2行目の値を1つずつ出力 ---") for data in row_list: print(data) |
それぞれ取得できました。
「複数」のセルの値を「列ごと取得」する
続いて、「列ごと」にデータを取得します。先ほどのスプレッドシートの「2列目（B列）」を取得してみましょう。
「列ごと」のデータを取得するには、「col_values()」の引数に「列数」を指定して取得します。
では、先ほどと同じように次の3パターンを出力してみます。
①列のデータを「リスト」で出力、②「リスト」から1つの値を出力、③リストの全ての値を出力
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
# ①2列目をリストで取得 col_list = ws.col_values(2) print("--- 2列目をリストで出力 ---") print(col_list) # ②2列目の2つ目（B2）の値を出力 print("--- 2列目の2つ目（B2）の値を出力 ---") print(col_list[1]) # ③2列目の値を1つずつ出力 print("--- 2列目の値を1つずつ出力 ---") for data in col_list: print(data) |
それぞれ取得できました。
「複数」のセルの値を「すべて取得」する
最後に、スプレッドシート にあるデータを「全て」取得（A1:B7）してみましょう。
「全て」のデータは、「get_all_values()」を使用して「二次元リスト」で取得できます。
同じように次の3パターンを出力してみます。
①全てのデータを「二次元リスト」で出力、②「二次元リスト」から1つの値を出力、③全ての値を出力
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
# ①全ての値（A1:B7）を二次元リストで取得 list_of_lists = ws.get_all_values() print("--- 全ての値（A1:B7）を二次元リストで出力 ---") print(list_of_lists) # ②2列目の2つ目（B2）の値を出力 print("--- 2列目の2つ目（B2）の値を出力 ---") print(list_of_lists[1][1]) # ③全ての値を1つずつ出力 print("--- 全ての値を1つずつ出力 ---") for row_data in list_of_lists: for data in row_data: print(data) |
※ なお、②についてはインデックス2つを使用して取得。③については「for」文1回で「行ごと」のデータをリストで取得、「for」文2回で「全て」の値を1つずつ取り出しています。
こちらもそれぞれ取得できています。（一部省略）
「その他」のセルの操作方法
上記で説明した方法以外も一覧にまとめてみます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 |
# 「キー」でワークブックを取得 SPREADSHEET_KEY = '対象のスプレッドシートキー' wb = gc.open_by_key(SPREADSHEET_KEY) ws = wb.sheet1 # 行をrows分追加 ws.add_rows(rows) # 列をcols分追加 ws.add_cols(cols) # 行数の取得（データのある最終行ではない） ws.row_count # 列数の取得（データのある最終列ではない） ws.col_count # index行を削除 ws.delete_row(index) # index行に値を挿入 ws.insert_row(values, index) # 【例文】 ws.insert_row([6, 15], index = 4) # 【結果】 4行目（A4とB4）に「6,15」を追加 # R1C1表記をA1表記へ gspread.utils.rowcol_to_a1(1, 1) # A1表記をR1C1表記へ gspread.utils.a1_to_rowcol('A1') # 指定した文字列を検索して一番最初にマッチしたセルを取得 cell = ws.find("検索したい文字列") # 指定した文字列を検索してマッチした全てのセルを取得 cell = ws.findall("検索したい文字列") cell.row # マッチしたセルの「行」 cell.col # マッチしたセルの「列」 cell.value # マッチしたセルの「値」 # 【例文】 cell = ws.find("A5追加") # print("行:%s 列:%s 値:%s" % (cell.row, cell.col, cell.value)) # 【結果】 行:5 列:1 値:A5追加 # 【例文】 # import re # 正規表現で取得することも可能（reモジュールをインポート） # cells = ws.findall(re.compile(r'^B')) # 「B」から始まるセルを取得 # for cell in cells: # print("行:%s 列:%s 値:%s" % (cell.row, cell.col, cell.value)) # 【結果】 # 行:5 列:2 値:B5追加 # 行:6 列:2 値:B6追加 # 行:7 列:2 値:B7追加 # 全ての値を削除 ws.clear() |
「リクエスト回数」の制限について
Pythonでスプレッドシートを読み書きする場合、下記のように制限が設けられているようです。
例えば、一番始めに「A1」から「A4」までセルに値を1つずつ書き込みましたが、これだけで「4回」のリクエストになります。
- ユーザーごとに100秒あたり100件のリクエスト
- 1回のプログラムで設定できる最大値は1,000件まで
- さらに1秒あたり10件まで
【参考】API リクエストの制限と割り当て
【参考】Google Cloud Platform_Google Sheets API
リクエスト回数が増えてくると処理時間にも影響が出てきます。
大量のデータを扱う際は、一度リストに格納して一括で書き込むなど少し工夫が必要なようです。このあたりは別の機会に説明できればと思います。
終わりに
以上、「gspread」で「Google スプレッドシート」の「セル」の値の取得・入力などの操作方法をお伝えしました。
前回の記事（ワークブック、ワークシートの操作方法）と今回の記事を読んでもらえれば大体の作業はできると思います。
【参考】公式サイト（gspread）
次回はPythonの得意な「スクレイピング」で取得したデータを「gspread」を使ってGoogle スプレッドシートに書き出す処理について説明したいと思います。
以上となります。最後まで読んでいただきありがとうございました！
