Java 到 Google 电子表格

Java to Google Spreadsheet

提问by Tim Lai

I was trying to do programming using Java to connect to Google Spreadsheet to do data retrieval or modifying data in the cells.

我试图使用 Java 进行编程以连接到 Google 电子表格以进行数据检索或修改单元格中的数据。

My Google spreadsheet link is https://docs.google.com/spreadsheets/d/1UXoGD2gowxZ2TY3gooI9y7rwWTPBOA0dnkeNYwUqQRA

我的 Google 电子表格链接是https://docs.google.com/spreadsheets/d/1UXoGD2gowxZ2TY3gooI9y7rwWTPBOA0dnkeNYwUqQRA

I looked at the Sheets APIand it requires link like

我查看了Sheets API，它需要像这样的链接

https://spreadsheets.google.com/feeds/worksheets/key/private/full

https://spreadsheets.google.com/feeds/worksheets/key/private/full

I have tried different forms of the links, such as:

我尝试了不同形式的链接，例如：

1. https://spreadsheets.google.com/feeds/worksheets/1UXoGD2gowxZ2TY3gooI9y7rwWTPBOA0dnkeNYwUqQRA/private/full

2. https://spreadsheets.google.com/feeds/worksheets/1UXoGD2gowxZ2TY3gooI9y7rwWTPBOA0dnkeNYwUqQRA/private/full

1. https://spreadsheets.google.com/feeds/worksheets/1UXoGD2gowxZ2TY3gooI9y7rwWTPBOA0dnkeNYwUqQRA/private/full

2. https://spreadsheets.google.com/feeds/worksheets/1UXoGD2gowxZ2TY3gooI9y7rwWTPBOA0dnkeNYwUqQRA/private/full

They gave me different kinds of errors respectively:

他们分别给了我不同类型的错误：

1. com.google.gdata.util.ParseException: Unrecognized content type:application/binary
2. com.google.gdata.util.RedirectRequiredException: Moved Temporarily

1. com.google.gdata.util.ParseException: Unrecognized content type:application/binary
2. com.google.gdata.util.RedirectRequiredException: Moved Temporarily

I have no idea how to connect to the Googl Spreadsheet using Java. Please help me if you have experience on this.

我不知道如何使用 Java 连接到 Googl 电子表格。如果您有这方面的经验，请帮助我。

import com.google.gdata.client.authn.oauth.*;
import com.google.gdata.client.spreadsheet.*;
import com.google.gdata.data.*;
import com.google.gdata.data.batch.*;
import com.google.gdata.data.spreadsheet.*;
import com.google.gdata.util.*;
import org.testng.annotations.Test;

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

public class TestGoogleSheetsAPI {

    @Test
    public void testConnectToSpreadSheet() throws ServiceException, IOException {
        SpreadsheetService service = new SpreadsheetService("google-spreadsheet");

        URL SPREADSHEET_FEED_URL = new URL("https://spreadsheets.google.com/feeds/worksheets/1UXoGD2gowxZ2TY3gooI9y7rwWTPBOA0dnkeNYwUqQRA/public/full");
        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.
        }

        SpreadsheetEntry spreadsheet = spreadsheets.get(0);
        System.out.println(spreadsheet.getTitle().getPlainText());
    }
}

I didn't use service.setUserCredentials("xxx@gmail", "password")because I will have another error, which is com.google.gdata.util.AuthenticationException: Error authenticating (check service name)

我没有使用，service.setUserCredentials("xxx@gmail", "password")因为我会有另一个错误，那就是com.google.gdata.util.AuthenticationException: Error authenticating (check service name)

回答by Brian Chapman

You are getting the redirect because accessing your spreadsheet requires that you authenticate first. Google Sheets is using the old gdata API, but requires that you authenticate using OAuth 2.0. Therefore you will need to import both the gdata and Google API libraries as shown below:

您正在获得重定向，因为访问您的电子表格需要您先进行身份验证。Google 表格使用旧的 gdata API，但要求您使用 OAuth 2.0 进行身份验证。因此，您需要导入 gdata 和 Google API 库，如下所示：

<dependencies>
    <dependency>
        <groupId>com.google.gdata</groupId>
        <artifactId>core</artifactId>
        <version>1.47.1</version>
    </dependency>
    <dependency>
        <groupId>com.google.api-client</groupId>
        <artifactId>google-api-client-java6</artifactId>
        <version>1.20.0</version>
    </dependency>
