gspreadで指定行にまとめて書き込み

googleスプレッドシートにコードからアクセスして指定した行を編集したい。

（設定についてはあちこちで説明されているので割愛）

今回はgspreadを使ってみる。
Referenceを参考にいろいろためす
https://gspread.readthedocs.io/en/latest/

import gspread

scope = ['https://spreadsheets.google.com/feeds']
doc_id = 'xxxxx'
path = os.path.expanduser("./credentials.json")
credentials = ServiceAccountCredentials.from_json_keyfile_name(path, scope)
client = gspread.authorize(credentials)
gfile = client.open_by_key(doc_id)
worksheet = gfile.sheet1

上記で作業対象のシートを指定した後、
更新用のデータを用意してfor文で回してそれぞれをセルごとに更新してもどうにかなるけど、

insert_data = {
1:AAA
3:CCC
}

for k, v in insert_data.items():
    worksheet.update_cell(1, k, v)

これだとセル更新のたびスプレッドシートにアクセスするため更新するセルが多いと完了までに時間がかかる。
そこで

# 100行目の列1~21を取得
cell_list = worksheet.range(100, 1, 100, 21)
# 取得したcell_listを編集
for k, v in insert_data.items():
    cell_list[k - 1].value = v
# 編集したcell_listをシートに反映
worksheet.update_cells(cell_list) 

update_cellsを使うと行単位でまとめて更新できる 。

スプレッドシート上の関数の扱い

シート上に下記のようなデータがあった時、

A1	B1	C1
aaa	=A1

そのままだとスプレッドシートに予め入れてあったB1の関数から値しか取得できず（文字列aaaを取得）、行の更新時に文字列aaaとして上書きされるのでB1の式が消えてしまう。

A1	B1	C1
AAA	aaa	CCC

 
これを避けるには、
データ取得時にvalue_render_option='FORMULA'を指定する。

worksheet.row_values(300, value_render_option='FORMULA')

これでB1のデータとして'=A1'が取得できる。

また、更新時に下記value_input_option='USER_ENTERED'を指定

worksheet.update_cells(cell_list, value_input_option='USER_ENTERED')

これで関数をそのままシートに戻せる。

# cell_list取得
cell_list = worksheet.range(100, 1, 100, 21)

# シート上の対象列のデータを取得
target_row = worksheet.row_values(100, value_render_option='FORMULA')

# シートから取得したデータを編集
for k, v in insert_data.items():
    target_row[k - 1] = v

# cell_listに編集したデータを格納
for i, data in enumerate(target_row):
cell_list[i].value = data

# 更新
worksheet.update_cells(cell_list, value_input_option='USER_ENTERED')

A1	B1	C1
AAA	=A1	CCC

うまくいった。