CODEWITHSUNDEEP
chartsgoogle-apps-scriptgoogle-sheetsspreadsheetgoogle-sheets-api
ellockie
ellockie

Reputation: 4268

Named ranges in google spreadsheet charts?

Edit: This question relates to the "old" google spreadsheets that were current in 2013.

Is it possible to use named ranges in google spreadsheet charts?

I need to dynamically update the dimension (starting position and size) of a range and be able to reflect it in a chart, i.e. I need the chart source range to follow the position of moved source data range.

Extra information:

I've got two sheets. One has a header followed by 'input' row and data rows. When I type anything into 'input' row my script automatically adds a row just after this one and moves my new input there, therefore creating new row of data.

About chart: If I assign a data range that includes only data rows, new row won't be included because whole 'old' range got shifted. $ sign is ignored when specifying data range. So what I'm doing is to include in the range my 'input' + 1 header row. They have no values so in the chart you cannot see data points in the first two positions. I'm using a combo chart with another range from another sheet that has no header, so it looks like this:

enter image description here

(usually there is some value for the green histogram right at the origin of the chart).

So the line chart is shifted by two positions to the right, but it should start from position 0, so I want the chart to somehow follow the range or to redefine it, so it always stars from the first data row. I hope it makes sense.

Upvotes: 8

Views: 14754

Answers (3)

Adam J
Adam J

Reputation: 11

In searching for this, I found a solution I thought I'd share.

I have a set of data from which I'd like to create a set of charts. My fairly simple workaround was to create a new tab for each chart with a pivot table to pull in my data based on my criteria for each chart. I inserted a row at the top to hold a SUM of my values in the pivot table below, rather than referencing the moving "Total" cell in the pivot table itself.

Note: I hid the title row of the pivot table and unchecked the "Totals" within it to get the SUM to work correctly. Hope this helps someone!

Upvotes: 1

tic
tic

Reputation: 4189

Charts don't support named ranges. However, you can set up your chart so that the source data range is an entire column (for example, Sheet1!D:D). Then the chart will show all the values in that column, taking into account even rows that you will add (or delete) after chart creation. If the first rows (for example 4) contain table headers you can specify something like Sheet1!D5:D as your source data range

Upvotes: 4

Serge insas
Serge insas

Reputation: 46792

Charts use range as argument, have you tried to simply use getRangeByName to define the range ?

Upvotes: 0

Related Questions