We are testing some basic functionalities of the java Sheet API. We are able to authenticate, list some spreadsheets and retrieve their titles. However, when trying to access worksheets of a specific spreadsheet, we get a ResourceNotFound exception. The corresponding HTTP request returns an error code 404 (not found).
When using the URL of this HTTP request in a browser, it works fine and we are able to get the corresponding worksheets feed. The class below reproduces the problem. The method listSheets works fine. The method listWorkSheets throws a ResourceNotFoundException (see below) We have used a network traffic analyzer to get the exact HTTP request which causes the ResourceNotFoundException. The URL looks like this: https://spreadsheets.google.com/feeds/worksheets/<KEY>/private/full When we copy-past this URL into a browser, we have properly access to the corresponding feed (the user in the browser is authenticated). Please note we are using a Service Account with domain-wide delegation for authorizing the API calls. The domain-wide delegation has the following API scopes: https://spreadsheets.google.com/feeds/spreadsheets https://spreadsheets.google.com/feeds/worksheets The impersonated user (USER_EMAIL in the code) is the owner of the spreadsheets we are trying to access to. The libraries used to run the code below are the following (Maven pom.xml): <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</artifactId> <version>1.21.0</version> </dependency> <dependency> <groupId>com.google.http-client</groupId> <artifactId>google-http-client-jackson2</artifactId> <version>1.11.0-beta</version> </dependency> <dependency> <groupId>com.google.apis</groupId> <artifactId>google-api-services-drive</artifactId> <version>v3-rev14-1.21.0</version> </dependency> <dependency> <groupId>com.google.http-client</groupId> <artifactId>google-http-client</artifactId> <version>1.21.0</version> </dependency> ************************************************** import java.io.FileInputStream; import java.io.IOException; import java.net.URISyntaxException; import java.net.URL; import java.security.GeneralSecurityException; import java.security.KeyStore; import java.security.PrivateKey; import java.util.HashSet; import java.util.List; import java.util.Set; import com.google.api.client.googleapis.auth.oauth2.GoogleCredential; import com.google.api.client.http.HttpTransport; import com.google.api.client.http.javanet.NetHttpTransport; import com.google.api.client.json.jackson2.JacksonFactory; import com.google.gdata.client.spreadsheet.SpreadsheetService; import com.google.gdata.data.spreadsheet.SpreadsheetEntry; import com.google.gdata.data.spreadsheet.SpreadsheetFeed; import com.google.gdata.data.spreadsheet.WorksheetEntry; import com.google.gdata.util.ServiceException; public class SpreadsheetAPIExample { public static final String USER_EMAIL="<[email protected]>"; public static final String KEY_FILE_PATH="<path_to_pkcs12file.p12>"; public static final String KEY_PASSWORD="notasecret"; public static final String SERVICE_ACCOUNT_EMAIL="<emailaddress>@<projetname>.iam.gserviceaccount.com"; public static final String SPREADSHEET_SCOPE="https://spreadsheets.google.com/feeds/spreadsheets";;; public static final String WORKSHEET_SCOPE="https://spreadsheets.google.com/feeds/worksheets";;; public static void main(String[] args) { try { listSheets(); listWorksheets(); } catch (Exception e) { e.printStackTrace(); } } /** * * Build and returns a spreadsheet service object authorized with the service accounts * that act on behalf of the given user. * */ public static SpreadsheetService getSpreadsheetService() throws GeneralSecurityException, IOException, URISyntaxException { HttpTransport httpTransport = new NetHttpTransport(); JacksonFactory jsonFactory = new JacksonFactory(); KeyStore keystore = KeyStore.getInstance("PKCS12"); keystore.load(new FileInputStream(KEY_FILE_PATH), KEY_PASSWORD.toCharArray()); PrivateKey pk = (PrivateKey)keystore.getKey("privatekey", KEY_PASSWORD.toCharArray()); Set<String> scope = new HashSet<String>(); scope.add(SPREADSHEET_SCOPE); scope.add(WORKSHEET_SCOPE); GoogleCredential credential = new GoogleCredential.Builder() .setTransport(httpTransport) .setJsonFactory(jsonFactory) .setServiceAccountId(SERVICE_ACCOUNT_EMAIL) .setServiceAccountScopes(scope) .setServiceAccountUser(USER_EMAIL) .setServiceAccountPrivateKey(pk) .build(); credential.refreshToken(); SpreadsheetService service = new SpreadsheetService("Myapp"); service.setOAuth2Credentials(credential); service.setProtocolVersion(SpreadsheetService.Versions.V3); return service; } /** This method lists spreadsheets and gets their title. * Works OK * */ public static void listSheets() throws GeneralSecurityException, IOException, URISyntaxException, ServiceException { SpreadsheetService service = getSpreadsheetService(); URL spreadsheetsUrl = new URL("https://spreadsheets.google.com/feeds/spreadsheets/private/full";); SpreadsheetFeed spreadsheetFeed = service.getFeed(spreadsheetsUrl, SpreadsheetFeed.class); for (SpreadsheetEntry sheet : spreadsheetFeed.getEntries()) { System.out.println(sheet.getTitle().getPlainText()); } } /** This method gets a spreadsheet and lists its work sheets. * Throws a com.google.gdata.util.ResourceNotFoundException: Not Found * */ public static void listWorksheets() throws GeneralSecurityException, IOException, URISyntaxException, ServiceException { SpreadsheetService service = getSpreadsheetService(); URL spreadsheetsUrl = new URL("https://spreadsheets.google.com/feeds/spreadsheets/private/full";); SpreadsheetFeed spreadsheetFeed = service.getFeed(spreadsheetsUrl, SpreadsheetFeed.class); SpreadsheetEntry sheet = spreadsheetFeed.getEntries().get(0); // ResourceNotFoundException is thrown by the line below List<WorksheetEntry> worksheets = sheet.getWorksheets(); } } *************************************** The code above provides the following output Sheettitle1 Sheettitle2 ... com.google.gdata.util.ResourceNotFoundException: Not Found at com.google.gdata.client.http.HttpGDataRequest.handleErrorResponse(HttpGDataRequest.java:599) at com.google.gdata.client.http.GoogleGDataRequest.handleErrorResponse(GoogleGDataRequest.java:564) at com.google.gdata.client.http.HttpGDataRequest.checkResponse(HttpGDataRequest.java:560) at com.google.gdata.client.http.HttpGDataRequest.execute(HttpGDataRequest.java:538) at com.google.gdata.client.http.GoogleGDataRequest.execute(GoogleGDataRequest.java:539) at com.google.gdata.client.Service.getFeed(Service.java:1135) at com.google.gdata.client.Service.getFeed(Service.java:998) at com.google.gdata.client.GoogleService.getFeed(GoogleService.java:645) at com.google.gdata.client.Service.getFeed(Service.java:1017) at com.google.gdata.data.spreadsheet.SpreadsheetEntry.getWorksheets(SpreadsheetEntry.java:128) at reppmod.sandbox.SpreadsheetAPIExampleReal.listWorksheets(SpreadsheetAPIExample.java:135) at reppmod.sandbox.SpreadsheetAPIExampleReal.main(SpreadsheetAPIExample.java:57) To subsequent readers: If you are also experiencing this issue, please click the star next to the issue number. We prioritize issues with the most stars. -- You received this message because you are subscribed to the Google Groups "Google Spreadsheets API" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/d/optout.
