NullPointerExceptions while trying to read data from a public Google Spreadsheet

Question

I am trying to use google spreadsheet as a simple database in android. But when I make calls to the spread sheet, I get NullpointerExeptions when I tried to tried to get the size of the worksheet, it returned 0. for example, when I call getAllCells() I get a NullpointerExeption. I appreciate any help/ suggestions you can offer.

(I made the spreadsheet published to the web and Anyone with the link can edit )

here is the spreadsheet https://docs.google.com/spreadsheets/d/1arJFUxSghwdv0QpnJJ3lBu36X4I3d_uB4xx_xNGLKHU/edit#gid=0

here is the zip file of the small project https://drive.google.com/file/d/0B36AnWs56yGIdVFpdzNxSE9BSlU/view?usp=sharing

here is the apk https://drive.google.com/file/d/0B36AnWs56yGITkIyUGpJcHpuUlk/view?usp=sharing

here is the error

java.lang.NullPointerException
1at com.google.gdata.data.spreadsheet.WorksheetEntry.getFeedUrlString(WorksheetEntry.java:133)
2at com.google.gdata.data.spreadsheet.WorksheetEntry.getCellFeedUrl(WorksheetEntry.java:113)
3at com.example.tempo.util.SpDatabase.getAllCells(SpDatabase.java:67)
4at com.example.tempo.TempoMain.testDataBase(TempoMain.java:101)
5at com.example.tempo.TempoMain$1$1.doOnBackground(TempoMain.java:70)
6at com.example.tempo.util.AsyncJob$2.run(AsyncJob.java:59)
7at java.lang.Thread.run(Thread.java:841)

Here is the java class I use to access it

package com.example.tempo.util;
import android.widget.Toast;

import com.google.gdata.client.spreadsheet.*;
import com.google.gdata.data.spreadsheet.*;
import com.google.gdata.util.*;

import java.io.IOException;
import java.net.*;
import java.util.*;



public class SpDatabase {

    WorksheetEntry worksheet;
    SpreadsheetService service;
    String spTitle;

    public SpDatabase()throws AuthenticationException, MalformedURLException, IOException, ServiceException {
                service = new SpreadsheetService("MySpreadsheetIntegration-v1");

                // TODO: Authorize the service object for a specific user (see other sections)

                // Define the URL to request.  This should never change.

                String key = "1arJFUxSghwdv0QpnJJ3lBu36X4I3d_uB4xx_xNGLKHU";


                URL SPREADSHEET_FEED_URL  = FeedURLFactory.getDefault().getWorksheetFeedUrl(key, "public", "basic");


                // 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.
                }

                // TODO: Choose a spreadsheet more intelligently based on your
                // app's needs.
                SpreadsheetEntry spreadsheet = spreadsheets.get(0);
                spTitle = spreadsheet.getTitle().getPlainText();

                // Make a request to the API to fetch information about all
                // worksheets in the spreadsheet.
                List<WorksheetEntry> worksheets = spreadsheet.getWorksheets();
//              WorksheetEntry worksheet = worksheets.get(0);
                worksheet = worksheets.get(0);

    }

    public int getRowCount(){
        int rowCount = worksheet.getRowCount();

        return rowCount;
    }
    public int getColumnCount(){
        int columnCount = worksheet.getColCount();

        return columnCount;
    }

    public ArrayList<CellEntry> getAllCells() throws IOException, ServiceException{
          // Fetch the cell feed of the worksheet.
        URL cellFeedUrl = worksheet.getCellFeedUrl();
        CellFeed cellFeed = service.getFeed(cellFeedUrl, CellFeed.class);

        return (ArrayList<CellEntry>) cellFeed.getEntries();
    }
                            // or getRow() or getCollumn()
    public ArrayList<CellEntry> getSpecificCells(int minRow, int maxRow, int minCol, int maxCol) throws IOException, ServiceException, URISyntaxException{

        //example: Fetch column 4, and every row after row 1 --> "?min-row=2&min-col=4&max-col=4"
        String pref =  "?";
        if(minRow != -1){
            pref = appendAndIfFirstValue(pref);
            pref += "min-row=" + minRow;
        }
        if(minCol != -1){
            pref = appendAndIfFirstValue(pref);
            pref += "&min-col=" + minCol;
        }

        if(maxRow != -1){
            pref = appendAndIfFirstValue(pref);
            pref += "&max-row=" + maxRow;
        }
        if(maxCol != -1){
            pref = appendAndIfFirstValue(pref);
            pref += "&max-col=" + maxCol;
        }

          // Fetch column 4, and every row after row 1.
        URL cellFeedUrl = new URI(worksheet.getCellFeedUrl().toString() + pref).toURL();
        CellFeed cellFeed = service.getFeed(cellFeedUrl, CellFeed.class);



        return (ArrayList<CellEntry>) cellFeed.getEntries();


    }

    public int getCellNumValue(CellEntry cell){
        return (Integer) cell.getCell().getNumericValue();
    }

    public String getCellStringValue(CellEntry cell){
        return  cell.getCell().getValue();
    }


    public void incrementCellValue(CellEntry cell) throws IOException, ServiceException{
          String cellID = cell.getTitle().getPlainText();
          cell.changeInputValueLocal("=SUM(" + cellID + ", 1)");
          cell.update();

    }
    public void changeCellValue(CellEntry cell, String value) throws IOException, ServiceException{
        cell.changeInputValueLocal(value);
        cell.update();
    }
    private String appendAndIfFirstValue(String str){
        if(!str.contains("&")){
            return "&" + str;
        }else{
            return str;
        }

    }

    public String getSpreadSheetTitle() throws IOException, ServiceException, URISyntaxException{

    return spTitle;

    }

}

Answer 1

It turns out the problem was the scope of the projection and arcording to the the documentation google said The spreadsheets feed only supports the 'private' visibility and the 'full' projection.

I was using the 'public' and 'basic' so what I did to fix the problem was to access the worksheets(which supports more visibility parameters) directly like this:

public SpDatabase()throws AuthenticationException, MalformedURLException, IOException, ServiceException {
        service = new SpreadsheetService("Test");

        FeedURLFactory factory = FeedURLFactory.getDefault();

        String key = "1arJFUxSghwdv0QpnJJ3lBu36X4I3d_uB4xx_xNGLKHU";
        URL spreadSheetUrl = factory.getWorksheetFeedUrl(key, "public", "full");
        WorksheetFeed feed = service.getFeed(spreadSheetUrl, WorksheetFeed.class);

        worksheet = feed.getEntries().get(0);
        URL cellFeedURL = worksheet.getCellFeedUrl();
        CellFeed cellFeed = service.getFeed(cellFeedURL, CellFeed.class);

}