Convert Google Sheet to SQLite

May be you are interested in convert a Google Sheet to SQL compatible database. In order to do that, different approaches can be used. The one I have selected is to convert the GSheet to a SQLite file. If you can achieve this file, there are a lot of converters out there to have the data in any SQL Database system as MySQL or MariaDB.

First you have to get access to the Google Sheet data. As we have seen in previous posts, there different ways to do it. You can use the Google Sheets API or the Google Visualization API Query Language. Other approachs as get the public sheet as RSS feed is lack of data type information (And of course, it has to be public which is a limitation).

First thing you have to deal with is about permission to access the Google Sheet. This issue has been treated in previous posts. But for the porpouse of this post, the easy way to handle the permissions is to share the Google Sheet as public. And in this case, if you have multiple sheets in the spreadsheet, you will need an API key and use the Google Sheets API.

I have selected the use of Google Visualization API Query for retrieve data. In this case, the Sheet must be well formed (see this) in order to work properly. This approach allows to get easily the data type of the columns and convert the sheet adequately.

The code is here:

<?php
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

$spreadsheet = '1qmUlxl2QnLlSWVqFuxVfW1U2URpRG-ib6PmKr4sqbd8';
$api_key = 'AIzaryDM9wKd7gtBJZq64hgUHlO_XgZtwluEftk';

$url = 'https://sheets.googleapis.com/v4/spreadsheets/'.$spreadsheet.'?key='.$api_key;
$result = json_decode(file_get_contents($url));

$sqlitefile = 'test.db';
unlink($sqlitefile);
if( file_exists($sqlitefile) ) {
   // unlink failed
   echo 'Error: Permisson denied when writing temporary file.';
   return;
}

$db = new PDO("sqlite:". $sqlitefile);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // set the PDO error mode to exception

$sheets = array();
foreach($result->sheets as $sheet) {	
	$url = 'https://docs.google.com/spreadsheets/d/'.$spreadsheet.'/gviz/tq?';
	$data = http_build_query(
		array(		
			'gid' => $sheet->properties->sheetId,
			'tqx' => 'out:json',
			'tq'  => 'select *'
		)
	);	
	$table = parseGV(file_get_contents($url.$data));
	echo ('<b>Find table: '.$sheet->properties->title.'</b><br>');	
	$columns = createSchema($sheet->properties->title, $table->cols);	
	$num_rows = insertRows($sheet->properties->title, $columns, $table->rows);	
}

function createSchema($tableTitle, $cols) {	
	$command = 'CREATE TABLE IF NOT EXISTS `'.$tableTitle.'` (';
	$columns = array();
	$column = array();
	foreach($cols as $key => $col) {
		$label = $col->label;
		$gsheetType = $col->type;		
		$sqliteType = '';
		
		if(empty($label)) {
			$label = 'blank_'.($key+1);			
		}
		$column['label'] = $label;
		$column['type'] = $gsheetType;
		//$column = (object) ['label' => $label, 'type'=> $gsheetType];
		$columns[] = $column;		
		switch ($gsheetType) {		// Supported data types in https://developers.google.com/chart/interactive/docs/querylanguage
			case 'string':
				$sqliteType = 'TEXT';
				break;
			case 'number':
				$sqliteType = 'INTEGER';
				break;
			case 'boolean':
				$sqliteType = 'BOOLEAN';
				break;
			case 'date':
				$sqliteType = 'DATE';
				break;
			case 'datetime':
				$sqliteType = 'DATETIME';
				break;
			case 'timeofday':
				$sqliteType = 'TIME';
				break;
		}		
		echo ('Find Column '.$key.' ('.$gsheetType.'): '.$label.'<br>');
		$command .= '`'.$label.'` '.$sqliteType.', ';		
	}
	$command = substr($command, 0, -2); // get rid of the last comma and whitespace
	$command .= ')';	
		
	$GLOBALS['db']->exec($command);
	echo ('<i>Schema '.$tableTitle.' successfully created.</i><br>');	
	return $columns;
}
function insertRows($tableTitle, $columns, $rows) {
	
	$query = 'INSERT INTO `'.$tableTitle.'` VALUES (';	
	foreach($columns as $column) {
		$query .= '?, ';
	}	
	
	$query = substr($query, 0, -2); // get rid of the last comma and whitespace
	$query .= ')';
	
	$GLOBALS['db']->beginTransaction();
	
	foreach ($rows as $row) {	// Explanation of rows values in https://developers.google.com/chart/interactive/docs/reference#methods
		$values = array();
		foreach ($row->c as $key => $cell) {			
			if(!isset($cell->v)) {
				$values[] = "";
				continue;								
			}			
			switch ($columns[$key]['type']) {		// Supported data types in https://developers.google.com/chart/interactive/docs/querylanguage
				case 'string':
					$values[] = $cell->v;
					break;
				case 'number':
					$values[] = $cell->v;
					break;
				case 'boolean':
					$values[] = ($cell->v) ? 1 : 0;					
					break;
				case 'date':
					$date = date_create_from_format('!\D\a\t\e(Y,m,d)', $cell->v);
					$values[] = $date->format('Y-m-d');
					break;
				case 'datetime':
					$datetime = date_create_from_format('!\D\a\t\e(Y,m,d,H,i,s)', $cell->v);
					$values[] = $datetime->format('Y-m-d H:i:s');
					break;
				case 'timeofday':
					$timeString = implode(',', $cell->v);					
					$time = date_create_from_format('H,i,s,u', $timeString);					
					$values[] = $time->format('H:i:s.v');
					break;
			}	
		}		
		//echo ('Inserting Row '.$key.': '.$values.'<br>');				
		$GLOBALS['db']->prepare($query)->execute($values);		// run the query		
	}
	$GLOBALS['db']->commit();
	echo ('<i>'.count($rows).' rows successfully inserted.</i><br><br>');
}

function parseGV($gv) {
	$data = array();
	// Buh-Bye extra BS.
	$gv = preg_replace('/google.visualization.Query.setResponse\(/', '', $gv);	
	$gv = preg_replace('/\)\;/', '', $gv);
	$gv = preg_replace('/\/\*O_o\*\//','',$gv);	
	$json = json_decode($gv);
	$table = $json->table;
	return ($table);
}

?>

Code is documented. If you have any doubt you can comment below.