Using service key for SpreadsheetService authentication and getting Internal Server Error when trying to get the feed

Question

I'm working with SpreadsheetService and I'm trying to get the feed from the spreadsheets URL (https://spreadsheets.google.com/feeds/spreadsheets/private/full).

I'm doing the authentication using a service key. Seems like the authentication is working, but when I'm trying to get the feed I'm getting an error:

ServiceException (com.google.gdata.util.ServiceException: Internal Server Error).

This is the authentication method:

public static Credential authenticateWithServiceKey(GoogleApiProperties properties) throws IOException {
    GoogleCredential credential = GoogleCredential.fromStream(new FileInputStream("src/main/resources/prime-moment-274810-940d01c0244d.json"))
            .createScoped(Collections.singleton(properties.getScope()));

    return credential;
}

This is how I create the service and authenticate using the Credential object from the method above:

public void init(GoogleApiProperties properties) throws IOException, GeneralSecurityException {
    Credential credential = GoogleAuthentication.authenticateWithServiceKey(properties);
    service = new SpreadsheetService(properties.getApplicationName());
    service.setOAuth2Credentials(credential);
}

And this is where I'm trying to get the list of spreadsheets:

public List<String> getContentList() throws IOException, ServiceException {
    // Define the URL to request.  This should never change.
    URL SPREADSHEET_FEED_URL = new URL(SPREADSHEETS_URL);

    // Make a request to the API and get all spreadsheets.
    SpreadsheetFeed feed = service.getFeed(SPREADSHEET_FEED_URL, SpreadsheetFeed.class);

    // Return a list of spreadsheet names
    return feed.getEntries().stream()
            .map(entry -> entry.getTitle().getPlainText())
            .collect(Collectors.toList());
}

The exception is coming right after the service.getFeed call.

When I'm using the regular method for obtaining OAuth2.0 Credential, it works fine, but it require manual intervention (accepting the connection via Google page).

Kessy Kessy · Accepted Answer · 2020-06-24T10:44:07

The error is not with the credentials but with the execution. It seems that you have everything okay since it is a server error.

Although Sheets API v3 is being deprecated on Sep 2020 so I would recommend using Sheets API v4 method: get to get the feed of the spreadsheet.

If you go to the Sheets API v4 Migrate from previous API -> Retrieve sheet metadata you will find:

If you only want to read the sheet properties, set the includeGridData query parameter to false to prevent the inclusion of the spreadsheet cell data.

So following the Method: spreadsheets.get example can solve you issue.

This is a shorter version of the method I used:

public static void main(String... args) throws IOException, GeneralSecurityException {
        // Build a new authorized API client service.
        final NetHttpTransport HTTP_TRANSPORT = GoogleNetHttpTransport.newTrustedTransport();
        final String spreadsheetId = "YOUR SPREADSHEET ID";
        final String range = "Class Data!A2:E";
        Sheets service = new Sheets.Builder(HTTP_TRANSPORT, JSON_FACTORY, getCredentials(HTTP_TRANSPORT))
                .setApplicationName(APPLICATION_NAME)
                .build();
        Sheets.Spreadsheets.Get request = service.spreadsheets().get(spreadsheetId);
        request.setIncludeGridData(false);

        //Sheets.Spreadsheets response = request.execute();

        // TODO: Change code below to process the `response` object:
        System.out.println(request.execute());
    }

Edit

To list the spreadsheets you have to do it through the Drive API.

In this case through the method Files: List and on the query or q parameter, enter the spreadsheets MIME type: mimeType = 'application/vnd.google-apps.spreadsheet'

You can check how to use Files: list on the quickstart

Using service key for SpreadsheetService authentication and getting Internal Server Error when trying to get the feed

1 Answers