无法在使用SpreadSheet API时完成HTTP请求 [英] Unable to complete the HTTP request when using SpreadSheet API

问题描述

我正在开发一个Google App Engine应用程序，该应用程序可读取并编辑大约150列和500行的大型电子表格。除了特定的大小（可能会有所不同），我正在寻找一种提高性能的方法，因为大部分时间我都会遇到500内部服务器错误（如下所示）。

java.lang.RuntimeException：无法完成HTTP请求导致
by：java.net.SocketTimeoutException：获取URL时超时：
https://spreadsheets.google.com/feeds/worksheets/xxxxxxxxxxxxxxxxxxxxxxx/private/full

在下面的代码片段中，您可以看到我是如何读取SpreadSheet以及哪一行引发异常的。

  for（SpreadsheetEntry entry：spreadsheets）{
 if（entry.getTitle（）。getPlainText（）。compareTo（spreadsheetname）== 0）{
 spreadsheet = entry; 
} 
} 
 
 WorksheetFeed worksheetFeed = service.getFeed（spreadsheet.getWorksheetFeedUrl（），WorksheetFeed.class）; 
列表< WorksheetEntry>工作表= worksheetFeed.getEntries（）; 
 WorksheetEntry worksheet = worksheets.get（0）; 
 
 URL listFeedUrl = worksheet.getListFeedUrl（）; 
 //以下行是产生错误的行
 ListFeed listFeed = service.getFeed（listFeedUrl，ListFeed.class）; （ListEntry row：listFeed.getEntries（））{
 String content = row.getCustomElements（）。getValue（rowname）; 
 
 
 String content2 = row.getCustomElements（）。getValue（rowname2）; 
} 
  

我已经使用结构化查询改进了性能。基本上我在URL中应用了过滤器，并且允许我仅检索我需要的几行。请注意，无论如何，我仍然会收到上述错误。

  URL listFeedUrl = new URI（worksheet.getListFeedUrl（）。 toString（）+？sq = rowname =+ URLEncoder.encode（\+ filter +\）。toString（））。toURL（）; 
  

然而，我的问题是不同的，首先有一些时候我必须读取所有行，但只有几列（约5）。我仍然需要找到一种方法来实现这一点，我知道有另一个参数tq允许选择列，但该语句需要字母符号（如A，B，AA），我想用列名。

最重要的是我需要摆脱500内部服务器错误。由于它听起来像是一个超时问题，我想将这个值增加到合理的时间。我的用户也可以等待几秒钟，因为它看起来完全是随机的。当它工作时，它会在大约2-3秒内加载页面。当它不起作用，但我得到一个500内部服务器错误，这将是最终用户令人沮丧。

任何想法？我在App Engine设置中找不到任何内容。到目前为止，唯一的想法是将电子表格分成多个电子表格（或工作表），以便读取较少的列。但是，如果有一个选项可以让我增加Timeout，那就太棒了。

编辑：我在网路上四处张望，我可能找到了一些可以帮我。

  //设置超时
 $ b我发现服务对象提供了一个setConnectionTimeout方法， $ b int timeout = 60000; 
 service.setConnectTimeout（timeout）; 
  

解决方案

超时

我用10秒的时间重试。它适合我。

纸张大小

我一次使用了80,000个单元格。它工作正常，我没有看到重试失败。我正在使用CellFeed，而不是ListFeed。

是的，它不喜欢大单张，1000单元左右的小单更快。即使我只写入纸张的一部分，小纸张也要快得多。 （感觉像重新计算整张纸一样，因为看起来没有数据量，但我不确定）

指数回退

Zig提出了一种指数回退 - 对数字感兴趣 - 什么超时值和失败率指数回退 - 还有纸张大小的影响。

我怀疑以3秒的时间开始，并且每次重试都可能有效，但还没有测试过。

I am developing a Google App Engine application which reads and edits a big SpreadSheet with around 150 columns and 500 rows. Beside the specific size (it may vary) I am looking for a way to improve performance since most of the times I get a 500 Internal Server Error (as you can see below).

java.lang.RuntimeException: Unable to complete the HTTP request Caused by: java.net.SocketTimeoutException: Timeout while fetching URL: https://spreadsheets.google.com/feeds/worksheets/xxxxxxxxxxxxxxxxxxxxxxx/private/full

In the code snippet below you can see how I read my SpreadSheet and which line throws the exception.

for (SpreadsheetEntry entry : spreadsheets) {
    if (entry.getTitle().getPlainText().compareTo(spreadsheetname) == 0) {
        spreadsheet = entry;
    }
}

WorksheetFeed worksheetFeed = service.getFeed(spreadsheet.getWorksheetFeedUrl(), WorksheetFeed.class);
List<WorksheetEntry> worksheets = worksheetFeed.getEntries();
WorksheetEntry worksheet = worksheets.get(0);

URL listFeedUrl = worksheet.getListFeedUrl();
// The following line is the one who generates the error
ListFeed listFeed = service.getFeed(listFeedUrl, ListFeed.class);

for (ListEntry row : listFeed.getEntries()) {
    String content = row.getCustomElements().getValue("rowname");
    String content2 = row.getCustomElements().getValue("rowname2");
}

I already improved the performance using structured queries. Basically I apply filters within the URL and that allows me to only retrieve the few rows I need. Please notice that I still get the above error sometimes no matter what.

URL listFeedUrl = new URI(worksheet.getListFeedUrl().toString() + "?sq=rowname=" + URLEncoder.encode("\"" + filter+ "\"").toString()).toURL();

My problem however is different, first of all there are certain times where I must read ALL rows but only FEW columns (around 5). I still need to find a way to achieve that, I do know that there is another parameter "tq" which allows to select columns but that statement requires the letter notation (such as A,B,AA), I'd like to use column names instead.

Most important I need to get rid of the 500 Internal Server Error. Since it sounds like a Timeout problem I'd like to increase that value to a resonable amount of time. My users can wait for a few seconds also because it seems completely random. When it works it loads the page in around 2-3 seconds. When it doesn't work however I get a 500 Internal Server Error which is going to be really frustrating for the enduser.

Any idea? I couldn't find anything on the App Engine settings. The only idea I had so far is to split the spreadsheet in multiple spreadsheets (or worksheets) in order to read less columns. However if there's an option that can allow me to increase the Timeout it would be awesome.

EDIT: I was looking around on the Internet and I may have found something that can help me. I just found out service object offers a setConnectionTimeout method, testing it right away.

// Set timeout

int timeout = 60000;
service.setConnectTimeout(timeout);

解决方案

Time Out

I use a 10 Second time out with a retry. It works ok for me.

Sheet size

I have used it with 80,000 cells at a time. It works fine, I have not seen the retry fail. I am using CellFeed, not ListFeed.

Yes, it does not like large sheets, small sheets of 1000 cells or so are much faster. Even if I only write to part of the sheet, small sheets are much faster. (Feels like it recalculates whole sheets, as does not look to be down to data volume, but I am not sure)

Exponential backoff

Zig suggests an exponential backoff - would be be interested in numbers - what timeout values and failure rates people get with exponential backoff - also the impact of sheet size.

I suspect start with a 3 Second Time out and double with every retry might work, but have not tested it.

这篇关于无法在使用SpreadSheet API时完成HTTP请求的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！