Chart in App script google spreadsheet

Question

My first post and I must admit that I'm bad at explaining stuffs. let me try.

I have this java code in spreadsheet which adds a UI, have certain checkboxes & a chart attached to UI. When the first (EDC) checkbox is clicked range(C2) goes as true/false, changes the chart values in data.

Since the chart doesnt automatically update, I decided to remove the existing chart and add a new one. When the code is runned for first time...the UI is visible along with Populate charts, when I click checkbox the existing chart gets delete but the populate_chart function does not. Can any one help me??? Almost my first code (such big).

function Show_chart() {

var ss = SpreadsheetApp.getActive();

var calc = ss.getSheetByName("Calculations");

var data = calc.getRange(6, 19, 22, 2)

//  var values = data.getValues()

//   for (var row in values) {

//   for (var col in values[row]) {

//     Logger.log(values[row][col]);

//   }

//   }


var app = UiApp.createApplication().setHeight(600).setWidth(1200).setTitle("Attrition Report");

var mygrid = app.createGrid(7, 2)

var label1 = mygrid.setWidget(0, 0, app.createLabel("EDC Project"));

var label2 = mygrid.setWidget(1, 0, app.createLabel("Customer Project"));

var label3 = mygrid.setWidget(2, 0, app.createLabel("Support"));



var checkbox1 = mygrid.setWidget(0, 1, app.createCheckBox().setName("EDC"));

var checkbox2 = mygrid.setWidget(1, 1, app.createCheckBox().setName("CP"));

var checkbox3 = mygrid.setWidget(2, 1, app.createCheckBox().setName("Support"));


checkbox1.addClickHandler(app.createServerHandler("myClickHandler"));

var panel = app.createVerticalPanel();

panel.add(mygrid);

app.add(panel);

populate_charts()

ss.show(app);

}

function populate_charts(){ 

var ss = SpreadsheetApp.getActive();

var app = UiApp.getActiveApplication();

var calc = ss.getSheetByName("Calculations");

var data = calc.getRange(6, 19, 22, 2);

//1200 300

var chart = Charts.newLineChart().setDimensions(300, 100)

.setDataTable(data)

.build();

var chartpanel = app.createHorizontalPanel().setId("tbd");

chartpanel.add(chart);

return app.add(chartpanel);

}


function myClickHandler(e) {

var ss = SpreadsheetApp.getActive();

var calc = ss.getSheetByName("Calculations");

 var app = UiApp.getActiveApplication();

//  var data = calc.getRange(6, 19, 22, 2)

 var chvalue  = e.parameter.EDC;

 calc.getRange("C2").setValue(chvalue);

 var del = app.getElementById("tbd");

 return app.remove(del);

 populate_charts()


  }

Thanks...

Taras Taras · Accepted Answer · 2013-06-25T09:52:00

You should call populate_charts(); before return app.remove(del); in your function myClickHandler. Whatever you add after returnin your function simply doesn't happen.

Don't delete an UiElement and recreate a new element with the same Id - very bad things will happen in UiApp, or GWT which is the mechanics behind UiApp.

You could simplify populate_charts:

function populate_charts(app, sSheet){ 
  var data = sSheet.getSheetByName("Calculations").getRange(6, 19, 22, 2);
  var chart = Charts.newLineChart().setDimensions(300, 100)
                .setDataTable(data).build();
  return chart;
}

myClickHandler could be reduced as well:

function myClickHandler(e) {
  var app = UiApp.getActiveApplication();
  var chvalue  = e.parameter.EDC;
  var sSheet = SpreadsheetApp.getActive();
  sSheet.getSheetByName("Calculations").getRange("C2").setValue(chvalue);
  app.getElementById("chartContainer").clear().add(populate_charts(app, sSheet));
  return app;
}

The last 7 lines of Show_chart changed to:

  app.add(mygrid.setId('grid'));
  app.add(app.createFlowPanel()
             .setId('chartContainer').add(populate_charts(app, ss)));
  checkbox1.addClickHandler(
        app.createServerHandler('myClickHandler')
          .addCallbackElement(app.getElementById('grid'))
          .addCallbackElement(app.getElementById('chartContainer')));
  return app;
}

Don't forget to add all missing semicolons ; .

Chart in App script google spreadsheet

1 Answers