How do I create a Google Spreadsheet with a service account and share to other google users in java?

Question

I have an application where I, with a Google Service Account, gather lots of information about my site from the Analytics API. My next step is to create a spreadsheet with the service account and share the document with a couple of users.

I have checked out the documentation at https://developers.google.com/google-apps/spreadsheets/ but I can't find anything there about service accounts and sharing the documents.

So my first question is this possible? If not do I need to implement the "use my personal account" as exemplified in the documentation? If yes could you please provide me with an example?

Thank you!

any final solution did you tried ? – PreguntonCojoneroCabrón Aug 11 '15 at 17:37 — PreguntonCojoneroCabrón, Aug 11 '15 at 17:37

score 17 · Accepted Answer · edited May 23 '17 at 12:18

17

It is possible, see the example below (the example does need a bit of tweaking):

Create the drive service:

   GoogleCredential credential = new GoogleCredential.Builder().setTransport(HTTP_TRANSPORT).setJsonFactory(JSON_FACTORY)
            .setServiceAccountId(confBean.getServiceAccountId()).setServiceAccountScopes("https://www.googleapis.com/auth/drive")
            .setServiceAccountPrivateKeyFromP12File(new File("path to the P12File"))
            .setServiceAccountUser("user@domain.com")
            .build();

    Drive drive = new Drive.Builder(HTTP_TRANSPORT, JSON_FACTORY, credential).build();

Create the spreadsheet:

  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");
  Insert insert = this.drive.files().insert(file);
  file = insert.execute();

Create a spreadsheet service:

GoogleCredential credential = new GoogleCredential.Builder().setTransport(HTTP_TRANSPORT).setJsonFactory(JSON_FACTORY)
        .setServiceAccountId(confBean.getServiceAccountId()).setServiceAccountScopes("https://spreadsheets.google.com/feeds")
        .setServiceAccountPrivateKeyFromP12File(new File("path to the P12File"))
        .setServiceAccountUser("user@domain.com")
        .build();
SpreadsheetService service = new SpreadsheetService("MySpreadsheetIntegration-v1");
service.setOAuth2Credentials(credential);

Retrieve the sheet:

SpreadsheetService s = googleConn.getSpreadSheetService();
String spreadsheetURL = "https://spreadsheets.google.com/feeds/spreadsheets/" + file.getId();
SpreadsheetEntry spreadsheet = s.getEntry(new URL(spreadsheetURL), SpreadsheetEntry.class);

Add the data:

WorksheetFeed worksheetFeed = s.getFeed(spreadsheet.getWorksheetFeedUrl(), WorksheetFeed.class);
List<WorksheetEntry> worksheets = worksheetFeed.getEntries();
WorksheetEntry worksheet = worksheets.get(0);

URL cellFeedUrl= worksheet.getCellFeedUrl ();
CellFeed cellFeed= s.getFeed (cellFeedUrl, CellFeed.class);

CellEntry cellEntry= new CellEntry (1, 1, "aa");
cellFeed.insert (cellEntry);

Also, see this related question

edited May 23 '17 at 12:18

Community

1
1

answered Nov 05 '12 at 22:38

Jasper Duizendstra

2,587
1
21
32

Thank you for your answer, but I have encountered some issues with the impl. When doing setServiceAccountUser("user@domain.com") I get a TokenResponseException: 400 Bad Request "error" : "access_denied" back from the API. If I remove the setServiceAccountUser part i get a 500 Internal server error. I have enabled Google Drive API and Google Drive SDK in the Google services console. What am I missing here? There is no problem with the analytics as I describe here: http://stackoverflow.com/questions/9084840/google-analytics-authorization-in-java/12892390#12892390 – jakob Nov 06 '12 at 09:00
Make sure you enable both the drive and docs api in the api console, and authorize the application for: https://docs.google.com/feeds https://spreadsheets.google.com/feeds https://www.googleapis.com/auth/drive. And I assume you changed the user@domain.com to a real user in the domain? – Jasper Duizendstra Nov 06 '12 at 10:27
Hmm It seems to me that I I'm missing some crucial part here. – jakob Nov 06 '12 at 11:01
Now I'm getting com.google.api.client.auth.oauth2.TokenResponseException: 400 Bad Request { "error" : "invalid_grant"}. I'm using the the same client id as I did for analytics:2363681XX719.apps.googleusercontent.com and the user@domain.com I have tried with 2363681XX719@developer.gserviceaccount.com and an email that is registered at my domain. Maybe that is the problem how do I check if the user is correct at my domain? Do I need anything more? Maybe I have forgot something? – jakob Nov 06 '12 at 11:07
There are two sections in the code, creating the spreadsheet and manipulating it. Where do you get the error? – Jasper Duizendstra Nov 06 '12 at 11:17
The creating part on: The file = insert.execute(); – jakob Nov 06 '12 at 11:26
Make sure both the drive and the docs api are enabled in the API Console. And https://www.googleapis.com/auth/drive is granted access in the "Manage API client access" – Jasper Duizendstra Nov 06 '12 at 11:33
Hmmm I can't find docs in my console, isn't docs included in the drive api? In my Manage API client access have have tried with 2363681XX719.apps.googleusercontent.com for scopes https://www.docs.google.com/feeds https://www.spreadsheets.google.com/feeds https://www.googleapis.com/auth/drive – jakob Nov 06 '12 at 11:47
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/19152/discussion-between-jasper-duizendstra-and-jakob) – Jasper Duizendstra Nov 06 '12 at 11:49

How do I create a Google Spreadsheet with a service account and share to other google users in java?

1 Answers1

Linked