通过电子表格api更新包含大量数据的谷歌电子表格的最快方法是什么? [英] What is the fastest way to update a google spreadsheet with a lot of data through the spreadsheet api?
问题描述
我正在使用 Google 电子表格 API 更新包含大量数据(数百行和大约 20 列)的电子表格.
I am using the Google Spreadsheet API to update a spreadsheet with a lot of data (hundreds of rows and around twenty columns).
我测试了批量调用以更新 2500 个单元格.调用大约需要 40 秒才能完成,请求大约为 1mb,响应大约为 2mb.
I have tested making a batch call to update 2500 cells. The call takes around 40 seconds to complete, with the request being about 1mb and the response being ~2mb.
有什么办法可以让它更快地工作吗?
Is there any way to get it to work faster?
推荐答案
我能够加快官方 API 中提供的批处理请求 http://code.google.com/apis/spreadsheets/data/3.0/developers_guide.html#SendingBatchRequests更新.所以这就是他们在示例中的内容:
I was able to speed up the batch request provided in the official API http://code.google.com/apis/spreadsheets/data/3.0/developers_guide.html#SendingBatchRequests by skipping the QUERY part before the UPDATE. So this is what they have in the example:
// Prepare the update
// getCellEntryMap is what makes the update fast.
Map cellEntries = getCellEntryMap(ssSvc, cellFeedUrl, cellAddrs);
CellFeed batchRequest = new CellFeed();
for (CellAddress cellAddr : cellAddrs) {
URL entryUrl = new URL(cellFeedUrl.toString() + "/" + cellAddr.idString);
CellEntry batchEntry = new CellEntry(cellEntries.get(cellAddr.idString));
batchEntry.changeInputValueLocal(cellAddr.idString);
BatchUtils.setBatchId(batchEntry, cellAddr.idString);
BatchUtils.setBatchOperationType(batchEntry, BatchOperationType.UPDATE);
batchRequest.getEntries().add(batchEntry);
}
// Submit the update
Link batchLink = cellFeed.getLink(Link.Rel.FEED_BATCH, Link.Type.ATOM);
CellFeed batchResponse = ssSvc.batch(new URL(batchLink.getHref()), batchRequest);
这是我改成的
CellFeed batchRequest = new CellFeed();
for (CellInfo cellAddr : cellsInfo) {
CellEntry batchEntry = new CellEntry(cellAddr.row, cellAddr.col, cellAddr.idString);
batchEntry.setId(String.format("%s/%s", worksheet.getCellFeedUrl().toString(), cellAddr.idString));
BatchUtils.setBatchId(batchEntry, cellAddr.idString);
BatchUtils.setBatchOperationType(batchEntry, BatchOperationType.UPDATE);
batchRequest.getEntries().add(batchEntry);
}
CellFeed cellFeed = ssSvc.getFeed(worksheet.getCellFeedUrl(), CellFeed.class);
Link batchLink = cellFeed.getLink(Link.Rel.FEED_BATCH, Link.Type.ATOM);
ssSvc.setHeader("If-Match", "*");
CellFeed batchResponse = ssSvc.batch(new URL(batchLink.getHref()), batchRequest);
ssSvc.setHeader("If-Match", null);
请注意,应更改标题以使其工作.
Notice, the header should be changed to make it work.
这篇关于通过电子表格api更新包含大量数据的谷歌电子表格的最快方法是什么?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!