java - 尝试从公共(public) Google 电子表格读取数据时出现 NullPointerExceptions

Question

我正在尝试将谷歌电子表格用作 android 中的简单数据库。但是当我调用电子表格时，我得到 NullpointerExeptions当我试图获取工作表的大小时，它返回 0。例如，当我调用 getAllCells() 时，我得到一个 NullpointerExeption。感谢您提供的任何帮助/建议。

(我制作了电子表格发布到网络并且知道链接的任何人都可以编辑)

这是电子表格 https://docs.google.com/spreadsheets/d/1arJFUxSghwdv0QpnJJ3lBu36X4I3d_uB4xx_xNGLKHU/edit#gid=0

这是小项目的 zip 文件 https://drive.google.com/file/d/0B36AnWs56yGIdVFpdzNxSE9BSlU/view?usp=sharing

这是apk https://drive.google.com/file/d/0B36AnWs56yGITkIyUGpJcHpuUlk/view?usp=sharing

这里是错误

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)

这是我用来访问它的java类

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

事实证明，问题出在投影的范围上，根据 google 的文档，电子表格提要仅支持“私有(private)”可见性和“完整”投影。

我使用的是 'public' 和 'basic'所以我解决这个问题的方法是像这样直接访问工作表(支持更多可见性参数):

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);

}