Cloud GCP Python Python 活用 プログラミング

【python できること】 Googleスプレッドシートにデータを読み書きする方法

 

pythonではデータ解析などで、データを取り合うことが多いです。

データベースにして共有するのが一般的ですが、環境の用意が手間です。

ちょっとしたデータを使うのであれば、スプレッドシートを使うのもありです。

 

本記事はこんな方におすすめです。

hituji
Google Cloudの設定から知りたい。

pythonでスプレッドシートを操作したい。

自動操作でデータをリアルタイムに反映させて共有したい。

 

本記事の内容

  • GCPでのAPI設定
  • ライブラリのインストール
  • スプレッドシートの書き込み
  • スプレッドシートの読み込み

 

この記事を読むと、Google Cloud PlatformのAPIの設定から、スプレッドシートの読み込みや書き込みを知ることができます。

pythonでデータ処理と書き込みを自動化すれば、自動更新されたデータが共有できることでしょう。

Googleアカウントが必要ですので、無い方は用意しておいてください。

 

サンプルコード

 

スポンサーリンク

GCPでのAPI設定

GDriveやスプレッドシートのAPIをGCPで有効にしていきます。

 

GCPについて

GCPはGoogleCloudPlatformの略です。

AmazonのAWSやIBM Cloud、WindowsのAzureと同じクラウドサービスです。

サーバーが簡単に構築できたり、AIの処理の実装などを手伝ってくれます。

まず、GCPにアクセスします。

>> Google Cloud Platform

 

プロジェクトの作成

GCPではプロジェクト単位で使うサービスなどを管理できます。

まず、プロジェクトを作成します。

GCPのダッシュボードの「プロジェクト選択」から「新しいプロジェクト」を選択します。

もしくは、下記のリンクに直接飛びます。

>> 新規プロジェクト作成

 

適当な名前でプロジェクトを作成します。

 

これで、新しいプロジェクトが作成され、次のようなダッシュボードがもらえます。

ここで、色々なサービスを管理します。

 

Google Drive APIを有効にする

Google Driveのスプレッドシートにpythonからアクセスできるようにします。

 

ナビゲーションメニューより、API＞ライブラリを選択します。

 

次に、Google Drive APIを検索します。

「drive api」を入力して、Google Drive APIを選びます。

 

APIを有効にします。

 

Google Sheet APIを有効にする

Google Drive APIと同じように、ライブラリ検索で、「Google Sheet API」を検索します。

適当に、「sheet api」と入れると出てきます。

こちらも有効にします。

 

これで必要なAPIを有効にしたので、接続するための認証情報を作っていきます。

Google APIの認証を取得

ナビケーションメニューから、API>認証情報へいきます。

 

認証情報を作成から、サービスアカウントを作成します。

 

サービスアカウント名と説明を入力して、作成を押します。

 

サービスアカウントができるので、リンクを押します

 

キーの項目から、鍵を追加から新しい鍵を作成を押します。

 

JSONを作成します。

 

ダウンロードしましょう。

 

このJSONファイルを使って、GDriveのスプレッドシートにアクセスします。

 

 

スプレッドシートを作成

GDriveにアクセスして、スプレッドシートを作成しましょう。

>> GDriveリンク

 

新規を押します。

 

Google スプレッドシートから、空白のスプレッドシートを作成します。

 

ファイル名を変更します。

 

次に、ファイルをpythonでアクセスできるようにします。

APIの認証でダウンロードしたJSONファイルを開いてください。

ここから、client_emailの情報をコピーしてください。

 

この情報を、共有から設定します。

 

client_emailを入力すると、該当するものが下に出てくるので、選択します。

選択したら、送信します。

 

さてここまでで、Google Driveに置いた、スプレッドシートがpythonで書き込み、読み込みができるようになりました。

続いて、コーディングを進めていきます。

ライブラリのインストール

スプレッドシートを操作するには、2つのライブラリを使います。

pipでインストールしてください。

pip install gspread
pip install oauth2client

 

>> gspreadドキュメント

 

ライブラリの読み込み

2つのライブラリ以外に、csvも使うので、csvも読み込みます。

import gspread
from oauth2client.service_account import ServiceAccountCredentials
import csv

 

設定

認証のjsonファイル、シート名などを設定します。

# 設定
json_file = '********.json'
file_name = '********'
sheet_name1 = 'シート1'
sheet_name2 = 'csv_sheet'
csv_file_name = 'Davis.csv'

 

認証のjson_fileとfile_nameは各自のものに変更ください。

csv_file_nameのファイルはサンプルコードのところにおいてあります。

サンプルコード

 

スプレッドシートにアクセス

次のコードでスプレッドシートにアクセスしましょう。

scope = ['https://spreadsheets.google.com/feeds',
         'https://www.googleapis.com/auth/drive']

# スプレッドシートにアクセス
credentials = ServiceAccountCredentials.from_json_keyfile_name(json_file, scope)
gc = gspread.authorize(credentials)
sh = gc.open(file_name)

 

シートの作成と削除

