How do I access a Google spreadsheet from App Engine using a service account?

Question

I have an application in the Google App Engine (hosted on appspot.com) that several users will use to grab some things from the Internet, store it in a data store, and then write it to a Google spreadsheet. I want to use a service account to access a spreadsheet, but I ran into 400 OK {"error": "invalid_grant"} when I try to do something, regardless of whether I try to create a file using the API drive interface or access to an existing file using table APIs.

I turned on the Drive API and Drive SDK in the API console and generated a P12 key. This is my method of building GoogleCredential :

 private GoogleCredential getGoogleCredential() throws IOException, GeneralSecurityException { GoogleCredential credential = new GoogleCredential.Builder() .setTransport(TRANSPORT) .setJsonFactory(FACTORY) .setServiceAccountId("1511XXX90247.apps.googleusercontent.com") .setServiceAccountScopes(scopes) .setServiceAccountPrivateKeyFromP12File( new File("05a605b0fXXXd2a6be864be15d81a2bd629d3bd6-privatekey.p12")) .setServiceAccountUser(" [e-mail] ") // My personal e-mail address which I used to create the project .build(); return credential; } 

ServiceAccountID comes from the API console. I tried using [e-mail] as my ServiceAccountUser as well as [e-mail] . The areas I use are as follows:

 https://spreadsheets.google.com/feeds https://docs.google.com/feeds https://www.googleapis.com/auth/drive https://www.googleapis.com/auth/drive.file 

Here is the code that fails when I try to use the spreadsheet API:

  SpreadsheetService service = new SpreadsheetService("name of my app"); service.setOAuth2Credentials(getGoogleCredential()); FeedURLFactory factory = FeedURLFactory.getDefault(); SpreadsheetQuery query = new SpreadsheetQuery(factory.getSpreadsheetsFeedUrl()); query.setTitleQuery("test"); SpreadsheetFeed feed = service.query(query, SpreadsheetFeed.class); 

The code does not work when the service tries to fulfill the request.

Here the code of the disk I tried also does not work:

  Drive drive = new Drive.Builder(TRANSPORT, FACTORY, getGoogleCredential()).build(); com.google.api.services.drive.model.File file = new com.google.api.services.drive.model.File(); file.setTitle("test"); file.setMimeType("application/vnd.google-apps.spreadsheet"); drive.files().insert(file).execute(); // This is where the code fails 

No matter what I try, I always seem to get the same "failure" error. Here's the partial stacktrace:

 com.google.gdata.util.AuthenticationException: Failed to refresh access token: 400 OK { "error" : "invalid_grant" } at com.google.gdata.client.GoogleAuthTokenFactory$OAuth2Token.refreshToken(GoogleAuthTokenFactory.java:260) at com.google.gdata.client.GoogleAuthTokenFactory.handleSessionExpiredException(GoogleAuthTokenFactory.java:702) at com.google.gdata.client.GoogleService.handleSessionExpiredException(GoogleService.java:738) at com.google.gdata.client.GoogleService.getFeed(GoogleService.java:680) at com.google.gdata.client.Service.query(Service.java:1237) at com.google.gdata.client.Service.query(Service.java:1178) at spam.gwt.scraper.server.spreadsheets.APIConnector.doGet(APIConnector.java:66) 

I tried to search for answers everywhere, including, but not limited to, the Google Spreadsheet Design Guide , this is the Account Stack Overflow Question and this stack overflow question in the App Engine application . That with developments (for example, Docs → Drive) it is difficult to find up-to-date information without paying attention to information specific to GAE.

Answer 1

I use the built-in App Engine application service account and not create my own; Thus, the private key is saved (not stored locally, only in the application).

For disk:

  protected Drive createDriveService() throws BookingSheetException { HttpTransport httpTransport = new NetHttpTransport(); JsonFactory jsonFactory = new JacksonFactory(); GoogleClientRequestInitializer keyInitializer = new CommonGoogleClientRequestInitializer(API_KEY); AppIdentityCredential credential = new AppIdentityCredential.Builder(Arrays.asList(DriveScopes.DRIVE)).build(); Drive service = new Drive.Builder(httpTransport, jsonFactory, null) .setHttpRequestInitializer(credential) .setGoogleClientRequestInitializer(keyInitializer) .setApplicationName(appname).build(); return service; } 

For the table:

 private Credential creds; /** * Keep the expiration time of the access token to renew it before expiry */ private Calendar expirationTime = Calendar.getInstance(); protected void initCredentials() { List<String> scopes = Arrays.asList("https://spreadsheets.google.com/feeds"); AppIdentityService appIdentity = AppIdentityServiceFactory.getAppIdentityService(); AppIdentityService.GetAccessTokenResult accessToken = appIdentity.getAccessToken(scopes); expirationTime.setTime(accessToken.getExpirationTime()); expirationTime.add(Calendar.MINUTE,-REFRESH_MINUTES); //refresh some time before expiry creds = new Credential(BearerToken.authorizationHeaderAccessMethod()); creds.setAccessToken(accessToken.getAccessToken()); } public SpreadsheetUtil(String appname) { myService = new SpreadsheetService(appname); myService.setOAuth2Credentials(cred); } 

Be sure to creds.refreshToken() or reinitialize when they expire.