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

Question

I played around with the google sheets api v4 because it looks quite interesting with the ability to also render charts. I'm using the google api client for php.

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

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

I always end up with the following error message:

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

The only id that I set is the one for the charts anchor cell for the second sheet I 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);

Taking a look into the spreadsheet, there is a sheet with id: 1 and it has also the type grid, so i have no idea what the problem might be here.

Update Here is the post Body of 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 it with the example, the only one I could find that covers adding charts, https://codelabs.developers.google.com/codelabs/sheets-api/#9, it looks correct to me.

score 3 · Answer 1 · answered Sep 19 '16 at 14:45

3

Ok, I found out the solution. I was thinking that the sheetId is the index of the sheet, but it's an id a sheet gets once it is created.

So the solution is to get the correct ids:

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

Those ids are generated when you upload the spreadsheet, so afaik it is not possible yet to create a sheet with its charts in one create request, but you have to create the sheets you need first and then you can add the charts in another request.

answered Sep 19 '16 at 14:45

Kasihasi

1,062
1
8
20

1

If you want to add the chart to a new sheet, you can use the `newSheet: true` property of [EmbeddedObjectPosition](https://developers.google.com/sheets/reference/rest/v4/spreadsheets#EmbeddedObjectPosition) in `AddChartRequest`. If you'd like to add the chart to an existing sheet, you'll need to specify the ID of the sheet when adding that sheet and then use the same ID for the chart's sheet ID. – Sam Berlin Sep 19 '16 at 16:05
Thanks Sam! Good call! – Kasihasi Sep 27 '16 at 13:30

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

1 Answers1