Matt J. Wilson

So I recently came across the need to read in a Google Docs spreadhseet via PHP, which is actually incredibly easy to do.  I opted to use CSV output and had Google Docs give me a link to use.  It’s as simple as clicking “Share”, “Start Publishing”, then changing the format from “Web-Page” to “CSV (comma-separated values)”.  The link for this example is:

http://spreadsheets.google.com/pub?key=rwY-bmP3cyAzaS5xzxv3XFg&output=csv

Now for the code; my example reads in the file and inserts into my custom ‘markers’ table, but you should be able to get the picture from this:

<?
require("includes/db_conn.php");

// Opens a connection to a MySQL server
$connection = mysql_connect("localhost", $username, $password);
if (!$connection) {
die("Not connected : " . mysql_error());
}

// Set the active MySQL database
$db_selected = mysql_select_db($database, $connection);
if (!$db_selected) {
die("Can\'t use db : " . mysql_error());
}

$file = fopen("http://spreadsheets.google.com/pub?key=rwY-bmP3cyAzaS5xzxv3XFg&amp;output=csv","r");

//we don't want to get the first line
$firstLine = true;

$addresses = array();
$i = 0;

while (($arr = fgetcsv($file, 1000, ",")) !== FALSE)
{
if ($firstLine)
{
$firstLine = false;
}
else
{
$arr = str_replace("'", "\'", $arr);
$addresses[$i++] = "'" . $arr[1] . "'";

$sql = "select * from markers where address ='" . $arr[1] . "';";
$result = mysql_query($sql);
if(mysql_num_rows($result) == 0)
{
$sql = "insert into markers (name, address, description, type) values (".
"'" . $arr[0] . "', '" . $arr[1] . "', '" . $arr[2] . "', '" . $arr[3] . "');";
mysql_query($sql);
}
else
{
$sql = "UPDATE markers SET name='" . $arr[0] . "', description = '" . $arr[2] . "', type = '" . $arr[3] . "' WHERE address='" . $arr[1] . "';";
mysql_query($sql);
}
if(mysql_error())
{
echo mysql_error() ."<br>\n";
}
}
}

// delete any addresses that existed previously, but aren't in the file now
$sql = "DELETE FROM markers WHERE address NOT IN(" . implode(",", $addresses) . ");";
mysql_query($sql);

fclose($file)
?>