2023年02月16日 に更新

【Laravel】Google sheet と DB を同期する（Apps Script）

こんにちは❗フリーランス・コンサルタント＆エンジニアの 九保すこひ です。

さてさて、私も長らく開発のお仕事をさせていただいているのですが、最近

「あ❗これは気をつけないと」

と感じることがありました。

詳細は話せませんが、あるとき「直接 DB データを変更したい」というご希望があったのでPhpMyAdminをご提案したのですが、返答としては、

「Google Sheet が使い慣れてるのでそっちでいけないでしょうか…😅」

というものでした。

そして、この件で感じたのが「開発者にとって馴染みのある PhpMyAdmin は使いやすいけど、一般人からするとなかなか大変だよな…」というものでした。

そこで❗

今回はGoogle sheetで変更されたらリアルタイムでデータを更新できるウェブフックをLaravelで実装してみることにしました。

ぜひ何かの参考になりましたら嬉しいです。😄✨

「今回から Laravel 10.x
を使って記事を書いてます」

開発環境： Laravel 10.x ✨

Google Sheet 側の作業

まずはGoogle Sheetに「変更や追加があったらウェブフックを実行する」機能を作っていきます。

Google App Script を書く

では、わかりやすいようにGoogle Driveから移動する方法をご紹介します。

画面左上にある「新規」というボタンをクリックします。

メニューが表示されるので、「Google スプレッドシート」をクリック。

すると、新しいシートが表示されるので適当にタイトルを変更してください。（Google Sheetから直接このページへ来ても問題ありません）

次に、シートが変更されたとき実行される「Apps Script」を書きます。

「拡張機能 ＞ Apps Script」をクリックしてください。

すると、Apps Scriptのコードエディタが表示されるので、中身を以下のコードに入れ替えてください。

コード.gs

function onSheetUpdated() {

    const spreadSheetId = '（あなたのスプレッドシートID）'; // DB と同期したいスプレッドシートのID
    const activeSpreadSheet = SpreadsheetApp.getActiveSpreadsheet();
    const activeSpreadSheetId = activeSpreadSheet.getId();

    if(spreadSheetId === activeSpreadSheetId) {
        
        const url = 'https://（あなたのドメイン）/google_sheet/webhook';
        const data = { spreadsheet_id: spreadSheetId };
        const options = {
            method: 'post',
            contentType: 'application/json',
            payload: JSON.stringify(data)
        };

        UrlFetchApp.fetch(url, options);

  }

}

この中でやっているのは、以下のとおりです。

  1. 現在作業しているスプレッドシートを取得
  2. そのスプレッドシートの ID を取得
  3. 同期したいスプレッドシートの ID と一致すればウェブフック（HTTPリクエスト）を送信

※ なお、このコード内にある「（あなたのスプレッドシートID）」「（あなたのドメイン）」はそれぞれあなたの環境に合わせてください。

ちなみに、スプレッドシートIDの取得方法は以下の記事を参考にしてください。

📝 参考記事： 作成したスプレッドシートのIDを取得する

これでApps Scriptの作業は完了です❗

トリガーを追加する

次に、先ほど保存したApps Scriptに「トリガー」と呼ばれる連動機能をつくります。

そして、実際に連動する条件は「シートが変更されたとき」です。

では、メニューから「トリガー」をクリックしてください。

ページ移動した先のページ右下にある「トリガーを追加」ボタンをクリック。

するとトリガーを設定するポップアップが表示されるのでその中から以下２つを変更してください。

  • 実行する関数を選択： onSheetUpdated（👈 Apps Script で設定した関数です）
  • イベントの種類を選択： 変更時

そして、保存ボタンをクリック。

すると、以下のように実行権限を与えるユーザーが表示されるのでログインしているものを選択。

最終確認が表示されるので「Advanced」を開いて「（unsafe）」と書かれたリンクをクリックします。

※ 内容としては「リスクあるけどホントに OK ね❓」というものです。（もちろん自己責任ですが、ウェブフック自体はそれほど危険なものではないのでご自身で判断してください）

すると、トリガーが保存され以下のように一覧に表示されることになります。

Laravel 側の作業

では、ここからがLaravel（ウェブフックを受ける側）の作業になります。

