Class EmbeddedChartBuilder

EmbeddedChartBuilder

Builder used to edit an EmbeddedChart . Changes made to the chart are not saved until Sheet.updateChart(chart) is called on the rebuilt chart.

 var 
  
 sheet 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSheet 
 (); 
 var 
  
 range 
  
 = 
  
 sheet 
 . 
 getRange 
 ( 
 "A1:B8" 
 ); 
 var 
  
 chart 
  
 = 
  
 sheet 
 . 
 getCharts 
 ()[ 
 0 
 ]; 
 chart 
  
 = 
  
 chart 
 . 
 modify 
 () 
  
 . 
 addRange 
 ( 
 range 
 ) 
  
 . 
 setOption 
 ( 
 'title' 
 , 
  
 'Updated!' 
 ) 
  
 . 
 setOption 
 ( 
 'animation.duration' 
 , 
  
 500 
 ) 
  
 . 
 setPosition 
 ( 
 2 
 , 
 2 
 , 
 0 
 , 
 0 
 ) 
  
 . 
 build 
 (); 
 sheet 
 . 
 updateChart 
 ( 
 chart 
 );

Methods

Method	Return type	Brief description
`addRange(range)`	`EmbeddedChartBuilder`	Adds a range to the chart this builder modifies.
`asAreaChart()`	`EmbeddedAreaChartBuilder`	Sets the chart type to AreaChart and returns an `EmbeddedAreaChartBuilder` .
`asBarChart()`	`EmbeddedBarChartBuilder`	Sets the chart type to BarChart and returns an `EmbeddedBarChartBuilder` .
`asColumnChart()`	`EmbeddedColumnChartBuilder`	Sets the chart type to ColumnChart and returns an `EmbeddedColumnChartBuilder` .
`asComboChart()`	`EmbeddedComboChartBuilder`	Sets the chart type to ComboChart and returns an `EmbeddedComboChartBuilder` .
`asHistogramChart()`	`EmbeddedHistogramChartBuilder`	Sets the chart type to HistogramChart and returns an `EmbeddedHistogramChartBuilder` .
`asLineChart()`	`EmbeddedLineChartBuilder`	Sets the chart type to LineChart and returns an `EmbeddedLineChartBuilder` .
`asPieChart()`	`EmbeddedPieChartBuilder`	Sets the chart type to PieChart and returns an `EmbeddedPieChartBuilder` .
`asScatterChart()`	`EmbeddedScatterChartBuilder`	Sets the chart type to ScatterChart and returns an `EmbeddedScatterChartBuilder` .
`asTableChart()`	`EmbeddedTableChartBuilder`	Sets the chart type to TableChart and returns an `EmbeddedTableChartBuilder` .
`build()`	`EmbeddedChart`	Builds the chart to reflect all changes made to it.
`clearRanges()`	`EmbeddedChartBuilder`	Removes all ranges from the chart this builder modifies.
`getChartType()`	`ChartType`	Returns the current chart type.
`getContainer()`	`ContainerInfo`	Return the chart `ContainerInfo` , which encapsulates where the chart appears on the sheet.
`getRanges()`	`Range[]`	Returns a copy of the list of ranges currently providing data for this chart.
`removeRange(range)`	`EmbeddedChartBuilder`	Removes the specified range from the chart this builder modifies.
`setChartType(type)`	`EmbeddedChartBuilder`	Changes the type of chart.
`setHiddenDimensionStrategy(strategy)`	`EmbeddedChartBuilder`	Sets the strategy to use for hidden rows and columns.
`setMergeStrategy(mergeStrategy)`	`EmbeddedChartBuilder`	Sets the merge strategy to use when more than one range exists.
`setNumHeaders(headers)`	`EmbeddedChartBuilder`	Sets the number of rows or columns of the range that should be treated as headers.
`setOption(option, value)`	`EmbeddedChartBuilder`	Sets advanced options for this chart.
`setPosition(anchorRowPos, anchorColPos, offsetX, offsetY)`	`EmbeddedChartBuilder`	Sets the position, changing where the chart appears on the sheet.
`setTransposeRowsAndColumns(transpose)`	`EmbeddedChartBuilder`	Sets whether the chart's rows and columns are transposed.

