Google Spread Sheetでデータベスを作る - プログラムを楽しもう

GoogleSpreadSheetを元に、Mysqlのデータベースを作るプログラムを作ってみる

対応は

タイトル名　ー＞　データベース名

シート名　ー＞　テーブル名

シートの中は、

フィールド名

フィールドの型

データ

として、１列目に、上記内容を指定できる書式とする

フィールド名　ー＞　fieldname

フィールドの型　ー＞　fieldtype

テーブルのコメント　ー＞　table comment

フィールドのコメント　ー＞　fieldcomment

SQLのテンプレート　-> template

データ　ー＞　>

コメント　ー＞　＃

言語はPHP

データはGoogleSpreadSheet

取得方法はGoogleAPI

作成はMysql

とする

データの元となるGggleSpreadSheetを使って、閲覧許可にして置く

ID　を、取得しておく

以下を参考にして

https://www.otsuka-bs.co.jp/web-creation/blog/archive/20230904-03.html

接続とタイトルの取得確認をする

シートの内容は、以下のような感じ

実データを書き込むと以下になる

fieldtypeを２行にしてみました

作成した、シートは閲覧許可にしておく

データを参照するプログラムは以下となる

&lt;?php
// php importer.php
ini_set('display_errors', "On");
require './vendor/autoload.php';
$key ="my-project-php-XXXXXXXX-XXXXXXXfca9.json";
$client = new \Google_Client();
$client->setAuthConfig($key);
$client->addScope(\Google_Service_Sheets::SPREADSHEETS);
$client->setApplicationName("Test"); // 適当な名前でOK
$service = new Google_Service_Sheets($client);
$spreadsheet_id = 'xxxxxxxxxx';
//https://docs.google.com/spreadsheets/d/105NXXXXXXXXlzrizMVGDH7i-UEi-eKRlvDKODGBAHnso/edit?gid=0#gid=0
$spreadsheet_id = '105NXXXXXXXXlzrizMVGDH7i-UEi-eKRlvDKODGBAHnso';
$response = $service->spreadsheets->get($spreadsheet_id);
print($response->properties->title."\n"); // タイトル
$sheets = $response->getSheets();
print(count($sheets)."\n");
print($sheets[0]->properties->title."\n");
print($sheets[1]->properties->title."\n");
$sheet_name=$sheets[0]->properties->title;
$sheet_range="A1:F11";
$response = $service->spreadsheets_values->get($spreadsheet_id, $sheet_name.'!'.$sheet_range);
$notice = $response->getValues();
print_r($notice);
for ($i = 0; $i &lt;count($notice); $i++) {
print_r($notice[$i]);

for ($j = 0; $j &lt;count($notice[$i]); $j++) {
print_r($notice[$i][$j]);
}
}
exit("ok");

<?php

// php importer.php

ini_set('display_errors', "On");

require './vendor/autoload.php';

$key ="my-project-php-XXXXXXXX-XXXXXXXfca9.json";

$client = new \Google_Client();

$client->setAuthConfig($key);

$client->addScope(\Google_Service_Sheets::SPREADSHEETS);

$client->setApplicationName("Test"); // 適当な名前でOK

$service = new Google_Service_Sheets($client);

$spreadsheet_id = 'xxxxxxxxxx';

//https://docs.google.com/spreadsheets/d/105NXXXXXXXXlzrizMVGDH7i-UEi-eKRlvDKODGBAHnso/edit?gid=0#gid=0

$spreadsheet_id = '105NXXXXXXXXlzrizMVGDH7i-UEi-eKRlvDKODGBAHnso';

$response = $service->spreadsheets->get($spreadsheet_id);

print($response->properties->title."\n"); // タイトル

$sheets = $response->getSheets();

print(count($sheets)."\n");

print($sheets[0]->properties->title."\n");

print($sheets[1]->properties->title."\n");

$sheet_name=$sheets[0]->properties->title;

$sheet_range="A1:F11";

$response = $service->spreadsheets_values->get($spreadsheet_id, $sheet_name.'!'.$sheet_range);

$notice = $response->getValues();

print_r($notice);

