I’m trying to learn the Google Sheets API for PHP to read data from a sheet. I’m using https://github.com/googleapis/google-api-php-client/ to read a Google Sheet that is not published to the web. I’ve set up access permissions in the Google Sheets API in my Google Account. My basic connection works, using this:
// Connect the Google Sheets API client
require_once __DIR__ . '/vendor/autoload.php';
// Load service key
$googleAccountKeyFilePath = __DIR__ . '/service_key.json';
putenv('GOOGLE_APPLICATION_CREDENTIALS=' . $googleAccountKeyFilePath);
// Create new client
$client = new Google_Client();
// Set credentials
$client->useApplicationDefaultCredentials();
// Access for reading, editing, creating and deleting tables
$client->addScope('https://www.googleapis.com/auth/spreadsheets');
$service = new Google_Service_Sheets($client);
// Spreadsheet ID
$spreadsheetId = 'my_id';
$response = $service->spreadsheets->get($spreadsheetId);
// Get properties of spreadsheet
$spreadsheetProperties = $response->getProperties();
// spreadsheet name
var_dump($spreadsheetProperties->title);
and var_dump outputs
string(4) "Test"
But when I try
$range = 'Test';
$response = $service->spreadsheets_values->get($spreadsheetId, $range);
var_dump($response);
to dump the all cells in the sheet Test, I get the error
Uncaught Google\Service\Exception: { "error": { "code": 400, "message": "Unable to parse range: Test", "errors": [ { "message": "Unable to parse range: Test", "domain": "global", "reason": "badRequest" } ], "status": "INVALID_ARGUMENT" } }
What’s an example of how to dump the cell data?
>Solution :
The range must use A1 notation or R1C1 notation, per the documentation. So range should be
$range = 'A1:A6';
or
$range = 'A[1]:A[6]';
If you need to specify the tab name, that goes before the range. From the example, it would look like
$range = 'Class Data!A2:E';