Changing date range for chart based on selected dropdown value

0
votes

I have a Google Spreadsheet like this with a chart and a dropdown menu. I would like the data range of that chart to update based on a value selected from a dropdown menu.

I thought I might be able to add a Google App Script to the dropdown menu, but I can't find a way to add such script to anything but a drawing. Can someone tell me how to achieve this? Or point me into the right direction (Doc link?) as I'm new to both Google Spreadsheets and App Scripts.

Update: From the comments it seems using App Scripts onEdit trigger might work. The idea would be

  1. to trigger onEdit when the value from the dropdown cell changes
  2. fetch the cell value to use as an argument for another function
  3. that in turn alters the data range of a chart.

As far as I can see onEdit triggers with changes made to any cell. Is there a way to limit this trigger to specific cells/ranges?

1
google-apps-scriptgoogle-sheets
This question is really vague, also the spreadsheet has no date range on it! You cannot assign a script to a drop-down menu. However, you can use sidebar or onEdit trigger to change the data in chart data range to update the chart accordingly. - Jack Brown
@JackBrown date was typo. I actually meant data range. The onEdit trigger looks like the way to go. - SomewhereDave

1 Answers

0
votes

If I were you, I would use your drop-down menu as reference in a Query(). Often, I'll use a Query() in a hidden section of columns on my sheet, and use that hidden section as the data range for my charts. For example, if your drop down was in A1 you could have in B1 a Query() that uses a cell reference to find your data, and then hide columns B through however many you need to display your data. Then you make your data range B1:D (or whatever the range actually ended up being). Your example sheet has been moved to your trash, but if you share a data set with me, I could show you a pertinent example. Here is a less than pertinent example Ta-Da. I know that it is probably too general to use for your specific case, but perhaps if we knew more we could point you to a more specific work-around.