CODEWITHSUNDEEP
google-apps-scriptgoogle-visualization
Muhammad Gelbana
Muhammad Gelbana

Reputation: 4008

How to draw charts without displaying it's data in the sheet?

I'm trying to display charts in a Google Sheet using Google App Script.

It appears to me that the only way to feed data to a chart is through displaying this data on a sheet and passing the data's range to the chart. As such:

var chart = SpreadsheetApp.getActiveSheet().newChart().asPieChart()
    .addRange(range)
    .build();

SpreadsheetApp.getActiveSheet().insertChart(chart);

Where range is a range in the sheet holding the chart's data.

My question is if there is a way to display the chart without having to display it's raw data as such ?

Upvotes: 0

Views: 271

Answers (1)

Tanaike
Tanaike

Reputation: 201643

How about these workarounds? I couldn't find how to create charts without range of spreadsheet for EmbeddedChartBuilder. So I thought other 2 workarounds.

Workaround 1

In this case, in order to display no data for charts, it uses a range of other spreadsheet.

var id = "### spreadsheet id ###"; // There is data in this spreadsheet.
var name = "### sheet name ###";
var datarange = "### data range ###";
var range = SpreadsheetApp.openById(id).getSheetByName(name).getRange(datarange);
var chart = SpreadsheetApp.getActiveSheet().newChart().asPieChart()
  .addRange(range)
  .build();
SpreadsheetApp.getActiveSheet().insertChart(chart);

Workaround 2

In this case, in order to display no data for charts, it reads data using newDataTable(). You can add your data to newDataTable().

The created chart is imported to spreadsheet as an image. The chart sample is from here.

var data = Charts.newDataTable()
    .addColumn(Charts.ColumnType.STRING, 'Month')
    .addColumn(Charts.ColumnType.NUMBER, 'In Store')
    .addColumn(Charts.ColumnType.NUMBER, 'Online')
    .addRow(['January', 10, 1])
    .addRow(['February', 12, 1])
    .addRow(['March', 20, 2])
    .addRow(['April', 25, 3])
    .addRow(['May', 30, 4])
    .build();

var chart = Charts.newAreaChart()
    .setDataTable(data)
    .setStacked()
    .setRange(0, 40)
    .setTitle('Sales per Month')
    .build().getBlob();

SpreadsheetApp.getActiveSheet().insertImage(chart, 1, 1);

References :

If these are not useful for you, I'm sorry.

Upvotes: 1

Related Questions