The Sheets API lets you create and update charts within spreadsheets as needed. The examples on this page illustrate how you can achieve some common charts operations with the API.
In these examples, the placeholders spreadsheetId and sheetId are used to indicate where you would provide those IDs. The spreadsheet ID can be discovered from the spreadsheet URL; the sheet ID can be obtained from the spreadsheet.get method.
The placeholder chartId is also used to indicate the ID of a given chart. You can set this ID when creating a chart with API, or allow the API to generate one for you. You can read the IDs of existing charts with the spreadsheet.get method.
Chart source data
For these examples, assume the spreadsheet being used has the following source data in its first sheet (whose ID is sourceSheetId). The strings in the first row are labels for the individual columns.
| 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 following spreadsheets.batchUpdate request creates a new column chart from the source data, placing it in a new sheet. The request does the following to configure the chart:
- Sets the chart type to be a column chart.
- Configures three data series, representing sales for three different months and using default formatting and colors.
- Sets a chart and axis titles.
- Sets a legend at the bottom of the chart.
The request protocol is shown below. The Updating Spreadsheets guide shows how to implement a batch update in different languages using the Google API client libraries.
POST https://sheets.googleapis.com/v4/spreadsheets/spreadsheetId: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": sourceSheetId,
"startRowIndex": 0,
"endRowIndex": 7,
"startColumnIndex": 0,
"endColumnIndex": 1
}
]
}
}
}
],
"series": [
{
"series": {
"sourceRange": {
"sources": [
{
"sheetId": sourceSheetId,
"startRowIndex": 0,
"endRowIndex": 7,
"startColumnIndex": 1,
"endColumnIndex": 2
}
]
}
},
"targetAxis": "LEFT_AXIS"
},
{
"series": {
"sourceRange": {
"sources": [
{
"sheetId": sourceSheetId,
"startRowIndex": 0,
"endRowIndex": 7,
"startColumnIndex": 2,
"endColumnIndex": 3
}
]
}
},
"targetAxis": "LEFT_AXIS"
},
{
"series": {
"sourceRange": {
"sources": [
{
"sheetId": sourceSheetId,
"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 that looks like this:
Add a pie chart
The following spreadsheets.batchUpdate request creates a new 3D pie chart from the source data. The request does the following to configure the chart:
- Sets the chart to be a 3D pie chart. Note that 3D pie charts cannot have a "donut hole" in the center the way flat pie charts can.
- Sets the chart data series as the total sales for each model number.
- Sets the chart title.
- Sets a legend at the right of the chart.
- Anchors the chart on cell C3 of the sheet specified by sheetId, with a 50 pixel offset in both the X and Y directions.
The request protocol is shown below. The Updating Spreadsheets guide shows how to implement a batch update in different languages using the Google API client libraries.
POST https://sheets.googleapis.com/v4/spreadsheets/spreadsheetId:batchUpdate
{
"requests": [
{
"addChart": {
"chart": {
"spec": {
"title": "Model Q1 Total Sales",
"pieChart": {
"legendPosition": "RIGHT_LEGEND",
"threeDimensional": true,
"domain": {
"sourceRange": {
"sources": [
{
"sheetId": sourceSheetId,
"startRowIndex": 0,
"endRowIndex": 7,
"startColumnIndex": 0,
"endColumnIndex": 1
}
]
}
},
"series": {
"sourceRange": {
"sources": [
{
"sheetId": sourceSheetId,
"startRowIndex": 0,
"endRowIndex": 7,
"startColumnIndex": 4,
"endColumnIndex": 5
}
]
}
},
}
},
"position": {
"overlayPosition": {
"anchorCell": {
"sheetId": sheetId,
"rowIndex": 2,
"columnIndex": 2
},
"offsetXPixels": 50,
"offsetYPixels": 50
}
}
}
}
}
]
}
The request creates a chart that looks like this:
Delete a chart
The following spreadsheets.batchUpdate request deletes the chart specified by chartId.
The request protocol is shown below. The Updating Spreadsheets guide shows how to implement a batch update in different languages using the Google API client libraries.
POST https://sheets.googleapis.com/v4/spreadsheets/spreadsheetId:batchUpdate
{
"requests": [
{
"deleteEmbeddedObject": {
"objectId": chartId
}
}
]
}
Edit a chart's properties
The following spreadsheets.batchUpdate
request edits the chart created in the
Add a column chart recipe, modifing
its data, type and appearance. Subsets of chart properties cannot be changed
individually; you must supply the entire spec field with a
updateChartSpec
request to make edits. Essentially, editing a chart specification requires
replacing it with a new one.
The following request makes these changes to the original chart (specified by chartId):
- Drops the "W-24" data from the chart (that is, row 7 in the chart source data).
- Changes the chart type to "BAR".
- Inverts the axes so that "Sales" is on the horizontal axis and "Model Number" in on the vertical axis.
- Changes the format of the axis titles to be 24 point font, bold, and italized.
- Moves the legend to be on the right.
The request protocol is shown below. The Updating Spreadsheets guide shows how to implement a batch update in different languages using the Google API client libraries.
POST https://sheets.googleapis.com/v4/spreadsheets/spreadsheetId:batchUpdate
{
"requests": [
{
"updateChartSpec": {
"chartId": chartId,
"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": sourceSheetId,
"startRowIndex": 0,
"endRowIndex": 6,
"startColumnIndex": 0,
"endColumnIndex": 1
}
]
}
}
}
],
"series": [
{
"series": {
"sourceRange": {
"sources": [
{
"sheetId": sourceSheetId,
"startRowIndex": 0,
"endRowIndex": 6,
"startColumnIndex": 1,
"endColumnIndex": 2
}
]
}
},
"targetAxis": "BOTTOM_AXIS"
},
{
"series": {
"sourceRange": {
"sources": [
{
"sheetId": sourceSheetId,
"startRowIndex": 0,
"endRowIndex": 6,
"startColumnIndex": 2,
"endColumnIndex": 3
}
]
}
},
"targetAxis": "BOTTOM_AXIS"
},
{
"series": {
"sourceRange": {
"sources": [
{
"sheetId": sourceSheetId,
"startRowIndex": 0,
"endRowIndex": 6,
"startColumnIndex": 3,
"endColumnIndex": 4
}
]
}
},
"targetAxis": "BOTTOM_AXIS"
}
],
"headerCount": 1
}
}
}
}
]
}
After this request the chart looks like this:
Move or resize a chart
The following spreadsheets.batchUpdate request moves and resizes a chart. After the request, the chart specified by chartId is:
- Anchored to cell A5 of the same sheet it was on originally.
- Offset in the X direction by 100 pixels.
- Resized to be 1200 by 742 pixels (the default size for a chart is 600 by 371 pixels).
The request only changes those properties specified with the fields parameter.
Other properties (such as offsetYPixels) retain the values they had prior to
the request.
The request protocol is shown below. The Updating Spreadsheets guide shows how to implement a batch update in different languages using the Google API client libraries.
POST https://sheets.googleapis.com/v4/spreadsheets/spreadsheetId:batchUpdate
{
"requests": [
{
"updateEmbeddedObjectPosition": {
"objectId": chartId,
"newPosition": {
"overlayPosition": {
"anchorCell": {
"rowIndex": 4,
"columnIndex": 0
},
"offsetXPixels": 100,
"widthPixels": 1200,
"heightPixels": 742
}
},
"fields": "anchorCell(rowIndex,columnIndex),offsetXPixels,widthPixels,heightPixels"
}
}
]
}
Read chart data
The following spreadsheets.get
request gets 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 a Spreadsheet
object, which contains an array of Sheet
objects. Any charts present on a given sheet are represented in the
Sheet object. If a given
response field is currently set to the default value, it is omitted from the
response.
In this example, the first sheet (sourceSheetId) does not have any charts, so is returned as an empty pair of braces. The second sheet has the chart created by Add a column chart, and nothing else.
GET https://sheets.googleapis.com/v4/spreadsheets/spreadsheetId?fields=sheets(charts)
{
"sheets": [
{},
{
"charts": [
{
"chartId": chartId,
"position": {
"sheetId": sheetId
},
"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": sourceSheetId,
"startColumnIndex": 0,
"startRowIndex": 0,
}
]
}
}
}
],
"legendPosition": "BOTTOM_LEGEND",
"series": [
{
"series": {
"sourceRange": {
"sources": [
{
"endColumnIndex": 2,
"endRowIndex": 7,
"sheetId": sourceSheetId,
"startColumnIndex": 1,
"startRowIndex": 0,
}
]
}
},
"targetAxis": "LEFT_AXIS"
},
{
"series": {
"sourceRange": {
"sources": [
{
"endColumnIndex": 3,
"endRowIndex": 7,
"sheetId": sourceSheetId,
"startColumnIndex": 2,
"startRowIndex": 0,
}
]
}
},
"targetAxis": "LEFT_AXIS"
},
{
"series": {
"sourceRange": {
"sources": [
{
"endColumnIndex": 4,
"endRowIndex": 7,
"sheetId": sourceSheetId,
"startColumnIndex": 3,
"startRowIndex": 0,
}
]
}
},
"targetAxis": "LEFT_AXIS"
}
]
},
"hiddenDimensionStrategy": "SKIP_HIDDEN_ROWS_AND_COLUMNS",
"title": "Model Q1 Sales",
},
}
]
}
]
}