Created
August 23, 2019 12:36
-
-
Save divinity76/10ca8f878c720a6ea79eb8f3ee4c50fb to your computer and use it in GitHub Desktop.
google spreadsheet tests
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<?php | |
declare(strict_types=1); | |
require_once('hhb_.inc.php'); | |
require __DIR__ . '/vendor/autoload.php'; | |
if (php_sapi_name() != 'cli') { | |
throw new Exception('This application must be run on the command line.'); | |
} | |
/** | |
* Returns an authorized API client. | |
* @return Google_Client the authorized client object | |
*/ | |
function getClient() | |
{ | |
$client = new Google_Client(); | |
$client->setApplicationName('Google Sheets API PHP Quickstart'); | |
$client->setScopes(Google_Service_Sheets::SPREADSHEETS_READONLY); | |
$client->setAuthConfig('credentials.json'); | |
$client->setAccessType('offline'); | |
$client->setPrompt('select_account consent'); | |
// Load previously authorized token from a file, if it exists. | |
// The file token.json stores the user's access and refresh tokens, and is | |
// created automatically when the authorization flow completes for the first | |
// time. | |
$tokenPath = 'token.json'; | |
if (file_exists($tokenPath)) { | |
$accessToken = json_decode(file_get_contents($tokenPath), true); | |
$client->setAccessToken($accessToken); | |
} | |
// If there is no previous token or it's expired. | |
if ($client->isAccessTokenExpired()) { | |
// Refresh the token if possible, else fetch a new one. | |
if ($client->getRefreshToken()) { | |
$client->fetchAccessTokenWithRefreshToken($client->getRefreshToken()); | |
} else { | |
// Request authorization from the user. | |
$authUrl = $client->createAuthUrl(); | |
printf("Open the following link in your browser:\n%s\n", $authUrl); | |
print 'Enter verification code: '; | |
$authCode = trim(fgets(STDIN)); | |
// Exchange authorization code for an access token. | |
$accessToken = $client->fetchAccessTokenWithAuthCode($authCode); | |
$client->setAccessToken($accessToken); | |
// Check to see if there was an error. | |
if (array_key_exists('error', $accessToken)) { | |
throw new Exception(join(', ', $accessToken)); | |
} | |
} | |
// Save the token to a file. | |
if (!file_exists(dirname($tokenPath))) { | |
mkdir(dirname($tokenPath), 0700, true); | |
} | |
file_put_contents($tokenPath, json_encode($client->getAccessToken())); | |
} | |
return $client; | |
} | |
// Get the API client and construct the service object. | |
$client = getClient(); | |
$service = new Google_Service_Sheets($client); | |
// Prints the names and majors of students in a sample spreadsheet: | |
// https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit | |
//$spreadsheetId = '1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms'; | |
$spreadsheetId='1dqgG9SgPcvqNMkP2vTh-UPxVHnS8IcvPg1mPnrBgLUU'; | |
$parsed=array(); | |
//$range = 'Class Data!A2:E'; | |
$page_names=array(); | |
foreach($service->spreadsheets->get($spreadsheetId)->getSheets() as $tmp){ | |
$page_names[]=$tmp['properties']['title']; | |
} | |
foreach($page_names as $page_name){ | |
//die("GOFUCKURSELF"); | |
$range=$page_name.'!A1:ZZ'; | |
$response = $service->spreadsheets_values->get($spreadsheetId,$range); | |
$values_raw = $response->getValues(); | |
$parsed[$page_name]=array(); | |
unset($values_raw[0][0]);// A1, empty | |
foreach($values_raw[0] as $product_name){ | |
$parsed[$page_name][$product_name]=array(); | |
} | |
unset($values_raw[0]); | |
$page_name_keys=array_keys($parsed[$page_name]); | |
foreach($values_raw as $right_row){ | |
$feature_name=$right_row[0]; | |
unset($right_row[0]); | |
$i=0; | |
foreach($right_row as $column){ | |
$parsed[$page_name][$page_name_keys[$i]][$feature_name]=$column; | |
++$i; | |
} | |
} | |
} | |
hhb_var_dump($values_raw,$parsed); | |
if(0){ | |
if (empty($values)) { | |
print "No data found.\n"; | |
} else { | |
print "Name, Major:\n"; | |
foreach ($values as $row) { | |
// Print columns A and E, which correspond to indices 0 and 4. | |
printf("%s, %s\n", $row[0], $row[4]); | |
} | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment