/**
* @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;
}
/**
* @return SpreadsheetFeed
*/
private function spreadsheetFeed()
{
$spreadsheetService = new SpreadsheetService();
return $spreadsheetService->getSpreadsheets();
}
/**
* Add new tracker
*/
public function newTracker()
{
$client = new Google_Client();
$client->setAuthConfigFile(app_path() . '/../config/gapi_client_secrets.json');
$redirect_url = URL::to('/dashboard/new_tracker');
$client->setRedirectUri($redirect_url);
$client->setScopes(array('https://spreadsheets.google.com/feeds'));
$auth = 0;
if (isset($_GET['code'])) {
$data['sheets'] = array();
$client->authenticate($_GET['code']);
$accessToken = $client->getAccessToken();
//store access token in session
session(['access_token' => $accessToken['access_token']]);
$serviceRequest = new DefaultServiceRequest($accessToken['access_token']);
ServiceRequestFactory::setInstance($serviceRequest);
//retrieve list of spreadsheets
$spreadsheetService = new SpreadsheetService();
$spreadsheetFeed = $spreadsheetService->getSpreadsheets();
$i = 1;
foreach ($spreadsheetFeed as $sf) {
//get spreadsheet title
$spreadsheet = array_values((array) $sf)[0];
$sheet = array();
$sheet['title'] = $spreadsheet->title;
$sheet['id'] = $i;
$sheet['sheet_id'] = str_replace('https://spreadsheets.google.com/feeds/spreadsheets/private/full/', '', $spreadsheet->id);
$data['sheets'][] = $sheet;
$i++;
}
$auth = 1;
} else {
$data['auth_url'] = $client->createAuthUrl();
}
$data['auth'] = $auth;
return View::make('new_tracker')->with("data", $data);
}
// 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) {
$data = ['email' => 'test123' . rand() . '@test.com', 'acceptedterms' => 'TRUE'];
$listFeed->insert($data);
