Please disable your adblock and script blockers to view this page

Search this blog

Wednesday 10 October 2018

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.




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

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

No comments :

Post a Comment