Power Automateを動画で学ぼう!Udemyキャンペーン中

【2021年】PythonでGoogleスプレッドシートに書き込みする方法

目次

Pythonでスプレッドシートに書き込む方法(Spread Sheet)

Pythonからスプレッドシートにアクセスするには、下記の2点の準備が必要です。この記事では下記設定手順について解説します。

じょじお

この記事で紹介している設定は15分ほどの作業となっています。GCPには無料枠があるので、枠の範囲内であればどなたでも無料で利用することができます

  1. (STEP1)Google Cloud Platform(GCP)でプロジェクトの設定
  2. (STEP2)Googleスプレッドシートの設定

GCPの無料枠について確認しておこう!

じょじお

はじめにGCPの無料枠について確認してみましょう!

GCPをはじめて利用開始すると1年間300ドル分のクレジットがついた無料期間がはじまります。この期間であれば課金が発生することはありません。クレジットカードの登録は必須ですが期間が終了しても自動的に課金が始まることはありません。

1年間が経過後は本登録のお手続きをすれば利用中のAPIを使い続けることができます。本登録後もAlways Freeという、GCPの各サービスに設けられた無料枠がありますので、その範囲内で使用する限りは無料で使い続けることが可能です。(具体的な使用量についてはこちらのGoogle公式情報をご確認ください。)

ただし、本登録後は無料使用量を超えると自動で課金されてしまいますのでご注意ください。使用量が一定のラインを越えたらメール通知を受ける設定もあるので設定しておくことをおすすめします。(メール通知を設定しても自動課金がストップするわけではありません。)

本記事の内容は古くなる可能性があります。無料枠で使っているつもりが自動的に課金が始まってしまう可能性もありますので、ご利用の際は必ずご自身でGoogleの公式情報を確認してください。この記事の内容によって損害が生じても当方では補償できかねます。

(STEP1)GCPの設定

STEP
GCPの利用登録をします。

GoogleサービスのAPIを使用するには、Google Cloud Platform(以下GCP)の利用登録が必要です。

GCPアカウントをお持ちでない方はアカウントを作成します無料枠で利用する場合もクレジットカードの登録が必要になります

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

[GCP]プロジェクトの作成

▼登録ができましたら、GCPコンソールを開きます。

GCPコンソール画面

▼プロジェクトの選択をクリックします。

gcpプロジェクトの選択

▼プロジェクトの選択画面が開きます。「新しいプロジェクト」をクリックします。

▼プロジェクト名を入力して「次へ」をクリックします。

プロジェクト名を入力

これでプロジェクトが作成されます。

STEP
プロジェクトへライブラリを追加します 。

[GCP]プロジェクトへライブラリを追加する

▼左上メニューを開き、「APIとサービス」→「ライブラリ」をクリックします。

GCPライブラリの追加

▼Google driveと検索して、「Google Drive API」を探してクリックします。

Driveapiを追加する

▼追加できました。

drive APIの追加

▼同じ要領で「Sheets api」と検索して「Google Sheets api」を追加します。

sheets api 追加

▼2つライブラリが追加できたか確認をします。左上のハンバーガーメニューを開き、「api」とサービス」→「ダッシュボード」をクリックします。

ライブラリの確認

▼ダッシュボードの中に2つのAPIの名前が表示されていたら追加成功です。

apiダッシュボード
STEP
サービスアカウントを作成します。

[GCP]サービスアカウントを作成する。

▼左上のハンバーガーメニューを開き、「APIとサービス」→「認証情報」をクリックします。

認証情報の追加

▼認証情報を作成をクリックします。

認証情報を追加

▼「サービスアカウント」をクリックします。

サービスアカウントの追加

▼サービスアカウントの①名前、②説明を入力して③作成して続行をクリックします。

サービスアカウントの作成

▼サービスアカウントのロールを追加します。書き込みを行う場合は編集者を選択します。

サービスアカウントのロール

▼完了をクリックします。

サービスアカウントの作成完了

以上でサービスアカウントの作成ができました。

STEP
JSON秘密鍵ファイルを作成します。

[GCP]JSON秘密鍵ファイルを作成する。

▼サービスアカウントの追加ができました。作成したサービスアカウントの編集アイコンをクリックします。

秘密鍵の追加

▼「キー」タブをクリックします。

キーをクリックします。

▼鍵を追加をクリックします。

カギを追加

▼新しい鍵を作成をクリックします。

新しい鍵を作成

▼キーのタイプを「JSON」を選択し「作成」をクリックします。

キーのタイプの選択

▼キーが作成されダウンロードが始まります。このファイルは今後、これから作成するPythonスクリプトファイルを実行するたびに読み込むファイルとなります。大切に保管しましょう

キーの作成
じょじお

ここまででGCPの設定はおわりです。次はスプレッドシートを作成と設定をしていきます!

(STEP2)スプレッドシートの設定

じょじお

ここからは、スプレッドシートの設定を行います。

STEP
スプレッドシートの共有設定とアクセス権の設定

▼ STEP1にてGCPでダウンロードしたJSON秘密鍵ファイルを開き“client_email”のところのメール形式のバリューをコピーします。(赤字部分)

JSONの中身
{
  "type""service_account",
  "project_id""searchresultpythontogspread",
  "private_key_id""***************************",
  "private_key""-----BEGIN PRIVATE KEY-----\************",
  "client_email""**************@*************.iam.gserviceaccount.com",
 イカ省略
}

▼ブラウザでスプレッドシートを開き、画面右上の共有ボタンをクリックします。

▼JSONに記述されていたメールアドレスを追加して完了ボタンをクリックします。

メールアドレスを追加

以上でスプレッドシートにPythonからアクセスして書き込む設定は終わりです。

じょじお

Pythonで読み込みだけではなく、書き込みも行う場合は、編集権限も付与してくださいね。

STEP
スプレッドシートIDのシートIDを取得します。

スプレッドシートIDの取得

▼Pythonコードを記述するときに必要になるシートIDをコピーします。シートIDは、スプレッドシートを開いたときにブラウザのアドレスバーに表示されるURLの下記赤字の部分です。赤字部分をコピーしておきましょう。

https://docs.google.com/spreadsheets/d/********************************************/edit#gid=0

シートIDとは、シートを特定するためにスプレッドシートファイルに割り当てられたユニークIDです。

じょじお

以上でスプレッドシートの設定はおわりです。何度もいいますがSTEP1でDLしたJSONファイルはPythonコードから読み込みを行うので、この後も大切に保管してくださいね。

Pythonプログラムの作成

じょじお

ここからは、Pythonを操作していきます。わたしの環境はWindows+VisualStudio Code+Pip環境ですので、その環境を前提として解説させていただきます。Pythonのインストールの説明は省略します。

必要なPythonパッケージをインストールします。

VS Codeのターミナル(あるいはターミナル、コマンドプロンプト)を開き、下記の2つのパッケージをインストールしましょう。(Macはpip→pip3コマンドかもしれません。また、pipではなくcondaの人もいるかもしれません。)

$ pip install gspread
$ pip install oauth2client

gspreadはスプレッドシートを操作するパッケージ、oauth2clientはGoogleとの認証を簡略化するためのパッケージです。

お決まりの文句を記述する。

下記は、スプレッドシートにアクセスするときに必ず記述するお決まりのコードです。(良く使うのでコピペツールに保存しておくと便利です。ちなみに私はBoostNoteとCliborを使っています。)

import gspread
from google.oauth2.service_account import Credentials


# お決まりの文句
# 2つのAPIを記述しないとリフレッシュトークンを3600秒毎に発行し続けなければならない
scope = ['https://www.googleapis.com/auth/spreadsheets','https://www.googleapis.com/auth/drive']
#ダウンロードしたjsonファイル名をクレデンシャル変数に設定。
credentials = Credentials.from_service_account_file("JSONファイルのパス", scopes=scope)
#OAuth2の資格情報を使用してGoogle APIにログイン。
gc = gspread.authorize(credentials)

#スプレッドシートIDを変数に格納する。
SPREADSHEET_KEY = 'シートID'
# スプレッドシート(ブック)を開く
workbook = gc.open_by_key(SPREADSHEET_KEY)

コード文中の、

「シートID」のところは、先程取得したスプレッドシートのURLから取得したシートIDに置き換えてください。

「JSONファイルのパス」は、先程取得したJSONファイルのフルパスか相対パスに置き換えてください。

テストコード

さて、ようやくこれでいよいよgspreadパッケージを使用してコードを好きなように書いていく準備は整いました。

ですがその前に、簡単なコードを書いてスプレッドシートとの連携が本当にできるのかテストをしてみましょう。下記のコードを先ほど紹介した”お決まりの文句”の下に追記してみてください。「スプレッドシートの”シート1”という名前のシートのA1セルに”test value”という文字を挿入する」という簡単なコードです。コードが作成できたらスクリプトを実行して、スプレッドシートのA1セルに出力されるかチェックしてみてくださいね。

    # スプレッドシート(ブック)を開く
    workbook = gc.open_by_key(SPREADSHEET_KEY)

    # シートの一覧を取得する。(リスト形式)
    worksheets = workbook.worksheets()
    print(worksheets)

    # シートを開く
    worksheet = workbook.worksheet('シート1')

    # セルA1に”test value”という文字列を代入する。
    worksheet.update_cell(1, 1, 'test value')

エラーがでた、スプレッドシートに書き込みができない場合

良くあるエラーとして下記があります。確認してみてくださいね。

  • JSONファイルのパスの記述が間違っている。
    1. 混乱しないようにJSONファイルをPythonスクリプトファイルと同じフォルダに保存して、スクリプトファイル内でファイル名だけで指定してみてください。
  • JSONファイルが壊れている。
    • STEP1に戻り、JSONファイルの取得のところをもう一度実施し、JSONファイルを再取得してみてください。JSONファイルの名前を変更することは良くないという情報もありました。名前を変更しないでトライしてみてください。(わたしは変更しても使用できました。)
  • シートIDの記入ミス
    • シートIDをもう一度確認しなおしてみてください。

▼スプレッドシートの共有設定ができていないとコンソールに下記のようなエラーがでます。きちんと「編集者」になっているか確認しましょう。

gspread.exceptions.APIError: {‘code’: 403, ‘message’: ‘The caller does not have permission’, ‘status’: ‘PERMISSION_DENIED’}

Pythonプログラムの作り方

じょじお

以上でPythonから、スプレッドシートを操作するための準備と基本は終わりです。あとはgspreadパッケージを使ってコードをゴリゴリ書いていきましょう!

ぽこがみさま

gspreadの具体的な使い方は公式ドキュメントを確認してください!!

Pythonを学ぶならコチラの講座がおすすめです。

わたしは「デイトラPythonコース」でPythonを学びました。

デイトラの良いところ
  • SNS(インスタ・Twitter・Youtube等)のAPIを駆使したマーケ特化のモダンなシステムを作りながら学べる。
  • 機械学習の基礎を実用的なシステムを作りながら学べる。
  • 現役エンジニアがメンター。1年間Slackで質問し放題。
  • SlackやTwitterで受講生の発信が盛んなのでひとりでの学習でもモチベ維持しやすい。
  • 月に2~4回行われるオンラインセミナー(現役のマーケター・フリーランサー・デザイナー社長・エンジニア等のWeb界隈のすごい人達が講師)が無料で受講可能。
メンターさんがとても親切でガチ中のガチな初心者でも質問しやすい環境でした。

2021年08月16日現在 10,000円OFFキャンペーン中のようですので興味のある方はリンクから詳細をご確認ください。キャンペーンは終了し現在は通常価格のようです。

ノンプログラマーでも自動化スキルを身につける。

この記事が気に入ったら
フォローしてね!

よかったらシェアしてね!
URLをコピーする
URLをコピーしました!
目次
閉じる