Detailed documentation

`addRange(range)`

Adds a range to the chart this builder modifies. Does not add the range if it has already been added to the chart.

 var 
  
 ss 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSpreadsheet 
 (); 
 var 
  
 sheet 
  
 = 
  
 ss 
 . 
 getSheets 
 ()[ 
 0 
 ]; 
 var 
  
 chart 
  
 = 
  
 sheet 
 . 
 newChart 
 () 
  
 . 
 setChartType 
 ( 
 Charts 
 . 
 ChartType 
 . 
 BAR 
 ) 
  
 . 
 addRange 
 ( 
 sheet 
 . 
 getRange 
 ( 
 "A1:B8" 
 )) 
  
 . 
 setPosition 
 ( 
 5 
 , 
  
 5 
 , 
  
 0 
 , 
  
 0 
 ) 
  
 . 
 build 
 (); 
 sheet 
 . 
 insertChart 
 ( 
 chart 
 );

Parameters

Name	Type	Description
`range`	`Range`	The range to add.

Return

EmbeddedChartBuilder — this builder, for chaining

`asAreaChart()`

Sets the chart type to AreaChart and returns an EmbeddedAreaChartBuilder .

Return

EmbeddedAreaChartBuilder — a builder for an area chart

`asBarChart()`

Sets the chart type to BarChart and returns an EmbeddedBarChartBuilder .

Return

EmbeddedBarChartBuilder — a builder for a bar chart

`asColumnChart()`

Sets the chart type to ColumnChart and returns an EmbeddedColumnChartBuilder .

Return

EmbeddedColumnChartBuilder — a builder for a column chart

`asComboChart()`

Sets the chart type to ComboChart and returns an EmbeddedComboChartBuilder .

Return

EmbeddedComboChartBuilder — a builder for a combo chart

`asHistogramChart()`

Sets the chart type to HistogramChart and returns an EmbeddedHistogramChartBuilder .

Return

EmbeddedHistogramChartBuilder — a builder for a histogram chart

`asLineChart()`

Sets the chart type to LineChart and returns an EmbeddedLineChartBuilder .

Return

EmbeddedLineChartBuilder — a builder for a line chart

`asPieChart()`

Sets the chart type to PieChart and returns an EmbeddedPieChartBuilder .

Return

EmbeddedPieChartBuilder — a builder for a pie chart

`asScatterChart()`

Sets the chart type to ScatterChart and returns an EmbeddedScatterChartBuilder .

Return

EmbeddedScatterChartBuilder — a builder for a scatter chart

`asTableChart()`

Sets the chart type to TableChart and returns an EmbeddedTableChartBuilder .

Return

EmbeddedTableChartBuilder — a builder for a table chart

`build()`

Builds the chart to reflect all changes made to it.

This method does not automatically draw the chart on top of the spreadsheet. A new chart must be inserted via sheet.insertChart(chart) , and an existing chart should be updated via sheet.updateChart(chart) .

 var 
  
 ss 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSpreadsheet 
 (); 
 var 
  
 sheet 
  
 = 
  
 ss 
 . 
 getSheets 
 ()[ 
 0 
 ]; 
 var 
  
 range 
  
 = 
  
 sheet 
 . 
 getRange 
 ( 
 "A1:B5" 
 ); 
 var 
  
 chart 
  
 = 
  
 sheet 
 . 
 newChart 
 () 
  
 . 
 setChartType 
 ( 
 Charts 
 . 
 ChartType 
 . 
 BAR 
 ) 
  
 . 
 addRange 
 ( 
 range 
 ) 
  
 . 
 setPosition 
 ( 
 5 
 , 
  
 5 
 , 
  
 0 
 , 
  
 0 
 ) 
  
 . 
 build 
 (); 
 sheet 
 . 
 insertChart 
 ( 
 chart 
 );

Return

EmbeddedChart — the created chart, which must still be added to the spreadsheet

`clearRanges()`

