0

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

Dr Phil
  • 833
  • 6
  • 18
  • Have you checked if your file exist same with the id? – Jessica Rodriguez Mar 19 '19 at 14:52
  • yes. If i use the oauth example i can access the same file. And i can see the file using the link. The problem is that when I use the service account it returns "not found". Probably that is some permissions problem but I have no idea how to troubleshoot it. – Dr Phil Mar 20 '19 at 12:28
  • The solution turned out was to make sure $spreadsheet_range = ''; is set with a valid tab name – Dr Phil Mar 22 '19 at 11:07

1 Answers1

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.

Tanaike
  • 181,128
  • 11
  • 97
  • 165
  • Thanks a lot. The missing piece was that it required a sheet/tab name. Wen i did step one of your points it suddenly worked like a charm. I think i tried that before but probably dind't do it correctly in the first place. – Dr Phil Mar 22 '19 at 11:06
  • @Dr Phil Thank you for replying. I'm glad your issue was resolved. – Tanaike Mar 23 '19 at 01:20