シートを作成してみます。

先ほど開いたスプレッドシートのshのadd_worksheetメソッドを使います。

行列の、rows、colsも必要ですので、入力しておきます。

# シートの作成
wks = sh.add_worksheet(title="new worksheet", rows='100', cols='30')

 

シートができていることを確認したら、シートを削除します。

del_worksheetを使って、読み込み済みのシートオブジェクト（wks）を渡します。

# シートの削除
sh.del_worksheet(wks)

 

 

シートのリストを取得

どんなシート名のシートがあるか確認します。

worksheetsメソッドでシート情報が取得できます。

sheet_list = [ws.title for ws in sh.worksheets()]
print(sheet_list)
['シート1']

 

 

シートの選択

次に、シートを選択します。

選択したシートに対して、読み込み、書き込みをします。

シートは左から0,1,2,..の番号か、シート名で選択できます。

ここでは、sheet_name1='シート1'のシートを読み込んでいます。

# シートの選択　シートの番号でも名前でもよい
wks = sh.get_worksheet(0)
wks = sh.worksheet(sheet_name1)

 

get_worksheetが番号で、worksheetがシート名なので、注意してください。

 

スプレッドシートの書き込み

スプレッドシートに書き込む方法を紹介します。

 

セルに書き込み

セルを1つもしくは複数指定して、値を書き込みます。

# 単一セル
wks.update_acell('A1', '1')
wks.update_cell(1, 2, '2')

# 複数セル
list_data = [[1, 2], [3, 4]]
wks.update('A2:B3', list_data)

 

acellはA1、B1などの指定。cellは行列数で指定します。

ここら辺はエクセルと同じですね。

 

複数の場合は、updateメソッドで、範囲とデータをリストで渡します。

 

CSVをシートに書き込み

ここでは、新しいシートを作って、CSVデータを書き込みます。

シートの作成は、先ほどやったadd_worksheetを使います。

やっていることは、CSVファイルをlistにして書き込んでいるだけです。

# 書き込むシート作成。すでにあれば読み込む
if sheet_name2 in sheet_list:
    wks = sh.worksheet(sheet_name2)
else:
    wks = sh.add_worksheet(title=sheet_name2, rows='300', cols='10')
# CSVを書き込み
wks.update(list(csv.reader(open(csv_file_name, encoding='cp932'))))

CSVはサンプルコードに一緒に置いてある、Davis.csvを使っています。

 

numpyの書き込み

同じシートに書き込みますので、一旦データをクリアします。

values_clearメソッドでシート名と範囲を指定します。

# シートデータクリア
sh.values_clear(f"{sheet_name2}!A1:F300")

 

numpyもlistにして書き込むだけです。

# numpy書き込み
import numpy as np
data = np.array([[1,2,3],[1,2,3],[1,2,3]])
wks.update('A1:C3', data.tolist())

 

pandasの書き込み

pandasもDavis.csvを読み込んで書き込んでいきます。

こちらもやることは同じで、listにして書き込みますが、DataFrameのcolumnsとvaluesを連結して書き込んでいます。

# pandas書き込み
import pandas as pd
df = pd.read_csv(csv_file_name).fillna('')
# 列名とデータを連結して書き込み
wks.update([df.columns.values.tolist()] + df.values.tolist())

 

 

スプレッドシートの読み込み

スプレッドシートから読み込む方法を紹介します。

 

セルから読み込み

単一、複数セルから読み込んでいきます。

wks = sh.worksheet(sheet_name1)
# 単一セル
val1 = wks.acell('A1').value
val2 = wks.cell(1, 2).value

print(val1, val2)

# 複数セル
list_data = wks.get('A2:B3')
print(list_data)
1 2
[['1', '2'], ['3', '4']]

sheet_name1='シート1'を読み込んでから、先ほど書き込んだデータを読み込んでいます。

 

単一のセルはacell,cellで対象のセルを指定して、valueで値をとっています。

複数の場合はgetメソッドで、セルの範囲を指定します。

 

シートから全部から読み込み

シート全体を読み込んで、numpyやpandasのDataFrameに入れていきます。

シートはsheet_name2='csv_sheet'を読み込みます。

 

pandasの時はget_all_recordsメソッドを使います。

# pandas
wks = sh.worksheet(sheet_name2)
df = pd.DataFrame(wks.get_all_records())

 

numpyの時はget_all_valuesメソッドを使います。

# numpy
wks = sh.worksheet(sheet_name2)
array = np.array(wks.get_all_values())

 

簡単ですね！！

手軽に使えるので、ビジネスで使っている方もいるのではないでしょうか？

毎日、人が処理していることがあれば、pythonで自動化してみましょう！

 

他の活用方法は「python 活用 できることまとめ」も参考にしてください。

>> python 活用 できることまとめ

Pythonスキルが身に付いたら、ぜひスキルを生かして稼いでいきましょう！

>> Pythonでの副業の始め方紹介【隙間時間 で可能】

-Cloud, GCP, Python, Python 活用, プログラミング