Removes all ranges from the chart this builder modifies.

 var 
  
 ss 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSpreadsheet 
 (); 
 var 
  
 sheet 
  
 = 
  
 ss 
 . 
 getSheets 
 ()[ 
 0 
 ]; 
 // 
  
 This 
  
 code 
  
 updates 
  
 the 
  
 chart 
  
 to 
  
 use 
  
 only 
  
 the 
  
 new 
  
 ranges 
  
 while 
  
 preserving 
  
 the 
  
 existing 
 // 
  
 formatting 
  
 of 
  
 the 
  
 chart 
 . 
 var 
  
 chart 
  
 = 
  
 sheet 
 . 
 getCharts 
 ()[ 
 0 
 ]; 
 var 
  
 newChart 
  
 = 
  
 chart 
  
 . 
 modify 
 () 
  
 . 
 clearRanges 
 () 
  
 . 
 addRange 
 ( 
 sheet 
 . 
 getRange 
 ( 
 "A1:A5" 
 )) 
  
 . 
 addRange 
 ( 
 sheet 
 . 
 getRange 
 ( 
 "B1:B5" 
 )) 
  
 . 
 build 
 (); 
 sheet 
 . 
 updateChart 
 ( 
 newChart 
 );

Return

EmbeddedChartBuilder — this builder, for chaining

`getChartType()`

Returns the current chart type.

Return

ChartType — the chart type

`getContainer()`

Return the chart ContainerInfo , which encapsulates where the chart appears on the sheet.

 var 
  
 ss 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSpreadsheet 
 (); 
 var 
  
 sheet 
  
 = 
  
 ss 
 . 
 getSheets 
 ()[ 
 0 
 ]; 
 var 
  
 chartBuilder 
  
 = 
  
 sheet 
 . 
 newChart 
 () 
  
 . 
 setChartType 
 ( 
 Charts 
 . 
 ChartType 
 . 
 BAR 
 ) 
  
 . 
 addRange 
 ( 
 sheet 
 . 
 getRange 
 ( 
 "A1:B8" 
 )) 
  
 . 
 setPosition 
 ( 
 5 
 , 
  
 5 
 , 
  
 0 
 , 
  
 0 
 ); 
 // 
  
 This 
  
 method 
  
 returns 
  
 the 
  
 exact 
  
 same 
  
 data 
  
 as 
  
 Chart 
 #getContainerInfo() 
 var 
  
 containerInfo 
  
 = 
  
 chartBuilder 
 . 
 getContainer 
 (); 
 // 
  
 Logs 
  
 the 
  
 values 
  
 we 
  
 used 
  
 in 
  
 setPosition 
 () 
 Logger 
 . 
 log 
 ( 
 "Anchor Column: 
 %s 
 \r\n 
 Anchor Row 
 %s 
 \r\n 
 Offset X 
 %s 
 \r\n 
 Offset Y 
 %s 
 " 
 , 
  
 containerInfo 
 . 
 getAnchorColumn 
 (), 
  
 containerInfo 
 . 
 getAnchorRow 
 (), 
  
 containerInfo 
 . 
 getOffsetX 
 (), 
  
 containerInfo 
 . 
 getOffsetY 
 ());

Return

ContainerInfo — an object containing the chart container's position

`getRanges()`

Returns a copy of the list of ranges currently providing data for this chart. Use addRange(range) and removeRange(range) to modify this list.

 var 
  
 ss 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSpreadsheet 
 (); 
 var 
  
 sheet 
  
 = 
  
 ss 
 . 
 getSheets 
 () 
 [ 
 0 
 ] 
 ; 
 var 
  
 chartBuilder 
  
 = 
  
 sheet 
 . 
 newChart 
 () 
  
 . 
 setChartType 
 ( 
 Charts 
 . 
 ChartType 
 . 
 BAR 
 ) 
  
 . 
 addRange 
 ( 
 sheet 
 . 
 getRange 
 ( 
 "A1:B8" 
 )) 
  
 . 
 setPosition 
 ( 
 5 
 , 
  
 5 
 , 
  
 0 
 , 
  
 0 
 ); 
 var 
  
 ranges 
  
 = 
  
 chartBuilder 
 . 
 getRanges 
 (); 
 // 
  
 There 
 ' 
 s 
  
 only 
  
 one 
  
 range 
  
 as 
  
 a 
  
 data 
  
 source 
  
 for 
  
 this 
  
 chart 
 , 
 // 
  
 so 
  
 this 
  
 logs 
  
 "A1:B8" 
 for 
  
 ( 
 var 
  
 i 
  
 in 
  
 ranges 
 ) 
  
 { 
  
 var 
  
 range 
  
 = 
  
 ranges 
 [ 
 i 
 ] 
 ; 
  
 Logger 
 . 
 log 
 ( 
 range 
 . 
 getA1Notation 
 ()); 
 }

