Skip to content

Instantly share code, notes, and snippets.

What would you like to do?
google spreadsheet tests
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->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);
// 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()) {
} 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);
// 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:
//$spreadsheetId = '1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms';
//$range = 'Class Data!A2:E';
foreach($service->spreadsheets->get($spreadsheetId)->getSheets() as $tmp){
foreach($page_names as $page_name){
$response = $service->spreadsheets_values->get($spreadsheetId,$range);
$values_raw = $response->getValues();
unset($values_raw[0][0]);// A1, empty
foreach($values_raw[0] as $product_name){
foreach($values_raw as $right_row){
foreach($right_row as $column){
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
You can’t perform that action at this time.