Python日記

【GAS】Pythonで、スプレッドシートの最終行に自動入力して更新する方法

GAS スプレッドシートの最終行を取得の巻
お悩みさん

Pythonとスプレッドシートの連携ができたけど、

最終行に更新できないじゃん!

Pythonとスプレッドシートの連携をしていて、こんな風に思ったことないですか。

僕も調べたのですが、なぜか最終行を指定する機能が設定されていないんですよね。

エクセルを使うときのopenpyxlでは、

max_rowという機能が標準でついていて、とっても便利なのですが、、、。

そんなわけで今回は、Pythonとスプレッドシートを連携した際に、

最終行に更新する関数を作成していきます。

この記事で学べること

  1. スプレッドシートの最終行を取得する変数の作成
  2. Pythonでスプレッドシートの最終行に入力する方法

環境

OSMac 10.15.4
Pythonpython 3.9
仮想環境Poetry
Homebrew8.0.19 Homebrew

最初に、おさらい記事のご紹介

もっと基本的なことから教えて欲しい!

そんな人は以下の記事を参考にしてください。

Pythonとスプレッドシートの連携 おさらい

Pythonとスプレッドシートの連携方法については、以下の記事で詳しく解説しています。

>>>【GAS】Pythonでスプレッドシートに権限を付与する方法【Google Sheets APIの始め方】

Pythonとスプレッドシートの連携 準備運動

Pythonとスプレッドシートの連携方法ができたら、よければ以下の記事も参考にしてみてください。

ちょっとした準備運動にはぴったりだと思います。

>>>【GAS】Pythonでスプレッドシートの文字検索と、シートに転記(コピー)する方法【Google Sheets API】

>>>【GAS】Pythonでスプレッドシートの行ごと別シートに転記(コピー)する方法【Google Sheets API】

スプレッドシートの準備

前回以下の記事で使ったGoogle Analytics フォーマットを使って、

以下ののスプレッドシートを作成しました。

スプレッドシート month activeUser newUser conversion

見ての通り3行目の10月がブランクになっているので、

そこにうまくPythonから取り出した情報を入力できるように設定をしていきます。

Pythonのスクリプト:next_rowの作成

スプレッドシートの最終行を、

next_row変数に入れるようにスクリプトを書いていきます。

import gspread
from oauth2client.service_account import ServiceAccountCredentials
import os
import re
import json
os.environ['GOOGLE_APPLICATION_CREDENTIALS'] = 'xxxxxxx.json' #ご自身のjsonファイルを指定

scope = ["https://spreadsheets.google.com/feeds","https://www.googleapis.com/auth/spreadsheets","https://www.googleapis.com/auth/drive"]
creds = ServiceAccountCredentials.from_json_keyfile_name('xxxxxxxxx.json',scope) #ご自身のjsonファイルを指定
client = gspread.authorize(creds)

sh = client.open_by_key('xxxxxxxxxxxxx') #今回はkeyでスプレッドシートを呼び出します。
sheet1 = sh.sheet1 #シート1を指定しています

#_________________________________

def next_available_row(sheet1):
    str_list = list(filter(None, sheet1.col_values(2)))
    return str(len(str_list)+1)

next_row = next_available_row(sheet1)

※前半部分の記述はいつも通りスプレッドシートを連携させています。

def next_available_row の作成

まずstr_list変数を作成しています。

最終行を取り出すために、大きく4段階の処理をしています。

  • str_list変数の作成
  • filterでNone(セルに記述なし)までの値を抽出
  • それらをすべてリスト化して、str_listに取り込む
  • 最後に+1

sheet1.col_values(x):xに指定の行数を入力

今回は先のスプレッドシートで見ていただいた通り、

1列目にはすでに「10月」の記載があるので、

2列目の最終行を取得する必要がありました。

ので、sheet1.col_values() の引数に2(列目)を入れています。

next_row の作成

最後に取り出した最終行(+1)の行数を、

next_row変数に入れれば完了です!

【補足】スプレッドシート更新時のポイント

スプレッドシートを更新する際の一例です。

sheet1.update_cell(next_row,2,100)

update_cellを使う場合、引数は、①行番号 ②列番号 ③値 の順番で入力します。

上記の場合、最終行の2列目に、100が入力されます。

行ではなく最終「列」に入力したい場合

ちなみに、今回は行をピックアップしましたが列も同じ考え方で指定ができます。

def next_available_col(sheet1):
    str_list = list(filter(None, sheet1.row_values(2)))
    return str(len(str_list)+1)

next_col = next_available_col(sheet6)

列を取り出す場合は、row_values()を使うことに注意してください!

【参考】スクリプトの実行

もう必要な説明はすべて終えていますが、念のため、next_row変数を実際に使ってみます。

今回は、Google Analytics4の数値を取り出して、

スプレッドシートに入力したため、余計なモジュールなどがたくさんありますがご容赦ください。

from google.analytics.data_v1beta import BetaAnalyticsDataClient
from google.analytics.data_v1beta.types import DateRange
from google.analytics.data_v1beta.types import Dimension
from google.analytics.data_v1beta.types import Metric
from google.analytics.data_v1beta.types import RunReportRequest
import gspread
from oauth2client.service_account import ServiceAccountCredentials
import os
import re
import json
os.environ['GOOGLE_APPLICATION_CREDENTIALS'] = 'xxxxxxx.json' #ご自身のjsonファイルを指定

scope = ["https://spreadsheets.google.com/feeds","https://www.googleapis.com/auth/spreadsheets","https://www.googleapis.com/auth/drive"]
creds = ServiceAccountCredentials.from_json_keyfile_name('xxxxxxxxx.json',scope) #ご自身のjsonファイルを指定
client = gspread.authorize(creds)

sh = client.open_by_key('xxxxxxxxxxxxx') #今回はkeyでスプレッドシートを呼び出します。
sheet1 = sh.sheet1 #シート1を指定しています

#_________________________________

def next_available_row(sheet1):
    str_list = list(filter(None, sheet1.col_values(2)))
    return str(len(str_list)+1)

next_row = next_available_row(sheet1)

#_________________________________

def sample_run_report(property_id="xxxxxxxx"):
    client = BetaAnalyticsDataClient()
    #rows = cursor.fetchall()
    request = RunReportRequest(
        property=f"properties/{xxxxxxxx}",
        metrics=[Metric(name="activeUsers"),Metric(name="newUsers"),Metric(name="conversions")],
        date_ranges=[DateRange(start_date="2021-10-01", end_date="2021-10-31")],
    )


    response = client.run_report(request)
    print("Report result:")
    for row in response.rows:
        #print(row.metric_values[0].value, row.metric_values[1].value, row.metric_values[2].value)
        sheet1.update_cell(next_row,2,row.metric_values[0].value) #ユーザー数
        sheet1.update_cell(next_row,3,int(row.metric_values[1].value))#新規ユーザー
        sheet1.update_cell(next_row,4,int(row.metric_values[2].value)) #コンバージョン


if __name__ == '__main__':
    sample_run_report()

実行結果

スプレッドシート 実行結果 next_rowへ入力
解決さん

やった!最終行に入力できた!

まとめ

この記事で学べんだこと

  1. スプレッドシートの最終行を取得する変数の作成
  2. Pythonでスプレッドシートの最終行に入力する方法

実は今回の課題は、僕自身ずっとやりたくてもできなかったことだったんです。

ずっとエクセルを使っていたんですが、

改めてスプレッドシートを使ってみると非常に便利ですよね。

エクセルのopenpyxlのような関数がなくても、

簡単に作成ができるので、ぜひ今後活用してください!

-Python日記