Java 到 Google 电子表格

Question

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

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

我查看了 Sheets API它需要像

这样的链接

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

我尝试过不同形式的链接，例如:

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

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

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

1. com.google.gdata.util.ParseException:无法识别的内容类型:application/binary
2. com.google.gdata.util.RedirectRequiredException:临时移动

我不知道如何使用 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());
    }
}

我没有使用 service.setUserCredentials("xxx@gmail", "password") 因为我会遇到另一个错误，即 com.google.gdata.util.AuthenticationException : 验证错误(检查服务名称)

Answer 1

您获得重定向是因为访问您的电子表格需要您先进行身份验证。 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>

下面的代码显示了如何使用 OAuth 向 Google 进行身份验证。您将需要 follow the instructions for creating a service account and downloading the P12 key first.创建您的服务帐户后，将电子邮件地址复制到下面的 CLIENT_ID 字段中，将您的 P12 文件添加到您的类路径中，然后输入 P12FILE 以指向您的 P12 文件。

我能够使用以下 SPREADSHEET_FEED_URL“https://spreadsheets.google.com/feeds/worksheets/:worksheetId/private/basic”来实现此功能，其中“:worksheetId”是您的工作表 ID。这与您使用的略有不同。

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

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 {
    JacksonFactory JSON_FACTORY = JacksonFactory.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;
}

}