Google Sheets APIを使ったPHPとJavaScriptによるCRUD操作の実装と認証機能の追加

前回の記事で紹介したGoogle Sheets APIを使ったPHPとJavaScriptによるCRUD操作の実装に、認証機能を追加する方法をご紹介します。認証機能を追加することで、ログインしたユーザーのみがデータを操作できるようにします。

必要なもの

Googleアカウント
Composer（PHPの依存管理ツール）
Webサーバ（例：Apache）

ステップ1：Google Sheets APIの設定

まず、Google Cloud ConsoleでGoogle Sheets APIを有効にし、サービスアカウントを作成します。

Google Cloud Consoleにアクセスし、プロジェクトを作成します。
「APIとサービス」 > 「ライブラリ」で「Google Sheets API」を検索し、有効にします。
「APIとサービス」 > 「認証情報」で「認証情報を作成」 > 「サービスアカウント」を選択します。
サービスアカウントを作成し、JSONキーを生成してダウンロードします。このJSONファイルは後で必要になります。

ステップ2：必要なライブラリをインストール

次に、Composerを使って必要なライブラリをインストールします。プロジェクトディレクトリで以下のコマンドを実行します。

composer require google/apiclient:^2.0

ステップ3：PHPスクリプトの作成

まず、Google Sheets APIに接続するための設定ファイルを作成します。

config.php

<?php
// スプレッドシート関連の設定
define('SPREADSHEET_ID', 'YOUR_SPREADSHEET_ID'); // 使用するスプレッドシートのID
define('SERVICE_ACCOUNT_KEY', 'YOUR_SERVICE_ACCOUNT_KEY.json'); // サービスアカウントのJSONキー
define('SHEET_NAME', 'results'); // 使用するシート名
?>

次に、APIエンドポイントを処理するPHPスクリプトを作成します。

api.php

<?php
require 'auth.php'; // 認証ミドルウェアを読み込む
require 'vendor/autoload.php'; // Composerのオートローダーを読み込む
require 'config.php'; // 設定ファイルを読み込む

use Google\Client;
use Google\Service\Sheets;
use Google\Service\Sheets\BatchUpdateSpreadsheetRequest;
use Google\Service\Sheets\Request;

// Google Sheets APIに接続するためのクライアントを取得する関数
function getClient()
{
    $client = new Google_Client();
    // アプリケーション名を設定（任意）
    $client->setApplicationName('My Custom Application Name');
    // 使用するAPIのスコープを設定
    $client->setScopes([Google_Service_Sheets::SPREADSHEETS]);
    // サービスアカウントの認証情報を設定
    $client->setAuthConfig(SERVICE_ACCOUNT_KEY);
    $client->setAccessType('offline');
    return $client;
}

// クライアントとサービスの初期化
$client = getClient();
$service = new Google_Service_Sheets($client);
$spreadsheetId = SPREADSHEET_ID;
$sheetName = SHEET_NAME;

header('Content-Type: application/json'); // レスポンスのContent-TypeをJSONに設定

// HTTPリクエストの種類とアクションに応じて処理を分岐
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_GET['action'])) {
    $action = $_GET['action']; // アクションを取得
    $input = json_decode(file_get_contents('php://input'), true); // JSON形式のリクエストボディをデコード

    // データの追加処理
    if ($action === 'add') {
        $values = $input['data']; // 追加するデータを取得
        $id = $values[0]; // IDを取得

        // IDが整数であることを確認
        if (!is_numeric($id) || intval($id) != $id) {
            echo json_encode(['status' => 'error', 'message' => 'ID must be an integer']);
            exit;
        }

        // IDが既存のデータと重複していないか確認
        $existingData = readData($service, $spreadsheetId, $sheetName . '!A2:A');
        foreach ($existingData as $row) {
            if ($row[0] == $id) {
                echo json_encode(['status' => 'error', 'message' => 'ID already exists']);
                exit;
            }
        }

        // データを追加
        addRow($service, $spreadsheetId, $sheetName . '!A2:E', [$values]);
        echo json_encode(['status' => 'success']);

    // データの更新処理
    } elseif ($action === 'update') {
        $values = $input['data']; // 更新するデータを取得
        $updateRange = $input['range']; // 更新するセル範囲を取得
        $rowIndex = $input['rowIndex']; // 更新する行のインデックスを取得

        // 既存データを取得して'created'フィールドの値を保持
        $existingData = readData($service, $spreadsheetId, $sheetName . '!A' . $rowIndex . ':E' . $rowIndex);
        $created = $existingData[0][3]; // 'created'フィールドの値を取得
        $values[3] = $created; // 'created'フィールドを保持

        // データを更新
        updateCell($service, $spreadsheetId, $updateRange, [$values]);
        echo json_encode(['status' => 'success']);

    // データの削除処理
    } elseif ($action === 'delete') {
        $rowIndex = $input['rowIndex']; // 削除する行のインデックスを取得
        deleteRow($service, $spreadsheetId, $sheetName, $rowIndex); // 行を削除
        echo json_encode(['status' => 'success']);
    }

// データの読み取り処理
} elseif ($_SERVER['REQUEST_METHOD'] === 'GET' && $_GET['action'] === 'read') {
    $data = readData($service, $spreadsheetId, $sheetName . '!A2:E'); // データを読み取る
    echo json_encode($data);
} else {
    echo json_encode(['status' => 'error', 'message' => 'Invalid request']); // 無効なリクエストの場合のエラーメッセージ
}

