Named ranges in google spreadsheet charts?

Question

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:

(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.

Solution 1

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

Solution 2

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

Solution 3

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!

Comments

[ellockie]

After your suggestion I tried something like that in chart's advanced edit dialog: getRangeByName('Range_Workdays_Ago'), unfortunately it didn't work...

[Serge insas]

Are you telling me that you create the chart manually? My suggestion was of course supposed to work in the context of gas script chart creation.

[ellockie]

Yes, that's how I was doing it. So do you mean to create chart programatically each time The row is being added/removed? What about performance? Is it possible to update an existing chart by script? If you could please edit your answer I would 'accept' it.

[ellockie]

Thanks for your suggestion. It's a good solution, but in my case I have 4 rows of headers and don't want to include them...

[tic]

@ellockie, really this is not a problem. Read my updated answer.

[Serge insas]

I'm not an expert with charts but I could try if you give some more information about your data and the type of chart you are willing to create...:-)

[ellockie]

Unfortunately it would not include the newly inserted data row as I've just now explained in an edit to my question.

[tic]

It's your script that breaks the chart updating.

[ellockie]

I think it's just what naturally happens when a new row is inserted in the beginning of the range, or even higher...

[tic]

Suppose you input row is 3rd. Do not add line before 4th one, but before 5th one, and leave 4th row empty. It's all.

[ellockie]

I appreciate your comment and probably my problem isn't worth your time, but my goal is for both data series to start right from the beginning of the chart

[tic]

You have to decide, because your description of the problem is slippery. At the beginning, you asked how to use named ranges in charts; then, you spoke about a script; eventually, you asked how to manage two different data series. I suggest you to split your problem in clear pieces.

[Fernando Wittmann]

That was exactly my problem :) Thanks!