Service Account for google sheets returns not found

Question

I am trying to read a spreadsheet using a service account (I cannot use OAuth, which works, since the process will be running on a server to periodically check sheet data)

I tried several approaches. If I follow the example using oauth I can see the sheet values. However, I need the run the script without any GUI on the background.

I have found this tutorial https://github.com/juampynr/google-spreadsheet-reader I have created a projec, service account, added viewer role, shared the spreadsheet with the service account email. Generated the key. It seems that the test program can connect to the google services but the moment it request the spreadsheet the end result is "404 not found".

require 'vendor/autoload.php';

$service_account_file = '/secrets/readsheetmar2019-08b737d1c1cb._portfolio_test.json';


$spreadsheet_id = '1TAWybckPrnWlQxBZh0ScDsFOvftwi2dvTBNGarSdY30';

$spreadsheet_range = '';

putenv('GOOGLE_APPLICATION_CREDENTIALS=' . $service_account_file);
$client = new Google_Client();
$client->useApplicationDefaultCredentials();
$client->addScope(Google_Service_Sheets::SPREADSHEETS_READONLY);
$client->fetchAccessTokenWithAssertion();
$service = new Google_Service_Sheets($client);

//added by me
if ($client->isAccessTokenExpired()) {
  print "expired\n";
}else{
  print "not expired\n";
}

$result = $service->spreadsheets_values->get($spreadsheet_id, $spreadsheet_range);
var_dump($result->getValues());

Error:PHP Fatal error: Uncaught exception 'Google_Service_Exception' with message ' Error 404 (Not Found)!!1

Answer 1

• When the access token retrieved by OAuth2 is used, the Spreadsheet of $spreadsheet_id = '1TAWybckPrnWlQxBZh0ScDsFOvftwi2dvTBNGarSdY30'; can retrieve the values.
• When the access token retrieved by Service Account is used, Error 404 (Not Found)!!1 is returned.

If my understanding is correct, please confirm the following points.

Confirmation points:

1. As a test run, please set the range $spreadsheet_range = '';.
   • For example, it's $spreadsheet_range = 'Sheet1'.
2. If the error message of The caller does not have permission is returned, please confirm as follows.
   • Whether the Spreadsheet of 1TAWybckPrnWlQxBZh0ScDsFOvftwi2dvTBNGarSdY30 is sharing the email of Service Account.
   • If you didn't share the Service Account to the Spreadsheet, please share the email of client_email in the file of readsheetmar2019-08b737d1c1cb._portfolio_test.json to the Spreadsheet you want to access.
3. If the error message of Google Sheets API has not been used in project ### before or it is disabled. is returned, please enable Sheets API.

If this was not the solution for your issue, I apologize.