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.
