Java到Google Spreadsheet [英] Java to Google Spreadsheet
问题描述
我试图用Java编程来连接到Google Spreadsheet来执行数据检索或修改单元格中的数据。
我的Google电子表格链接是 https://docs.google.com/spreadsheets/d/1UXoGD2gowxZ2TY3gooI9y7rwWTPBOA0dnkeNYwUqQRA
我查看了表格API ，它需要链接像
https://spreadsheets.google.com/feeds/worksheets/key/private/full
我尝试过不同形式的链接，例如：
-
https://spreadsheets.google.com/feeds/worksheets/1UXoGD2gowxZ2TY3gooI9y7rwWTPBOA0dnkeNYwUqQRA/private/full -
https://spreadsheets.google.com/feeds/worksheets/1UXoGD2gowxZ2TY3gooI9y7rwWTPBOA0dnkeNYwUqQRA/private/full
他们分别给了我不同的错误：
-
com.google.gdata.util.ParseException：无法识别的内容类型：application / binary -
com。 google.gdata.util.RedirectRequiredException：暂时移动
我不知道如何连接Google Googl使用Java的电子表格。请帮助我，如果你有这方面的经验。
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;
导入java.net。*;
import java.util。*;
公共类TestGoogleSheetsAPI {
@Test
公共无效testConnectToSpreadSheet（）抛出ServiceException，IOException异常{
SpreadsheetService服务=新SpreadsheetService（谷歌电子表格）;
URL SPREADSHEET_FEED_URL =新的URL（ https://spreadsheets.google.com/feeds/worksheets/1UXoGD2gowxZ2TY3gooI9y7rwWTPBOA0dnkeNYwUqQRA/public/full）;
SpreadsheetFeed feed = service.getFeed（SPREADSHEET_FEED_URL，SpreadsheetFeed.class）;
列表< SpreadsheetEntry> spreadsheets = feed.getEntries（）;
if（spreadsheets.size（）== 0）{
// TODO：没有电子表格，按照相应的步骤操作。
}
SpreadsheetEntry spreadsheet = spreadsheets.get（0）;
System.out.println（spreadsheet.getTitle（）。getPlainText（））;
$ b 我没有使用 service.setUserCredentials（xxx @ gmail，password）因为我将有另一个错误，它是 com.google.gdata.util.AuthenticationException：错误验证（检查服务名称）
解决方案您正在获取重定向，因为访问电子表格需要先进行身份验证。 Google表格使用旧的gdata API，但要求您使用OAuth 2.0进行身份验证。因此，您需要导入gdata和Google API库，如下所示：
<依赖关系>
< dependency>
< groupId> com.google.gdata< / groupId>
< artifactId>核心< / artifactId>
< version> 1.47.1< / version>
< /依赖关系>
< dependency>
< groupId> com.google.api-client< / groupId>
< artifactId> google-api-client-java6< / artifactId>
< version> 1.20.0< / version>
< /依赖关系>
< /依赖关系>
下面的代码显示了您如何使用OAuth对Google进行身份验证。您需要按照说明创建服务帐户并首先下载P12密钥。创建你的服务帐户后，将电子邮件地址复制到下面的CLIENT_ID字段中，将你的P12文件添加到你的类路径中，并将P12FILE添加到你的P12文件中。
能够得到这个工作有以下SPREADSHEET_FEED_URL https://spreadsheets.google。 com / feeds / worksheets /：worksheetId / private / basic 其中：worksheetId是您的工作表ID。这与你使用的稍有不同。
务必确保您的服务帐户有权读取或写入电子表格，方法是先与服务帐户电子邮件地址共享。
public class GoogleSheetsApiTest {
//生成服务帐号和P12键：
// https：// developers.google.com/identity/protocols/OAuth2ServiceAccount
private final String CLIENT_ID =<您的服务帐户电子邮件地址>;
//添加请求的范围。
private final List< String> SCOPES = Arrays
.asList（https://spreadsheets.google.com/feeds）;
//获取服务帐户时创建的p12文件的名称
private final String P12FILE =/<您的p12文件名称> .p12;
$ b @Test
public void testConnectToSpreadSheet（）throws GeneralSecurityException，
IOException，ServiceException，URISyntaxException {
SpreadsheetService service = new SpreadsheetService
google-spreadsheet）;
GoogleCredential凭证= getCredentials（）;
service.setOAuth2Credentials（凭证）;
URL SPREADSHEET_FEED_URL =新网址（
https://spreadsheets.google.com/feeds/worksheets/1UXoGD2gowxZ2TY3gooI9y7rwWTPBOA0dnkeNYwUqQRA/private/basic）;
SpreadsheetFeed feed = service.getFeed（SPREADSHEET_FEED_URL，
SpreadsheetFeed.class）;
列表< SpreadsheetEntry> spreadsheets = feed.getEntries（）;
if（spreadsheets.size（）== 0）{
// // TODO：没有电子表格，按照相应的步骤行事。
}
//
SpreadsheetEntry spreadsheet = spreadsheets.get（0）;
System.out.println（spreadsheet.getTitle（）。getPlainText（））;
$ b私有GoogleCredential getCredentials（）抛出GeneralSecurityException，
IOException，URISyntaxException {
JacksonFactory JSON_FACTORY = JacksonFactory.getDefaultInstance（）;
HttpTransport httpTransport = GoogleNetHttpTransport
.newTrustedTransport（）;
URL fileUrl = this.getClass（）。getResource（P12FILE）;
GoogleCredential凭证=新GoogleCredential.Builder（）
.setTransport（httpTransport）
.setJsonFactory（JSON_FACTORY）
.setServiceAccountId（CLIENT_ID）
.setServiceAccountPrivateKeyFromP12File（
new File（fileUrl.toURI（）））
.setServiceAccountScopes（SCOPES）.build（）;
返回凭证;
}
}
I was trying to do programming using Java to connect to Google Spreadsheet to do data retrieval or modifying data in the cells.
My Google spreadsheet link is https://docs.google.com/spreadsheets/d/1UXoGD2gowxZ2TY3gooI9y7rwWTPBOA0dnkeNYwUqQRA
I looked at the Sheets API and it requires link like
https://spreadsheets.google.com/feeds/worksheets/key/private/full
I have tried different forms of the links, such as:
https://spreadsheets.google.com/feeds/worksheets/1UXoGD2gowxZ2TY3gooI9y7rwWTPBOA0dnkeNYwUqQRA/private/full
https://spreadsheets.google.com/feeds/worksheets/1UXoGD2gowxZ2TY3gooI9y7rwWTPBOA0dnkeNYwUqQRA/private/full
They gave me different kinds of errors respectively:
com.google.gdata.util.ParseException: Unrecognized content type:application/binarycom.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.
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)
解决方案 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:
<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.
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.
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 {
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;
}
}
这篇关于Java到Google Spreadsheet的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
