📄

Pythonによるスプレッドシートへの書込みとGASイベントの変更

kshizume

2023/07/04に公開

SREホールディングスでエンジニアをしています鎮目です。

今回は、すでに業務で利用しているスプレットシートに対しCSVを取り込んで書き換えを行うといった作業があったため、初歩的な内容ではありますが、そのなかで行った作業を紹介致します。

前提

スプレットシートがすでに業務で利用されている
スプレットシートにGoogle Apps Script(以下GAS)のonEditイベントが設定されており、書き換えを行った際もそのイベントの実行が必要
作業時間が限られているため、GASのイベント内容をスプレッドシートの書き換えを行っているプログラムに移行ができない
スプレッドシートの書き換えはPythonを利用

ゴール

CSVの内容をスプレッドシートに転記する
スプレッドシート上に記載されているIDとCSVの項目のIDが一致している場合には、上書き、一致しない場合は、新規の行を追加する
スプレットシートにGASのイベントが設定されているのでスプレッドシートに転記した時に動作させる
※GASのイベントでは本来業務的な処理が記載されているが、今回はサンプルのため項目1、項目2の内容と”Gas処理結果”の文字列を結合しただけのもの

変更前

変更後

CSV内容

"ID","項目名1","項目名2"
"A1","D","DD"
"A3","C","CC"

手順

GCP（Google Cloud Platform）、スプレッドシートでの設定
PythonでCSVを読み込みスプレッドシートに書き込みを行う
GASに設定されているonEdit（編集時）イベントを変更時に動作するように変更する

GCP（Google Cloud Platform）、スプレッドシートでの設定

スプレットシートを外部から操作する場合、GCPでいくつかの設定が必要になります。
行う設定は、以下の3点です。
①プロジェクトの作成
②APIの有効化（スプレッドシート操作のために有効化するAPIはGoogle Drive API、Google Sheets API）　
③秘密鍵の作成と秘密鍵jsonファイルの取得
④③で取得した秘密鍵jsonファイルの中に記載されている「client_email」をスプレッドシートの共有に追加

GCPでの設定方法の詳細は、多くの方が丁寧な説明を紹介されているので割愛させていただきます。

PythonでCSVを読み込みスプレッドシートに書き込みを行う

スプレッドシートへCSVの内容を転記するために以下のPythonコードを作成しました。

import gspread
import csv
from google.oauth2.service_account import Credentials

scope = [
    'https://www.googleapis.com/auth/spreadsheets',
    'https://www.googleapis.com/auth/drive'
    ]
#GCPからダウンロードした認証用のjson
credentials_json = 'ダウンロードした認証jsonファイルのパス'
credentials = Credentials.from_service_account_file(credentials_json, scopes=scope)
#認証情報を取得
gc = gspread.authorize(credentials)

wb = gc.open_by_key('対象のスプレッドシートのキー')
ws = wb.worksheet('対象のシート名')
# スプレッドシートのヘッダー行取得
sheet_header = ws.row_values(1)
# スプレッドシートのID列取得
sheet_id_list = ws.col_values(1)
with open("スプレッドシート更新情報が入っているCSVのパス", encoding="utf-8") as f:
    reader = csv.DictReader(f)
    for line in reader:
        if line["ID"] in sheet_id_list:
            #IDがスプレッドシートに存在するため対象行の更新 
            row_no = sheet_id_list.index(line["ID"])+1
        else:
            #IDがスプレッドシートに存在しないため新規行追加 
            row_no = len(sheet_id_list)+1
            sheet_id_list.append(line["ID"])

        # スプレッドシートの更新・追加対象行の取得
        update_cells = ws.range(gspread.utils.rowcol_to_a1(row_no, 1)+ ":" + gspread.utils.rowcol_to_a1(row_no, len(sheet_header)))
        
        for key in line.keys():
            # スプレッドシート上に存在する項目のみ更新対象とする
            if key in sheet_header:
                update_cells[sheet_header.index(key)].value = line[key]

        # スプレッドシート行更新
        ws.update_cells(update_cells)

ws.update_cell(2, 3, ‘new value’) のように１セルずつ書き換えをすることもできますが、項目数が増えた時にGoogleのAPIの使用上限に達してしまいます。
そのため、行単位で書き換えのリクエストを行っています。

gspread.utils.rowcol_to_a1　について

