CODEWITHSUNDEEP
google-apps-scriptchartsgoogle-sheets
ubugnu
ubugnu

Reputation: 1082

Script to add a "chart" sheet

In Google Sheets one has the possibility to move a chart to its own sheet, I want to do the same thing programmatically, here is my code:

  var sheet = SpreadsheetApp.getActiveSheet(),
      spreadSheet = SpreadsheetApp.getActiveSpreadsheet(),
      rows = sheet.getDataRange(),
      numRows = rows.getNumRows(),
      values = rows.getValues();
  // ....
  var pos = values[i][0],
    name = values[i][1],
    avg = values[i][13],
    range = sheet.getRange("C" + i + ":M" + i);
  // ....
  var chart = sheet.newAreaChart()
      .setPosition(1, 1, 10, 10)
      .setChartType(Charts.ChartType.BAR)
      .setOption('title', name)
      .addRange(range)
      .build();
  spreadSheet.insertSheet(name).insertChart(chart);

How can I do this?

Edit:

To be more clear here is what my script did:

enter image description here

And here is what I want it to do:

enter image description here

Upvotes: 2

Views: 1764

Answers (2)

Fred
Fred

Reputation: 1111

There isn't such a method currently. Charts on their sheets don't even show up when you run

spreadsheet.getSheets();

That threw me off in some of my scripts at first. If you can create the chart programmatically, though, then you can create it in a gadget and show it on a Site. Some types can't be edited using GAS (i.e. combo charts).

Upvotes: 1

Kalyan Reddy
Kalyan Reddy

Reputation: 1142

I don't think there is a way to programatically move a chart to its own sheet in one step, but you can do it with something like the following:

var sheet = SpreadsheetApp.getActiveSheet();
var charts = sheet.getCharts();
sheet.removeChart(charts[0]); // move first chart to another sheet

var newSheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet();
newSheet.insertChart(charts[0]);

Upvotes: 0

Related Questions