How to fetch a Google Spreadsheet having more than 80K rows using ListFeed in JAVA (GAE)?

Question

I want to know how can I fetch the Complete Google Spreadsheet with more than 80K rows using "List-based Feeds" in Google Spreadsheet.

To make it more clear, the flow of the application is as follows:

1. Connect to Google Spreadsheet using service.getFeed()
2. Using List-based Feeds fetch all the rows and push the task in the task queue to enter the data into datastore.

Problems: 1. The application works fine on localhost, but when deployed, timeout error occurs stating "HardDeadlineExceeded Exception". I had read the documentation of this exception and found that handling such exception would be of not much use. Following code is used to establish connection and get List-based Feeds:

            try
            {
                lf = service.getFeed(url, ListFeed.class); //Exception occurs at this point
                timeoutflag=1;
            }
            catch(Exception e)
            {
                timeoutinc += 3;
                service.setConnectTimeout(timeoutinc * 10000);
                service.setReadTimeout(timeoutinc * 10000);
            }

2. The second exception I get is: out of memory Exception

   java.lang.OutOfMemoryError: Java heap space at com.sun.org.apache.xerces.internal.parsers.AbstractSAXParser.parse (AbstractSAXParser.java:1213) at com.sun.org.apache.xerces.internal.jaxp.SAXParserImpl$JAXPSAXParser.parse (SAXParserImpl.java:642) at org.xml.sax.helpers.ParserAdapter.parse (ParserAdapter.java:430) ...

I had gone through the official documentation of Google and found that I can use Cell-based feeds, but as my application completely depends upon List-based feeds, shifting to Cell-based feeds is not an optimal choice for my use case as I need to fetch the data row by row and not cell by cell.

Please guide...!

Answer 1

1. The application works fine on localhost, but when deployed, timeout error occurs stating "HardDeadlineExceeded Exception". I had read the documentation of this exception and found that handling such exception would be of not much use.

Based from this documentation, if the DeadlineExceededException is not caught, an uncatchable HardDeadlineExceededError is thrown. The instance is terminated in both cases, but the HardDeadlineExceededError does not give any time margin to return a custom response. To make sure your request returns within the allowed time frame, you can use the ApiProxy.getCurrentEnvironment().getRemainingMillis() method to checkpoint your code and return if you have no time left.

2. The second exception I get is: out of memory Exception

From this related SO post, you got the error maybe because the heap is being over-allocated. The only way to solve other then increasing the heap space is to see what is using all the heap space and then trying to make sure objects can be collected that stay around longer then they are needed. If it is a file or something that can't be collected that is making you run out of heap space, you should re-engineer your program if the file sizes aren't constant and keep changing. If they are constant just increase the heap space above the file size. You can check this thread for more information.

For your question, How to fetch a Google Spreadsheet having more than 80K rows using ListFeed in JAVA (GAE)?, I suggest to check this documentation. This sample code might also help.

// Make a request to the API and get all spreadsheets.
    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.
    }