Google Spreadsheet API が使えるようにする

まずは、LaravelからGoogle Sheetへアクセスできるようにする必要があります。

以下の記事をご覧いただいて先に準備しておいてください。

📝 参考記事： Google APIでスプレッドシートを有効にする

※ 認証用JSONのファイル名もこのページのものと一致させています。（credentials.json）

パッケージをインストールする

Googleが提供してくれているPHPパッケージをインストールします。
以下のコマンドを実行してください。

composer require google/apiclient:^2.12.1

モデル＆マイグレーションをつくる

続いて、DBまわりの作業です。
以下のコマンドを実行してください。

php artisan make:model Food -m

すると、モデルとマイグレーションの２ファイルが一気に作成されるので、以下のように変更します。

app/Models/Food.php

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

class Food extends Model
{
    use HasFactory;

    protected $guarded = ['id']; // 👈 Mass assignment エラーにならないようにしています
}

そして、マイグレーションです。

database/migrations/****_**_**_******_create_food_table.php

// 省略

public function up(): void
{
    Schema::create('food', function (Blueprint $table) {
        $table->id();
        $table->string('name')->comment('食べ物の名前');
        $table->unsignedInteger('quantity')->comment('数量');
        $table->timestamps();
    });
}

// 省略

では、この状態でマイグレーションを実行しておいてください。

php artisan migrate

実際のテーブルはこうなりました。

コントローラーをつくる

次に、コントローラーです。
以下のコマンドを実行してください。

php artisan make:controller GoogleSheetController

すると、ファイルが作成されるのでこの中身を以下のように変更します。

app/Http/Controllers/GoogleSheetController.php

<?php

namespace App\Http\Controllers;

use App\Models\Food;
use Illuminate\Http\Request;
use Google_Client;
use Google_Service_Sheets;
use Illuminate\Support\Str;

class GoogleSheetController extends Controller
{
    const SPREADSHEET_ID = '（あなたのスプレッドシートID）';
    const MAX_ROW = 100000; // 最大 100000 行を読み取る

    public function webhook(Request $request): void
    {
        $spreadsheet_id = $request->spreadsheet_id;

        if($spreadsheet_id !== self::SPREADSHEET_ID) {

            abort(400, '不正なアクセスです');

        }

        $sheet = $this->getSheetClient();

        $range = 'A1:B'. self::MAX_ROW;
        $response = $sheet->spreadsheets_values->get(self::SPREADSHEET_ID, $range);
        $sheet_data = $response->getValues();
        $existing_food_ids = [];

        foreach ($sheet_data as $index => $row) {

            if($index === 0) continue; // 1行目はヘッダーなのでスキップ

            $name = data_get($row, 0, '');
            $quantity = data_get($row, 1, '');

            if(Str::length($name) > 0 && Str::length($quantity) > 0) {

                $food = Food::firstOrNew(['name' => $name]);
                $food->quantity = $quantity;
                $food->save();

                $existing_food_ids[] = $food->id;

            }

        }

        if(count($existing_food_ids) > 0) {

            Food::whereNotIn('id', $existing_food_ids)->delete();

        }
    }

    private function getSheetClient(): Google_Service_Sheets
    {
        $credentials_path = storage_path('app/json/credentials.json');

        $client = new Google_Client();
        $client->setAuthConfig($credentials_path);
        $client->setScopes([
            Google_Service_Sheets::SPREADSHEETS,
        ]);

        return new Google_Service_Sheets($client);
    }
}

この中では、まず「シートからデータを取ってきて、DBのデータを更新（or 新規作成）」しています。

※ なお、今回はバリデーションはすごくユルくなっていますが、本番環境の場合は型などもチェックすることをおすすめします。

なお、１点だけ少しトリッキーなのが、$existing_food_idsの部分です。

これは「存在しているデータ全てのID」なのですが、目的としては「それ以外のデータを削除するため」です。

というのも、以下のようなスプレッドシートにあるとします。

  • ラーメン：１
  • カレー：２
  • お寿司：３

そして、この状態でカレーの行を消してしまった場合どうなるでしょうか。

  • ラーメン：１
  • お寿司：３

ウェブフック側とすると、「削除された食べものが何なのか」を知ることができません。

そのため、「存在しているものの逆（＝存在していないもの）を削除する」というアプローチにしているのです。

また、（あなたのスプレッドシートID）の部分は先ほどのApps Script同様ご自身のものへ変更しておいてください。

ルートをつくる

そして、ルートです。

routes/web.php

// 省略

use App\Http\Controllers\GoogleSheetController;

// 省略

Route::post('google_sheet/webhook', [GoogleSheetController::class, 'webhook'])->name('google_sheet.webhook');

CSRF 制限を解除する

Laravelは、初期状態でCSRF攻撃（クロスサイト・リクエスト・フォージェリ）をブロックするようになっているのですが、これがあるとウェブフックまでブロックされてしまいます。

そのため、今回ウェブフックとして利用する「http://****/google_sheet/webhook」はその制限を解除しておきましょう。

