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

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

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

```python
from google.colab import auth
auth.authenticate_user()

# スプレッドシート連携するのに必要なライブラリをダウンロード
!pip install gspread
import gspread
from google.auth import default
creds, _ = default()

# 認証処理
gc = gspread.authorize(creds)
```

認証なしで実行したい場合は下記の記事を参考にしてください。
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を使ってデータを更新することが可能です。

```python
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`メソッドで検索したセルの場所は下記で取得可能です

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

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

```python:数値からアルファベットに変更する関数
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)
```

gspredにあるutilsを使用した書き方を追加しました。
https://qiita.com/plumfield56/items/7ae3323bc57750849ea0

### cell操作のメソッド
| 説明 | コード例 |
|:---|:---|
| セルの値取得 | cell.value |
| セルの行インデックス取得 | cell.row |
| セルの列インデックス取得 | cell.col |

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

```python: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`メソッド](https://gspread.readthedocs.io/en/latest/api.html#gspread.models.Spreadsheet.values_get)を使用する必要があります。
`values_get`メソッドはworkbookに使用するメソッドなので、rangeに`シート名!範囲`と指定をした上でパラメータで`valueRenderOption`を`FORMULA`にすることによってセルの中の関数を取得することが可能になります。

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

パラメータの設定の詳細は[公式ドキュメント](https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/get#query-parameters)を参照ください。

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

```python
workbook.values_get(range, params={'valueRenderOption':'FORMULA'})['values']
```
ただしこの状態だと日付は数値データとして取得されてしまうので、数値データを日付へ関数で変換してあげる必要があります。

```python:スマートな書き方
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')

```



```python:過去に使っていた内容

# 数値データを日付データへ変換
# テキストデータが混じっていた場合と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))

```