Help us understand the problem. What is going on with this article?

Pythonでのスプレッドシート操作方法(gspreadの使い方)

Pythonでスプレッドシートを操作する時のライブラリgspreadでもよく使う操作をまとめました。
個人的によく使用する操作をピックアップしたので、詳細を知りたい場合は下記の公式サイトを参照ください。
https://gspread.readthedocs.io/en/latest/

スプレッドシートを扱うための事前準備

# スプレッドシート連携するのに必要なライブラリをダウンロード
!pip install gspread
from google.colab import auth
from oauth2client.client import GoogleCredentials
import gspread

# 認証処理
auth.authenticate_user()
gc = gspread.authorize(GoogleCredentials.get_application_default())

認証なしで実行したい場合は下記の記事を参考にしてください。
https://qiita.com/plumfield56/items/3d9e234366bcaea794ac

gspreadのコード一覧

gcの操作

名前、URL、IDからスプレッドシートのworkbookを取得するメソッド

説明 コード例
名前でworkbookを取得 gc.open_by_key('TITLE')
IDでworkbookを取得 gc.open_by_key('ID')
URLでworkbookを取得 gc.open_by_url('URL')
workbookの作成 ※マイドライブに作成される gc.create('NAME')

workbookの操作

workbookを操作して、タイトルやシートの取得をするメソッド

説明 コード例
workbookのタイトル取得 workbook.title
workbookのID取得 workbook.id
indexでworksheetを取得 workbook.get_worksheet(0)
名前でworksheetを取得 workbook.worksheet("sheet1")
全てのworksheetを取得 workbook.worksheets()
worksheetの作成 workbook.add_worksheet(title="A worksheet", rows="100", cols="20")
worksheetの削除 workbook.del_worksheet(worksheet)

下記は少し特殊でworkbookからDataFrameを使ってデータを更新することが可能です。

workbook.values_update(
  # sheet!A1などセルを指定
  target_cell,
  # パラメーターを指定してシートへ張り付ける場合
  params={
      'valueInputOption': 'USER_ENTERED'
  },
  # 二次元のリストで反映させたい内容を記載
  body={
      'values': [df.columns.values.tolist()] + df.values.tolist()
  }
)

worksheetの操作(データ取得や検索)

worksheetを操作して、指定範囲のデータ取得、データ更新、削除などを行うメソッド

データを取得するメソッド一覧

説明 コード例
worksheet名を取得 worksheet.title
worksheetIDを取得 worksheet.id
A1形式でセルの値を取得 worksheet.acell('B1').value
R1C1形式でセルの値を取得 worksheet.cell(1, 2).value
A1形式でセルの関数を取得 worksheet.acell('B1', value_render_option='FORMULA').value
R1C1形式でセルの関数を取得 worksheet.cell(1, 2, value_render_option='FORMULA').value
●行目のデータをリストで取得 worksheet.row_values(1)
●列目のデータをリストで取得 worksheet.col_values(1)
worksheetの全データをリストで取得 worksheet.get_all_values()
worksheetの全データを辞書で取得 worksheet.get_all_records()

データを更新するメソッド一覧

説明 コード例
A1形式でセルの更新 worksheet.update_acell('B1', 'word')
R1C1形式でセルの更新 worksheet.update_cell(1, 2, 'word')
指定範囲での更新 worksheet.update('A1:B2', [[1, 2], [3, 4]])

データを検索するメソッド一覧

| 文字の検索 | worksheet.find("word") |
| 正規表現での検索 | worksheet.find(re.compile(r'正規表現'))|
| 文字の検索(一致する全て) | worksheet.findall("word") |
| 正規表現での検索(一致する全て) | worksheet.findall(re.compile(r'正規表現'))|| 範囲を配列で取得

上記のfindメソッドで検索したセルの場所は下記で取得可能です

fineメソッド
cell = worksheet.fine('serch_word')
print(f'値:{cell.value}、列のindex:{cell.col}、行のindex:{cell.row}')

上記の findメソッドで列を取得した場合は数値で返ってくるので、
アルファベットで知りたい場合は変換する必要があります。
その場合は下記の関数で変更できます。

数値からアルファベットに変更する関数
def conv_num_to_col(num):  
  if num <= 26:
    return chr(64 + num)
  else:
    if num % 26 == 0:
      return conv_num_to_col(num//26-1) + 'Z'
    else:
      return conv_num_to_col(num//26) + chr(64+num%26)

cell操作のメソッド

説明 コード例
セルの値取得 cell.value
セルの行インデックス取得 cell.row
セルの列インデックス取得 cell.col

pandasを使ったデータ取得からシートへの反映

pandas操作
import pandas as pd

# リストで取得したデータをデータフレームにする
df = pd.DataFrame(worksheet.get_all_records())

# データフレームをリストに反映させる
worksheet.update([dataframe.columns.values.tolist()] + dataframe.values.tolist())

# パラメーターを指定してシートへ張り付ける場合
workbook.values_update(
  # sheet!A1などセルを指定
  target_cell,
  params={
      'valueInputOption': 'USER_ENTERED'
  },
  body={
      'values': [df.columns.values.tolist()] + df.values.tolist()
  }
)

日付(数値)を取得する方法

日付を取得したいけど基本的にはテキストで取得してしまうので、
日付を取得するためには一工夫が必要になります。

まずvalues_getメソッドを使用する必要があります。
values_getメソッドはworkbookに使用するメソッドなので、rangeにシート名!範囲と指定をした上でパラメータでvalueRenderOptionFORMULAにすることによってセルの中の関数を取得することが可能になります。

workbook.values_get(range, params={'valueRenderOption':'FORMULA'})

パラメータの設定の詳細は公式ドキュメントを参照ください。

そうするとmajorDimension, range, valuesをキーにした辞書(dict)型で返ってくるのでvaluesを取り出せばlistの二次元配列が取得できます。

workbook.values_get(range, params={'valueRenderOption':'FORMULA'})['values']

ただしこの状態だと日付は数値データとして取得されてしまうので、数値データを日付へ関数で変換してあげる必要があります。

スマートな書き方
values= workbook.values_get(range, params={'valueRenderOption':'FORMULA'})['values']
df = pd.DataFrame(values, columns = values[0])
df[column_name] = pd.to_timedelta(df[column_name].astype('float'), unit='D') + pd.to_datetime('1900/01/01')

過去に使っていた内容
# 数値データを日付データへ変換
# テキストデータが混じっていた場合とNoneが入っているとエラーにあるのでその場合はそのままreturnする

from datetime import datetime, timedelta

def conv_num_to_date(data):
  if type(data) == str:
    return data
  elif data is None:
    return data
  else:
    return str(datetime(1899, 12, 30) + timedelta(days=int(data)))[:10]

values= workbook.values_get(range, params={'valueRenderOption':'FORMULA'})['values']
df = pd.DataFrame(values, columns = values[0])
df[column_name].map(lambda data: conv_num_to_date(data))

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away