</dependencies>

The code below shows how you can authenticate with Google using OAuth. You will need to follow the instructions for creating a service account and downloading the P12 key first.After creating your service account, copy the email address into the CLIENT_ID field below, add your P12 file to your classpath and chante P12FILE to point to your P12 file.

下面的代码显示了如何使用 OAuth 向 Google 进行身份验证。您需要先按照说明创建服务帐户并下载 P12 密钥。创建您的服务帐户后，将电子邮件地址复制到下面的 CLIENT_ID 字段中，将您的 P12 文件添加到您的类路径中，并使用 P12FILE 指向您的 P12 文件。

I was able to get this working with the following SPREADSHEET_FEED_URL "https://spreadsheets.google.com/feeds/worksheets/:worksheetId/private/basic" where ":worksheetId" is your worksheet Id. This is slightly different than the one you were using.

我能够使用以下 SPREADSHEET_FEED_URL " https://spreadsheets.google.com/feeds/worksheets/:worksheetId/private/basic"来完成这项工作，其中 ":worksheetId" 是您的工作表 ID。这与您使用的略有不同。

Be sure to make sure that your service account has permission to read or write to the spreadsheet by sharing it with the service account email address first.

请务必先与服务帐户电子邮件地址共享电子表格，以确保您的服务帐户有权读取或写入电子表格。

public class GoogleSheetsApiTest {

// Generate a service account and P12 key:
// https://developers.google.com/identity/protocols/OAuth2ServiceAccount
private final String CLIENT_ID = "<your service account email address>";
// Add requested scopes.
private final List<String> SCOPES = Arrays
        .asList("https://spreadsheets.google.com/feeds");
// The name of the p12 file you created when obtaining the service account
private final String P12FILE = "/<your p12 file name>.p12";


@Test
public void testConnectToSpreadSheet() throws GeneralSecurityException,
        IOException, ServiceException, URISyntaxException {

    SpreadsheetService service = new SpreadsheetService(
            "google-spreadsheet");
    GoogleCredential credential = getCredentials();
    service.setOAuth2Credentials(credential);

    URL SPREADSHEET_FEED_URL = new URL(
            "https://spreadsheets.google.com/feeds/worksheets/1UXoGD2gowxZ2TY3gooI9y7rwWTPBOA0dnkeNYwUqQRA/private/basic");
    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.
    }
    //
    SpreadsheetEntry spreadsheet = spreadsheets.get(0);
    System.out.println(spreadsheet.getTitle().getPlainText());

}

private GoogleCredential getCredentials() throws GeneralSecurityException,
        IOException, URISyntaxException {
    HymansonFactory JSON_FACTORY = HymansonFactory.getDefaultInstance();
    HttpTransport httpTransport = GoogleNetHttpTransport
            .newTrustedTransport();

    URL fileUrl = this.getClass().getResource(P12FILE);
    GoogleCredential credential = new GoogleCredential.Builder()
            .setTransport(httpTransport)
            .setJsonFactory(JSON_FACTORY)
            .setServiceAccountId(CLIENT_ID)
            .setServiceAccountPrivateKeyFromP12File(
                    new File(fileUrl.toURI()))
            .setServiceAccountScopes(SCOPES).build();

    return credential;
}

}

回答by Aaron Blenkush

I was also getting the com.google.gdata.util.ParseException: Unrecognized content type:application/binaryerror, but I seem to have stumbled on a workaround for this strange bug. The code in Alex R's answercan be used as a starting point.

我也遇到了com.google.gdata.util.ParseException: Unrecognized content type:application/binary错误，但我似乎偶然发现了这个奇怪错误的解决方法。Alex R 的答案中的代码可以用作起点。

1. First I tried changing the visibility to "public", just to see what would happen. Since the document is not published, I got the expected error response, containing:

   We're sorry. This document is not published.

2. So I changed the visibility back to "private", and tried again just for fun...

   Lo, and behold; it worked!

1. 首先，我尝试将可见性更改为"public"，只是为了看看会发生什么。由于文档未发布，我得到了预期的错误响应，其中包含：

   我们很抱歉。本文档未发布。

2. 所以我将可见性改回"private"，并再次尝试只是为了好玩......

   瞧，瞧；有效！

It works with either the Drive or Spreadsheets Feed scope (both can be included if you prefer):

它适用于 Drive 或 Spreadsheets Feed 范围（如果您愿意，可以包括两者）：

