Google Apps Script run when SpreadSheet is change by another Script

Question

I'm trying to write a script that display row value in a SpreadSheet file using UiApp.

And this script will need to update the data whenever there is a change to the SpreadSheet. This would normally be simple with just Trigger onChange.

However, the SpreadSheet itself is also updated by another function, and it seem that onChange is not triggered when the changes made are caused by another AppScript.

Is there an alternative way to achieve what I described?

Code below, thanks

    var ss = SpreadsheetApp.openById("tqwJk280I1O_yW5oNX9nLQA");

function doGet() {
  var app = UiApp.createApplication();
  var masPanel = app.createFlowPanel().setId("panel");
  var number = parseInt(ss.getRange("A1").getValue());
  masPanel.add(app.createLabel(number));
  masPanel.add(app.createFlowPanel().add(app.createFormPanel()
           .add(app.createFlowPanel()
           .add(app.createSubmitButton("Plus!")))));

  app.add(masPanel);

  return app;
}

function doPost()
{
  var num = parseInt(ss.getRange("A1").getValue()) + 1;
  ss.getRange("A1").setValue(num);
}

function onChange()
{
  ss.getRange("A2").setValue("hahaha");
  var app = UiApp.createApplication();
  app.remove(app.getElementById("panel"))
  var masPanel = app.createFlowPanel().setId("panel");
  var number = parseInt(ss.getRange("A1").getValue());
  masPanel.add(app.createLabel(number));
  masPanel.add(app.createFlowPanel().add(app.createFormPanel()
           .add(app.createFlowPanel()
           .add(app.createSubmitButton("Plus!")))));

  app.add(masPanel);

  return app;
}

Srik Srik · Accepted Answer · 2013-06-28T07:16:43

The onChange() function will not have reference to the UI. For example, if there are multiple people who have the same UI open, which one should be updated ?

So, the solution to your problem is for your UI code to poll the spreadsheet regularly and update the UI if the spreadsheet has changed. To do so, you can use the undocumented addTimer() method. See this SO question & answer for a nice example

Google Apps Script run when SpreadSheet is change by another Script

1 Answers