So I created a Spreadsheet class that is a combination of a few solutions I found online for accessing Google Sheets API with PHP. It works.
class Spreadsheet {
private $token;
private $spreadsheet;
private $worksheet;
private $spreadsheetid;
private $worksheetid;
private $client_id = '<client id>';
private $service_account_name = '<service_account>'; // email address
private $key_file_location = 'key.p12'; //key.p12
private $client;
private $service;
public function __construct() {
$this->client = new Google_Client();
$this->client->setApplicationName("Sheets API Testing");
$this->service = new Google_Service_Drive($this->client);
$this->authenticate();
}
public function authenticate()
{
if (isset($_SESSION['service_token'])) {
$this->client->setAccessToken($_SESSION['service_token']);
}
$key = file_get_contents($this->key_file_location);
$cred = new Google_Auth_AssertionCredentials(
$this->service_account_name,
array('https://www.googleapis.com/auth/drive', 'https://spreadsheets.google.com/feeds'), $key
);
$this->client->setAssertionCredentials($cred);
if ($this->client->getAuth()->isAccessTokenExpired()) {
$this->client->getAuth()->refreshTokenWithAssertion($cred);
}
$_SESSION['service_token'] = $this->client->getAccessToken();
// Get access token for spreadsheets API calls
$resultArray = json_decode($_SESSION['service_token']);
$this->token = $resultArray->access_token;
}
public function setSpreadsheet($title) {
$this->spreadsheet = $title;
return $this;
}
public function setSpreadsheetId($id) {
$this->spreadsheetid = $id;
return $this;
}
public function setWorksheet($title) {
$this->worksheet = $title;
return $this;
}
public function insert() {
if (!empty($this->token)) {
$url = $this->getPostUrl();
} else {
echo "Authentication Failed";
}
}
public function add($data) {
if(!empty($this->token)) {
$url = $this->getPostUrl();
if(!empty($url)) {
$columnIDs = $this->getColumnIDs();
if($columnIDs) {
$fields = '<entry xmlns="http://www.w3.org/2005/Atom" xmlns:gsx="http://schemas.google.com/spreadsheets/2006/extended">';
foreach($data as $key => $value) {
$key = $this->formatColumnID($key);
if(in_array($key, $columnIDs)) {
$fields .= "<gsx:$key><![CDATA[$value]]></gsx:$key>";
}
}
$fields .= '</entry>';
$headers = [
"Authorization" => "Bearer $this->token",
'Content-Type' => 'application/atom+xml'
];
$method = 'POST';
$req = new Google_Http_Request($url, $method, $headers, $fields);
$curl = new Google_IO_Curl($this->client);
$results = $curl->executeRequest($req);
var_dump($results);
}
}
}
}
private function getColumnIDs() {
$url = "https://spreadsheets.google.com/feeds/cells/" . $this->spreadsheetid . "/" . $this->worksheetid . "/private/full?max-row=1";
$headers = array(
"Authorization" => "Bearer $this->token",
"GData-Version: 3.0"
);
$method = "GET";
$req = new Google_Http_Request($url, $method, $headers);
$curl = new Google_IO_Curl($this->client);
$results = $curl->executeRequest($req);
if($results[2] == 200) {
$columnIDs = array();
$xml = simplexml_load_string($results[0]);
if($xml->entry) {
$columnSize = sizeof($xml->entry);
for($c = 0; $c < $columnSize; ++$c) {
$columnIDs[] = $this->formatColumnID($xml->entry[$c]->content);
}
}
return $columnIDs;
}
return "";
}
private function getPostUrl() {
if (empty($this->spreadsheetid)){
#find the id based on the spreadsheet name
$url = "https://spreadsheets.google.com/feeds/spreadsheets/private/full?title=" . urlencode($this->spreadsheet);
$method = 'GET';
$headers = ["Authorization" => "Bearer $this->token"];
$req = new Google_Http_Request($url, $method, $headers);
$curl = new Google_IO_Curl($this->client);
$results = $curl->executeRequest($req);
if($results[2] == 200) {
$spreadsheetXml = simplexml_load_string($results[0]);
if($spreadsheetXml->entry) {
$this->spreadsheetid = basename(trim($spreadsheetXml->entry[0]->id));
$url = "https://spreadsheets.google.com/feeds/worksheets/" . $this->spreadsheetid . "/private/full";
if(!empty($this->worksheet)) {
$url .= "?title=" . $this->worksheet;
}
$req = new Google_Http_Request($url, $method, $headers);
$response = $curl->executeRequest($req);
if($response[2] == 200) {
$worksheetXml = simplexml_load_string($response[0]);
if($worksheetXml->entry) {
$this->worksheetid = basename(trim($worksheetXml->entry[0]->id));
}
}
}
}
}
if(!empty($this->spreadsheetid) && !empty($this->worksheetid)) {
return "https://spreadsheets.google.com/feeds/list/" . $this->spreadsheetid . "/" . $this->worksheetid . "/private/full";
}
return "";
}
private function formatColumnID($val) {
return preg_replace("/[^a-zA-Z0-9.-]/", "", strtolower($val));
}
}
I then use this test php file to add rows to to my spreadsheet:
$Spreadsheet = new Spreadsheet();
$Spreadsheet->
setSpreadsheet("test spreadsheet")->
setWorksheet("Sheet1")->
add(array("name" => "Cell 1", "email" => "Cell 2"));
With this I can delete a row / update a row and append a row. However, the MAIN reason I needed this was to INSERT a row. Has anyone figured out a way to do this? Any language is fine although id prefer a php solution.
