/**
* 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;
}
/**
* @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();
}
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;
}
<?php
include_once "dbconnect.php";
$table_exist = $conn->query("SELECT 1 FROM topVC");
if ($table_exist == FALSE) {
$create = "CREATE TABLE topVC (\n\t\t\t \tid INT(30) UNSIGNED AUTO_INCREMENT PRIMARY KEY, \n name_of_vc VARCHAR(30) NOT NULL,\n number_of_investment VARCHAR(30) NOT NULL,\n amount_invested VARCHAR(30) NOT NULL\n\t\t\t)";
$conn->query($create);
} else {
$conn->query("TRUNCATE TABLE topVC");
}
include_once "google_spreadsheet_access.php";
/**
* Get spreadsheet by title
*/
$spreadsheetTitle = 'startupinfo';
$spreadsheetService = new Google\Spreadsheet\SpreadsheetService();
$spreadsheetFeed = $spreadsheetService->getSpreadsheets();
$spreadsheet = $spreadsheetFeed->getByTitle($spreadsheetTitle);
$worksheetTitle = 'TopVc';
// it's generally named 'Sheet1'
$worksheetFeed = $spreadsheet->getWorksheets();
$worksheet = $worksheetFeed->getByTitle($worksheetTitle);
/**
* Get row lists of worksheet
*/
$listFeed = $worksheet->getListFeed();
$sql = "";
foreach ($listFeed->getEntries() as $entries) {
$values = "( NULL, ";
foreach ($entries->getValues() as $key => $value) {
if ($key != "amountinvested") {
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)
}
public static function addHeaderToWorkSheet()
{
$spreadsheetService = new Google\Spreadsheet\SpreadsheetService();
$spreadsheetFeed = $spreadsheetService->getSpreadsheets();
$spreadsheet = $spreadsheetFeed->getByTitle('test');
$worksheetFeed = $spreadsheet->getWorksheets();
$worksheet = $worksheetFeed->getByTitle('Sheet 2');
$cellFeed = $worksheet->getCellFeed();
$cellFeed->editCell(1, 1, "Row1Col1Header");
$cellFeed->editCell(1, 2, "Row1Col2Header");
$cellFeed->editCell(1, 3, "Row1Col3Header");
$cellFeed->editCell(1, 4, "Row1Col4Header");
}
function submit_reg_form()
{
//ReCaptcha
$recaptcha = new ReCaptcha(RECAPTCHA_SECRET);
$resp = $recaptcha->verify($_POST['g-recaptcha-response'], $_SERVER['REMOTE_ADDR']);
if (!$resp->isSuccess()) {
throw new Exception('Invalid captcha!');
}
//Get & Process form data
$valid_inputs = 'name|email|tel|inst|std|day1|day2';
foreach (get_presentations() as $key => $data) {
$valid_inputs .= "|{$key}";
}
$form_data = getInputsWithKey($valid_inputs);
//----Pricing
//Count days
$days = 0;
for ($i = 1; $i <= 2; $i++) {
if ($form_data["day{$i}"] != 'no') {
$days++;
}
}
$price = $days * 35;
// if ($days > 1)
// $price -= 10;
//Discount
$discount_rate = 0;
if (isset($form_data['std'])) {
switch ($form_data['std']) {
case 'aut':
$discount_rate = 0.6;
break;
case 'std':
$discount_rate = 0.25;
break;
}
}
$price *= 1.0 - $discount_rate;
$price = round($price);
$form_data['price'] = $price;
//Tel
$form_data['tel'] = intval($form_data['tel']);
//Generate tracking code
$hashids = new Hashids\Hashids(HASHID_SALT);
$tracking_code = $hashids->encode(time() . rand(0, 100));
$form_data['code'] = $tracking_code;
//Check Google Client Expired
$token = json_decode(gapi_token, true);
$client = new Google_Client();
$client->setClientId(GAPI_CLIENT_ID);
$client->setClientSecret(GAPI_CLIENT_SECRET);
$client->setAccessToken(gapi_token);
if ($client->isAccessTokenExpired()) {
//Refresh on expire
$client->refreshToken($client->getRefreshToken());
foreach (json_decode($client->getAccessToken(), true) as $k => $v) {
$token[$k] = $v;
}
file_put_contents(GAPI_TOKEN_LOCATION, "<?php define('gapi_token','" . json_encode($token) . "');");
}
//Get Registration sheet
$serviceRequest = new DefaultServiceRequest($token['access_token']);
ServiceRequestFactory::setInstance($serviceRequest);
$spreadsheetService = new Google\Spreadsheet\SpreadsheetService();
$sheet = $spreadsheetService->getSpreadsheets()->getByTitle('linuxfest_2015')->getWorksheets()->getByTitle('List');
//Insert Submitted data
$sheet->getListFeed()->insert($form_data);
//Message
$msg = "\n<div dir='rtl'>\n\n با تشکر، ثبت نام آنلاین شما با موفقیت انجام شد و مورد بررسی قرار خواهد گرفت.\n <br>\n هزینه ی ثبت نام شما : <b>{$price}</b> هزار تومان\n <br>\n کد رهگیری شما : <b>{$tracking_code}</b>\n (این کد را برای مراحل بعدی حتما نگهدارید)<br>\n <br>\n لطفا در اسرع وقت با مراجعهی حضوری به دفتر انجمن علمی دانشکده هزینهی دورههایی را که در آنها ثبت نام کردهاید پرداخت نمایید. در صورتی که مراجعهی حضوری برایتان مقدور نیست هزینهی ثبت نام را به کارت 5022291100266525 (بانک پاسارگاد به نام آرمین باشیزاده) واریز نمایید و کد رهگیری و شماره کارت خود را به آدرس hello@linuxfest.ir ارسال کنید. به دلیل محدود بودن ظرفیت کارگاهها، اولویت با کسانی است که زودتر ثبت نام خود را نهایی کنند.\n<br>\nتهران، چهارراه ولی عصر، روبهروی خیابان بزرگمهر، دانشگاه صنعتی امیرکبیر ، دانشکده مهندسی کامپیوتر و فناوری اطلاعات ، دفتر انجمن علمی\n\t<br>\n در صورت تمایل به ایجاد هرگونه تغییر در وضعیت ثبت نام خود از ثبت نام مجدد <b> جدا خودداری فرمایید</b>. در غیر این صورت ثبت نام شما تایید نخواهد شد.<br>\n میتوانید این تغییرات را از دو طریق ایمیل جشنواره hello@linuxfest.ir یا مراجعهی حضوری اعلام فرمایید.\n\n</div>\n ";
$msg_2 = "جشنواره لینوکس امیرکبیر\n هزینه ی ثبت نام : {$price} هزار تومان\n کد رهگیری شما : {$tracking_code}\n جهت ثبت نام نهایی در اسرع وقت به دفتر انجمن علمی دانشکده مراجعه فرمایید\n ";
//Email
if (isset($form_data['email'])) {
sendmail($form_data['email'], $msg);
}
//SMS
if (isset($form_data['tel'])) {
sendsms($form_data['tel'], $msg_2);
}
return $msg;
}
private function google_connect($key, $spreadsheet, $worksheet)
{
//Get access token from DB using $key
$accessToken = $this->get_key($key);
$serviceRequest = new DefaultServiceRequest($accessToken);
ServiceRequestFactory::setInstance($serviceRequest);
$spreadsheetService = new Google\Spreadsheet\SpreadsheetService();
$spreadsheetFeed = $spreadsheetService->getSpreadsheets();
$spreadsheet = $spreadsheetFeed->getByTitle(urldecode($spreadsheet));
$worksheetFeed = $spreadsheet->getWorksheets();
$worksheet = $worksheetFeed->getByTitle(urldecode($worksheet));
return $worksheet;
}
<?php
use Google\Spreadsheet\DefaultServiceRequest;
use Google\Spreadsheet\ServiceRequestFactory;
require_once './vendor/autoload.php';
mb_language("ja");
mb_internal_encoding("UTF-8");
// -----------
// GoogleClientの作成
// -----------
$G_CLIENT_ID = 'XXXXXXXXXXXXXXXXXXXX';
$G_CLIENT_EMAIL = 'XXXXXXXXXXXXXXXXXXXX';
$G_CLIENT_KEY_PATH = 'XXXXXXXXXXXXXXXXXXXX';
$G_CLIENT_KEY_PW = 'XXXXXXXXXXXXXXXXXXXX';
$obj_client_auth = new Google_Client();
$obj_client_auth->setApplicationName('XXXXXXXXXXXXXXXXXXXX');
$obj_client_auth->setClientId($G_CLIENT_ID);
$obj_client_auth->setAssertionCredentials(new Google_Auth_AssertionCredentials($G_CLIENT_EMAIL, array('https://spreadsheets.google.com/feeds', 'https://docs.google.com/feeds'), file_get_contents($G_CLIENT_KEY_PATH), $G_CLIENT_KEY_PW));
$obj_client_auth->getAuth()->refreshTokenWithAssertion();
$obj_token = json_decode($obj_client_auth->getAccessToken());
$accessToken = $obj_token->access_token;
// -----------
// スプレットシートの取得
// -----------
$serviceRequest = new Google\Spreadsheet\DefaultServiceRequest($accessToken);
Google\Spreadsheet\ServiceRequestFactory::setInstance($serviceRequest);
$spreadsheetService = new Google\Spreadsheet\SpreadsheetService();
$spreadsheetFeed = $spreadsheetService->getSpreadsheets();
//タスクを管理しているワークシート取得
$spreadsheet = $spreadsheetService->getSpreadsheetById('XXXXXXXXXXXXXXXXXXXX');
$worksheetFeed = $spreadsheet->getWorksheets();
function googleDocs_connect_to_spreadsheet($spreadsheetName, $worksheetName)
{
global $googleDocs;
if (!$googleDocs) {
return;
}
global $accessToken;
global $listFeed;
debug("Trying accessToken '{$accessToken}'...");
$request = new Google\Spreadsheet\Request($accessToken);
$serviceRequest = new Google\Spreadsheet\DefaultServiceRequest($request);
Google\Spreadsheet\ServiceRequestFactory::setInstance($serviceRequest);
debug('Success!', 'success');
// Find and connect to spreadsheet
$spreadsheetService = new Google\Spreadsheet\SpreadsheetService();
$spreadsheetFeed = $spreadsheetService->getSpreadsheets();
$spreadsheet = $spreadsheetFeed->getByTitle($spreadsheetName);
// Get all worksheets
$worksheetFeed = $spreadsheet->getWorksheets();
// Get the worksheet
$worksheet = $worksheetFeed->getByTitle($worksheetName);
// Get all the rows
$listFeed = $worksheet->getListFeed();
/* Sample code */
/*
// Get all the rows
$listFeed = $worksheet->getListFeed();
// Loop over the rows
foreach ($listFeed->getEntries() as $entry) {
$values = $entry->getValues();
echo '<p>';
print_r($values);
echo '</p>';
}
*/
// Insert row
/*
$row = array(
'portalname' => 'Test',
'recharge' => 'Test2',
'ownsince' => '1/1/2014',
'daysowned' => '',
'zone' => 'Test3',
'kmfromhome' => '999',
'favorite' => '',
'risk' => '10',
'access' => '0',
'record' => '0',
'strategy' => 'none',
'days' => 'some formula',
'days_2'=> 'a formula',
'dididiscoverit' => '',
'enemies' => '',
'allies' => '',
'address' => '',
'link' => '',
'latitude' => '',
'longitude' => '',
'computationstuff' => 'formula',
'computationstuff_2' => 'formula',
'computationstuff_3' => 'formula'
);
$listFeed->insert($row);
*/
// Display all the rows in the spreadsheet
if (isset($_REQUEST['test']) && $_REQUEST['test'] == 'read') {
// Loop over the rows
foreach ($listFeed->getEntries() as $entry) {
$values = $entry->getValues();
echo '<p>';
print_r($values);
echo '</p>';
}
}
}
