スプレッドシートに外部サイトから書き込む方法

webサイトからスプレッドシートに書き込む方法を考えます。今回はC#とは関係がありません。Google Sheets APIとPHPを使います。

まずはGoogle Cloud Platform で Projectを作成しましょう。そしてGoogle Sheet APIの有効化したらサービスアカウントを作成します。作成すると認証情報を含んだJSONファイルがダウンロードされるので保存します。

データを書き込みたいスプレッドシートを作成してアカウントユーザーと共有します。共有すべきアカウントユーザーはJSONファイルのなかに書かれています。”client_email”:と書かれている部分です。

次にgoogleapis/google-api-php-clientを使うために必要なPHPを取得するのですが、ここではcomposerを使います。composerをインストールします。そしてコマンドプロンプトで適当なフォルダに移動して

composer require google/apiclient:"^2.0"

1	composer require google/apiclient:"^2.0"

と入力します。するとgoogleapis/google-api-php-clientを使うために必要なPHPファイルがダウンロードされます。フォルダのなかをみてみるとけっこうな数になっています。

このフォルダのなかにvendorというフォルダが生成されていますが、このフォルダがあるフォルダのなかにindex.phpというファイルをつくります。それからこのフォルダのなかにサービスアカウントを作成したときにダウンロードした認証情報を含んだJSONファイルもコピーします。

index.phpに以下を書きます。これはスプレッドシートに書き込むためのコードです。作成したスプレッドシートのIDはスプレッドシートのurlのXXXXXXXの部分です。

https://docs.google.com/spreadsheets/d/XXXXXXX/edit#gid=0

1	https://docs.google.com/spreadsheets/d/XXXXXXX/edit#gid=0

index.php

<?php
    $SheetID = "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX";
    $SheetName = "シート1";

    require __DIR__. '/vendor/autoload.php';

    $keyFile = __DIR__. "/file.json"; // ダウンロードしたファイルを指定する

    // アカウント認証情報インスタンスを作成
    $client = new Google_Client();
    $client->setAuthConfig($keyFile);

    //任意名
    $client->setApplicationName("Sheet API TEST");

    //サービスの権限スコープ
    $scopes = [Google_Service_Sheets::SPREADSHEETS];
    $client->setScopes($scopes);

    //シート情報を操作するインスタンスを生成
    $sheet = new Google_Service_Sheets($client);

    //保存するデータ
    $values = [
        ["書き込みテスト", "書き込みました"]
    ];

    //データ操作領域を設定
    $body = new Google_Service_Sheets_ValueRange([
        'values' => $values,
    ]);

    //追記する
    $response = $sheet->spreadsheets_values->append(
        $SheetID, // 作成したスプレッドシートのIDを入力
        $SheetName, //シート名を指定する
        $body,
        ["valueInputOption" => 'USER_ENTERED']
    );

<?php

$SheetID = "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX";

$SheetName = "シート1";

require __DIR__. '/vendor/autoload.php';

$keyFile = __DIR__. "/file.json"; // ダウンロードしたファイルを指定する

// アカウント認証情報インスタンスを作成

$client = new Google_Client();

$client->setAuthConfig($keyFile);

//任意名

$client->setApplicationName("Sheet API TEST");

//サービスの権限スコープ

$scopes = [Google_Service_Sheets::SPREADSHEETS];

$client->setScopes($scopes);

//シート情報を操作するインスタンスを生成

$sheet = new Google_Service_Sheets($client);

//保存するデータ

$values = [

["書き込みテスト", "書き込みました"]

];

//データ操作領域を設定

$body = new Google_Service_Sheets_ValueRange([

'values' => $values,

]);

//追記する

$response = $sheet->spreadsheets_values->append(

$SheetID, // 作成したスプレッドシートのIDを入力

$SheetName, //シート名を指定する

$body,

["valueInputOption" => 'USER_ENTERED']

);

あとはこれをサーバーにアップロードします。ただ実際にアップロードするまえにローカル環境でテストをしておきましょう。

簡易クリック解析？

つぎにリンクをクリックするとリダイレクトされ、それが時刻とリダイレクトの結果がスプレッドシートに記録されるというアプリをつくってみます。簡単なクリック解析ができます。

https://lets-csharp.com/sample/redirect/?to=yahoo
https://lets-csharp.com/sample/redirect/?to=youtube

これをクリックするとヤフー・ジャパンやYou Tubeのトップページに飛びます。

そしてここに記録されます。リンクをクリックして確認してみてください。

