通过电子表格API更新大量数据的Google电子表格的最快方法是什么？ [英] What is the fastest way to update a google spreadsheet with a lot of data through the spreadsheet api?

问题描述

我使用Google Spreadsheet 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 在UPDATE之前跳过QUERY部分。所以这就是他们在这个例子中的内容：

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更新大量数据的Google电子表格的最快方法是什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！