• https://www.googleapis.com/auth/drive
• https://spreadsheets.google.com/feeds

• https://www.googleapis.com/auth/drive
• https://spreadsheets.google.com/feeds

I'm not sure if its a factor, but I'm not using OAuth 2.0, but rather a Credentialcreated from a PrivateKey(PKCS12 file), as in Brian Chapman's answer.

我不确定它是否是一个因素，但我没有使用 OAuth 2.0，而是Credential从PrivateKey（PKCS12 文件）创建的，如Brian Chapman 的回答。

Conclusion

结论

So, although it's kludgy, I've modified my application to alwaysmake a "public"request (wrapped in a tryblock to absorb the resulting Exception) just before making the "private"request. It's the only way I've been able to deterministically get the correct result.

所以，虽然它很笨拙，但我已经修改了我的应用程序，以便在发出请求之前总是发出"public"请求（包裹在一个try块中以吸收结果Exception）"private"。这是我能够确定性地获得正确结果的唯一方法。

回答by Shariq Bharat

1 reason for getting com.google.gdata.util.RedirectRequiredException: Moved Temporarilyis when we forget to publish the document/spreadsheet. Make sure you have published your document after creating it by going to Menu > File > Published to Web. This is required only once and not after every edit.

获得com.google.gdata.util.RedirectRequiredException: Moved Temporously 的1 个原因是我们忘记发布文档/电子表格。通过转到菜单 > 文件 > 发布到 Web，确保在创建文档后已发布文档。这仅需要一次，而不是在每次编辑之后。

回答by Rajith

I feel like this is a bug. As a workaround you could invoke below getfeed request first and then your feed requests will start working. below is modified code for better understanding

我觉得这是一个错误。作为一种解决方法，您可以先调用下面的 getfeed 请求，然后您的 Feed 请求将开始工作。下面是修改后的代码，以便更好地理解

import com.google.gdata.client.authn.oauth.*;
import com.google.gdata.client.spreadsheet.*;

import com.google.gdata.data.*;
import com.google.gdata.data.batch.*;
import com.google.gdata.data.spreadsheet.*;
import com.google.gdata.util.*;
import org.testng.annotations.Test;

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

public class TestGoogleSheetsAPI {

    @Test
    public void testConnectToSpreadSheet() throws ServiceException, IOException {
        SpreadsheetService service = new SpreadsheetService("google-spreadsheet");

        URL SPREADSHEET_FEED_URL = new URL("https://spreadsheets.google.com/feeds/worksheets/1UXoGD2gowxZ2TY3gooI9y7rwWTPBOA0dnkeNYwUqQRA/public/full");
        //added new line *******************************
        service.getFeed(new URL("https://spreadsheets.google.com/feeds/spreadsheets/private/full?xoauth_requestor_id=test"),WorksheetFeed.class);
        //************************
        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.
        }

        SpreadsheetEntry spreadsheet = spreadsheets.get(0);
        System.out.println(spreadsheet.getTitle().getPlainText());
    }
}

Did some debugging as well. Seems if we try to invoke worksheet with key, then response seems to have content type "application/binary" which will be regected in gdata library, but if we try to get all the feeds first then response will have correct content type and that type is used in next requests. so flow starts working

也做了一些调试。似乎如果我们尝试使用键调用工作表，那么响应似乎具有内容类型“应用程序/二进制”，它将在 gdata 库中被重新定义，但是如果我们首先尝试获取所有提要，那么响应将具有正确的内容类型和该类型用于下一个请求。所以流程开始工作

回答by Alex R

The feed you get from that URL is a WorksheetFeed, not a SpreadsheetFeed. Try this:

您从该 URL 获得的提要是WorksheetFeed，而不是SpreadsheetFeed。试试这个：

    SpreadsheetService service = new SpreadsheetService("google-spreadsheet");

    FeedURLFactory urlFactory = FeedURLFactory.getDefault();
    WorksheetFeed worksheetFeed = service.getFeed(urlFactory.getWorksheetFeedUrl("1V4jT4vSqmY4YNY1VJhariLRLbxfFWf5z8bSTpDcSBPE", "public", "full"), WorksheetFeed.class);
    List<WorksheetEntry> worksheets = worksheetFeed.getEntries();
    WorksheetEntry worksheet = worksheets.get(0);
    System.out.println(worksheet.getTitle().getPlainText());