The shortcoming of the Script Recorder is that it notes the exact cells and selected ranges during the recording session.
Notice how the range selected for the char is fixed to cells A3:B8.
We want this range reference to be dynamic so we can apply the script to tables of various sizes.
While we’re at it, let’s make the title dynamic. Instead of always displaying “Salary Department”, we want the title to display the text located in cell A1 of the respective sheet.
Dynamic Chart Titles
First, we need to capture the contents of cell A1 and store those contents in a variable. We will name our variable “myTitle”.
After the first LET line of code, add the following lines of code to perform the text capture step.
// Get the Chart Title
let myTitle = selectedSheet.getRange(“A1”).getText();
Update the line of script that sets the chart title from this …
… to this …
Dynamic Range Selection
As the range selector is fixed to cell A3:B8, we need to create a variable to capture the range of data for each processed sheet.
After the previously created LET line of code, add the following lines of code to perform the range capture step.
// Get Dynamic Chart Range
let myRange = selectedSheet.getRange(“A3”).getSurroundingRegion().getAddress();
Let’s update the existing code from this …
… to this …
If we delete the existing chart and re-run the script, we see the updated chart results.
Observe the dynamic title taken from cell A1 and the expanded data range (A3:B11).
Testing the script on a smaller dataset that occupies cells A3:B6, we get the following results by clicking our listed script on the Automate ribbon and select RUN.