Return

Range[] — an array of ranges that serve as the chart to be built's data source

`removeRange(range)`

Removes the specified range from the chart this builder modifies. Does not throw an error if the range is not in this chart.

The range removed must match up with a range added via addRange(range) ; otherwise no change is made to the chart. This method cannot be used to partially remove values from a range.

 var 
  
 ss 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSpreadsheet 
 (); 
 var 
  
 sheet 
  
 = 
  
 ss 
 . 
 getSheets 
 ()[ 
 0 
 ]; 
 var 
  
 firstRange 
  
 = 
  
 sheet 
 . 
 getRange 
 ( 
 "A1:B5" 
 ); 
 var 
  
 secondRange 
  
 = 
  
 sheet 
 . 
 getRange 
 ( 
 "A6:B8" 
 ); 
 var 
  
 chartBuilder 
  
 = 
  
 sheet 
 . 
 newChart 
 () 
  
 . 
 setChartType 
 ( 
 Charts 
 . 
 ChartType 
 . 
 BAR 
 ) 
  
 . 
 addRange 
 ( 
 firstRange 
 ) 
  
 // 
  
 This 
  
 range 
  
 will 
  
 render 
  
 in 
  
 a 
  
 different 
  
 color 
  
 . 
 addRange 
 ( 
 secondRange 
 ) 
  
 . 
 setPosition 
 ( 
 5 
 , 
  
 5 
 , 
  
 0 
 , 
  
 0 
 ); 
 // 
  
 Note 
  
 that 
  
 you 
  
 can 
  
 use 
  
 either 
  
 of 
  
 these 
  
 two 
  
 formats 
 , 
  
 but 
  
 the 
  
 range 
 // 
  
 MUST 
  
 match 
  
 up 
  
 with 
  
 a 
  
 range 
  
 that 
  
 was 
  
 added 
  
 via 
  
 addRange 
 (), 
  
 or 
  
 it 
 // 
  
 will 
  
 not 
  
 be 
  
 removed 
 , 
  
 and 
  
 will 
  
 not 
  
 throw 
  
 an 
  
 exception 
 chartBuilder 
 . 
 removeRange 
 ( 
 firstRange 
 ); 
 chartBuilder 
 . 
 removeRange 
 ( 
 sheet 
 . 
 getRange 
 ( 
 "A6:B8" 
 )); 
 var 
  
 chart 
  
 = 
  
 chartBuilder 
 . 
 build 
 (); 
 sheet 
 . 
 insertChart 
 ( 
 chart 
 );

Parameters

Name	Type	Description
`range`	`Range`	The range to remove.

Return

EmbeddedChartBuilder — this builder, for chaining

`setChartType(type)`

Changes the type of chart. Not all embedded chart types are currently supported. See ChartType .

 var 
  
 ss 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSpreadsheet 
 (); 
 var 
  
 sheet 
  
 = 
  
 ss 
 . 
 getSheets 
 ()[ 
 0 
 ]; 
 var 
  
 range 
  
 = 
  
 sheet 
 . 
 getRange 
 ( 
 "A1:B5" 
 ); 
 var 
  
 chart 
  
 = 
  
 sheet 
 . 
 newChart 
 () 
  
 . 
 setChartType 
 ( 
 Charts 
 . 
 ChartType 
 . 
 BAR 
 ) 
  
 . 
 addRange 
 ( 
 range 
 ) 
  
 . 
 setPosition 
 ( 
 5 
 , 
  
 5 
 , 
  
 0 
 , 
  
 0 
 ) 
  
 . 
 build 
 (); 
 sheet 
 . 
 insertChart 
 ( 
 chart 
 );

