Charts (original) (raw)

Skip to main content

Charts

Stay organized with collections Save and categorize content based on your preferences.

The Google Sheets API lets you create and update charts within spreadsheets as needed. The examples on this page illustrate how you can achieve some common chart operations with the Sheets API.

These examples are presented in the form of HTTP requests to be language neutral. To learn how to implement a batch update in different languages using the Google API client libraries, seeUpdate spreadsheets.

In these examples, the placeholders SPREADSHEET_ID and SHEET_ID

indicates where you would provide those IDs. You can find the spreadsheet ID in the spreadsheet URL. You can get the sheet ID by using thespreadsheets.getmethod. The ranges are specified using A1 notation. An example range is Sheet1!A1:D5.

Additionally, the placeholder CHART_ID indicates the ID of a given chart. You can set this ID when creating a chart with the Sheets API, or allow Sheets API to generate one for you. You can get the IDs of existing charts with thespreadsheets.getmethod.

Finally, the placeholder SOURCE_SHEET_ID indicates your sheet with the source data. In these examples, this is the table listed under Chart source data.

Chart source data

For these examples, assume the spreadsheet being used has the following source data in its first sheet ("Sheet1"). The strings in the first row are labels for the individual columns. To view examples of how to read from other sheets in your spreadsheet, see A1 notation.

A B C D E
1 Model Number Sales - Jan Sales - Feb Sales - Mar Total Sales
2 D-01X 68 74 60 202
3 FR-0B1 97 76 88 261
4 P-034 27 49 32 108
5 P-105 46 44 67 157
6 W-11 75 68 87 230
7 W-24 74 52 62 188

Add a column chart

The followingspreadsheets.batchUpdatecode sample shows how to use theAddChartRequestto create a column chart from the source data, placing it in a new sheet. The request does the following to configure the chart:

The request protocol is shown below.

POST https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID:batchUpdate

{ "requests": [ { "addChart": { "chart": { "spec": { "title": "Model Q1 Sales", "basicChart": { "chartType": "COLUMN", "legendPosition": "BOTTOM_LEGEND", "axis": [ { "position": "BOTTOM_AXIS", "title": "Model Numbers" }, { "position": "LEFT_AXIS", "title": "Sales" } ], "domains": [ { "domain": { "sourceRange": { "sources": [ { "sheetId": SOURCE_SHEET_ID, "startRowIndex": 0, "endRowIndex": 7, "startColumnIndex": 0, "endColumnIndex": 1 } ] } } } ], "series": [ { "series": { "sourceRange": { "sources": [ { "sheetId": SOURCE_SHEET_ID, "startRowIndex": 0, "endRowIndex": 7, "startColumnIndex": 1, "endColumnIndex": 2 } ] } }, "targetAxis": "LEFT_AXIS" }, { "series": { "sourceRange": { "sources": [ { "sheetId": SOURCE_SHEET_ID, "startRowIndex": 0, "endRowIndex": 7, "startColumnIndex": 2, "endColumnIndex": 3 } ] } }, "targetAxis": "LEFT_AXIS" }, { "series": { "sourceRange": { "sources": [ { "sheetId": SOURCE_SHEET_ID, "startRowIndex": 0, "endRowIndex": 7, "startColumnIndex": 3, "endColumnIndex": 4 } ] } }, "targetAxis": "LEFT_AXIS" } ], "headerCount": 1 } }, "position": { "newSheet": true } } } } ] }

The request creates a chart in a new sheet like this:

Add column chart recipe result

Add a pie chart

The followingspreadsheets.batchUpdatecode sample shows how to use theAddChartRequestto create a 3D pie chart from the source data. The request does the following to configure the chart:

The request protocol is shown below.

POST https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID:batchUpdate

