Google java table

Question

I tried to do Java programming to connect to Google Spreadsheet to perform data retrieval or cell data modification.

Link to my Google Spreadsheets https://docs.google.com/spreadsheets/d/1UXoGD2gowxZ2TY3gooI9y7rwWTPBOA0dnkeNYwUqQRA

I looked through the API Sheets and it needs a link, for example

https://spreadsheets.google.com/feeds/worksheets/key/private/full

I tried different forms of links, for example:

• https://spreadsheets.google.com/feeds/worksheets/1UXoGD2gowxZ2TY3gooI9y7rwWTPBOA0dnkeNYwUqQRA/private/full

• https://spreadsheets.google.com/feeds/worksheets/1UXoGD2gowxZ2TY3gooI9y7rwWTPBOA0dnkeNYwUqQRA/private/full

They gave me different types of errors respectively:

• com.google.gdata.util.ParseException: Unrecognized content type:application/binary
• com.google.gdata.util.RedirectRequiredException: Moved Temporarily

I don't know how to connect to a Googl spreadsheet using Java. Please help me if you have experience with this.

 import com.google.gdata.client.authn.oauth.*; import com.google.gdata.client.spreadsheet.*; import com.google.gdata.data.*; import com.google.gdata.data.batch.*; import com.google.gdata.data.spreadsheet.*; import com.google.gdata.util.*; import org.testng.annotations.Test; import java.io.IOException; import java.net.*; import java.util.*; public class TestGoogleSheetsAPI { @Test public void testConnectToSpreadSheet() throws ServiceException, IOException { SpreadsheetService service = new SpreadsheetService("google-spreadsheet"); URL SPREADSHEET_FEED_URL = new URL("https://spreadsheets.google.com/feeds/worksheets/1UXoGD2gowxZ2TY3gooI9y7rwWTPBOA0dnkeNYwUqQRA/public/full"); SpreadsheetFeed feed = service.getFeed(SPREADSHEET_FEED_URL, SpreadsheetFeed.class); List<SpreadsheetEntry> spreadsheets = feed.getEntries(); if (spreadsheets.size() == 0) { // TODO: There were no spreadsheets, act accordingly. } SpreadsheetEntry spreadsheet = spreadsheets.get(0); System.out.println(spreadsheet.getTitle().getPlainText()); } } 

I did not use service.setUserCredentials(" xxx@gmail ", "password") because I will have another error: com.google.gdata.util.AuthenticationException: Error authenticating (check service name)

Answer 1

You get redirected because accessing your spreadsheet requires that you authenticate first. Google Sheets uses the old gdata API, but requires authentication using OAuth 2.0. Therefore, you will need to import both the gdata libraries and the Google APIs, as shown below:

 <dependencies> <dependency> <groupId>com.google.gdata</groupId> <artifactId>core</artifactId> <version>1.47.1</version> </dependency> <dependency> <groupId>com.google.api-client</groupId> <artifactId>google-api-client-java6</artifactId> <version>1.20.0</version> </dependency> </dependencies> 

The code below shows how you can authenticate with Google using OAuth. You need to follow the instructions to create a service account and first download the P12 key. After creating the service account, copy the email address to CLIENT_ID below, add your P12 file to your classes path and chante P12FILE to point to your P12 file.

I managed to get this to work with the following SPREADSHEET_FEED_URL " https://spreadsheets.google.com/feeds/worksheets/:worksheetId/private/basic " where ": worksheetId" is your worksheet sheet. This is a little different than the one you used.

Be sure to ensure that your service account has permission to read or write to the spreadsheet by first sharing it with the email address of the service account.

 public class GoogleSheetsApiTest { // Generate a service account and P12 key: // https://developers.google.com/identity/protocols/OAuth2ServiceAccount private final String CLIENT_ID = "<your service account email address>"; // Add requested scopes. private final List<String> SCOPES = Arrays .asList("https://spreadsheets.google.com/feeds"); // The name of the p12 file you created when obtaining the service account private final String P12FILE = "/<your p12 file name>.p12"; @Test public void testConnectToSpreadSheet() throws GeneralSecurityException, IOException, ServiceException, URISyntaxException { SpreadsheetService service = new SpreadsheetService( "google-spreadsheet"); GoogleCredential credential = getCredentials(); service.setOAuth2Credentials(credential); URL SPREADSHEET_FEED_URL = new URL( "https://spreadsheets.google.com/feeds/worksheets/1UXoGD2gowxZ2TY3gooI9y7rwWTPBOA0dnkeNYwUqQRA/private/basic"); SpreadsheetFeed feed = service.getFeed(SPREADSHEET_FEED_URL, SpreadsheetFeed.class); List<SpreadsheetEntry> spreadsheets = feed.getEntries(); if (spreadsheets.size() == 0) { // // TODO: There were no spreadsheets, act accordingly. } // SpreadsheetEntry spreadsheet = spreadsheets.get(0); System.out.println(spreadsheet.getTitle().getPlainText()); } private GoogleCredential getCredentials() throws GeneralSecurityException, IOException, URISyntaxException { JacksonFactory JSON_FACTORY = JacksonFactory.getDefaultInstance(); HttpTransport httpTransport = GoogleNetHttpTransport .newTrustedTransport(); URL fileUrl = this.getClass().getResource(P12FILE); GoogleCredential credential = new GoogleCredential.Builder() .setTransport(httpTransport) .setJsonFactory(JSON_FACTORY) .setServiceAccountId(CLIENT_ID) .setServiceAccountPrivateKeyFromP12File( new File(fileUrl.toURI())) .setServiceAccountScopes(SCOPES).build(); return credential; } } 

Answer 2

I also got the error com.google.gdata.util.ParseException: Unrecognized content type:application/binary , but I seem to have stumbled upon a workaround for this strange error. You can use the Alex R answer code as a starting point.

• At first I tried changing the visibility to "public" to see what happens. Since the document is not published, I received an expected error response containing:

  We regret. This document is not published.

• So, I changed the visibility to "private" and again tried to just have fun ...

  Lo, and lo; it works!

It works either with the disk feed area or using spreadsheets (both can be included if you prefer):

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

I'm not sure if this is a factor, but I am not using OAuth 2.0, but rather Credential , created from PrivateKey (PKCS12 file), as in Brian Chapman's Answer .

Conclusion

So, although it is kludgy, I changed my application to always by making a request of "public" (wrapped in a try block to absorb the resulting Exception ) immediately before making a "private" request. This is the only way I was able to deterministically obtain the correct result.

Answer 3

The feed you get from this URL is WorksheetFeed , not SpreadsheetFeed . Try the following:

  SpreadsheetService service = new SpreadsheetService("google-spreadsheet"); FeedURLFactory urlFactory = FeedURLFactory.getDefault(); WorksheetFeed worksheetFeed = service.getFeed(urlFactory.getWorksheetFeedUrl("1V4jT4vSqmY4YNY1VJhariLRLbxfFWf5z8bSTpDcSBPE", "public", "full"), WorksheetFeed.class); List<WorksheetEntry> worksheets = worksheetFeed.getEntries(); WorksheetEntry worksheet = worksheets.get(0); System.out.println(worksheet.getTitle().getPlainText()); 

Answer 4

I feel this is a mistake. As a workaround, you can first call the getfeed request and then your channel requests will start working. Below is a modified code for a better understanding

 import com.google.gdata.client.authn.oauth.*; import com.google.gdata.client.spreadsheet.*; import com.google.gdata.data.*; import com.google.gdata.data.batch.*; import com.google.gdata.data.spreadsheet.*; import com.google.gdata.util.*; import org.testng.annotations.Test; import java.io.IOException; import java.net.*; import java.util.*; public class TestGoogleSheetsAPI { @Test public void testConnectToSpreadSheet() throws ServiceException, IOException { SpreadsheetService service = new SpreadsheetService("google-spreadsheet"); URL SPREADSHEET_FEED_URL = new URL("https://spreadsheets.google.com/feeds/worksheets/1UXoGD2gowxZ2TY3gooI9y7rwWTPBOA0dnkeNYwUqQRA/public/full"); //added new line ******************************* service.getFeed(new URL("https://spreadsheets.google.com/feeds/spreadsheets/private/full?xoauth_requestor_id=test"),WorksheetFeed.class); //************************ SpreadsheetFeed feed = service.getFeed(SPREADSHEET_FEED_URL, SpreadsheetFeed.class); List<SpreadsheetEntry> spreadsheets = feed.getEntries(); if (spreadsheets.size() == 0) { // TODO: There were no spreadsheets, act accordingly. } SpreadsheetEntry spreadsheet = spreadsheets.get(0); System.out.println(spreadsheet.getTitle().getPlainText()); } } 

There was also some debugging. It seems that if we try to call a sheet with a key, then the answer seems to have the content type "application / binary", which will be fixed in the gdata library, but if we try to get all the channels first, then the answer will have the correct content type and this type is used in the following queries. so the flow starts to work

Answer 5

1 reason to get com.google.gdata.util.RedirectRequiredException: moving temporarily is when we forget to publish a document / spreadsheet. Make sure you publish your document after it was created by choosing Menu> File> Publish to Web. This is required only once, and not after each editing.