Google sheet API:在数据透视表中单击时无法加载文件 [英] Google sheet API : Unable to Load File when clicked in Pivot table

问题描述

这是一个将数据透视表追加到Google工作表的函数.并创建一个数据透视表，如下所示: .

This is a function that appends Pivot table to google sheet. And it creates a pivot table as shown: .

public void appendPivotTableTemplate(String  spreadsheetId,int sheetid , String title) throws Exception{
        List<Request> sheetsRequests = new ArrayList<>();
        List<RowData> rdata = new ArrayList<>();
        List<CellData> cdata = new ArrayList<>();
        List<PivotGroup> pgroup_row = new ArrayList<>();
        List<PivotGroup> pgroup_col = new ArrayList<>();
        Map<String, PivotFilterCriteria> colcriteria = new HashMap<>();
        List<String> colval = Arrays.asList("Completed in Time", "Completed after Time", "Not completed");

        List<PivotValue> pvalue = new ArrayList<>();

        colcriteria.put("5",new PivotFilterCriteria().setVisibleValues(colval)); // 5 is the column offset for the Status( (26,5) in Grid Offset)

        pgroup_row.add(new PivotGroup()
                .setSourceColumnOffset(2) // for week
                .setShowTotals(false)
                .setSortOrder("DESCENDING")

        );

        pgroup_col.add(new PivotGroup()
                .setSourceColumnOffset(5) // for status
                .setShowTotals(false)
                .setSortOrder("ASCENDING"));

        pvalue.add(new PivotValue().setSourceColumnOffset(5).setSummarizeFunction("COUNTA").setName("Count of Task")); // https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/pivot-tables#PivotValueSummarizeFunction

        cdata.add(new CellData().setPivotTable(new
                PivotTable()
                .setSource(new GridRange()
                    .setSheetId(sheetid)
                    .setStartRowIndex(24)
                    .setStartColumnIndex(0)
                    .setEndColumnIndex(6))
                .setRows(pgroup_row)
                .setColumns(pgroup_col)
                .setValues(pvalue)
                .setCriteria(colcriteria)


        ));

数据透视表在源数据所在的同一工作表中创建.问题是，每当我单击数据透视表中的任何单元格时，它就会弹出无法加载文件的状态，并要求随时刷新页面.

The pivot table is created in the same sheet where the source data is. The issue is whenever I click any cells in the pivot table, It pops unable to load file and asks to refresh the page anytime.

这是Google Sheet API的问题吗?还是我在这里想念什么?

Is this issue with google sheet API? Or am I missing something here?

推荐答案

我找到了解决方案.那将设置setEndRowIndex.

I found the solution to this. That would be setting the setEndRowIndex.

.setSheetId(sheetid)
.setStartRowIndex(24)
.setEndRowIndex(1000) // or the limit to your rowIndex
.setStartColumnIndex(0)
.setEndColumnIndex(6))

这篇关于Google sheet API:在数据透视表中单击时无法加载文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！