Please disable your adblock and script blockers to view this page

Search this blog

Friday 30 October 2015

Read data from Google Spreadsheet without authentication using Java

Google Spreadsheets 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.1\gdata\java\lib, 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 class path

Next Step is create and configure Google Sheet -


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 class path)



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 output is -
Cheers :) Happy Learning

32 comments :

  1. Hi Ashish, I tried your above code but I am getting below error-
    java.net.UnknownHostException: spreadsheets.google.com.
    Can you help?

    ReplyDelete
    Replies
    1. Shariq

      Have you added all Jar files in project ?
      Are you using this code in pure java application or with some framework ?

      Ashish

      Delete
  2. 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".

    ReplyDelete
    Replies
    1. Shariq

      I haven't posted article on "Writing Data to Google Spreadsheet" but you can check Google Sheets API page
      There you will get sample code about this

      Ashish

      Delete
    2. Shariq

      Can you explain how to solve the network issue, which was blocking access to google

      Delete
    3. 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.

      Delete
  3. 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)

    ReplyDelete
    Replies
    1. Have you added all Jars ?
      Gdata-core-10.jar, Gdata-spreadsheet-30.jar, Google-collect-10-rc1.jar
      Check again

      Ashish

      Delete
    2. Yes Ashish, I have already added.
      gdata-core.jar,gdata-spreadsheet-1.0.jar,google-collect-1.0-rc1.jar

      Delete
    3. Hi Ashish, I found the issue, it's with gdata-spreadsheet.jar version. Now I added 3.0 version. It's working fine.
      Thank you.

      Asritha.

      Delete
  4. I am getting the above exception at this line
    SpreadsheetService service = new SpreadsheetService("Sheet1");

    ReplyDelete
    Replies
    1. Asritha

      You have added gdata-spreadsheet-1.0.jar but I mentioned gdata-spreadsheet-3.0.jar
      Once change Jar and check it again
      Download Jar- Here

      Ashish

      Delete
  5. Ashish,

    I am not able to read the values from second sheet of the same spreadhsheet.

    ReplyDelete
    Replies
    1. Srinivas

      Check Manage Worksheets
      Here you'll get code to manage worksheets

      Ashish

      Delete
    2. 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!

      Delete
    3. Yeah Srinivas
      That'll work too :) Thanks for sharing

      Ashish

      Delete
  6. Thanks for this tutorial, Ashish!

    ReplyDelete
  7. 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.

    ReplyDelete
  8. Thank you for the post..Its really very helpful and well described:)

    -Shalvi Kapadia

    ReplyDelete
  9. Can you please tell how to insert data into google spreadsheet using java without authentication..

    ReplyDelete
  10. dear sir
    could you illustrate the steps to upload data from .xls or .xlsx to AF:table

    ReplyDelete
  11. Dear Sir, I tried that code in android but getting runtime error... shall u send me android code to me..

    ReplyDelete
    Replies
    1. You must have missed something , It'll work in android too

      Delete
  12. 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

    ReplyDelete
    Replies
    1. Have you used appropriate Jar files ? You can download jar files from the link mentioned in above comments

      Delete
  13. 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)

    ReplyDelete
  14. 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)

    ReplyDelete
    Replies
    1. But there is nothing like authentication in code ?
      Are you using some aditional code ?

      Delete
  15. Is there any way to download google spreadsheet file as html in local machine using java??

    ReplyDelete
  16. if i want specified the sheet name instead to setting default sheet, how could i do that

    ReplyDelete