使用Google Sheets API v4将新数据插入Google Spreadsheet的顶部 [英] Insert New Data to Top of Google Spreadsheet using Google Sheets API v4

问题描述

我正在使用Google表格来跟踪我网站上的最近订单.我想将每个新订单的详细信息添加到Google电子表格中，并在工作表顶部显示最新的订单详细信息，在底部显示较旧的条目.

I'm using Google Sheets to keep track of recent orders on my website. I'd like to add details of each new order to a Google spreadsheet and have the most recent order details show up on top of the sheet and the older entries on the bottom.

我知道我可以使用以下代码在不知道/不为Google提供行号的情况下，在最后一行之后添加新行:

I'm aware I could append new row after last row without knowing/providing Google with the row number using the following code:

// INSERTS NEW DATA AFTER LAST ROW
ValueRange body = new ValueRange().setValues(writeData);
AppendValuesResponse appendValuesResponse =
        service.spreadsheets().values().append(spreadsheetId, range, body)
                .setValueInputOption(valueInputOption)
                .execute();

但是，这意味着将较新的条目添加到工作表的底部而不是顶部.我的要求恰恰相反.我需要在工作表的顶部添加/添加较新的条目，并将较旧的条目移至底部.

However, this would mean that the newer entries are added to the bottom of the sheet instead of the top. My requirements are exactly the opposite. I need to add/append newer entries to the top of the sheet and have older entries move to the bottom.

是否有一种使用API​​原生完成此操作的方法?

Is there a way to accomplish this natively using the API?

推荐答案

我有一个非常相似的要求，我将分享解决方法.

I had a very similar requirement and I am sharing how I solved it.

我知道我想在顶部插入的行数.我在标题下方的顶部插入了空白行.我根据行数设置 startIndex 和 endIndex ，并在电子表格上请求BatchUpdate.然后使用值更新范围(此调用后现在为空白).

I know the number of rows I want to insert at the top. I inserted blank rows at the top under the headers. I set startIndex and endIndex based on row count and request a BatchUpdate on the Spreadsheet. Then updated the range (which are now blank after this call) with values.

    log.trace("Inserting Blank Row At the Top of Sheet");

    DimensionRange dimensionRange = new DimensionRange();
    dimensionRange.setDimension("ROWS");
    dimensionRange.setStartIndex(1); // depends on your requirement
    dimensionRange.setEndIndex(10);  // depends on your requirement

    InsertDimensionRequest insertDimension = new InsertDimensionRequest();
    insertDimension.setRange(dimensionRange);

    Request insertionRequest = new Request();
    insertionRequest.setInsertDimension(insertDimension);

    BatchUpdateSpreadsheetRequest batchUpdateSpreadsheetRequest = new BatchUpdateSpreadsheetRequest();
    batchUpdateSpreadsheetRequest.setRequests(Arrays.asList(insertionRequest));

    try {
        BatchUpdateSpreadsheetResponse execute = sheets.spreadsheets()
                .batchUpdate(spreadsheetId, batchUpdateSpreadsheetRequest)
                .execute();
    } catch (IOException e) {
        log.error("Exception Occurred While Inserting Blank Row - Details: {}", e.getMessage());
        e.printStackTrace();
    }

Google参考文档: InsertDimensionRequest-Sheets API参考

Google Reference Doc: InsertDimensionRequest - Sheets API Reference

注意:C#的此答案帮助我使用了以下Java解决方案: https://stackoverflow.com/a/53587428/7878602

Note: This answer for C# helped me come with this Java solution: https://stackoverflow.com/a/53587428/7878602

这篇关于使用Google Sheets API v4将新数据插入Google Spreadsheet的顶部的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！