When I try to programmatically add a chart to google spreadsheet, api v4 with Google_Service_Exception about grid id error

Question

I played with google sheets api v4 because it looks pretty interesting with the ability to render charts. I am using google api client for php.

First of all, I created a new table with two sheets and filled in the data on the first sheet. This worked as expected.

Then I wanted to display a chart based on the data on the first sheet, on the second sheet. I wanted to start the easy way with a pie chart because you only have one series of data.

I always get the following error message:

"message": "Invalid requests [0] .addChart: No grid with ID: 1"

The only identifier I set is the chart binding cell for the second sheet already created:

$googleSheetsSheetGridCoordinate = new Google_Service_Sheets_GridCoordinate();
$googleSheetsSheetGridCoordinate->setSheetId(1);
$googleSheetsSheetGridCoordinate->setColumnIndex(0);
$googleSheetsSheetGridCoordinate->setRowIndex(0);

$googleSheetsSheetOverlayPosition = new Google_Service_Sheets_OverlayPosition();
$googleSheetsSheetOverlayPosition->setAnchorCell($googleSheetsSheetGridCoordinate);
$googleSheetsSheetOverlayPosition->setHeightPixels(500);
$googleSheetsSheetOverlayPosition->setWidthPixels(700);

Looking at the spreadsheet, there is a sheet with id: 1, and it also has a type grid, so I have no idea what might be here.

Update. Here you can send a message about my addChart request:

{
   "requests":[
      {
         "addChart":{
            "chart":{
               "spec":{
                  "title":"Pie Chart",
                  "pieChart":{
                     "legendPosition":"BOTTOM_LEGEND",
                     "domain":{
                        "sourceRange":{
                           "sources":[
                              {
                                 "endRowIndex":3,
                                 "sheetId":0,
                                 "startColumnIndex":0,
                                 "startRowIndex":2
                              }
                           ]
                        }
                     },
                     "series":{
                        "sourceRange":{
                           "sources":{
                              "endRowIndex":4,
                              "sheetId":0,
                              "startColumnIndex":0,
                              "startRowIndex":3
                           }
                        }
                     }
                  }
               },
               "position":{
                  "overlayPosition":{
                     "heightPixels":500,
                     "widthPixels":700,
                     "anchorCell":{
                        "columnIndex":0,
                        "rowIndex":0,
                        "sheetId":1
                     }
                  }
               }
            }
         }
      }
   ]
}

When I compare this with an example, the only thing I could find that included adding diagrams, https://codelabs.developers.google.com/codelabs/sheets-api/#9 , it looks right for me.

Answer 1

Ok, I found a solution. I thought sheetId is the index of the sheet, but this is the id that the sheet gets after it is created.

, , :

$sourceId = $googleSheetsSpreadsheet->getSheets()[0]->getProperties()->getSheetId();
$targetId = $googleSheetsSpreadsheet->getSheets()[1]->getProperties()->getSheetId();

, afaik , , .