// データを追加する関数
function addRow($service, $spreadsheetId, $range, $values)
{
    $body = new Google_Service_Sheets_ValueRange(['values' => $values]); // 追加するデータを設定
    $params = ['valueInputOption' => 'RAW']; // データをそのまま挿入するオプションを設定
    $result = $service->spreadsheets_values->append($spreadsheetId, $range, $body, $params); // データを追加
    return $result;
}

// データを読み取る関数
function readData($service, $spreadsheetId, $range)
{
    $response = $service->spreadsheets_values->get($spreadsheetId, $range); // データを取得
    return $response->getValues(); // 取得したデータを返す
}

// セルのデータを更新する関数
function updateCell($service, $spreadsheetId, $range, $values)
{
    $body = new Google_Service_Sheets_ValueRange(['values' => $values]); // 更新するデータを設定
    $params = ['valueInputOption' => 'RAW']; // データをそのまま更新するオプションを設定
    $result = $service->spreadsheets_values->update($spreadsheetId, $range, $body, $params); // データを更新
    return $result;
}

// 行を削除する関数 
function deleteRow($service, $spreadsheetId, $sheetName, $rowIndex)
{
    // 削除する行の範囲を設定
    $requests = [
        new Google_Service_Sheets_Request([
            'delete

Dimension' => [
                'range' => [
                    'sheetId' => getSheetId($service, $spreadsheetId, $sheetName),
                    'dimension' => 'ROWS',
                    'startIndex' => $rowIndex - 1,
                    'endIndex' => $rowIndex
                ]
            ]
        ])
    ];

    // バッチリクエストを作成して実行
    $batchUpdateRequest = new Google_Service_Sheets_BatchUpdateSpreadsheetRequest([
        'requests' => $requests
    ]);

    $service->spreadsheets->batchUpdate($spreadsheetId, $batchUpdateRequest);
}

// シートIDを取得する関数
function getSheetId($service, $spreadsheetId, $sheetName)
{
    // スプレッドシートを取得
    $spreadsheet = $service->spreadsheets->get($spreadsheetId);
    // 指定されたシート名のシートIDを返す
    foreach ($spreadsheet->getSheets() as $sheet) {
        if ($sheet->getProperties()->getTitle() === $sheetName) {
            return $sheet->getProperties()->getSheetId();
        }
    }
    // シートが見つからない場合は例外を投げる
    throw new Exception('Sheet not found: ' . $sheetName);
}
?>

ステップ4：ユーザーデータベースの設定

ユーザーデータベースを設定します。users.jsonファイルをウェブサーバーの公開ディレクトリ外に配置します。

users.json

[
    {
        "username": "user1",
        "password": "password1"
    },
    {
        "username": "user2",
        "password": "password2"
    }
]

ディレクトリ構造

/my_project/
    /public_html/
        login.html
        authenticate.php
        ...
    users.json

ステップ5：ログインページの作成

ユーザーがログインするためのページを作成します。

login.html

<!DOCTYPE html>
<html>
<head>
    <title>Login</title>
</head>
<body>
    <h1>Login</h1>
    <form action="authenticate.php" method="post">
        <label for="username">Username:</label>
        <input type="text" id="username" name="username">
        <br>
        <label for="password">Password:</label>
        <input type="password" id="password" name="password">
        <br>
        <button type="submit">Login</button>
    </form>
