/**
*
* @param string $format
*/
public function write(Table $table, OutputInterface $output)
{
$serviceRequest = new DefaultServiceRequest();
ServiceRequestFactory::setInstance($serviceRequest);
$spreadsheetService = new SpreadsheetService();
$spreadsheetFeed = $spreadsheetService->getSpreadsheetFeed();
}
public function testUpdate()
{
$mockRequest = $this->getMockBuilder(DefaultServiceRequest::class)->setMethods(["post"])->disableOriginalConstructor()->getMock();
$mockRequest->expects($this->once())->method("post")->with($this->equalTo("https://spreadsheets.google.com/feeds/cells/15L06yklgflGRDjnN-VvhGYOoVLCH40DJoW5fFiqSTc5U/od6/private/full"), $this->stringContains("MyVal"))->willReturn("<entry/>");
ServiceRequestFactory::setInstance($mockRequest);
$this->cellEntry->update("MyVal");
}
/**
* @param GoogleDriveAuth @auth
*/
private function authenticate(GoogleDriveAuth $auth)
{
$token = json_decode($auth->auth()->getAccessToken());
$accessToken = $token->access_token;
$serviceRequest = new DefaultServiceRequest($accessToken);
ServiceRequestFactory::setInstance($serviceRequest);
}
public function testInsert()
{
$mockServiceRequest = $this->getMockBuilder("Google\\Spreadsheet\\DefaultServiceRequest")->setMethods(array("post"))->disableOriginalConstructor()->getMock();
$mockServiceRequest->expects($this->once())->method("post")->with($this->equalTo("https://spreadsheets.google.com/feeds/list/G3345eEsfsk60/od6/private/full"), $this->stringContains("<gsx:occupation>software engineer</gsx:occupation>"));
ServiceRequestFactory::setInstance($mockServiceRequest);
$this->listFeed->insert(["name" => "asim", "occupation" => "software engineer"]);
}
public function testInsert()
{
$mockServiceRequest = $this->getMockBuilder('Google\\Spreadsheet\\DefaultServiceRequest')->setMethods(array("post"))->disableOriginalConstructor()->getMock();
$mockServiceRequest->expects($this->once())->method('post')->with($this->equalTo('https://spreadsheets.google.com/feeds/list/G3345eEsfsk60/od6/private/full'), $this->stringContains('<gsx:occupation><![CDATA[software engineer]]></gsx:occupation>'));
ServiceRequestFactory::setInstance($mockServiceRequest);
$listFeed = new ListFeed(file_get_contents(__DIR__ . '/xml/list-feed.xml'));
$listFeed->insert(["name" => "asim", "occupation" => "software engineer"]);
}
/**
* Insert a new row into this feed
*
* @param array $row
*
* @return void
*/
public function insert($row)
{
$entry = '<entry xmlns="http://www.w3.org/2005/Atom" xmlns:gsx="http://schemas.google.com/spreadsheets/2006/extended">';
foreach ($row as $colName => $value) {
$entry .= sprintf('<gsx:%s><![CDATA[%s]]></gsx:%s>', $colName, $value, $colName);
}
$entry .= '</entry>';
ServiceRequestFactory::getInstance()->post($this->getPostUrl(), $entry);
}
/**
* Returns a list feed of the specified worksheet.
*
* @param string $accessToken Google Auth Access Token
* @param string $sheetTitle title of spreadsheet
*
* @return object $result
*/
function getSpreadsheet($accessToken, $sheetTitle)
{
$serviceRequest = new DefaultServiceRequest($accessToken);
ServiceRequestFactory::setInstance($serviceRequest);
$spreadsheetService = new Google\Spreadsheet\SpreadsheetService();
$spreadsheetFeed = $spreadsheetService->getSpreadsheets();
$result = $spreadsheetFeed->getByTitle($sheetTitle);
return $result;
}
/**
* @expectedException Google\Spreadsheet\Exception\ResourceNotFoundException
*/
public function testGetResourceByIdException()
{
$resourceId = "http://resource";
$mockRequest = $this->getMockBuilder(DefaultServiceRequest::class)->setMethods(["get"])->disableOriginalConstructor()->getMock();
$mockRequest->expects($this->once())->method("get")->with($this->equalTo($resourceId))->will($this->throwException(new BadRequestException()));
ServiceRequestFactory::setInstance($mockRequest);
$spreadsheetService = new SpreadsheetService();
$spreadsheet = $spreadsheetService->getResourceById(Spreadsheet::class, $resourceId);
}
public function testDelete()
{
$mockServiceRequest = $this->getMockBuilder(DefaultServiceRequest::class)->setMethods(["delete"])->disableOriginalConstructor()->getMock();
$mockServiceRequest->expects($this->once())->method("delete")->with($this->equalTo("https://spreadsheets.google.com/feeds/list/G3345eEsfsk60/od6/private/full/cokwr/bnkj8i7jo6c"));
ServiceRequestFactory::setInstance($mockServiceRequest);
$listFeed = new ListFeed($this->getSimpleXMLElement("list-feed"));
$entry = current($listFeed->getEntries());
$entry->delete();
}
public function testGetListFeedWithQuery()
{
$feedUrl = "https://spreadsheets.google.com/feeds/list/tA3TdJ0RIVEem3xQZhG2Ceg/od8/private/full?reverse=true&sq=age+%3E+45";
$mockServiceRequest = $this->getMockBuilder('Google\\Spreadsheet\\DefaultServiceRequest')->setMethods(array("get"))->disableOriginalConstructor()->getMock();
$mockServiceRequest->expects($this->once())->method('get')->with($this->equalTo($feedUrl))->willReturn(file_get_contents(__DIR__ . '/xml/list-feed.xml'));
ServiceRequestFactory::setInstance($mockServiceRequest);
$worksheet = new Worksheet(new SimpleXMLElement(file_get_contents(__DIR__ . '/xml/worksheet.xml')));
$worksheet->getListFeed(array("reverse" => "true", "sq" => "age > 45"));
}
protected function setServiceRequest($return, $simpleXml = false)
{
$serviceRequest = new TestServiceRequest(new Request('accesstoken'));
$xml = file_get_contents(__DIR__ . '/xml/' . $return);
if ($simpleXml) {
$xml = new SimpleXMLElement($xml);
}
$serviceRequest->setExecuteReturn($xml);
ServiceRequestFactory::setInstance($serviceRequest);
}
public function testUpdate()
{
$mockServiceRequest = $this->getMockBuilder('Google\\Spreadsheet\\DefaultServiceRequest')->setMethods(array("put"))->disableOriginalConstructor()->getMock();
$mockServiceRequest->expects($this->once())->method('put')->with($this->equalTo('https://spreadsheets.google.com/feeds/list/G3345eEsfsk60/od6/private/full/cokwr/bnkj8i7jo6c'), $this->stringContains('<gsx:nname><![CDATA[Asim]]></gsx:nname>'));
ServiceRequestFactory::setInstance($mockServiceRequest);
$listFeed = new ListFeed(file_get_contents(__DIR__ . '/xml/list-feed.xml'));
$entry = current($listFeed->getEntries());
$data = $entry->getValues();
$data["nname"] = "Asim";
$entry->update($data);
}
/**
* @return \Google\Spreadsheet\SpreadsheetFeed
*/
public static function getSpreadsheetFeed()
{
if (isset(static::$spreadsheetFeed)) {
return static::$spreadsheetFeed;
}
$token = static::getToken();
$serviceRequest = new \Google\Spreadsheet\DefaultServiceRequest($token['access_token']);
\Google\Spreadsheet\ServiceRequestFactory::setInstance($serviceRequest);
$spreadsheetService = new \Google\Spreadsheet\SpreadsheetService();
return static::$spreadsheetFeed = $spreadsheetService->getSpreadsheets();
}
public function testInsertBatch()
{
$mockBatchRequest = $this->getMockBuilder(BatchRequest::class)->setMethods(["createRequestXml"])->disableOriginalConstructor()->getMock();
$mockBatchRequest->expects($this->once())->method("createRequestXml")->will($this->returnValue(new \SimpleXMLElement("<batch/>")));
$mockCellFeed = $this->getMockBuilder(CellFeed::class)->setMethods(["getBatchUrl"])->disableOriginalConstructor()->getMock();
$mockCellFeed->expects($this->once())->method("getBatchUrl")->will($this->returnValue("https://spreadsheets.google.com/"));
$mockServiceRequest = $this->getMockBuilder(DefaultServiceRequest::class)->setMethods(array("post"))->disableOriginalConstructor()->getMock();
$mockServiceRequest->expects($this->once())->method("post")->with($this->equalTo("https://spreadsheets.google.com/"), $this->stringContains("<batch/>"))->will($this->returnValue("<response/>"));
ServiceRequestFactory::setInstance($mockServiceRequest);
$this->assertTrue($mockCellFeed->insertBatch($mockBatchRequest) instanceof BatchResponse);
}
/**
* Insert a new row into this feed
*
* @param array $row
*
* @return void
*/
public function insert($row)
{
$entry = '<entry xmlns="http://www.w3.org/2005/Atom" xmlns:gsx="http://schemas.google.com/spreadsheets/2006/extended">';
foreach ($row as $colName => $value) {
# added by @aravindanve
$value = preg_replace('#(?:\\<\\!\\[CDATA\\[|\\]\\]\\>)#i', '*removed*', $value);
$colName = strtolower(preg_replace('#[^a-z0-9\\-]#i', '', $colName));
$entry .= sprintf('<gsx:%s><![CDATA[%s]]></gsx:%s>', $colName, $value, $colName);
}
$entry .= '</entry>';
ServiceRequestFactory::getInstance()->post($this->getPostUrl(), $entry);
}
public function addEntry($lead)
{
$serviceRequest = new DefaultServiceRequest($this->token);
ServiceRequestFactory::setInstance($serviceRequest);
$spreadsheetService = new SpreadsheetService();
$spreadsheetFeed = $spreadsheetService->getSpreadsheetById($this->spreadsheetId);
$worksheetFeed = $spreadsheetFeed->getWorksheets();
$worksheet = $worksheetFeed->getByTitle($this->tabTitle);
$listFeed = $worksheet->getListFeed();
$result = $listFeed->insert($lead);
return $result;
}
function __construct($ssName)
{
parent::__construct();
// Accessing the data from the Spreadsheet
$serviceRequest = new DefaultServiceRequest($this->accessToken);
ServiceRequestFactory::setInstance($serviceRequest);
$spreadsheetService = new Google\Spreadsheet\SpreadsheetService();
$spreadsheetFeed = $spreadsheetService->getSpreadsheets();
$this->spreadsheet = $spreadsheetFeed->getByTitle($ssName);
// dd($this->spreadsheet);
$worksheetFeed = $this->spreadsheet->getWorksheets();
// dd($worksheetFeed);
// foreach ($worksheetFeed as $key => $value) {
// d($key, $value);
// }
// die();
$worksheet = $worksheetFeed->getByTitle('Sheet1');
// dd($worksheet);
$this->listFeed = $worksheet->getListFeed();
// dd($listFeed);
}
static function getWorksheet()
{
if (empty(self::$worksheet)) {
$config = json_decode(file_get_contents("EG Robotics-581b91515ac6.json"));
$user_to_impersonate = '*****@*****.**';
$scopes = ["https://www.googleapis.com/auth/drive", "https://spreadsheets.google.com/feeds"];
$credentials = new Google_Auth_AssertionCredentials($config->client_email, $scopes, $config->private_key, 'notasecret', 'http://oauth.net/grant_type/jwt/1.0/bearer', $user_to_impersonate);
$client = new Google_Client();
$client->setAssertionCredentials($credentials);
if ($client->getAuth()->isAccessTokenExpired()) {
$client->getAuth()->refreshTokenWithAssertion();
}
$accessToken = json_decode($client->getAuth()->getAccessToken())->access_token;
$serviceRequest = new DefaultServiceRequest($accessToken);
ServiceRequestFactory::setInstance($serviceRequest);
$spreadsheetService = new Google\Spreadsheet\SpreadsheetService();
$spreadsheetFeed = $spreadsheetService->getSpreadsheets();
$spreadsheet = $spreadsheetFeed->getByTitle('Employee Hour Tracking Payroll');
$workSheetFeed = $spreadsheet->getWorksheets();
self::$worksheet = $workSheetFeed->getByTitle("Office Hours");
self::$archive_worksheet = $workSheetFeed->getByTitle("15-16 OH");
}
return self::$worksheet;
}
/**
* Delete this worksheet
*
* @return null
*/
public function delete()
{
ServiceRequestFactory::getInstance()->delete($this->getEditUrl());
}
/**
*
* @param \Google\Spreadsheet\Batch\BatchRequest $batchRequest
*
* @return \Google\Spreadsheet\Batch\BatchResponse
*/
public function insertBatch(BatchRequest $batchRequest)
{
$xml = $batchRequest->createRequestXml($this);
$response = ServiceRequestFactory::getInstance()->addHeader("If-Match", "*")->post($this->getBatchUrl(), $xml);
ServiceRequestFactory::getInstance()->removeHeader("If-Match");
return new BatchResponse(new SimpleXMLElement($response));
}
/**
* Returns a cell feed of the specified worksheet.
*
* @see \Google\Spreadsheet\Worksheet::getWorksheetId()
*
* @param string $worksheetId
*
* @return \Google\Spreadsheet\CellFeed
*/
public function getCellFeed($worksheetId)
{
return new CellFeed(ServiceRequestFactory::getInstance()->get("feeds/cells/{$worksheetId}/od6/private/full"));
}
public static function getEvents($user, $widget)
{
switch ($widget->widget_type) {
case 'google-spreadsheet-line-cell':
Log::info("google-spreadsheet-line-cell - widget_id - " . $widget['id']);
$widget_source = json_decode($widget['widget_source'], true);
$spreadsheetId = $widget_source['googleSpreadsheetId'];
$worksheetName = $widget_source['googleWorksheetName'];
# setup Google stuff
$client = GooglespreadsheetHelper::setGoogleClient();
$access_token = GooglespreadsheetHelper::getGoogleAccessToken($client, $user);
# init service
$serviceRequest = new DefaultServiceRequest($access_token);
ServiceRequestFactory::setInstance($serviceRequest);
$spreadsheetService = new Google\Spreadsheet\SpreadsheetService();
# get spreadsheet
$spreadsheet = $spreadsheetService->getSpreadsheetById($spreadsheetId);
$worksheetFeed = $spreadsheet->getWorksheets();
# get worksheet
$worksheet = $worksheetFeed->getByTitle($worksheetName);
$listFeed = $worksheet->getListFeed();
# get celldata (first line = header, second line = content)
$listArray = array();
$values = array();
foreach ($listFeed->getEntries() as $entry) {
$values = $entry->getValues();
break;
# break, so we just the first line
}
$time = time();
$data = new Data();
$data->widget_id = $widget['id'];
$data->data_object = json_encode($values);
$data->date = date("Y-m-d", $time);
$data->timestamp = date('Y-m-d H:i:s', $time);
$data->save();
$widget->widget_ready = true;
$widget->save();
break;
# / case 'google-spreadsheet-line-cell'
# / case 'google-spreadsheet-line-cell'
case 'google-spreadsheet-line-column':
Log::info("google-spreadsheet-line-column - widget_id - " . $widget['id']);
$widget_source = json_decode($widget['widget_source'], true);
$spreadsheetId = $widget_source['googleSpreadsheetId'];
$worksheetName = $widget_source['googleWorksheetName'];
# setup Google stuff
$client = GooglespreadsheetHelper::setGoogleClient();
$access_token = GooglespreadsheetHelper::getGoogleAccessToken($client, $user);
# init service
$serviceRequest = new DefaultServiceRequest($access_token);
ServiceRequestFactory::setInstance($serviceRequest);
$spreadsheetService = new Google\Spreadsheet\SpreadsheetService();
# get spreadsheet
$spreadsheet = $spreadsheetService->getSpreadsheetById($spreadsheetId);
$worksheetFeed = $spreadsheet->getWorksheets();
# get worksheet
$worksheet = $worksheetFeed->getByTitle($worksheetName);
# get feeddata (first line = header)
$listFeed = $worksheet->getListFeed();
foreach ($listFeed->getEntries() as $entry) {
$array = $entry->getValues();
$date = array_values($array)[0];
$value = array_values($array)[1];
# format date (from almost everything to Y-m-d)
$time = strtotime(trim(str_replace('.', '-', $date), '-'));
# have we saved data for this date?
$db_data = Data::where('widget_id', '=', $widget['id'])->where('date', '=', date("Y-m-d", $time));
if ($db_data->count() == 0) {
# nope, save it
$data = new Data();
$data->widget_id = $widget['id'];
$data->data_object = json_encode(array("value" => $value));
$data->date = date("Y-m-d", $time);
$data->timestamp = date('Y-m-d H:i:s', $time);
$data->save();
} else {
# yes, update it
$db_data->update(['data_object' => json_encode(array("value" => $value))]);
}
}
$widget->widget_ready = true;
$widget->save();
break;
# / case 'google-spreadsheet-line-column'
# / case 'google-spreadsheet-line-column'
case 'google-spreadsheet-text-cell':
Log::info("google-spreadsheet-text-cell - widget_id - " . $widget['id']);
$widget_source = json_decode($widget['widget_source'], true);
$spreadsheetId = $widget_source['googleSpreadsheetId'];
$worksheetName = $widget_source['googleWorksheetName'];
# setup Google stuff
$client = GooglespreadsheetHelper::setGoogleClient();
$access_token = GooglespreadsheetHelper::getGoogleAccessToken($client, $user);
# init service
$serviceRequest = new DefaultServiceRequest($access_token);
ServiceRequestFactory::setInstance($serviceRequest);
$spreadsheetService = new Google\Spreadsheet\SpreadsheetService();
# get spreadsheet
$spreadsheet = $spreadsheetService->getSpreadsheetById($spreadsheetId);
$worksheetFeed = $spreadsheet->getWorksheets();
# get worksheet
$worksheet = $worksheetFeed->getByTitle($worksheetName);
# get feeddata (first line = header)
$listFeed = $worksheet->getListFeed();
foreach ($listFeed->getEntries() as $entry) {
$array = $entry->getValues();
$value = array_values($array)[0];
$time = time();
# have we saved data for this widget?
$db_data = Data::where('widget_id', '=', $widget['id']);
if ($db_data->count() == 0) {
# nope, save it
$data = new Data();
$data->widget_id = $widget['id'];
$data->data_object = json_encode(array("value" => $value));
$data->date = date("Y-m-d", $time);
$data->timestamp = date('Y-m-d H:i:s', $time);
$data->save();
} else {
# yes, update it
$db_data->update(['data_object' => json_encode(array("value" => $value)), 'date' => date("Y-m-d", $time), 'timestamp' => date('Y-m-d H:i:s', $time)]);
}
break;
# just the first line
}
$widget->widget_ready = true;
$widget->save();
break;
# / case 'google-spreadsheet-text-cell'
# / case 'google-spreadsheet-text-cell'
case 'google-spreadsheet-text-column':
Log::info("google-spreadsheet-text-column - widget_id - " . $widget['id']);
$widget_source = json_decode($widget['widget_source'], true);
$spreadsheetId = $widget_source['googleSpreadsheetId'];
$worksheetName = $widget_source['googleWorksheetName'];
# setup Google stuff
$client = GooglespreadsheetHelper::setGoogleClient();
$access_token = GooglespreadsheetHelper::getGoogleAccessToken($client, $user);
# init service
$serviceRequest = new DefaultServiceRequest($access_token);
ServiceRequestFactory::setInstance($serviceRequest);
$spreadsheetService = new Google\Spreadsheet\SpreadsheetService();
# get spreadsheet
$spreadsheet = $spreadsheetService->getSpreadsheetById($spreadsheetId);
$worksheetFeed = $spreadsheet->getWorksheets();
# get worksheet
$worksheet = $worksheetFeed->getByTitle($worksheetName);
# get feeddata (first line = header)
$listFeed = $worksheet->getListFeed();
foreach ($listFeed->getEntries() as $entry) {
$array = $entry->getValues();
$value = array_values($array)[0];
$time = time();
# have we saved data for this widget?
$db_data = Data::where('widget_id', '=', $widget['id']);
if ($db_data->count() == 0) {
# nope, save it
$data = new Data();
$data->widget_id = $widget['id'];
$data->data_object = json_encode(array("value" => $value));
$data->date = date("Y-m-d", $time);
$data->timestamp = date('Y-m-d H:i:s', $time);
$data->save();
} else {
# yes, update it
$db_data->update(['data_object' => json_encode(array("value" => $value)), 'date' => date("Y-m-d", $time), 'timestamp' => date('Y-m-d H:i:s', $time)]);
}
}
$widget->widget_ready = true;
$widget->save();
break;
# / case 'google-spreadsheet-text-column'
# / case 'google-spreadsheet-text-column'
case 'google-spreadsheet-text-column-random':
Log::info("google-spreadsheet-text-column-random - widget_id - " . $widget['id']);
$widget_source = json_decode($widget['widget_source'], true);
$spreadsheetId = $widget_source['googleSpreadsheetId'];
$worksheetName = $widget_source['googleWorksheetName'];
# setup Google stuff
$client = GooglespreadsheetHelper::setGoogleClient();
$access_token = GooglespreadsheetHelper::getGoogleAccessToken($client, $user);
# init service
$serviceRequest = new DefaultServiceRequest($access_token);
ServiceRequestFactory::setInstance($serviceRequest);
$spreadsheetService = new Google\Spreadsheet\SpreadsheetService();
# get spreadsheet
$spreadsheet = $spreadsheetService->getSpreadsheetById($spreadsheetId);
$worksheetFeed = $spreadsheet->getWorksheets();
# get worksheet
$worksheet = $worksheetFeed->getByTitle($worksheetName);
# get feeddata (first line = header)
$listFeed = $worksheet->getListFeed();
$key = 0;
foreach ($listFeed->getEntries() as $entry) {
$array = $entry->getValues();
$value = array_values($array)[0];
$time = time();
# have we saved data for this widget?
$db_data = Data::where('widget_id', '=', $widget['id'])->where('data_key', '=', $key);
if ($db_data->count() == 0) {
# nope, save it
$data = new Data();
$data->widget_id = $widget['id'];
$data->data_key = $key;
$data->data_object = json_encode(array("value" => $value));
$data->date = date("Y-m-d", $time);
$data->timestamp = date('Y-m-d H:i:s', $time);
$data->save();
$key++;
} else {
# yes, update it
$db_data->update(['data_object' => json_encode(array("value" => $value)), 'date' => date("Y-m-d", $time), 'timestamp' => date('Y-m-d H:i:s', $time), 'data_key' => $key]);
}
}
$widget->widget_ready = true;
$widget->save();
break;
# / case 'google-spreadsheet-text-column-random'
}
# / switch ($widget_type)
}
if ($method === "post") {
$values = array();
$parentPropertiesToCopy = array("fathersfirstname", "fatherslastname", "fathersemail", "fathersphone", "mothersfirstname", "motherslastname", "mothersemail", "mothersphone", "comments", "centercommunication");
$childPropertiesToCopy = array("firstnameofchild", "lastnameofchild", "ssegroupofchild", "schoolgradeofchild", "allergiesofchild");
for ($i = 1; $i < 3; ++$i) {
if (empty($_REQUEST["firstnameofchild{$i}"])) {
break;
}
$value = array();
$value["timestamp"] = date("n/j/Y H:i:s");
foreach ($parentPropertiesToCopy as $parentProperty) {
$value[$parentProperty] = trim($_REQUEST[$parentProperty]);
}
foreach ($childPropertiesToCopy as $childProperty) {
$value[$childProperty] = trim($_REQUEST["{$childProperty}{$i}"]);
}
array_push($values, $value);
}
foreach ($values as $value) {
$combinedKey = strtolower(sprintf("%s-%s", $value["firstnameofchild"], $value["lastnameofchild"]));
if ($rows[$combinedKey] && $rows[$combinedKey]["url"] && $rows[$combinedKey]["sheetTitle"] === "2015 Registration") {
ServiceRequestFactory::getInstance()->delete($rows[$combinedKey]["url"]);
}
$registrationFeed->insert($value);
}
echo json_encode(true);
}
}
?>
/**
* @expectedException Exception
*/
public function testGetInstanceException()
{
ServiceRequestFactory::setInstance(null);
ServiceRequestFactory::getInstance();
}
<?php
session_start();
/**
* Created by PhpStorm.
* User: hungnguyen
* Date: 02/03/16
* Time: 4:06 PM
*/
require_once __DIR__ . '/../vendor/autoload.php';
require_once __DIR__ . '/../GoogleSheet.php';
require_once __DIR__ . '/../Config.php';
$feedTitle = $_GET['feedTitle'];
use Google\Spreadsheet\DefaultServiceRequest;
use Google\Spreadsheet\ServiceRequestFactory;
use Lib\Common;
$serviceRequest = new DefaultServiceRequest(Common::getGoogleTokenFromKeyFile());
ServiceRequestFactory::setInstance($serviceRequest);
$spreadsheetService = new Google\Spreadsheet\SpreadsheetService();
$spreadsheetFeed = $spreadsheetService->getSpreadsheets();
$spreadsheet = $spreadsheetFeed->getByTitle($feedTitle);
$worksheetFeed = $spreadsheet->getWorksheets();
foreach ($worksheetFeed as $key => $value) {
$sheet[] = $worksheetFeed[$key]->getTitle();
}
echo json_encode($sheet);
/**
* @param string $accessToken The access token
* @param string $spreadsheetTitle The exact title of the spreadsheet
* @return null|\Google\Spreadsheet\WorksheetFeed
*/
protected function getWorksheets($accessToken, $spreadsheetTitle)
{
$serviceRequest = new \Google\Spreadsheet\DefaultServiceRequest($accessToken);
ServiceRequestFactory::setInstance($serviceRequest);
$spreadsheetService = new \Google\Spreadsheet\SpreadsheetService();
$spreadsheetFeed = $spreadsheetService->getSpreadsheets();
$spreadsheet = $spreadsheetFeed->getByTitle($spreadsheetTitle);
if (!$spreadsheet || $spreadsheet === null) {
return null;
}
$worksheetFeed = $spreadsheet->getWorksheets();
return $worksheetFeed;
}
/**
* @param OAuth2ServiceRequest $oAuth2ServiceRequest
*/
public function __construct(OAuth2ServiceRequest $oAuth2ServiceRequest)
{
ServiceRequestFactory::setInstance($oAuth2ServiceRequest);
}
/**
* Update the cell value
*
* @param string $value
*
* @return null
*/
public function update($value)
{
$entry = sprintf('
<entry xmlns="http://www.w3.org/2005/Atom"
xmlns:gs="http://schemas.google.com/spreadsheets/2006">
<gs:cell row="%u" col="%u" inputValue="%s"/>
</entry>', $this->row, $this->column, $value);
$res = ServiceRequestFactory::getInstance()->post($this->postUrl, $entry);
$this->xml = new SimpleXMLElement($res);
}
/**
* Update the cell value
*
* @param string $value Can be a simple constant value or a formula
*
* @return null
*/
public function update($value)
{
$entry = new \SimpleXMLElement("\n <entry\n xmlns=\"http://www.w3.org/2005/Atom\"\n xmlns:gs=\"http://schemas.google.com/spreadsheets/2006\">\n </entry>\n ");
$child = $entry->addChild("xmlns:gs:cell");
$child->addAttribute("row", $this->row);
$child->addAttribute("col", $this->column);
$child->addAttribute("inputValue", $value);
$res = ServiceRequestFactory::getInstance()->post($this->postUrl, $entry->asXML());
$this->xml = new \SimpleXMLElement($res);
}
/**
* Add a new worksheet to this spreadsheet
*
* @param string $title
* @param int $rowCount default is 100
* @param int $colCount default is 10
*
* @return \Google\Spreadsheet\Worksheet
*/
public function addWorksheet($title, $rowCount = 100, $colCount = 10)
{
$entry = sprintf('
<entry xmlns="http://www.w3.org/2005/Atom" xmlns:gs="http://schemas.google.com/spreadsheets/2006">
<title>%s</title>
<gs:rowCount>%u</gs:rowCount>
<gs:colCount>%u</gs:colCount>
</entry>', $title, $rowCount, $colCount);
$response = ServiceRequestFactory::getInstance()->post($this->getWorksheetsFeedUrl(), $entry);
return new Worksheet(new SimpleXMLElement($response));
}