/**
*
* @param string $format
*/
public function write(Table $table, OutputInterface $output)
{
$serviceRequest = new DefaultServiceRequest();
ServiceRequestFactory::setInstance($serviceRequest);
$spreadsheetService = new SpreadsheetService();
$spreadsheetFeed = $spreadsheetService->getSpreadsheetFeed();
}
/**
* @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 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;
}
/**
* @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;
}
/**
* Single worksheet
*/
public function singleWorksheet(Request $request, $spreadsheet_id, $id)
{
$access_token = $request->session()->get('access_token');
$serviceRequest = new DefaultServiceRequest($access_token);
ServiceRequestFactory::setInstance($serviceRequest);
//retrieve list of worksheets
$spreadsheetService = new SpreadsheetService();
$spreadsheet = $spreadsheetService->getSpreadsheetById($spreadsheet_id);
$data['spreadsheet_title'] = $spreadsheet->getTitle();
$data['spreadsheet_id'] = $spreadsheet_id;
$worksheets = $spreadsheet->getWorksheets();
$i = $id - 1;
$worksheet = $worksheets[$i];
$data['worksheet_title'] = $worksheet->getTitle();
//get content
$listFeed = $worksheet->getListFeed();
$entries = $listFeed->getEntries();
//get column names
$column_names = array();
foreach ($entries as $entry) {
$keys = array_keys($entry->getValues());
foreach ($keys as $key) {
if (!in_array($key, $column_names)) {
$column_names[] = $key;
}
}
}
//column names in 'create table' string format
$formatted_names = "";
foreach ($column_names as $column_name) {
$column_name = str_replace(".", "_", $column_name);
$formatted_names .= " `{$column_name}` TEXT NULL,";
}
//generate table name
$table_name = $this->generateRandomString();
$sql = "CREATE TABLE `{$table_name}` ( `id` INT NOT NULL AUTO_INCREMENT ,{$formatted_names} PRIMARY KEY (`id`))";
if (DB::statement($sql)) {
/*//insert values
foreach ($entries as $entry) {
DB::table($table_name)->insert($entry->getValues());
}*/
foreach ($entries as $entry) {
$insert_kv = array();
foreach ($entry->getValues() as $k => $v) {
$k = str_replace(".", "_", $k);
$insert_kv[$k] = $v;
}
DB::table($table_name)->insert($insert_kv);
}
}
$data['table'] = $table_name;
return View::make('add_tracker_details')->with("data", $data);
}
/**
* @return SpreadsheetFeed
*/
private function spreadsheetFeed()
{
$spreadsheetService = new SpreadsheetService();
return $spreadsheetService->getSpreadsheets();
}
throw new RuntimeException('First download your service account json from the google developer ' . 'console into service_account.json');
}
// get the access token through the client
$client = new Google_Client();
$credentials = $client->loadServiceAccountJson(__DIR__ . '/service_account.json', $scopes = ['https://spreadsheets.google.com/feeds']);
$client->setAssertionCredentials($credentials);
if ($client->getAuth()->isAccessTokenExpired()) {
$client->getAuth()->refreshTokenWithAssertion();
}
$response = json_decode($client->getAuth()->getAccessToken());
$accessToken = $response->access_token;
try {
// bootstrap
$serviceRequest = new DefaultServiceRequest($accessToken);
ServiceRequestFactory::setInstance($serviceRequest);
$spreadsheetService = new SpreadsheetService();
// get the spreadsheet
$spreadsheetFeed = $spreadsheetService->getSpreadsheets();
// all spreadsheets
// var_dump($spreadsheetFeed); exit;
$spreadsheet = $spreadsheetFeed->getByTitle('PostTest');
// get the worksheet
$worksheetFeed = $spreadsheet->getWorksheets();
$worksheet = $worksheetFeed->getByTitle('Sheet1');
// all rows
// var_dump($worksheet->getListFeed()); exit;
// get the rows that match a columnName / value
/** @var Google\Spreadsheet\ListFeed */
$listFeed = $worksheet->getListFeed(['sq' => 'email = "*****@*****.**"']);
// insert if not exists
if (count($listFeed->getEntries()) === 0) {