</body>
</html>

ステップ6：認証処理の作成

ユーザーの認証を処理するPHPスクリプトを作成します。

authenticate.php

<?php
session_start();

// 安全な場所に配置したユーザーデータベースを読み込む
$users = json_decode(file_get_contents(__DIR__ . '/../users.json'), true);

$username = $_POST['username'];
$password = $_POST['password'];

// ユーザー認証
foreach ($users as $user) {
    if ($user['username'] === $username && $user['password'] === $password) {
        $_SESSION['username'] = $username;
        header('Location: index.html');
        exit;
    }
}

// 認証失敗時の処理
header('Location: login.html?error=1');
?>

ステップ7：認証ミドルウェアの追加

認証が必要なページにアクセスする際に、ユーザーが認証済みかどうかをチェックするミドルウェアを追加します。

auth.php

<?php
session_start();

// ユーザーがログインしていない場合、ログインページにリダイレクト
if (!isset($_SESSION['username'])) {
    header('Location: login.html');
    exit;
}
?>

ステップ8：APIエンドポイントに認証ミドルウェアを追加

APIエンドポイントに認証ミドルウェアを追加して、ユーザーが認証されているかどうかをチェックします。

api.php

<?php
require 'auth.php'; // 認証ミドルウェアを読み込む
require 'vendor/autoload.php'; // Composerのオートローダーを読み込む
require 'config.php'; // 設定ファイルを読み込む

use Google\Client;
use Google\Service\Sheets;
use Google\Service\Sheets\BatchUpdateSpreadsheetRequest;
use Google\Service\Sheets\Request;

// Google Sheets APIに接続するためのクライアントを取得する関数
function getClient()
{
    $client = new Google_Client();
    $client->setApplicationName('My Custom Application Name'); // 任意のアプリケーション名に変更
    $client->setScopes([Google_Service_Sheets::SPREADSHEETS]);
    $client->setAuthConfig(SERVICE_ACCOUNT_KEY);
    $client->setAccessType('offline');
    return $client;
}

// クライアントとサービスの初期化
$client = getClient();
$service = new Google_Service_Sheets($client);
$spreadsheetId = SPREADSHEET_ID;
$sheetName = SHEET_NAME;

header('Content-Type: application/json');

// HTTPリクエストの種類に応じた処理
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_GET['action'])) {
    $action = $_GET['action'];
    $input = json_decode(file_get_contents('php://input'), true);

    // データの追加処理
    if ($action === 'add') {
        $values = $input['data'];
        $id = $values[0];

        // IDが整数であることを確認
        if (!is_numeric($id) || intval($id) != $id) {
            echo json_encode(['status' => 'error', 'message' => 'ID must be an integer']);
            exit;
        }

        // IDが既存のデータと重複していないか確認
        $existingData = readData($service, $spreadsheetId, $sheetName . '!A2:A');
        foreach ($existingData as $row) {
            if ($row[0] == $id) {
                echo json_encode(['status' => 'error', 'message' => 'ID already exists']);
                exit;
            }
        }

        // データを追加
        addRow($service, $spreadsheetId, $sheetName . '!A2:E', [$values]);
        echo json_encode(['status' => 'success']);

    // データの更新処理
    } elseif ($action === 'update') {
        $values = $input['data'];
        $updateRange = $input['range'];
        $rowIndex = $input['rowIndex'];

        // 既存データを取得して'created'フィールドの値を保持
        $existingData = readData($service, $spreadsheetId, $sheetName . '!A' . $rowIndex . ':E' . $rowIndex);
        $created = $existingData[0][3];
        $values[3] = $created; // 'created'フィールドを保持

        // データを更新
        updateCell($service, $spreadsheetId, $updateRange, [$values]);
        echo json_encode(['status' => 'success']);

    // データの削除処理
    } elseif ($action === 'delete') {
        $rowIndex = $input['rowIndex'];
        deleteRow($service, $spreadsheetId, $sheetName, $rowIndex);
        echo json_encode(['status' => 'success']);
    }

// データの読み取り処理
} elseif ($_SERVER['REQUEST_METHOD'] === 'GET' && $_GET['action'] === 'read') {
    $data = readData($service, $spreadsheetId, $sheetName . '!A2:E');
    echo json_encode($data);
} else {
    echo json_encode(['status' => 'error', 'message' => 'Invalid request']);
}

