How to access Google Spreadsheets with a service account credentials?

Question

I have created a server side application in PHP that's supposed to work with Google Spreadsheets.

I'm able to authenticate successfully with OAuth 2.0 authentication, but when requesting the list of the spreadsheets from Google, I only get the spreadsheets shared with the service account by the spreadsheet owner.

Is there a way that service account could retrieve all the spreadsheets owned by my main account not the service one, including those not explicitly shared with the service account?

Also I still want to keep the spreadsheets private so noone can access them without my permission, but I need the service account to have full access to both existing and new spreadsheets.

Any advice is appreciated.

Answer 1

Here is a sample script that uses a service account to read the contents of a Google Spreadsheet's sheet. Have a look at the README for instructions to set it up:

https://github.com/juampynr/google-spreadsheet-reader

Answer 2

You have to change approach:

1. Create a service account in Drive. You can manage and use this account only with the API (not with the web interface as usually)

2. With the Drive API you can list, create, update, delete and change the permissions of the files. When you create a new file, you can share it - always with API - to the users you want, make the new file public or change the ownership.

Please take a look: https://developers.google.com/drive/v2/reference/permissions

Answer 3

I am assuming you use Google Apps and not a personal GMail account. You can use your service account to impersonate your main account.

Note that here I am talking of a service account in the way Google means it : a private key (in p12 format) and an identifier. This is not a Google Apps account used for technical purposes. More information here.

This is done by :

1. Authorizing your service account on your Google Apps domain
2. Modify the code you use to generate the API credentials

The steps are exactly the same for the Spreadsheet API and for the Drive API.

To first authorize your service account to impersonate the domain users, you can follow this documentation. Here are the basic steps. You must be a domain super administrator to perform this task.

1. Go to your Google Apps domain’s Admin console : https://admin.google.com
2. Select Security from the list of controls. If you don't see Security listed, select More controls from the gray bar at the bottom of the page, then select Security from the list of controls.
3. Select Advanced settings from the list of options.
4. Select Manage third party OAuth Client access in the Authentication section.
5. In the Client name field enter the service account's Client ID. In the One or More API Scopes field enter the list of scopes that your application should be granted access to. In your case that would be at least the Spreadsheet API scope : https://spreadsheets.google.com/feeds

When this is done, you need to update your code so that it impersonates your main account :

$key = file_get_contents($SERVICE_ACCOUNT_PKCS12_FILE_PATH);
$auth = new Google_AssertionCredentials(
      'YOUR_SERVICE_ACCOUNT_EMAIL',
      array('https://spreadsheets.google.com/feeds'),
      $key);
$auth->sub = 'yourmainaccount@domain.com';

You can then use the $authvariable to generate the OAuth tokens you need to access the spreadsheet API. I am not sure which client you use for this so the way you inject the access token will depend on which client you use.

Also, note that this token will expire after 1 hour, and then the API will start returning Session Expired errors. If your client does not handle this automatically, you will need to catch the error and regenerate the token.

Answer 4

Here's how I did it:

Step 1: Create a service account

1. Head to console.cloud.google.com
2. Click IAM & Admin
3. On the left pane, click Service Accounts
4. Click Create service account
5. Proceed with creating a service account, at the end you'll get an email for that service account (e.g xyz-abcd@username.iam.gserviceaccount.com)
6. After creation is done, click on the email from the menu, then head to the KEYS tab, create a new key: ADD KEY --> Create new key (choose JSON).

Step 2: Using the service account

Now you will link the service-account with a spreadsheet on Google drive, open the spreadsheet, File -> Share -> Share with others and add the service account email from above. Make as an editor/viewer as desired.

After doing this, now you can use the service account using a client library (in my case I used Python client library along with but same concept will apply to the PHP library).

Here's a Python example that reads a range of cells:

from googleapiclient import discovery
from google.oauth2 import service_account
from pprint import pprint

credentials = service_account.Credentials.from_service_account_file('service-account.json')

service = discovery.build('sheets', 'v4', credentials=credentials)
spreadsheet_id = 'XYZ'  # TODO: Update placeholder value.
range_ = 'A1:B9'
request = service.spreadsheets().values().get(spreadsheetId=spreadsheet_id, range=range_)
response = request.execute()
pprint(response)