Skip to content

Instantly share code, notes, and snippets.

@meeuw

meeuw/googlespreadsheetproxy.php

Last active August 29, 2015 14:17
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save meeuw/713a207b15fc2ffa9bf4 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save meeuw/713a207b15fc2ffa9bf4 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
googlespreadsheetproxy.php
<?php
function curl($opts)
{
$ch = curl_init();
foreach ($opts as $opt_key => $opt_value) {
if ($opt_key == CURLOPT_POSTFIELDS) {
$opt_value = http_build_query($opt_value);
curl_setopt($ch, CURLOPT_POST, 1);
}
curl_setopt($ch, $opt_key, $opt_value);
}
curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
$result = curl_exec($ch);
return $result;
}
function refresh_token()
{
global $CLIENT_ID, $CLIENT_SECRET, $REFRESH_TOKEN;
return json_decode(curl(array(
CURLOPT_URL => "https://www.googleapis.com/oauth2/v3/token",
CURLOPT_POSTFIELDS => array(
"client_id" => $CLIENT_ID,
"client_secret" => $CLIENT_SECRET,
"grant_type" => "refresh_token",
"refresh_token" => $REFRESH_TOKEN,
)
)), true);
}
function query($action_args, $AUTH)
{
global $KEY, $WORKSHEETID;
if (isset($action_args[0])) {
$salonCode = str_replace('"', '', $action_args[0]);
} else {
$salonCode = 0;
}
$result = curl(array(
CURLOPT_URL => "https://spreadsheets.google.com/feeds/list/$KEY/$WORKSHEETID/private/full?".http_build_query(array(
"sq" => "saloncode=\"$salonCode\"",
)),
CURLOPT_HTTPHEADER => array("Authorization: Bearer {$AUTH["access_token"]}"),
));
$xml = simplexml_load_string($result);
return $xml;
}
/*
secrets.php contains:
// https://console.developers.google.com/project
// - start new project
// - credentials
// - Create new Client ID / Application Type: Installed Application, Installed Application Type: Other
// - Copy values to:
$CLIENT_ID = "";
$CLIENT_SECRET = "";
// - Copy from oauth-token
$REFRESH_TOKEN = "";
// - Copy from spreadsheets
$KEY = "";
// - Copy from worksheets
$WORKSHEETID = "";
*/
include "secrets.php";
$action = "";
if (isset($WORKSHEETID)) {
$actions = array("query", "update");
} else {
$actions = array("oauth-code", "oauth-token", "spreadsheets", "worksheets");
}
if (isset($argv) && count($argv) > 1) {
$output = "console";
if (in_array($argv[1], $actions)) {
$action = $argv[1];
$action_args = array_slice($argv, 2);
}
} elseif (isset($_REQUEST)) {
$output = "browser";
if (isset($_REQUEST["action"]) && in_array($_REQUEST["action"], $actions)) {
$action = $_REQUEST["action"];
if (isset($_REQUEST["action_args"])) {
$action_args = $_REQUEST["action_args"];
} else {
$action_args = array();
}
}
}
if ($action == "oauth-code") {
$a = "https://accounts.google.com/o/oauth2/auth?".http_build_query(array(
"access_type" => "offline",
"client_id" => $CLIENT_ID,
"redirect_uri" => "urn:ietf:wg:oauth:2.0:oob",
"response_type" => "code",
"scope" => "https://spreadsheets.google.com/feeds",
));
if ($output == "console") {
echo "$a\n";
echo "oauth-token CODE\n";
} elseif ($output = "browser") {
echo "<a href=\"$a\" target=\"_blank\">get token</a>";
echo "<html><body><form action=\"{$_SERVER["PHP_SELF"]}\"><input type=\"hidden\" name=\"action\" value=\"oauth-token\"><input name=\"action_args[]\"><input type=\"submit\"></form>";
}
} elseif ($action == "oauth-token") {
$CODE = $action_args[0];
$r = json_decode(curl(array(
CURLOPT_URL => "https://www.googleapis.com/oauth2/v3/token",
CURLOPT_POSTFIELDS => array(
"code" => $CODE,
"client_id" => $CLIENT_ID,
"scope" => "https://spreadsheets.google.com/feeds",
"client_secret" => $CLIENT_SECRET,
"grant_type" => "authorization_code",
"redirect_uri" => "urn:ietf:wg:oauth:2.0:oob",
),
)), true);
echo "\$REFRESH_TOKEN = \"{$r["refresh_token"]}\";\n";
if ($output == "console") {
echo "spreadsheets\n";
} elseif ($output == "browser") {
echo "<br><a href=\"{$_SERVER["PHP_SELF"]}?action=spreadsheets\">spreadsheets</a>";
}
} elseif ($action == "spreadsheets") {
$AUTH = refresh_token();
$result = curl(array(
CURLOPT_URL => "https://spreadsheets.google.com/feeds/spreadsheets/private/full",
CURLOPT_HTTPHEADER => array("Authorization: Bearer {$AUTH["access_token"]}")
));
$xml = simplexml_load_string($result);
$ns = $xml->getDocNamespaces();
$xml->registerXPathNameSpace('default', $ns['']);
if ($output == "browser") {
echo "<table border=1>";
}
foreach ($xml->xpath("//default:entry") as $entry) {
$s = explode("/", $entry->id);
$id = $s[count($s)-1];
if ($output == "browser") {
echo "<tr><td>";
}
echo $entry->title;
if ($output == "browser") {
echo "</td><td>";
}
if ($output == "console") {
echo "\t";
}
echo "\$KEY = \"$id\";";
if ($output == "browser") {
echo "</td></tr>";
}
if ($output == "console") {
echo "\n";
}
}
if ($output == "browser") {
echo "</table>";
echo "<a href=\"{$_SERVER["PHP_SELF"]}?action=worksheets\">worksheets</a>";
}
if ($output == "console") {
echo "worksheets\n";
}
} elseif ($action == "worksheets") {
$AUTH = refresh_token();
$result = curl(array(
CURLOPT_URL => "https://spreadsheets.google.com/feeds/worksheets/$KEY/private/full",
CURLOPT_HTTPHEADER => array("Authorization: Bearer {$AUTH["access_token"]}")
));
$xml = simplexml_load_string($result);
$ns = $xml->getDocNamespaces();
$xml->registerXPathNameSpace('default', $ns['']);
if ($output == "browser") {
echo "<table border=1>";
}
foreach ($xml->xpath("//default:entry") as $entry) {
$s = explode("/", $entry->id);
$id = $s[count($s)-1];
if ($output == "browser") {
echo "<tr><td>";
}
echo $entry->title;
if ($output == "browser") {
echo "</td><td>";
}
if ($output == "console") {
echo "\t";
}
echo "\$WORKSHEETID = \"$id\";";
if ($output == "browser") {
echo "</td></tr>";
}
if ($output == "console") {
echo "\n";
}
}
if ($output == "browser") {
echo "</table>";
}
} elseif ($action == "query") {
$AUTH = refresh_token();
$result = array();
$xml = query($action_args, $AUTH);
$ns = $xml->getDocNamespaces();
$xml->registerXPathNameSpace('default', $ns['']);
foreach ($xml->xpath("//default:entry") as $entry) {
$row = $entry->children($ns['gsx']);
$result_row = array();
foreach ($row as $header => $value) {
$result_row[(string)$header] = (string)$value;
}
$result[] = $result_row;
}
echo json_encode($result, JSON_PRETTY_PRINT)."\n";
} elseif ($action == "update") {
if (count($action_args) < 7) {
echo "niet genoeg argumenten";
exit;
}
$AUTH = refresh_token();
$gsxns = "http://schemas.google.com/spreadsheets/2006/extended";
$put = new SimpleXMLElement("<entry xmlns='http://www.w3.org/2005/Atom' xmlns:gsx='$gsxns'/>");
$put->addChild("scanpro", htmlspecialchars($action_args[1]), $gsxns);
$put->addChild("voornaam", htmlspecialchars($action_args[2]), $gsxns);
$put->addChild("achternaam", htmlspecialchars($action_args[3]), $gsxns);
$put->addChild("priveemail", htmlspecialchars($action_args[4]), $gsxns);
$put->addChild("functie", htmlspecialchars($action_args[5]), $gsxns);
$put->addChild("wachtwoord", htmlspecialchars($action_args[6]), $gsxns);
$xml = query($action_args, $AUTH);
$ns = $xml->getDocNamespaces();
$xml->registerXPathNameSpace('default', $ns['']);
foreach ($xml->xpath("//default:entry[1]") as $entry) {
$ch = curl_init();
curl_setopt($ch, CURLOPT_URL, $entry->id);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
curl_setopt($ch, CURLOPT_CUSTOMREQUEST, "PUT");
curl_setopt($ch, CURLOPT_POSTFIELDS, $put->asXML());
curl_setopt($ch, CURLOPT_HTTPHEADER, array(
"Content-Type: application/atom+xml; charset=UTF-8",
"If-Match: *",
"Authorization: Bearer {$AUTH["access_token"]}"
));
$result = curl_exec($ch);
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment