gspreadを利用してスプレッドシートを操作する方法リスト。
gspreadを使用できるための事前準備編は下記のページから↓↓
【Python×Googleスプレッドシート】スプレッドシートの操作をする方法《事前準備》
今回はPythonを使ってGoogleスプレッドシートを操作するには、事前に鍵を取得しなければならない。今回はその鍵の取得からスプレッ...
Contents
ワークシートの操作
ワークシートの新規作成
add_worksheet(title=”シート名”, rows=行数, cols=列数)
# 最初にidでワークブックを取得しておく
SP_id = '＊＊＊＊'
wb = gc.open_by_key(SP_id)
# 新しいワークシートの追加
wb.add_worksheet(title="newsheet", rows=50, cols=10)
今後ワークシートを指定して作業するのに、
[.id]でワークシートのidを取得しておくと便利。
# ワークブック、ワークシートを指定
SP_id = '＊＊＊＊'
wb = gc.open_by_key(SP_id)
ws = wb.worksheet("newsheet")
# ワークシートidの取得
ws_id = ws.id
ワークシートのコピー
duplicate_sheet(source_sheet_id = シートのid,
new_sheet_name = “コピーしたシートの名前”,
insert_sheet_index = コピーしたシートの設置index番号)
# 最初にidでワークブックを取得しておく
SP_id = '＊＊＊＊'
wb = gc.open_by_key(SP_id)
# ワークシートのid取得
ws = wb.worksheet("newsheet")
ws_id = ws.id
# ワークシートのコピー
wb.duplicate_sheet(source_sheet_id = ws_id, new_sheet_name = "copysheet", insert_sheet_index = 2)
ワークシートの削除
del_worksheet(ワークシート)
# 最初にidでワークブックとワークシートを指定しておく
SP_id = '＊＊＊＊'
wb = gc.open_by_key(SP_id)
ws = wb.worksheet("削除するシート名")
# ワークシートの削除
wb.del_worksheet(ws)
ワークシート一覧の取得
worksheets()
ワークシートの一覧が一次元配列に格納される
# 最初にidでワークブックとワークシートを指定しておく
SP_id = '＊＊＊＊'
wb = gc.open_by_key(SP_id)
# ワークシートのリストを取得
ws_list = wb.worksheets()
ワークシートの数を調べるのは「len(wb.worksheets())」
ワークシートコピーの際に、indexの位置をlen(wb.worksheets())にしておけば毎回一番右側にワークシートのコピーが作成される。
wb.duplicate_sheet(source_sheet_id = ws_id, new_sheet_name = "copysheet", insert_sheet_index = len(wb.worksheets())シート名の変更
update_title(‘変更後のシート名’)
# ワークブックとワークシートの指定
wb = gc.open('ワークブック')
ws = wb.worksheet('変更したいシート')
# シート名変更
ws.update_title('変更後のシート名')
セルの操作
以降の操作は基本的に下記画像のスプレッドシートを使用している。
セルの値を取得する
＜前提条件＞
# ワークブックとワークシートの指定
wb = gc.open('Pythonシート')
ws = wb.worksheet('シート1')- ws.acell(‘番地’).value
- ws.cell(行番号,列番号).value
# セルの指定
# セル番地を直接指定する
value_1 = ws.acell('C3').value
# 行番号と列番号を指定して取得 : cell(行番号,列番号)
value_2 = ws.cell(3,3).value
複数セルの値を取得する(範囲・行・列・シート)
＜前提条件＞
# ワークブックとワークシートの指定
wb = gc.open('Pythonシート')
ws = wb.worksheet('シート1')セルの範囲を1次元配列に格納する
ws.range(‘番地:番地’)
# セルの範囲を取得
sp_range = ws.range('A1:F4')
pprint(sp_range)
"""
出力結果
[<Cell R1C1 ''>,
<Cell R1C2 'テスト1'>,
<Cell R1C3 'テスト2'>,
<Cell R1C4 'テスト3'>,
<Cell R1C5 ''>,
<Cell R1C6 ''>,
<Cell R2C1 '値1'>,
<Cell R2C2 '1'>,
<Cell R2C3 '2'>,
<Cell R2C4 '3'>,
<Cell R2C5 '4'>,
<Cell R2C6 '5'>,
<Cell R3C1 '値2'>,
<Cell R3C2 '10'>,
<Cell R3C3 '20'>,
<Cell R3C4 '30'>,
<Cell R3C5 '40'>,
<Cell R3C6 '50'>,
<Cell R4C1 '合計(sum関数)'>,
<Cell R4C2 '11'>,
<Cell R4C3 '22'>,
<Cell R4C4 '33'>,
<Cell R4C5 '44'>,
<Cell R4C6 '55'>]
"""
行・列を1次元配列に格納
- ws.row_values(行番号)
- ws.col_values(列番号)
# 行の値を1次元配列に格納
row = ws.row_values(2)
print(row)
"""
出力結果
['値1', '1', '2', '3', '4', '5']
"""
# 列の値を1次元配列に格納
col = ws.col_values(4)
print(col)
"""
出力結果
['テスト3', '3', '30', '33']
"""ワークシートの全ての値を取得する
- リスト型で取得：ws.get_all_values()
- 辞書型で取得：ws.get_all_records()
辞書型で取得する際にエラーが出てしまうため、
今回は下記画像のようにスプレッドシートを編集している。
# リスト型で取得
ws_list = ws.get_all_values()
pprint(ws_list)
"""
出力結果
[['', 'テスト1', 'テスト2', 'テスト3', 'テスト4', 'テスト5'],
['値1', '1', '2', '3', '4', '5'],
['値2', '10', '20', '30', '40', ''],
['合計(sum関数)', '11', '22', '33', '44', '5']]
"""
# 辞書型で取得
ws_dict = ws.get_all_records()
pprint(ws_dict)
"""
出力結果
[{'': '値1', 'テスト1': 1, 'テスト2': 2, 'テスト3': 3, 'テスト4': 4, 'テスト5': 5},
{'': '値2', 'テスト1': 10, 'テスト2': 20, 'テスト3': 30, 'テスト4': 40, 'テスト5': ''},
{'': '合計(sum関数)', 'テスト1': 11, 'テスト2': 22, 'テスト3': 33, 'テスト4': 44, 'テスト5': 5}]
"""引数に「value_render_option=’FORMULA’」を設定すると、
値ではなく数式を取得する。
# 例
row = ws.row_values(4, value_render_option='FORMULA')
print(row)
"""
出力結果
['合計(sum関数)', '=sum(B2:B3)', '=sum(C2:C3)', '=sum(D2:D3)', '=sum(E2:E3)', '=sum(F2:F3)']
"""
セルの値を取得するものなら大体使える！
acell、row_values、col_values、get_all_values…
セルに値を入力する
＜前提条件＞
# ワークブックとワークシートの指定
wb = gc.open('Pythonシート')
ws = wb.worksheet('シート1')単一セルに値を入力する
- ws.update(‘番地’, ‘入力したい値’)
- ws.update_cell(行座標, 列座標, ‘入力したい値’)
# 番地指定でセルに値を入力
cell = ws.update('A7', 'りんご')
# 座標指定でセルに値を入力
cell2 = ws.update_cell(7, 2, 'Apple')
# セルに数式を入力
cell3 = ws.update('C7','=sum(C2:C4)',value_input_option='USER_ENTERED')
《セルの値入力のオプション設定》
数式として入力したいときは
value_input_option=’USER_ENTERED‘
文字列として入力したいときは
value_input_option=’RAW‘
複数セルにまとめて入力する
ws.update(‘番地:番地’, リスト)
# 複数セルへの入力
cell_list = [["a","b"],["c","d"]]
ws.update('A9:B10', cell_list)
1列or１行に1次元配列の値を入力したいときは
for文でループ処理をする。
cell_list2 = [1,2,3,4,5,6,7,8]
for i in range(len(cell_list2)):
ws.update_cell(i+1,8,cell_list2[i])
セルの値を消す
＜前提条件＞
# ワークブックとワークシートの指定
wb = gc.open('Pythonシート')
ws = wb.worksheet('シート1')- ws.batch_clear([“番地:番地”, “番地:番地”])
- ws.clear()
# 範囲を指定してセルをクリアするとき
ws.batch_clear(["A1:B2", "D1:E2"])
# シート全体のセルをクリアするとき
ws.clear()そのほかの操作
公式ドキュメントに、他にも色々な活用方法が載っている。