// データを追加する関数
function addRow($service, $spreadsheetId, $range, $values)
{
    $body = new Google_Service_Sheets_ValueRange(['values' => $values]);
    $params = ['valueInputOption' => 'RAW'];
    $result = $service->spreadsheets_values->append($spreadsheetId, $range, $body, $params);
    return $result;
}

// データを読み取る関数
function readData($service, $spreadsheetId, $range)
{
    $response = $service->spreadsheets_values->get($spreadsheetId, $range);
    return $response->getValues();
}

// セルのデータを更新する関数
function updateCell($service, $spreadsheetId, $range, $values)
{
    $body = new Google_Service_Sheets_ValueRange(['values' => $values]);
    $params = ['valueInputOption' => 'RAW'];
    $result = $service->spreadsheets_values->update($spreadsheetId, $range, $body, $params);
    return $result;
}

// 行を削除する関 function deleteRow($service, $spreadsheetId, $sheetName, $rowIndex)
{
    $requests = [
        new Google_Service_Sheets

_Request([
            'deleteDimension' => [
                'range' => [
                    'sheetId' => getSheetId($service, $spreadsheetId, $sheetName),
                    'dimension' => 'ROWS',
                    'startIndex' => $rowIndex - 1,
                    'endIndex' => $rowIndex
                ]
            ]
        ])
    ];

    $batchUpdateRequest = new Google_Service_Sheets_BatchUpdateSpreadsheetRequest(['requests' => $requests]);
    $service->spreadsheets->batchUpdate($spreadsheetId, $batchUpdateRequest);
}

// シートIDを取得する関数
function getSheetId($service, $spreadsheetId, $sheetName)
{
    $spreadsheet = $service->spreadsheets->get($spreadsheetId);
    foreach ($spreadsheet->getSheets() as $sheet) {
        if ($sheet->getProperties()->getTitle() === $sheetName) {
            return $sheet->getProperties()->getSheetId();
        }
    }
    throw new Exception('Sheet not found: ' . $sheetName);
}
?>

ステップ9：ログアウト機能の追加

ユーザーがログアウトできるようにするためのスクリプトを追加します。

logout.php

<?php
session_start();
session_destroy();
header('Location: login.html');
?>

ステップ10：フロントエンドにログインチェックを追加

フロントエンドの各ページにログインチェックを追加します。

index.html

