Google Sheets APIを使ったPHPとJavaScriptによるCRUD操作の実装

Google Sheets APIを使って、PHPとJavaScriptでCRUD（Create, Read, Update, Delete）操作を実装する方法を紹介します。

必要なもの

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

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

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

  1. Google Cloud Consoleにアクセスし、プロジェクトを作成します。
  2. 「APIとサービス」 > 「ライブラリ」で「Google Sheets API」を検索し、有効にします。
  3. 「APIとサービス」 > 「認証情報」で「認証情報を作成」 > 「サービスアカウント」を選択します。
  4. サービスアカウントを作成し、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 '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);
}
?>

ステップ4：フロントエンドの作成

次に、HTMLとJavaScriptを使用してフロントエンドを作成します。このフロントエンドからAPIエンドポイントを呼び出し、Google Sheetsのデータを操作します。

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;
        }
    </style>
    <script>
        // 日付を特定のフォーマットでフォーマットする関数
        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();
        };
    </script>
</head>
<body>
    <h1>Google Sheets CRUD</h1>
    <table id="data-table"></table>
    <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>
</body>
</html>

イメージ

フロント

スプレッドシート

まとめ

この記事では、Google Sheets APIを使ってPHPとJavaScriptでCRUD操作を実装する方法を紹介しました。これにより、Google Sheetsをデータベースとして利用し、ウェブアプリケーションからデータを操作できるようになります。

スポンサーリンク

コメント

タイトルとURLをコピーしました