Write data to google spreadsheet using service account and oauth authentication

Hello Everyone, Previously I have posted about reading data from Google spreadsheets and got lots of comments regarding writing data to google sheet. So in this post, I am describing how to write data to a google spreadsheet.

Here I am using a Google service account and auth key for authentication purposes. Here I'll show you how to obtain a key from google console.

• Go to Google Cloud Platform and log in using your google account
• Create a new project on the console

• Enable google sheets API for this project

• Click on the project -- APIs & Services -- Credentials and click on Create credentials button

• Select key type p12 and put account name

• After this step, p12 key is downloaded to your system

Now see the java code that inserts a new record in the existing google sheet, Here I am using the same Google sheet that I have used in the previous post.

https://docs.google.com/spreadsheets/d/1G3VABou70MUbRzY4vHgiDME5JW5rfo7lrAr_hZyEawU is the URL of the spreadsheet and it looks like this

1. package spreadsheetdemo;
3. import com.google.api.client.googleapis.auth.oauth2.GoogleCredential;
4. import com.google.api.client.http.HttpTransport;
5. import com.google.api.client.http.javanet.NetHttpTransport;
6. import com.google.api.client.json.jackson.JacksonFactory;
7. import com.google.gdata.client.spreadsheet.SpreadsheetService;
8. import com.google.gdata.data.spreadsheet.ListEntry;
9. import com.google.gdata.data.spreadsheet.SpreadsheetEntry;
10. import com.google.gdata.data.spreadsheet.SpreadsheetFeed;
11. import com.google.gdata.data.spreadsheet.WorksheetEntry;
12. import com.google.gdata.data.spreadsheet.WorksheetFeed;
13. import com.google.gdata.util.AuthenticationException;
14. import com.google.gdata.util.ServiceException;
16. import java.io.File;
17. import java.io.IOException;
19. import java.net.MalformedURLException;
20. import java.net.URL;
22. import java.security.GeneralSecurityException;
24. import java.util.Arrays;
25. import java.util.List;
28. public class MySpreadsheetIntegration {
29. public static void main(String[] args) throws AuthenticationException, MalformedURLException, IOException,
30. ServiceException, GeneralSecurityException {
32. SpreadsheetService service = new SpreadsheetService("MySpreadsheetIntegration-v1");
34. // Put the path of p12 file that is downloaded from Google Console
36. File p12 = new File("D:/API Project-f35e9ad5ad07.p12");
38. HttpTransport httpTransport = new NetHttpTransport();
39. JacksonFactory jsonFactory = new JacksonFactory();
40. String[] SCOPESArray = {
41. "https://spreadsheets.google.com/feeds", "https://spreadsheets.google.com/feeds/spreadsheets/private/full",
42. "https://docs.google.com/feeds"
43. };
44. final List SCOPES = Arrays.asList(SCOPESArray);
46. //Put your google service account id
47. GoogleCredential credential =
48. new GoogleCredential.Builder().setTransport(httpTransport).setJsonFactory(jsonFactory).setServiceAccountId("xxxxxxx.iam.gserviceaccount.com").setServiceAccountScopes(SCOPES).setServiceAccountPrivateKeyFromP12File(p12).build();
49. service.setOAuth2Credentials(credential);
50. // Define the URL to request. This should never change.
51. URL SPREADSHEET_FEED_URL =
52. new URL("https://spreadsheets.google.com/feeds/worksheets/1G3VABou70MUbRzY4vHgiDME5JW5rfo7lrAr_hZyEawU/private/full");
54. // Make a request to the API and get all spreadsheets.
55. SpreadsheetFeed feed = service.getFeed(SPREADSHEET_FEED_URL, SpreadsheetFeed.class);
56. List<SpreadsheetEntry> spreadsheets = feed.getEntries();
57. System.out.println("Total Sheets- " + spreadsheets.size());
58. if (spreadsheets.size() == 0) {
60. }
62. SpreadsheetEntry spreadsheet = spreadsheets.get(0);
63. System.out.println(spreadsheet.getTitle().getPlainText());
65. // Get the first worksheet of the first spreadsheet.
67. WorksheetFeed worksheetFeed = service.getFeed(spreadsheet.getWorksheetFeedUrl(), WorksheetFeed.class);
68. List<WorksheetEntry> worksheets = worksheetFeed.getEntries();
69. WorksheetEntry worksheet = worksheets.get(0);
71. System.out.println(worksheet.getTitle().getPlainText());
74. // Create a local representation of the new row.
75. ListEntry row = new ListEntry();
77. row.getCustomElements().setValueLocal("Cell1", "Testing");
78. row.getCustomElements().setValueLocal("Cell2", "New Data");
80. // Send the new row to the API for insertion.
81. row = service.insert(spreadsheet.getWorksheetFeedUrl(), row);
83. }
84. }

After running this code see the new row is inserted in Google Sheet

You can download the required jars from this link

Cheers  Happy Learning