<!DOCTYPE html>
<html>
<head>
    <title>Google Sheets CRUD</title>
    <style>
        /* テーブルのスタイル設定 */
        table {
            width: 100%;
            border-collapse: collapse;
        }
        th, td {
            border: 1px solid black;
            padding: 8px;
            text-align: left;
        }
        th {
            background-color: #f2f2f2;
        }
        button {
            margin: 5px;
        }
        #add-section, #logout-link {
            display: none; /* 初期状態で非表示 */
        }
        #login-link {
            display: none; /* 初期状態で非表示 */
        }
    </style>
    <script>
        // 認証状態をチェックする関数
        async function checkAuth() {
            const response = await fetch('check_auth.php');
            const result = await response.json();
            if (result.authenticated) {
                document.getElementById('add-section').style.display = 'block';
                document.getElementById('logout-link').style.display = 'block';
            } else {
                document.getElementById('login-link').style.display = 'block';
            }
        }

        // 日付を特定のフォーマットでフォーマットする関数
        function formatDate(date) {
            const d = new Date(date);
            const year = d.getFullYear();
            const month = String(d.getMonth() + 1).padStart(2, '0');
            const day = String(d.getDate()).padStart(2, '0');
            const hours = String(d.getHours()).padStart(2, '0');
            const minutes = String(d.getMinutes()).padStart(2, '0');
            const seconds = String(d.getSeconds()).padStart(2, '0');
            return `${year}-${month}-${day} ${hours}:${minutes}:${seconds}`;
        }

        // データを取得する関数
        async function fetchData() {
            const response = await fetch('api.php?action=read');
            const data = await response.json();
            return data;
        }

        // データを追加する関数
        async function addData() {
            const id = document.getElementById('id').value;
            const data1 = document.getElementById('data1').value;
            const data2 = document.getElementById('data2').value;
            const created = formatDate(new Date());
            const updated = created;
            const response = await fetch('api.php?action=add', {
                method: 'POST',
                headers: { 'Content-Type': 'application/json' },
                body: JSON.stringify({ data: [id, data1, data2, created, updated] })
            });
            const result = await response.json();
            if (result.status === 'error') {
                alert(result.message);
            } else {
                console.log(result);
                loadTable();
            }
        }

        // データを更新する関数
        async function updateData(row, id) {
            const data1 = document.getElementById(`data1_${row}`).value;
            const data2 = document.getElementById(`data2_${row}`).value;
            const updated = formatDate(new Date());
            const range = `results!A${row + 2}:E${row + 2}`;
            const response = await fetch('api.php?action=update', {
                method: 'POST',
                headers: { 'Content-Type': 'application/json' },
                body: JSON.stringify({ range: range, data: [id, data1, data2, '', updated], rowIndex: row + 2 })
            });
            const result = await response.json();
            console.log(result);
            loadTable();
        }

        // データを削除する関数
        async function deleteData(row) {
            const response = await fetch('api.php?action=delete', {
                method: 'POST',
                headers: { 'Content-Type': 'application/json' },
                body: JSON.stringify({ rowIndex: row + 2 })
            });
            const result = await response.json();
            console.log(result);
            document.getElementById(`row_${row}`).remove();
        }

        // テーブルをロードする関数
        async function loadTable() {
            const data = await fetchData();
            const table = document.getElementById('data-table');
            table.innerHTML = '<tr><th>ID</th><th>Data1</th><th>Data2</th><th>Created</th><th>Updated</th><th>Actions</th></tr>';

            data.forEach((row, index) => {
                const rowElement = document.createElement('tr');
                rowElement.id = `row_${index}`;
                rowElement.innerHTML = `
                    <td>${row[0]}</td>
                    <td><input type="text" id="data1_${index}" value="${row[1]}"></td>
                    <td><input type="text" id="data2_${index}" value="${row[2]}"></td>
                    <td>${row[3]}</td>
                    <td>${row[4]}</td>
                    <td>
                        <button onclick="updateData(${index}, '${row[0]}')">Update</button>
                        <button onclick="deleteData(${index})">Delete</button>
                    </td>
                `;
                table.appendChild(rowElement);
            });
        }

        // ページ読み込み時にテーブルをロードし、認証状態をチェック
        window.onload = () => {
            loadTable();
            checkAuth(); // 認証状態をチェック
        };
    </script>
</head>
<body>
    <h1>Google Sheets CRUD</h1>
    <table id="data-table"></table>
    <div id="add-section">
        <h2>Add New Data</h2>
        <label for="id">ID:</label>
        <input type="text" id="id">
        <label for="data1">Data1:</label>
        <input type="text" id="data1">
        <label for="data2">Data2:</label>
        <input type="text" id="data2">
        <button onclick="addData()">Add Data</button>
    </div>
    <br><br>
    <a href="logout.php" id="logout-link">Logout</a>
    <a href="login.html" id="login-link">Login</a>
</body>
</html>

check_auth.php

<?php
session_start();
header('Content-Type: application/json');

// ユーザーが認証されているかどうかを確認し、結果をJSONで返す
echo json_encode(['authenticated' => isset($_SESSION['username'])]);
?>

画面イメージ

フロントメイン

スプレッドシート

まとめ

この記事では、Google Sheets APIを使ってPHPとJavaScriptでCRUD操作を実装する方法に加えて、ユーザー認証機能を追加する方法を紹介しました。これにより、認証されたユーザーのみがGoogle Sheetsのデータを操作できるようになります。

必要なもの

ステップ1：Google Sheets APIの設定

ステップ2：必要なライブラリをインストール

ステップ3：PHPスクリプトの作成

config.php

api.php

ステップ4：ユーザーデータベースの設定

users.json

ディレクトリ構造

ステップ5：ログインページの作成

login.html

ステップ6：認証処理の作成

authenticate.php

ステップ7：認証ミドルウェアの追加

auth.php

ステップ8：APIエンドポイントに認証ミドルウェアを追加

api.php

ステップ9：ログアウト機能の追加

logout.php

ステップ10：フロントエンドにログインチェックを追加

index.html

check_auth.php

画面イメージ

ログイン

フロント メイン

スプレッドシート

まとめ

コメント

フロントメイン