Java to Google Spreadsheet

Question

I was trying to do programming using Java to connect to Google Spreadsheet to do data retrieval or modifying data in the cells.

My Google spreadsheet link is https://docs.google.com/spreadsheets/d/1UXoGD2gowxZ2TY3gooI9y7rwWTPBOA0dnkeNYwUqQRA

I looked at the Sheets API and it requires link like

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

I have tried different forms of the links, such as:

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

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

They gave me different kinds of errors respectively:

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

I have no idea how to connect to the Googl Spreadsheet using Java. Please help me if you have experience on 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 didn't use service.setUserCredentials("xxx@gmail", "password") because I will have another error, which is com.google.gdata.util.AuthenticationException: Error authenticating (check service name)

Answer 1

You are getting the redirect because accessing your spreadsheet requires that you authenticate first. Google Sheets is using the old gdata API, but requires that you authenticate using OAuth 2.0. Therefore you will need to import both the gdata and Google API libraries 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 will need to follow the instructions for creating a service account and downloading the P12 key first. After creating your service account, copy the email address into the CLIENT_ID field below, add your P12 file to your classpath and chante P12FILE to point to your P12 file.

I was able to get this working with the following SPREADSHEET_FEED_URL "https://spreadsheets.google.com/feeds/worksheets/:worksheetId/private/basic" where ":worksheetId" is your worksheet Id. This is slightly different than the one you were using.

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

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 was also getting the com.google.gdata.util.ParseException: Unrecognized content type:application/binary error, but I seem to have stumbled on a workaround for this strange bug. The code in Alex R's answer can be used as a starting point.

1. First I tried changing the visibility to "public", just to see what would happen. Since the document is not published, I got the expected error response, containing:

   We're sorry. This document is not published.

2. So I changed the visibility back to "private", and tried again just for fun...

   Lo, and behold; it worked!

It works with either the Drive or Spreadsheets Feed scope (both can be included if you prefer):

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

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

Conclusion

So, although it's kludgy, I've modified my application to always make a "public" request (wrapped in a try block to absorb the resulting Exception) just before making the "private" request. It's the only way I've been able to deterministically get the correct result.