Change the size of a graph in Google Sheets based on visible columns

Question

I have a google sheet I use for budgets which runs year to year. I usually create a new tab for each year, but the grouping columns feature means I can just hide a completed year which is ideal.

I also display a simple area graph as shown which can track my expected balance.

If I hide a group, the graph will remove that group's data which is exactly what I want and expect to happen, but it then leaves a graph size which doesn't match.

This is obviously nothing more than a minor irritation, but I'd love to find a way to solve this so that the graph size adjusts to match the included data. I'm not particularly skilled with App Scripts and such but suspect there is probably a way to do it.

If anyone could help me, that would be amazing.

Thank you

Answer 1

you can try something like this:

function adjustChartWidthBasedOnUnhiddenCells() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var row = 1;  // Specify the row you want to count unhidden cells in
  var startColumn = 1;  // Specify the starting column
  var endColumn = sheet.getLastColumn();  // Specify the ending column or use getLastColumn() for the last column in the sheet
  
  var unhiddenCount = 0;
  
  for (var col = startColumn; col <= endColumn; col++) {
    if (!sheet.isColumnHiddenByUser(col)) {
      unhiddenCount++;
    }
  }
  
  var chart = sheet.getCharts()[0];  // Assuming there's at least one chart on the sheet
  var newWidth = unhiddenCount * 50;  // Adjust the multiplier as needed for desired width
  var newChart = chart.modify()
                      .setOption('width', newWidth)
                      .build();
  
  sheet.updateChart(newChart);
}

briefly tested it on this setup:

un-hiding group and running script will up-size chart:

then hiding group and running script again will down-size chart...

---

further modification would of course be needed to exactly fit your needs