{ "requests": [ { "addChart": { "chart": { "spec": { "title": "Model Q1 Total Sales", "pieChart": { "legendPosition": "RIGHT_LEGEND", "threeDimensional": true, "domain": { "sourceRange": { "sources": [ { "sheetId": SOURCE_SHEET_ID, "startRowIndex": 0, "endRowIndex": 7, "startColumnIndex": 0, "endColumnIndex": 1 } ] } }, "series": { "sourceRange": { "sources": [ { "sheetId": SOURCE_SHEET_ID, "startRowIndex": 0, "endRowIndex": 7, "startColumnIndex": 4, "endColumnIndex": 5 } ] } }, } }, "position": { "overlayPosition": { "anchorCell": { "sheetId": SHEET_ID, "rowIndex": 2, "columnIndex": 2 }, "offsetXPixels": 50, "offsetYPixels": 50 } } } } } ] }

The request creates a chart like this:

Add pie chart recipe result

Alternatively, you can also update the legendPosition value from RIGHT_LEGENDto LABELED_LEGEND within the request so the legend values are connected to the pie chart slices.

'legendPosition': 'LABELED_LEGEND',

The updated request creates a chart like this:

Add pie chart recipe result

Add a line chart using multiple non-adjacent ranges

The followingspreadsheets.batchUpdatecode sample shows how to use theAddChartRequestto create a line chart from the source data, placing it in the source sheet. Selecting non-adjacent ranges can be used to exclude rows from theChartSourceRange.

The request does the following to configure the chart:

The request protocol is shown below.

POST https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID:batchUpdate

{ "requests": [ { "addChart": { "chart": { "spec": { "basicChart": { "chartType": "LINE", "domains": [ { "domain": { "sourceRange": { "sources": [ { "startRowIndex": 0, "endRowIndex": 3, "startColumnIndex": 0, "endColumnIndex": 1, "sheetId": SOURCE_SHEET_ID }, { "startRowIndex": 5, "endRowIndex": 7, "startColumnIndex": 0, "endColumnIndex": 1, "sheetId": SOURCE_SHEET_ID } ] } } } ], "series": [ { "series": { "sourceRange": { "sources": [ { "startRowIndex": 0, "endRowIndex": 3, "startColumnIndex": 1, "endColumnIndex": 2, "sheetId": SOURCE_SHEET_ID }, { "startRowIndex": 5, "endRowIndex": 7, "startColumnIndex": 1, "endColumnIndex": 2, "sheetId": SOURCE_SHEET_ID } ] } } } ] } }, "position": { "overlayPosition": { "anchorCell": { "sheetId": SOURCE_SHEET_ID, "rowIndex": 8, "columnIndex": 8 } } } } } } ] }

The request creates a chart in a new sheet like this:

Add non-adjacent range line chart recipe
result

Delete a chart

The followingspreadsheets.batchUpdatecode sample shows how to use theDeleteEmbeddedObjectRequestto delete a chart specified by the CHART_ID.

The request protocol is shown below.

POST https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID:batchUpdate

{ "requests": [ { "deleteEmbeddedObject": { "objectId": CHART_ID } } ] }

Edit a chart's properties

The followingspreadsheets.batchUpdatecode sample shows how to use theUpdateChartSpecRequestto edit the chart created in the Add a column chart recipe, modifying its data, type, and appearance. Subsets of chart properties cannot be changed individually. To make edits, you must supply the entire spec field with an UpdateChartSpecRequest. Essentially, editing a chart specification requires replacing it with a new one.

The following request updates the original chart (specified byCHART_ID):

The request protocol is shown below.

POST https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID:batchUpdate

{ "requests": [ { "updateChartSpec": { "chartId": CHART_ID, "spec": { "title": "Model Q1 Sales", "basicChart": { "chartType": "BAR", "legendPosition": "RIGHT_LEGEND", "axis": [ { "format": { "bold": true, "italic": true, "fontSize": 24 }, "position": "BOTTOM_AXIS", "title": "Sales" }, { "format": { "bold": true, "italic": true, "fontSize": 24 }, "position": "LEFT_AXIS", "title": "Model Numbers" } ], "domains": [ { "domain": { "sourceRange": { "sources": [ { "sheetId": SOURCE_SHEET_ID, "startRowIndex": 0, "endRowIndex": 6, "startColumnIndex": 0, "endColumnIndex": 1 } ] } } } ], "series": [ { "series": { "sourceRange": { "sources": [ { "sheetId": SOURCE_SHEET_ID, "startRowIndex": 0, "endRowIndex": 6, "startColumnIndex": 1, "endColumnIndex": 2 } ] } }, "targetAxis": "BOTTOM_AXIS" }, { "series": { "sourceRange": { "sources": [ { "sheetId": SOURCE_SHEET_ID, "startRowIndex": 0, "endRowIndex": 6, "startColumnIndex": 2, "endColumnIndex": 3 } ] } }, "targetAxis": "BOTTOM_AXIS" }, { "series": { "sourceRange": { "sources": [ { "sheetId": SOURCE_SHEET_ID, "startRowIndex": 0, "endRowIndex": 6, "startColumnIndex": 3, "endColumnIndex": 4 } ] } }, "targetAxis": "BOTTOM_AXIS" } ], "headerCount": 1 } } } } ] }