gspreadでrange（範囲）を指定して取得する場合、 range('A1:C20’) のように　
アルファベット＋数値　で指定する必要ありますが、この時　gspread.utils.rowcol_to_a1　を使うと列番号、行番号からアルファベット＋数値に変換してくれます。
これは地味に便利でした。
例

gspread.utils.rowcol_to_a1(1,2) →B1

実行結果

CSVの内容に基づきIDが一致する列は更新、存在しない列は追加をスプレッドシートにすることができました。
しかし、GASのイベント処理が動作しておらず、「Gas処理結果」の列は変化がありません。

原因と対応

onEditイベントは、スプレッドシートを直接入力した時のみ実行されるため、プログラムでの値の書き換えでは実行されません。
そのため、値の変更時のトリガーを設定して実行する必要があります。

GASに設定されているonEdit（編集時）イベントを変更時に動作するように変更する

Pythonで値を変更した時に、スプレッドシートの変更時のイベントが実行されるようにトリガーを設定します。
まずは、スプレッドシートの「拡張機能」から「Apps Script」を選択して画面移動をします。

ここで、現在設定されているonEditの内容も確認することができ、現在設定されているイベントは下の様な内容でした。

function onEdit(e) {
    //変更されたシートの取得
    const sheet = e.range.getSheet();
    //シートの最終行取得
    const lastCol = sheet.getLastColumn();
    //ヘッダー行の最取得
    const header = sheet.getRange(1, 1, 1, lastCol).getValues()[0];
    //イベントに関わるカラムのindexを取得
    const coloum1 = header.indexOf('項目名1');
    const coloum2 = header.indexOf('項目名2');
    const titleCol = header.indexOf('Gas処理結果');

    //編集された最初の行を取得
    const startRow = e.range.getRow();
    //編集された最後の行を取得
    const endRow = e.range.getLastRow();
    //編集された行の一覧を取得
    const rows = sheet.getRange(startRow, 1, endRow - startRow + 1, lastCol - 1).getValues();

  //~~~~~~~~~~~~~~~~~~~
  // 業務的なあれこれ
  //~~~~~~~~~~~~~~~~~~~

    //変更が加えられた行の「Gas処理結果」のセルを変更
    rows.forEach((row, rowIndex) => {
        if ((row[coloum1] !== '') && (row[coloum2] !== '')){
            sheet.getRange(startRow + rowIndex, titleCol + 1).setValue(`Gas処理結果${row[coloum1]}${row[coloum2]}`);
        }
    });
}

シンプルなトリガー

予約済み関数名のいずれかを使用する関数を作成すると、トリガー設定を自身で行わなくても自動的にイベントが実行されます。
onEdit(e)　は予約されている関数名のため、トリガーの設定はされていませんでした。

変更時のトリガーを設定する前にスクリプトに変更を行う必要があります。
理由として編集時と変更時では、取得できるイベントオブジェクトに違いがあるため、このまま単純に変更時のイベントを設定するとエラーが発生し、イベントが失敗してしまうためです。
今回であれば　e.range.getSheet();　の部分で、編集時に取得できるイベントオブジェクトではe.rangeがないため、ここでエラーになります。

そのため、変更時に取得できるイベントオブジェクトでも、エラーが発生しないようにスクリプトを変更しました。

function onChange(e) {
    //変更されたシートの取得
    const sheet = e.source.getActiveSheet();
    //シートの最終行取得
    const lastCol = sheet.getLastColumn();
    //ヘッダー行の最取得
    const header = sheet.getRange(1, 1, 1, lastCol).getValues()[0];
    //イベントに関わるカラムのindexを取得
    const coloum1 = header.indexOf('項目名1');
    const coloum2 = header.indexOf('項目名2');
    const titleCol = header.indexOf('Gas処理結果');
    //選択された範囲を取得
    const activeRange = sheet.getActiveRange();
    //編集された最初の行を取得
    const startRow = activeRange.getRow();
    //編集された最後の行を取得
    const endRow = activeRange.getLastRow();
    //編集された行の一覧を取得
    const rows = sheet.getRange(startRow, 1, endRow - startRow + 1, lastCol - 1).getValues();

  //~~~~~~~~~~~~~~~~~~~
  // 業務的なあれこれ
  //~~~~~~~~~~~~~~~~~~~

    //変更が加えられた行の「Gas処理結果」のセルを変更
    rows.forEach((row, rowIndex) => {
        if ((row[coloum1] !== '') && (row[coloum2] !== '')){
            sheet.getRange(startRow + rowIndex, titleCol + 1).setValue(`Gas処理結果${row[coloum1]}${row[coloum2]}`);
        }
    });
}