Charts (original) (raw)

Skip to main content

• Home

• Google Sheets

  • Overview
  • Guides
  • Reference
  • Samples
  • Support

• All products

• Resources

• Overview

• Recipes

• Basic reading

• Basic writing

• Basic formatting

• Charts

• Conditional formatting

• Data operations

• Named & protected ranges

• Pivot tables

• Row & column operations

• Sheet operations

• Learning resources

• Sheets API video library

• Sheets API intro codelab

Charts

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.

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:

• Sets the chart type as a column chart.
• Adds a legend to the bottom of the chart.
• Sets the chart and axis titles.
• Configures 3 data series, representing sales for 3 different months while using default formatting and colors.

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

• Sets the chart title.
• Adds a legend to the right of the chart.
• Sets the chart as 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.
• Anchors the chart on cell C3 of the sheet specified by SHEET_ID, with a 50 pixel offset in both the X and Y directions.

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:

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

• Sets the chart type as a line chart.
• Sets the horizontal x-axis title.
• Configures a data series representing sales. It sets A1:A3 and A6:A7 as adomain, and B1:B3 and B6:B7 as aseries, while using default formatting and colors. Ranges are specified using A1 notation in the request URL.
• Anchors the chart on cell H8 of the sheet specified by SHEET_ID.

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:

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

• Sets the chart type to BAR.
• Moves the legend to the right of the chart.
• Inverts the axes so that "Sales" is on the bottom axis and "Model Numbers" is on the left axis.
• Sets the axis title format to be 24-point font, bold, and italicized.
• Removes the "W-24" data from the chart (row 7 in chart source data).

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:

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:

• Anchored to cell A5 of its original sheet.
• Offset in the X direction by 100 pixels.
• Resized to 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 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.