スプレッドシートに書かれているurlからサイト情報を抜き出してシートに書き込む方法

スプレッドシートには複数のurlが書かれています。urlを取得してそのサイトにアクセスしてサイト情報を抜き出してシートに書き込んでみることにします。これまでと同様にPHPとgoogleapis/google-api-php-clientを使います。

スプレッドシートの確認はこちらから。urlリストと書き込み先のシート

ここへアクセスするとしばらく待たされますが、書き込み先のシートに追記されます。

これまで同様にGoogle Cloud PlatformにおけるProjectの作成、Google Sheet APIの有効化、サービスアカウントの作成と認証情報を含んだJSONファイルのダウンロードをしておきます。前回のダウンロードしたJSONファイルをそのまま使ってもかまいません。

シート「サイト情報1」に列挙されているurlを読み込んで、タイトルとdescriptionを取得し、これをシート「サイト情報2」に書き込みます。

まず自作関数getUrlsFromSpreadsheetでurlを読みとります。自作関数 getDataFromPageで実際にページにアクセスしてurlとタイトル、descriptionの配列を取得します。そして自作関数 setDataToSpreadsheetで別のシートに結果を書き込みます。

<?php

$spreadsheet_id = "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX";
$sheetName1 = "サイト情報1";
$sheetName2 = "サイト情報2";

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

$urls = getUrlsFromSpreadsheet($keyFile, $spreadsheet_id, $sheetName1);
$array = getDataFromPage($urls);
setDataToSpreadsheet($keyFile, $spreadsheet_id, $sheetName2, $array);
echo "完了しました";

<?php

$spreadsheet_id = "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX";

$sheetName1 = "サイト情報1";

$sheetName2 = "サイト情報2";

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

$urls = getUrlsFromSpreadsheet($keyFile, $spreadsheet_id, $sheetName1);

$array = getDataFromPage($urls);

setDataToSpreadsheet($keyFile, $spreadsheet_id, $sheetName2, $array);

echo "完了しました";

では次にurlを読みとるためのgetUrlsFromSpreadsheet関数をみてみましょう。

function getUrlsFromSpreadsheet($keyFile, $spreadsheet_id, $sheetName)
{
    require_once '../google-api-php-client/vendor/autoload.php';

    $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);

    $range = $sheetName.'!A1:A50'; // 取得する範囲
    $response = $sheet->spreadsheets_values->get($spreadsheet_id, $range);
    $values = $response->getValues();

    // urlは一番左のセルに書かれているものとする
    $array = array();
    foreach($values as $data) {
        $url = $data[0];
        $array[] = $url;
    }
    return $array;
}

function getUrlsFromSpreadsheet($keyFile, $spreadsheet_id, $sheetName)

{

require_once '../google-api-php-client/vendor/autoload.php';

$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);

$range = $sheetName.'!A1:A50'; // 取得する範囲

$response = $sheet->spreadsheets_values->get($spreadsheet_id, $range);

$values = $response->getValues();

// urlは一番左のセルに書かれているものとする

$array = array();

foreach($values as $data) {

$url = $data[0];

$array[] = $url;

}

return $array;

}

次に実際にページにアクセスしてurlとタイトル、descriptionの配列を取得するgetDataFromPage関数を示します。

HTMLの内容によっては警告がでることがあります。そこでlibxml_use_internal_errors( true );とlibxml_clear_errors();を実行しています。それからタイトルやdescriptionが取得できない場合は空白文字をいれます。

function getDataFromPage($urls)
{
    $data = array();

    foreach($urls as $url) {
        if ($html = @file_get_contents($url)) {
            $domDocument = new DOMDocument();

            libxml_use_internal_errors( true ); // ①
            $domDocument->loadHTML($html);
            libxml_clear_errors();   // ②
            // ①、② $domDocument->loadHTML($html)を実行したときに警告が出ないようにする

            $xmlString = $domDocument->saveXML();
            $xmlObject = simplexml_load_string($xmlString);

            $newdata = array();

            // Urlを取得
            $newdata[] = $url;

            // タイトルを取得
            $array1 = json_decode(json_encode($xmlObject), true);
            $title = "";
            if(@array_key_exists('head', $array1))
            {
                if(@array_key_exists('title', $array1['head']))
                    $title = $array1['head']['title'];
            }
            $newdata[] = $title;

            // descriptionを取得
            $tags = get_meta_tags($url);
            if(@array_key_exists('description', $tags))
                $newdata[] = $tags['description'];
            else
                $newdata[] = "";
        }
        $data[] = $newdata;
    }
    return $data;
}

function getDataFromPage($urls)

{

$data = array();

foreach($urls as $url) {

if ($html = @file_get_contents($url)) {

$domDocument = new DOMDocument();

libxml_use_internal_errors( true ); // ①

$domDocument->loadHTML($html);

libxml_clear_errors(); // ②

// ①、② $domDocument->loadHTML($html)を実行したときに警告が出ないようにする

$xmlString = $domDocument->saveXML();

$xmlObject = simplexml_load_string($xmlString);

$newdata = array();

// Urlを取得

$newdata[] = $url;

// タイトルを取得

$array1 = json_decode(json_encode($xmlObject), true);

$title = "";

if(@array_key_exists('head', $array1))

{

if(@array_key_exists('title', $array1['head']))

$title = $array1['head']['title'];

}

$newdata[] = $title;

// descriptionを取得

$tags = get_meta_tags($url);

if(@array_key_exists('description', $tags))

$newdata[] = $tags['description'];

else

$newdata[] = "";

}

$data[] = $newdata;

}

return $data;

}

最後に取得したデータをシートに書き込むgetDataFromPage関数を示します。おまけで最後に書き込み時刻をいれています。

function setDataToSpreadsheet($keyFile, $spreadsheet_id, $SheetName, $array)
{
    require_once '../google-api-php-client/vendor/autoload.php';

    $client = new Google_Client();
    $client->setAuthConfig($keyFile);

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

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

    $spreadsheet_service = new Google_Service_Sheets($client);

    $range = $SheetName.'!A1';

    // おまけ：データ更新時刻を書き込む
    date_default_timezone_set('Asia/Tokyo');
    $array[] = [date("Y/m/d"), date("H:i:s")];

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

    $params = ['valueInputOption' => 'USER_ENTERED'];
    $result = $spreadsheet_service->spreadsheets_values->append($spreadsheet_id, $range, $body, $params);
}

function setDataToSpreadsheet($keyFile, $spreadsheet_id, $SheetName, $array)

{

require_once '../google-api-php-client/vendor/autoload.php';

$client = new Google_Client();

$client->setAuthConfig($keyFile);

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

$scopes = [Google_Service_Sheets::SPREADSHEETS];

$client->setScopes($scopes);

$spreadsheet_service = new Google_Service_Sheets($client);

$range = $SheetName.'!A1';

// おまけ：データ更新時刻を書き込む

date_default_timezone_set('Asia/Tokyo');

$array[] = [date("Y/m/d"), date("H:i:s")];

$body = new \Google_Service_Sheets_ValueRange([

'values' => $array

]);

$params = ['valueInputOption' => 'USER_ENTERED'];

$result = $spreadsheet_service->spreadsheets_values->append($spreadsheet_id, $range, $body, $params);

}

動作確認とスプレッドシートの確認はこちらから。urlリストと書き込み先のシート

ここへアクセスするとしばらく待たされますが、書き込み先のシートに追記されます。

スプレッドシートに書かれているurlからサイト情報を抜き出してシートに書き込む方法

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

コメントについて

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

コメントをどうぞ