Read data from Google Spreadsheet without authentication using Java

Sharing is Caring

Google Spreadsheet provides a way to create, edit, save spreadsheets online.
The Google Sheets API (formerly called the Google Spreadsheets API) lets you develop client applications that read and modify worksheets and data in Google Sheets.
Read More about Sheets API

Let’s see how to use this API to read data from Spreadsheet, here i am going to use Spreadsheet version 3.0 , if this link doesn’t work then check on GitHub , from here you will get  client library for all Google APIs

Extract downloaded zip file in your directory and go to lib folder your_directory…gdata-samples.java-1.47.1gdatajavalib, from there copy Gdata-core-10.jar, Gdata-spreadsheet-30.jar, Google-collect-10-rc1.jar to your project path and add all jar files to project classpath

Next Step is creating and configure Google Sheet –

The first step is to log in  your google account and access Google Sheets

You will see some predefined templates to create new sheet or you can create a blank sheet (Just click on Blank option)

A new sheet opens , provide title and column name (as many as you want) and put some data in cells of each columns

Next step is to publish this spreadsheets, click on File menu and select Publish to web option

Publish with default settings  (Entire document as WebPage)

After publishing just copy URL to access published spreadsheet

But we can’t use this URL in our java code so change it to a different URL. Copy spreadsheet key from above URL
https://docs.google.com/spreadsheets/d/1G3VABou70MUbRzY4vHgiDME5JW5rfo7lrAr_hZyEawU/pubhtml
and use in this https://spreadsheets.google.com/feeds/list/SPREADSHEET_KEY/default/public/values 

After changes final URL to access spreadsheet feeds looks like this – https://spreadsheets.google.com/feeds/list/1G3VABou70MUbRzY4vHgiDME5JW5rfo7lrAr_hZyEawU/default/public/values

Now see the code to read data (All required jars are mentioned above, add all to project classpath)

import java.io.IOException;

import java.net.URL;

import com.google.gdata.client.spreadsheet.SpreadsheetService;
import com.google.gdata.data.spreadsheet.CustomElementCollection;
import com.google.gdata.data.spreadsheet.ListEntry;
import com.google.gdata.data.spreadsheet.ListFeed;
import com.google.gdata.util.ServiceException;

public class PrintSpreadsheet {
public static void main(String[] args) {
SpreadsheetService service = new SpreadsheetService("Sheet1");
try {
String sheetUrl =
"https://spreadsheets.google.com/feeds/list/1G3VABou70MUbRzY4vHgiDME5JW5rfo7lrAr_hZyEawU/default/public/values";

// Use this String as url
URL url = new URL(sheetUrl);

// Get Feed of Spreadsheet url
ListFeed lf = service.getFeed(url, ListFeed.class);

//Iterate over feed to get cell value
for (ListEntry le : lf.getEntries()) {
CustomElementCollection cec = le.getCustomElements();
//Pass column name to access it's cell values
String val = cec.getValue("Cell1");
System.out.println(val);
String val2 = cec.getValue("Cell2");
System.out.println(val2);
}
} catch (IOException e) {
e.printStackTrace();
} catch (ServiceException e) {
e.printStackTrace();
}
}
}

And the output is –

Cheers 🙂 Happy Learning

Related Posts

An Oracle ACE, Blogger, Reviewer, Technical Lead working on Oracle ADF

