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

2021年11月13日2022年12月22日

お悩みさん

Pythonとスプレッドシートの連携ができたけど、最終行に更新できないじゃん！

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

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

エクセルを使うときのopenpyxlでは、max_rowという機能が標準でついていて、とっても便利なのですが、、、。

そんなわけで今回は、Pythonとスプレッドシートを連携した際に、最終行に更新する関数を作成していきます。

この記事で学べること

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

環境

OS	Mac 10.15.4
Python	python 3.9
仮想環境	Poetry
Homebrew	8.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

※この記事のフォーマットを参照しました >>>【データ分析】GA4とエクセルを連携して、PV数とコンバージョン数を自動入力する方法

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

そこにうまく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変数を作成しています。

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

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

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

今回は先のスプレッドシートで見ていただいた通り、１列目にはすでに「10月」の記載があるので、２列目の最終行を取得する必要がありました。

ので、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()