app/Http/Middleware/VerifyCsrfToken.php

<?php

namespace App\Http\Middleware;

use Illuminate\Foundation\Http\Middleware\VerifyCsrfToken as Middleware;

class VerifyCsrfToken extends Middleware
{
    /**
     * The URIs that should be excluded from CSRF verification.
     *
     * @var array<int, string>
     */
    protected $except = [
        'google_sheet/webhook', // 👈 ここを追加しました
    ];
}

※ ちなみに、CSRF攻撃の詳細は以下の記事をご覧ください。

📝 参考記事： CSRF（クロスサイト・リクエスト・フォージェリ）攻撃をできるだけ分かりやすく解説

これで作業はすべて完了です❗
お疲れ様でした。😄✨

テストしてみる

では、実際にLaravelをサーバーへ設置してテストしてみましょう❗

まずは、何もしていない状態のデータベースはこうなっています。

では、この状態でスプレッドシートを以下のように変更してみましょう。

すると、DBは・・・・・・

はい❗
DBは何も触っていませんが、自動で同期されました。

まずは成功です😄

では、次に「カレーうどん」を「99個」で追加してみましょう。

DBはどうなったでしょうか・・・・・・

はい❗
カレーうどんもDBに追加されました👍

では、最後にスプレッドシートから「ハンバーグ」を削除してみましょう。

うまく削除されるでしょうか・・・・・・

はい❗
４番のハンバーグが自動で削除されました。

すべて成功です✨😄👍

企業様へのご提案

Google Sheetは今やMicrosoft Excelと並んで多くの人に利用されている表計算システムです。

そして、ウェブシステムとGoogle Sheetを統合させることで以下のようなメリットが考えられます。

  • データベースを触るのは怖い（知識がない）スタッフさんでも Google Sheet を操作するだけでデータの変更ができるようになる
  • Google Sheet は、データのコピーなどが容易なため、生産性を向上させることができる
  • Google Sheet は、モバイルアプリなども提供されているため、いろんな環境からデータ変更がしやすいです。そして、そのデータ変更時に今回のようなトリガー＆ウェブフックを仕込むことでより「いつでもどこでも」ウェブシステムを操作することが可能になります。

もしこういった機能をご希望でしたらお気軽にお問い合わせからご相談ください。

お待ちしております。😄✨

おわりに

とうことで、今回はLaravelでGoogle Sheetのウェブフックをつくってみました。

実は最初、Apps Scriptが標準で用意してくれているonSelectionChangeというイベントを使おうとしていたのですが、どうやらこのイベントは権限を付与することができないため、独自の名前に変更し、独自のトリガーをセットする必要があるとのことでちょっとあせりました。（その時点でこの記事は80%ぐらいできてたんですよね…💦）

正直過去にも「ほとんどできてたけど致命的な穴があってお蔵入りにした」記事がいくつかあって、そんなこともあってかブログ記事を続けるのは難しいですね。（やはり「好きだからやってる」という心構えが一番いいような気がします）

ぜひ皆さんもチャレンジしてみてくださいね。

ではでは〜❗

「お仕事で Livewire 使わせて
もらってます😄👍」

開発のご依頼お待ちしております 😊✨
開発のご依頼はこちらから: お問い合わせ
どうぞよろしくお願いいたします！
このエントリーをはてなブックマークに追加       follow us in feedly  

開発効率を上げるための機材・まとめ

合わせて読みたい