for ($i = 0; $i <count($notice); $i++) {

print_r($notice[$i]);

for ($j = 0; $j <count($notice[$i]); $j++) {

print_r($notice[$i][$j]);

}

exit("ok");

では、

テーブル名と

フィールドの行を見つける

フィールドのタイプを見つける

Create文を作ってみる

&lt;?php
// php importer.php
ini_set('display_errors', "On");
require './vendor/autoload.php';
$key ="my-project-php-xxxxxx-xxxxxxxxxca9.json";

$client = new \Google_Client();
$client->setAuthConfig($key);
$client->addScope(\Google_Service_Sheets::SPREADSHEETS);
$client->setApplicationName("Test"); // 適当な名前でOK
$service = new Google_Service_Sheets($client);

$spreadsheet_id = 'xxxxxxxxxx';
//https://docs.google.com/spreadsheets/d/105NExxxxxxlzrizMVGDH7i-UEi-eKRlvDKODGBAHnso/edit?gid=0#gid=0
$spreadsheet_id = '105NExxxxxxlzrizMVGDH7i-UEi-eKRlvDKODGBAHnso';
$response = $service->spreadsheets->get($spreadsheet_id);
print($response->properties->title."\n"); // タイトル
$sheets = $response->getSheets();
print(count($sheets)."\n");
print($sheets[0]->properties->title."\n");
print($sheets[1]->properties->title."\n");
$sheet_name=$sheets[0]->properties->title;
$sheet_range="A1:F12";
$response = $service->spreadsheets_values->get($spreadsheet_id, $sheet_name.'!'.$sheet_range);
$notice = $response->getValues();
print_r($notice);
for ($i = 0; $i &lt;count($notice); $i++) {
print_r($notice[$i]);

for ($j = 0; $j &lt;count($notice[$i]); $j++) {
print_r($notice[$i][$j]);
}
}
// 
$r_filedname=0;
$r_filetype1=1;
$r_filetype2=2;
$r_data_start=8;
$r_data_end=11;
$fileds="";
$slqtemp="CREATE TABLE IF NOT EXISTS  `#tablename#` ( `id` INT(11) NOT NULL AUTO_INCREMENT,#fields# , `pub_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,PRIMARY KEY (`id`) USING BTREE)COLLATE=`utf8mb4_general_ci`ENGINE=InnoDB";
$tablename="fruit";
for ($j = 1; $j &lt;count($notice[$r_filedname]); $j++) {
if($j!=1){$fileds=$fileds.",";}
$fileds=$fileds."`".$notice[$r_filedname][$j]."`".$notice[$r_filetype1][$j]." ".$notice[$r_filetype2][$j];
}
print("\n".$fileds);
// https://www.sejuku.net/blog/30092
//$text = 'apple,orange,apple,orange';
//指定した文字列が一致したら置き換える
//$replace = str_replace('apple', 'melon', $text);
$slqtemp=str_replace('#tablename#', $tablename, $slqtemp);
$slqtemp=str_replace('#fields#', $fileds, $slqtemp);
print("\n".$slqtemp);
$sql_insert="insert into ".$tablename."(";
$sql_insert2="";
for ($j = 1; $j &lt;count($notice[$r_filedname]); $j++) {
if($j!=1){
	$sql_insert=$sql_insert.",";
	$sql_insert2=$sql_insert2.",";
}
$sql_insert=$sql_insert."`".$notice[$r_filedname][$j]."`";
$sql_insert2=$sql_insert2."?";
}
$sql_insert=$sql_insert.") values (".$sql_insert2.")";
print("\n");
print($sql_insert);
try {
$pdo = new PDO('mysql:dbname=openbook;host=xxx.xxx.xxx.xxx;charset=utf8mb4', 'acountname', 'password', [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]);
} catch (PDOException $e) {
    exit($e->getMessage());    
}
// テーブル作成
$stmt = $pdo->prepare($slqtemp);
$flag = $stmt->execute();
print($flag);
// データインポート
for ($i = $r_data_start; $i &lt;= $r_data_end; $i++) {
//$r_data_start=8;
//$r_data_end=11;
	$stmt = $pdo->prepare($sql_insert);
	$mapdata=array();
	for ($j = 1; $j &lt;count($notice[$r_filedname]); $j++) {
		if(strpos(strtoupper($notice[$r_filetype1][$j]),"INT")!==false){
			print($notice[$r_filetype1][$j]."にはINT１が含まれます\n");
			if($notice[$i][$j]==""){
				$setdata="0";
			}else{
				$setdata=$notice[$i][$j];
			}
		}else{
			print($notice[$r_filetype1][$j]."にはINT１が含まれません\n");
			 $setdata=mb_convert_encoding($notice[$i][$j],"utf-8","sjis") ;
		}
		array_push($mapdata,$setdata);
	}
//			print_r($mapdata);
		$flag = $stmt->execute($mapdata);
	}
exit("\nok");

100

101

102

<?php

// php importer.php

ini_set('display_errors', "On");

require './vendor/autoload.php';

$key ="my-project-php-xxxxxx-xxxxxxxxxca9.json";

$client = new \Google_Client();

$client->setAuthConfig($key);

$client->addScope(\Google_Service_Sheets::SPREADSHEETS);

$client->setApplicationName("Test"); // 適当な名前でOK

$service = new Google_Service_Sheets($client);

$spreadsheet_id = 'xxxxxxxxxx';

//https://docs.google.com/spreadsheets/d/105NExxxxxxlzrizMVGDH7i-UEi-eKRlvDKODGBAHnso/edit?gid=0#gid=0

$spreadsheet_id = '105NExxxxxxlzrizMVGDH7i-UEi-eKRlvDKODGBAHnso';

$response = $service->spreadsheets->get($spreadsheet_id);

print($response->properties->title."\n"); // タイトル

$sheets = $response->getSheets();

print(count($sheets)."\n");

print($sheets[0]->properties->title."\n");

print($sheets[1]->properties->title."\n");

$sheet_name=$sheets[0]->properties->title;

$sheet_range="A1:F12";

$response = $service->spreadsheets_values->get($spreadsheet_id, $sheet_name.'!'.$sheet_range);

$notice = $response->getValues();

print_r($notice);

for ($i = 0; $i <count($notice); $i++) {

print_r($notice[$i]);

for ($j = 0; $j <count($notice[$i]); $j++) {

print_r($notice[$i][$j]);

}

$r_filedname=0;

$r_filetype1=1;

$r_filetype2=2;

$r_data_start=8;

$r_data_end=11;

$fileds="";

$slqtemp="CREATE TABLE IF NOT EXISTS `#tablename#` ( `id` INT(11) NOT NULL AUTO_INCREMENT,#fields# , `pub_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,PRIMARY KEY (`id`) USING BTREE)COLLATE=`utf8mb4_general_ci`ENGINE=InnoDB";

$tablename="fruit";

for ($j = 1; $j <count($notice[$r_filedname]); $j++) {

if($j!=1){$fileds=$fileds.",";}

$fileds=$fileds."`".$notice[$r_filedname][$j]."`".$notice[$r_filetype1][$j]." ".$notice[$r_filetype2][$j];

}

print("\n".$fileds);

// https://www.sejuku.net/blog/30092

//$text = 'apple,orange,apple,orange';

//指定した文字列が一致したら置き換える

//$replace = str_replace('apple', 'melon', $text);

$slqtemp=str_replace('#tablename#', $tablename, $slqtemp);

$slqtemp=str_replace('#fields#', $fileds, $slqtemp);

print("\n".$slqtemp);

$sql_insert="insert into ".$tablename."(";

$sql_insert2="";

for ($j = 1; $j <count($notice[$r_filedname]); $j++) {

if($j!=1){

$sql_insert=$sql_insert.",";

$sql_insert2=$sql_insert2.",";

}

$sql_insert=$sql_insert."`".$notice[$r_filedname][$j]."`";

$sql_insert2=$sql_insert2."?";

}

$sql_insert=$sql_insert.") values (".$sql_insert2.")";

print("\n");

print($sql_insert);

try {

$pdo = new PDO('mysql:dbname=openbook;host=xxx.xxx.xxx.xxx;charset=utf8mb4', 'acountname', 'password', [

PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,

]);

} catch (PDOException $e) {

exit($e->getMessage());

}

// テーブル作成

$stmt = $pdo->prepare($slqtemp);

$flag = $stmt->execute();

print($flag);

// データインポート

for ($i = $r_data_start; $i <= $r_data_end; $i++) {

//$r_data_start=8;

//$r_data_end=11;

$stmt = $pdo->prepare($sql_insert);

$mapdata=array();

for ($j = 1; $j <count($notice[$r_filedname]); $j++) {

if(strpos(strtoupper($notice[$r_filetype1][$j]),"INT")!==false){

print($notice[$r_filetype1][$j]."にはINT１が含まれます\n");

if($notice[$i][$j]==""){

$setdata="0";

}else{

$setdata=$notice[$i][$j];

}

}else{

print($notice[$r_filetype1][$j]."にはINT１が含まれません\n");

$setdata=mb_convert_encoding($notice[$i][$j],"utf-8","sjis") ;

}

array_push($mapdata,$setdata);

}

// print_r($mapdata);

$flag = $stmt->execute($mapdata);

}

exit("\nok");

フィールド名の位置はどは、ハードコーディングですが

これで、デーブルの作成と、データのインポートができました

一応数値が空の時の処理も、無事対応できているようです

あとは、一度にテーブルを読む込できるようにします

２回目で、不要なテーブルは先頭に-(マイナス)を付けておいて、スルーするようにしよう

また、テーブルをDropして、強制書き換えもできるが、必要なときには、機能を追加してみます