Google Sheets: Horizontal scrollbar can't be enabled to any chart

56 views
Skip to first unread message

Avinash Karri

unread,
Feb 10, 2024, 9:15:55 AMFeb 10
to Google Apps Script Community
Hi,

I have created a pivot table using Google Looker connection. With the generated result I have created a column chart. If number of columns increases, the generated visualization is shrinking the columns within allocated space, however it is not enabling the scrollbar with specific column spacing between each data columns. 

To overcome this issue, I have created a app script code that creates a column chart based the data range provided. I could not able find a best fit parameter to be added in the code to enable the horizontal scrolling in the generated visualization in the sheet.
I would request someone to suggest an app script code that generated a column chart with horizontal scrollbar enabled.

Sample App script code

```
function createScrollableColumnChart() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
 
  // Define the data range for the chart
  var dataRange = sheet.getRange("A1:Z14");

  var chart = sheet.newChart()
    .asColumnChart()
    .addRange(dataRange)
    .setChartType(Charts.ChartType.COLUMN)
    .setOption('isStacked', false)
    .setOption('bar.groupWidth', '100%') // Set inner spacing between columns to 0.5
    .setOption('hAxis.ticks', sheet.getRange("A2:Z14").getValues().flat())
    .setPosition(1, 1, 0, 0)
    .setOption('allowHtml', true)
    .setOption('scrollBars',{
      horizontal: {
        enabled: true
      }
    })
    .setOption('explorer',{actions:['dragToZoom', 'righClickToReset']})
    .build();
 
  // Insert the chart into the sheet
  sheet.insertChart(chart);
}
```​

Emerson Maia

unread,
Feb 10, 2024, 11:31:43 AMFeb 10
to google-apps-sc...@googlegroups.com
see if it suits you, 
const createColumnChart = () => { const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); // Define o intervalo de dados para o gráfico const dataRange = sheet.getRange("A1:Z14"); // Constrói o gráfico de colunas const chartBuilder = sheet.newChart() .asColumnChart() .addRange(dataRange) .setChartType(Charts.ChartType.COLUMN) .setOption('isStacked', false) .setPosition(1, 1, 0, 0); // Insere o gráfico na planilha sheet.insertChart(chartBuilder.build()); }


--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/adeac631-05ee-4b60-98a9-53b8a375b5e3n%40googlegroups.com.

Avinash Karri

unread,
Feb 14, 2024, 12:15:24 AMFeb 14
to Google Apps Script Community
Hi Emerson Maia,

Thanks for your response. I've tried this, however the generated visualization is still not showing any scrollbar in the visualization to scroll horizontally. For example if the data points are 100, it is supposed to show the data with specific spacing and scrollbar to move within allocated space. 
The issue here is the entire data points are shrinking within the allocated space.

Reply all
Reply to author
Forward
0 new messages