CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheetsgoogle-visualization
Jaimee
Jaimee

Reputation: 1

How to change chart position in Google Sheets?

I currently have a script that takes data, formats it, and creates a chart below the data.

I know how to move the chart within the tab using setPosition, but can't figure out how to write the chart to a different tab (to create a 'dashboard' where you wouldn't have to see all of the source data). Any ideas on this?

I've tried changing what's in .setPosition, but that only moves the chart within the existing tab.

I've also had glitches with this where I've gotten the chart to move to the right of the data in some cases, but using the same code in a different sheet it doesn't write the chart at all.

Here's the code I'm working with: 

var chartTitle = columnHeader;
  if (typeof subtitle[columnHeader] != 'undefined') {
    chartTitle = chartTitle + " - " + subtitle[columnHeader];
  }

  var chartBuilder = sheet.newChart()
  .setChartType(Charts.ChartType.LINE)

  .setOption('chartArea', {left:'10%',top:'15%',width:'80%',height:'70%'})
  .setPosition(4 + output.length, 1, 0, 0)
  .setOption('width', width)
  .setOption('height', 500)
  .setOption('title', chartTitle)
  .setOption('legend', {position: 'top'});

  var statFormat = {CPC: 'currency', CTR: 'percent', Impressions: 'decimal', Searches: 'decimal'};

  if (statsInChart.length == 0) {
    chartBuilder.setOption('vAxes', {
      // Adds titles to the axis.
      0: {title: columnHeader} });
  } else if (statsInChart.length == 1) {
    chartBuilder.setOption('vAxes', {
      // Adds titles to both axes.
      0: {title: columnHeader} ,
      1: {title: statsInChart[0], format: statFormat[statsInChart[0]]}});
  } else {
    chartBuilder.setOption('vAxes', {
      // Adds title to the first axis, blanks the others.
      0: {title: columnHeader} ,
      1: {format: statFormat[statsInChart[0]], textPosition: 'in'},
      2: {format: statFormat[statsInChart[1]], textPosition: 'out'},
      3: {textStyle: {color: 'white'}}
    });
  }

Upvotes: 0

Views: 2236

Answers (1)

chuckx
chuckx

Reputation: 6877

The code you provided does not cover when you actually build the chart, nor when you insert it into a sheet.

When you create the EmbeddedChartBuilder by calling the sheet.newChart() method, you have a sheet object that's associated with a particular sheet within the spreadsheet.

What you want to do is call the insertChart() method with a different sheet object, one that is associated with the sheet where you'd like the chart to appear.

Here's a small example:

function createChart() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var dataSheet = ss.getSheetByName('data');
  var chartSheet = ss.getSheetByName('chart');

  var dataRange = dataSheet.getDataRange();

  var chart = chartSheet.newChart()
      .addRange(dataRange)
      .setPosition(1, 1, 0, 0)
      .build();

  chartSheet.insertChart(chart);
}

In my test spreadsheet, I have two sheets. One is named data and it contains a small table of data. The other is a blank sheet named chart.

When I execute the above function, a chart is placed in the chart sheet based on the data extracted from the data sheet.

Upvotes: 1

Related Questions