Parameters

Name	Type	Description
`type`	`ChartType`	The type to change this chart into.

Return

EmbeddedChartBuilder — this builder, for chaining

`setHiddenDimensionStrategy(strategy)`

Sets the strategy to use for hidden rows and columns. Defaults to IGNORE_ROWS .

 var 
  
 ss 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSpreadsheet 
 (); 
 var 
  
 sheet 
  
 = 
  
 ss 
 . 
 getSheets 
 ()[ 
 0 
 ]; 
 var 
  
 range 
  
 = 
  
 sheet 
 . 
 getRange 
 ( 
 "A1:B5" 
 ); 
 var 
  
 chart 
  
 = 
  
 sheet 
 . 
 newChart 
 () 
  
 . 
 setChartType 
 ( 
 Charts 
 . 
 ChartType 
 . 
 BAR 
 ) 
  
 . 
 addRange 
 ( 
 range 
 ) 
  
 . 
 setHiddenDimensionStrategy 
 ( 
 Charts 
 . 
 ChartHiddenDimensionStrategy 
 . 
 IGNORE_COLUMNS 
 ) 
  
 . 
 setPosition 
 ( 
 5 
 , 
  
 5 
 , 
  
 0 
 , 
  
 0 
 ) 
  
 . 
 build 
 (); 
 sheet 
 . 
 insertChart 
 ( 
 chart 
 );

Parameters

Name	Type	Description
`strategy`	`ChartHiddenDimensionStrategy`	The strategy to use for hidden rows and columns.

Return

EmbeddedChartBuilder — this builder, for chaining

`setMergeStrategy(mergeStrategy)`

Sets the merge strategy to use when more than one range exists. If MERGE_ROWS , rows are merged; if MERGE_COLUMNS , columns are merged. Defaults to MERGE_COLUMNS .

 var 
  
 ss 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSpreadsheet 
 (); 
 var 
  
 sheet 
  
 = 
  
 ss 
 . 
 getSheets 
 ()[ 
 0 
 ]; 
 var 
  
 range 
  
 = 
  
 sheet 
 . 
 getRange 
 ( 
 "A1:B10" 
 ); 
 var 
  
 range2 
  
 = 
  
 sheet 
 . 
 getRange 
 ( 
 "C:C10" 
 ); 
 var 
  
 chart 
  
 = 
  
 sheet 
 . 
 newChart 
 () 
  
 . 
 setChartType 
 ( 
 Charts 
 . 
 ChartType 
 . 
 BAR 
 ) 
  
 . 
 addRange 
 ( 
 range 
 ) 
  
 . 
 addRange 
 ( 
 range2 
 ) 
  
 . 
 setMergeStrategy 
 ( 
 Charts 
 . 
 ChartMergeStrategy 
 . 
 MERGE_ROWS 
 ) 
  
 . 
 setPosition 
 ( 
 5 
 , 
  
 5 
 , 
  
 0 
 , 
  
 0 
 ) 
  
 . 
 build 
 (); 
 sheet 
 . 
 insertChart 
 ( 
 chart 
 );

Parameters

Name	Type	Description
`mergeStrategy`	`ChartMergeStrategy`	The merge strategy to use.

Return

EmbeddedChartBuilder — this builder, for chaining

`setNumHeaders(headers)`

Sets the number of rows or columns of the range that should be treated as headers.

 var 
  
 ss 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSpreadsheet 
 (); 
 var 
  
 sheet 
  
 = 
  
 ss 
 . 
 getSheets 
 ()[ 
 0 
 ]; 
 var 
  
 range 
  
 = 
  
 sheet 
 . 
 getRange 
 ( 
 "A1:B5" 
 ); 
 var 
  
 chart 
  
 = 
  
 sheet 
 . 
 newChart 
 () 
  
 . 
 setChartType 
 ( 
 Charts 
 . 
 ChartType 
 . 
 BAR 
 ) 
  
 . 
 addRange 
 ( 
 range 
 ) 
  
 . 
 setNumHeaders 
 ( 
 1 
 ) 
  
 . 
 setPosition 
 ( 
 5 
 , 
  
 5 
 , 
  
 0 
 , 
  
 0 
 ) 
  
 . 
 build 
 (); 
 sheet 
 . 
 insertChart 
 ( 
 chart 
 );