36 thoughts on “Read data from Google Spreadsheet without authentication using Java”

  1. Hi Ashish. Issue got resolved. I was on network which was blocking access to google. Thanks for your article. Have you written any code for "writing data to spreadsheet".

  2. Hi Ashish, I am getting below error:
    Exception in thread "main" java.lang.NoSuchMethodError: com.google.gdata.data.ExtensionDescription.setNamespace(Lcom/google/gdata/util/common/xml/XmlWriter$Namespace;)V
    at com.google.gdata.data.spreadsheet.RowCount.getDefaultDescription(Unknown Source)
    at com.google.gdata.data.spreadsheet.CellFeed.declareExtensions(Unknown Source)
    at com.google.gdata.client.spreadsheet.SpreadsheetService.addExtensions(Unknown Source)
    at com.google.gdata.client.spreadsheet.SpreadsheetService.(Unknown Source)
    at sheet.example.PrintSpreadsheet.main(PrintSpreadsheet.java:11)

  3. Thanks for the help Ashish! The easiest way I found is that in the Feeds URL, lets take The one you have provided in the Example :

    https://spreadsheets.google.com/feeds/list/1G3VABou70MUbRzY4vHgiDME5JW5rfo7lrAr_hZyEawU/default/public/values"

    The URL parameter "default" represents the Sheet number. it looks like the listed Feed Spread Sheet URLs we can access the sheets by its number. we can change default to 1,2….n to access the contents from the specific workspace.

    Hope it helps for others too!

  4. Hi Ashish, thanks for the tutorial but i am getting a exception.

    com.google.gdata.util.RedirectRequiredException: Moved Temporarily

    HTML
    HEAD
    TITLE Moved Temporarily /TITLE
    /HEAD
    BODY BGCOLOR="#FFFFFF" TEXT="#000000"
    H1 Moved Temporarily /H1
    The document has moved here.
    BODY
    HTML

    at com.google.gdata.client.http.GoogleGDataRequest.handleErrorResponse(GoogleGDataRequest.java:560)
    at com.google.gdata.client.http.HttpGDataRequest.checkResponse(HttpGDataRequest.java:481)
    at com.google.gdata.client.http.HttpGDataRequest.execute(HttpGDataRequest.java:460)
    at com.google.gdata.client.http.GoogleGDataRequest.execute(GoogleGDataRequest.java:534)
    at com.google.gdata.client.Service.getFeed(Service.java:962)
    at com.google.gdata.client.Service.getFeed(Service.java:819)
    at com.google.gdata.client.GoogleService.getFeed(GoogleService.java:600)
    at com.google.gdata.client.Service.getFeed(Service.java:838)
    at exercise1.readData.main(readData.java:23)

    please help me on this.

  5. I was on network which has firewall which was preventing me to connect to google. I change to my personal network & issue got resolve. Please check proxy also if issue persists.

  6. Hi Ashish, i tried your code but i am facing this error

    HTTP Status 500 – Handler processing failed; nested exception is java.lang.NoClassDefFoundError: Could not initialize class com.google.gdata.client.spreadsheet.SpreadsheetService

  7. hello, i just tried your code but I'm getting this error and i didn't find any solution on the net

    AVERTISSEMENT: [Line 3, Column 17]
    com.google.gdata.util.ParseException: Invalid root element, expected (namespace uri:local name) of (http://www.w3.org/2005/Atom:feed), found (:html
    Message: Invalid root element, expected (namespace uri:local name) of (http://www.w3.org/2005/Atom:feed), found (:html

    at com.google.gdata.util.XmlParser.startElement(XmlParser.java:794)
    at org.xml.sax.helpers.ParserAdapter.startElement(Unknown Source)
    at com.sun.org.apache.xerces.internal.parsers.AbstractSAXParser.startElement(Unknown Source)
    at com.sun.org.apache.xerces.internal.impl.dtd.XMLDTDValidator.startElement(Unknown Source)
    at com.sun.org.apache.xerces.internal.impl.XMLNSDocumentScannerImpl.scanStartElement(Unknown Source)
    at com.sun.org.apache.xerces.internal.impl.XMLNSDocumentScannerImpl$NSContentDriver.scanRootElementHook(Unknown Source)
    at com.sun.org.apache.xerces.internal.impl.XMLDocumentFragmentScannerImpl$FragmentContentDriver.next(Unknown Source)
    at com.sun.org.apache.xerces.internal.impl.XMLDocumentScannerImpl$PrologDriver.next(Unknown Source)
    at com.sun.org.apache.xerces.internal.impl.XMLDocumentScannerImpl.next(Unknown Source)
    at com.sun.org.apache.xerces.internal.impl.XMLNSDocumentScannerImpl.next(Unknown Source)
    at com.sun.org.apache.xerces.internal.impl.XMLDocumentFragmentScannerImpl.scanDocument(Unknown Source)
    at com.sun.org.apache.xerces.internal.parsers.XML11Configuration.parse(Unknown Source)
    at com.sun.org.apache.xerces.internal.parsers.XML11Configuration.parse(Unknown Source)
    at com.sun.org.apache.xerces.internal.parsers.XMLParser.parse(Unknown Source)
    at com.sun.org.apache.xerces.internal.parsers.AbstractSAXParser.parse(Unknown Source)
    at com.sun.org.apache.xerces.internal.jaxp.SAXParserImpl$JAXPSAXParser.parse(Unknown Source)
    at org.xml.sax.helpers.ParserAdapter.parse(Unknown Source)
    at com.google.gdata.util.XmlParser.parse(XmlParser.java:677)
    at com.google.gdata.util.XmlParser.parse(XmlParser.java:608)
    at com.google.gdata.data.BaseFeed.parseAtom(BaseFeed.java:759)
    at com.google.gdata.util.ParseUtil.parseFeed(ParseUtil.java:200)
    at com.google.gdata.util.ParseUtil.readFeed(ParseUtil.java:168)
    at com.google.gdata.data.BaseFeed.readFeed(BaseFeed.java:741)
    at com.google.gdata.client.Service.getFeed(Service.java:965)
    at com.google.gdata.client.Service.getFeed(Service.java:819)
    at com.google.gdata.client.GoogleService.getFeed(GoogleService.java:600)
    at com.google.gdata.client.Service.getFeed(Service.java:838)
    at com.TokenTest.main(TokenTest.java:24)

  8. hi ashish
    i get these errors
    Exception in thread "main" com.google.gdata.util.AuthenticationException: Error authenticating (check service name)
    at com.google.gdata.client.GoogleAuthTokenFactory.getAuthException(GoogleAuthTokenFactory.java:628) at com.google.gdata.client.GoogleAuthTokenFactory.getAuthException(GoogleAuthTokenFactory.java:628)
    at com.google.gdata.client.GoogleAuthTokenFactory.getAuthToken(GoogleAuthTokenFactory.java:500)
    at com.google.gdata.client.GoogleAuthTokenFactory.setUserCredentials(GoogleAuthTokenFactory.java:346)
    at com.google.gdata.client.GoogleService.setUserCredentials(GoogleService.java:362)
    at com.google.gdata.client.GoogleService.setUserCredentials(GoogleService.java:317)
    at com.google.gdata.client.GoogleService.setUserCredentials(GoogleService.java:301)

  9. I can read data successfully, but I can’t write to the worksheet. Based on the documentation, cec.setValueLocal(String, String) should edit the worksheet. Does anyone know what’s wrong with this? Is there any setting in google worksheet that need to enable editing?

  10. Exception in thread “main” java.lang.IllegalArgumentException: Trying to set foreign cookie
    at com.google.gdata.client.http.GoogleGDataRequest$GoogleCookie.(GoogleGDataRequest.java:166)
    at com.google.gdata.client.http.GoogleGDataRequest$GoogleCookieHandler.put(GoogleGDataRequest.java:399)
    at sun.net.www.http.HttpClient.parseHTTPHeader(HttpClient.java:728)
    at sun.net.www.http.HttpClient.parseHTTP(HttpClient.java:647)
    at sun.net.www.protocol.http.HttpURLConnection.getInputStream0(HttpURLConnection.java:1536)
    at sun.net.www.protocol.http.HttpURLConnection.getInputStream(HttpURLConnection.java:1441)
    at sun.net.www.protocol.http.HttpURLConnection.getHeaderFields(HttpURLConnection.java:2966)
    at sun.net.www.protocol.https.HttpsURLConnectionImpl.getHeaderFields(HttpsURLConnectionImpl.java:283)
    at com.google.gdata.client.http.HttpGDataRequest.isOAuthProxyErrorResponse(HttpGDataRequest.java:558)
    at com.google.gdata.client.http.HttpGDataRequest.checkResponse(HttpGDataRequest.java:549)
    at com.google.gdata.client.http.HttpGDataRequest.execute(HttpGDataRequest.java:530)
    at com.google.gdata.client.http.GoogleGDataRequest.execute(GoogleGDataRequest.java:535)
    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:631)
    at com.google.gdata.client.Service.getFeed(Service.java:1017)
    at war.PrintSpreadsheet.main(PrintSpreadsheet.java:19)
    hii ,i am getting this issue please solve it

  11. I am receiving “`com.google.gdata.util.ParseException: Unrecognized content type:text/html;charset=UTF-8
    at com.google.gdata.client.Service.parseResponseData(Service.java:2136)
    at com.google.gdata.client.Service.parseResponseData(Service.java:2098)
    at com.google.gdata.client.Service.getFeed(Service.java:1136)
    at com.google.gdata.client.Service.getFeed(Service.java:998)
    at com.google.gdata.client.GoogleService.getFeed(GoogleService.java:652)
    at com.google.gdata.client.Service.getFeed(Service.java:1017)
    at com.utils.DataReader.createUserSheet(DataReader.java:38)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
    at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
    at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
    at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
    at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
    at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78)
    at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57)
    at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
    at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
    at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
    at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
    at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
    at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
    at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:86)
    at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:459)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:675)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:382)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:192)

    “`
    When I try to read the data from the google sheet

Leave a Reply

Your email address will not be published. Required fields are marked *