Google sheet API : Unable to Load File when clicke

问题:

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.

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

回答1:

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))