Parameters

Name	Type	Description
`headers`	`Integer`	The number of rows or columns to treat as headers. Negative values cause headers to be auto-detected.

Return

EmbeddedChartBuilder — this builder, for chaining

`setOption(option, value)`

Sets advanced options for this chart. To view a list of the available options, see Chart configuration options .

This method doesn't validate the option you specify is valid for this chart type nor if the value is of the correct format/structure.

This example shows how to change the title and set a legend.

 builder 
 . 
 setOption 
 ( 
 'title' 
 , 
  
 'Earnings projections' 
 ); 
 builder 
 . 
 setOption 
 ( 
 'legend' 
 , 
  
 { 
 position 
 : 
  
 'top' 
 , 
  
 textStyle 
 : 
  
 { 
 color 
 : 
  
 'blue' 
 , 
  
 fontSize 
 : 
  
 16 
 } 
 } 
 );

Parameters

Name	Type	Description
`option`	`String`	The name of the option.
`value`	`Object`	The value of the option.

Return

EmbeddedChartBuilder — This builder, for chaining.

`setPosition(anchorRowPos, anchorColPos, offsetX, offsetY)`

Sets the position, changing where the chart appears on the sheet. anchorRowPos and anchorColPos are 1-indexed.

 var 
  
 ss 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSpreadsheet 
 (); 
 var 
  
 sheet 
  
 = 
  
 ss 
 . 
 getSheets 
 ()[ 
 0 
 ]; 
 var 
  
 range 
  
 = 
  
 sheet 
 . 
 getRange 
 ( 
 "A1:B5" 
 ); 
 var 
  
 chart 
  
 = 
  
 sheet 
 . 
 newChart 
 () 
  
 . 
 setChartType 
 ( 
 Charts 
 . 
 ChartType 
 . 
 BAR 
 ) 
  
 . 
 addRange 
 ( 
 range 
 ) 
  
 . 
 setPosition 
 ( 
 5 
 , 
  
 5 
 , 
  
 0 
 , 
  
 0 
 ) 
  
 . 
 build 
 (); 
 sheet 
 . 
 insertChart 
 ( 
 chart 
 );

Parameters

Name	Type	Description
`anchorRowPos`	`Integer`	The chart's top side is anchored in this row.
`anchorColPos`	`Integer`	The chart's left side is anchored in this column.
`offsetX`	`Integer`	The chart's upper right-hand corner is offset by this many pixels.
`offsetY`	`Integer`	The chart's lower left-hand corner is offset by this many pixels.

Return

EmbeddedChartBuilder — this builder, for chaining

`setTransposeRowsAndColumns(transpose)`

Sets whether the chart's rows and columns are transposed. If set to true , the rows and columns are switched. Defaults to false .

 var 
  
 ss 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSpreadsheet 
 (); 
 var 
  
 sheet 
  
 = 
  
 ss 
 . 
 getSheets 
 ()[ 
 0 
 ]; 
 var 
  
 range 
  
 = 
  
 sheet 
 . 
 getRange 
 ( 
 "A1:B5" 
 ); 
 var 
  
 chart 
  
 = 
  
 sheet 
 . 
 newChart 
 () 
  
 . 
 setChartType 
 ( 
 Charts 
 . 
 ChartType 
 . 
 BAR 
 ) 
  
 . 
 addRange 
 ( 
 range 
 ) 
  
 . 
 setTransposeRowsAndColumns 
 ( 
 true 
 ) 
  
 . 
 setPosition 
 ( 
 5 
 , 
  
 5 
 , 
  
 0 
 , 
  
 0 
 ) 
  
 . 
 build 
 (); 
 sheet 
 . 
 insertChart 
 ( 
 chart 
 );

Parameters

Name	Type	Description
`transpose`	`Boolean`	If `true` , the rows and columns used to construct the chart are transposed.

Return

EmbeddedChartBuilder — this builder, for chaining