After the request the chart appears like this:

Edit chart recipe result

Move or resize a chart

The followingspreadsheets.batchUpdatecode sample shows how to use theUpdateEmbeddedObjectPositionRequestto move and resize a chart. After the request, the chart specified by CHART_IDis:

The request only changes those properties specified with the fields parameter. Other properties (such as offsetYPixels) retain their original values.

The request protocol is shown below.

POST https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID:batchUpdate

{ "requests": [ { "updateEmbeddedObjectPosition": { "objectId": CHART_ID, "newPosition": { "overlayPosition": { "anchorCell": { "rowIndex": 4, "columnIndex": 0 }, "offsetXPixels": 100, "widthPixels": 1200, "heightPixels": 742 } }, "fields": "anchorCell(rowIndex,columnIndex),offsetXPixels,widthPixels,heightPixels" } } ] }

Read chart data

The followingspreadsheets.getcode sample shows how to get chart data from a spreadsheet. The fields query parameter specifies that only the chart data should be returned.

The response to this method call is aspreadsheetobject, which contains an array ofsheet objects. Any charts present on a sheet are represented in thesheet object. If a response field is set to the default value, it's omitted from the response.

In this example, the first sheet (SOURCE_SHEET_ID) doesn't have any charts, so an empty pair of curly braces is returned. The second sheet has the chart created by Add a column chart, and nothing else.

The request protocol is shown below.

GET https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID?fields=sheets(charts)

{ "sheets": [ {}, { "charts": [ { "chartId": CHART_ID, "position": { "sheetId": SHEET_ID }, "spec": { "basicChart": { "axis": [ { "format": { "bold": false, "italic": false }, "position": "BOTTOM_AXIS", "title": "Model Numbers" }, { "format": { "bold": false, "italic": false }, "position": "LEFT_AXIS", "title": "Sales" } ], "chartType": "COLUMN", "domains": [ { "domain": { "sourceRange": { "sources": [ { "endColumnIndex": 1 "endRowIndex": 7, "sheetId": SOURCE_SHEET_ID, "startColumnIndex": 0, "startRowIndex": 0, } ] } } } ], "legendPosition": "BOTTOM_LEGEND", "series": [ { "series": { "sourceRange": { "sources": [ { "endColumnIndex": 2, "endRowIndex": 7, "sheetId": SOURCE_SHEET_ID, "startColumnIndex": 1, "startRowIndex": 0, } ] } }, "targetAxis": "LEFT_AXIS" }, { "series": { "sourceRange": { "sources": [ { "endColumnIndex": 3, "endRowIndex": 7, "sheetId": SOURCE_SHEET_ID, "startColumnIndex": 2, "startRowIndex": 0, } ] } }, "targetAxis": "LEFT_AXIS" }, { "series": { "sourceRange": { "sources": [ { "endColumnIndex": 4, "endRowIndex": 7, "sheetId": SOURCE_SHEET_ID, "startColumnIndex": 3, "startRowIndex": 0, } ] } }, "targetAxis": "LEFT_AXIS" } ] }, "hiddenDimensionStrategy": "SKIP_HIDDEN_ROWS_AND_COLUMNS", "title": "Model Q1 Sales", }, } ] } ] }

Except as otherwise noted, the content of this page is licensed under the Creative Commons Attribution 4.0 License, and code samples are licensed under the Apache 2.0 License. For details, see the Google Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2025-06-04 UTC.