<?php
    // ここにリダイレクト先を登録する
    $redirects = array(
        'yahoo'=>'https://www.yahoo.co.jp/',
        'youtube'=>'https://www.youtube.com/',
    );

    $SheetID = "1Foj7L5n1wiMBrZZfK14dqXdpkjSdf2LbWXG04oz5e3I";
    $SheetName = "シート1";

    $to = "";
    $result = "";
    $reason = "";

    if( isset($_GET['to']) ){
        $to = $_GET['to'];
        if ( isset($redirects[$to]) )
        {
            header("Location:$redirects[$to]");
            $result = "成功";
            $reason = $redirects[$to];
        }
        else{
            echo "リダイレクトできません。不正な引数が渡されました。";
            $result = "失敗";
            $reason = "不正な引数：".$to;
        }
    }
    else{
        $to = "none";
        $result = "失敗";
        $reason = "引数が存在しない";
        echo "リダイレクトできません。必要な引数が存在しません。";
    }
    saveResult($SheetID, $SheetName, $to, $result, $reason);

    function saveResult($sheetID, $sheetName, $to, $result, $reason)
    {
        require __DIR__. '/vendor/autoload.php';

        $keyFile = __DIR__. "/file.json"; // ダウンロードしたファイルを指定する

        $client = new Google_Client();
        $client->setAuthConfig($keyFile);
        $client->setApplicationName("Sheet API TEST");

        $scopes = [Google_Service_Sheets::SPREADSHEETS];
        $client->setScopes($scopes);

        $sheet = new Google_Service_Sheets($client);

        date_default_timezone_set('Asia/Tokyo');
        $date = date("Y/m/d");
        $time = date("H:i:s");

        $values = [
            [$date, $time, $to, $result, $reason]
        ];

        $body = new Google_Service_Sheets_ValueRange([ 'values' => $values, ]);

        $response = $sheet->spreadsheets_values->append( $sheetID, $sheetName, $body, ["valueInputOption" => 'USER_ENTERED']);
    }

<?php

// ここにリダイレクト先を登録する

$redirects = array(

'yahoo'=>'https://www.yahoo.co.jp/',

'youtube'=>'https://www.youtube.com/',

);

$SheetID = "1Foj7L5n1wiMBrZZfK14dqXdpkjSdf2LbWXG04oz5e3I";

$SheetName = "シート1";

$to = "";

$result = "";

$reason = "";

if( isset($_GET['to']) ){

$to = $_GET['to'];

if ( isset($redirects[$to]) )

{

header("Location:$redirects[$to]");

$result = "成功";

$reason = $redirects[$to];

}

else{

echo "リダイレクトできません。不正な引数が渡されました。";

$result = "失敗";

$reason = "不正な引数：".$to;

}

else{

$to = "none";

$result = "失敗";

$reason = "引数が存在しない";

echo "リダイレクトできません。必要な引数が存在しません。";

}

saveResult($SheetID, $SheetName, $to, $result, $reason);

function saveResult($sheetID, $sheetName, $to, $result, $reason)

{

require __DIR__. '/vendor/autoload.php';

$keyFile = __DIR__. "/file.json"; // ダウンロードしたファイルを指定する

$client = new Google_Client();

$client->setAuthConfig($keyFile);

$client->setApplicationName("Sheet API TEST");

$scopes = [Google_Service_Sheets::SPREADSHEETS];

$client->setScopes($scopes);

$sheet = new Google_Service_Sheets($client);

date_default_timezone_set('Asia/Tokyo');

$date = date("Y/m/d");

$time = date("H:i:s");

$values = [

[$date, $time, $to, $result, $reason]

];

$body = new Google_Service_Sheets_ValueRange([ 'values' => $values, ]);

$response = $sheet->spreadsheets_values->append( $sheetID, $sheetName, $body, ["valueInputOption" => 'USER_ENTERED']);

}

https://lets-csharp.com/sample/redirect/?to=yahoo
https://lets-csharp.com/sample/redirect/?to=youtube

上のように引数が正しい場合はリダイレクトされます。

https://lets-csharp.com/sample/redirect/?to=abc（abcは登録されていない）やhttps://lets-csharp.com/sample/redirect/?abc=abc（必要な引数「to」が存在しない）にアクセスするとリダイレクトできないと表示され、スプレッドシートには失敗した理由が記録されます。

スプレッドシートに外部サイトから書き込む方法

簡易クリック解析？

鳩でも分かるC#管理人からのお願い

コメントについて

管理人のモチベーションアップのために

コメントをどうぞ