Macro Guide

The following is a general guideline on using Macro. For documentation of individual declarations, please refer to the Macro API Reference.

Introduction to EditGrid Macro

A macro consists of a name and a block of JavaScript code. The code resembles Grid API and will be executed in the browser. However, in contrast to Grid API, which requires both the RTU Grid and the Grid API code to run in your own domain, EditGrid Macro allows you to insert code to run in "The RTU Grid at EditGrid.com". This means you can still customize and add features to the RTU Grid without need of a separate web hosting service.

You may write Macro code that

Respond to spreadsheet changes and cursor events.

Expose actions to the user, which maybe triggered by a in-cell button, keyboard shortcut, toolbar icon or menu item.

Pop-up a toolbox for more complex user interactions.

Quick Start Guide

In the RTU Grid, go to the "Macro" Menu.

BERJAYA

Use "Create New" to create a new macro, and use "Manage" to edit existing macros.

Macro code attaches JavaScript functions (event handlers) to events defined in the Macro API Reference. For example, we may listen to a cell value change event using

data.onValueChange['Sheet1'] = function (cells) { alert('Hello world'); };

or we may listen to a key stroke event using

shortcut['CTRL H'] = function () { alert('Hello world'); };

or we may create a toolbox using

toolbox['Toolbox Title'] = { html: "<b>Hello, world</b>" }

Below we give example macro code which you can use to try out the macro functionality.

What macro cannot do?

Macro does not

run without write permission. If you want your co-workers run the macro you created, you need to share to them with write permission of the spreadsheet.

allow you manipulate permission, neither gaining addition permission or revoking it. You can modify a spreadsheet if and only if you have (full) write access to the spreadsheet. Write an application that uses the Web API if you want to grant only restricted access of a spreadsheet to somebody.

add HTML elements into the RTU Grid, though you can add a toolbox on top of the RTU Grid. Macro has full control on all contents of the toolbox it creates.

Macro Performance

See Macro Technology for the underlying technology that makes EditGrid Macro work.

Although Macro code seems to be calling Grid API, the code actually runs in another domain, at arms length from the RTU Grid. As a result, nearly every Grid API call requires the browser to relay the call to and fro the RTU Grid domain. This is time consuming.

The rule of thumb is, avoid making unnecessary Grid API calls. Use worksheet.getRange(...).getTexts() or worksheet.getRange(...).setTexts() instead of multiple worksheet.getCell(...).getText() or worksheet.getCell(...).setText() calls.

Sample Macros

Defining shortcuts, menu items and toolbar items
Defining an action (which can be called from a cell button)
Detecting value changes
Changing background color based on values
Defining a toolbox
Defining a sheetObject
Fetching data using proxy

Defining shortcuts, menu items and toolbar items

function alertComment() {
  alert(grid.getCursor().getCell().getCommentText());
};

shortcut['CTRL 4'] = alertComment;
toolbar['Show Comment'] = alertComment;
toolbar['Show Comment'].image = "http://farm4.static.flickr.com/3034/2798709075_6118a235ec.jpg";
menu['view/Show Comment'] = alertComment;

Defining an action (which can be called from a cell button)

action['alertComment'] = function () {
  alert(grid.getCursor().getCell().getCommentText());
};

// Assume =button("Show Comment","editgrid:action(MACRONAME:alertComment)") in cell A1
var cell = grid.getWorkbook().getSheetByName('Sheet1').getCell('A1');
cell.setText('=button("Show Comment","editgrid:action(MACRONAME:alertComment)")');

Detecting value changes

var sheet = grid.getWorkbook().getSheetByName('Sheet1');

macro.onLoad = function () {
  sheet.getCell('A21').setText('--------');
  sheet.getCell('A22').setText('=SUM(A1:A20)');
}

data.onValueChange['Sheet1!A1:A20'] = function (range) {
  var sum = sheet.getCell('A22').getValue();
  if (sum > 100) {
    alert('Sum of A1:A20 cannot be greater than 100!');
  }
}

Changing background color based on values

var RED = 0xff0000, BLACK = 0x000000, WHITE = 0xffffff, YELLOW = 0xffff00;

data.onValueChange['Sheet1'] = function (range) {
  range.eachCell(function (cell, r, c) {
    if (cell.getValue() >= 0) {
      if (cell.getStyle().getBackColor() != BLACK) cell.applyStyle({ backColor: BLACK, fontColor: WHITE });
    } else {
      if (cell.getStyle().getBackColor() != RED) cell.applyStyle({ backColor: RED, fontColor: YELLOW });
    }
  });
};

Defining a toolbox

For details on how to write a toolbox, please refer to Macro Toolbox.

// Assume =button("Pick a type", "editgrid:action(MACRONAME:pick)") in cell A1
var cell = grid.getWorkbook().getSheetByName('Sheet1').getCell('A1');
cell.setText('=button("Pick a type", "editgrid:action(MACRONAME:pick)")');

toolbox['picker'] = {
  html: ['<select id="picker"><option></option>',
    '<option>VCD</option>',
    '<option>DVD</option>',
    '<option>HD-DVD</option>',
    '<option>Blueray</option>',
  '</select>'].join(''),
  onPickerChange: function () {
    var text = this.picker.options[this.picker.selectedIndex].value;
    grid.getWorkbook().getSheetByName('Sheet1').getCell('A2').setText(text);
    toolbox['picker'].close();
  },
  width: 100, height: 20
};

action['pick'] = function () {
  toolbox['picker'].open();
};

Defining a sheetObject

For details on how to create a custom sheetObject please refer to Macro SheetObject.

//this example is called "FusionChart"
FusionChart = function () {
  //add any properties here (e.g. this.url = someUrl;)
};

//our sheet object inherits from editgrid's native sheet object
FusionChart.prototype = new editgrid.sheetobject.AbstractSheetObject();

//override doCreate method
FusionChart.prototype.doCreate = function () {
//called when a sheet object is created, such as when a user navigates to the sheet to which the object belongs
};

//override doUpdate method
FusionChart.prototype.doUpdate = function () {
//called when a sheet object updates, such as when its dependent data changes
};

//add the sheetObject
grid.addSheetObject('FusionChart', { width: 600, height: 400 }, { data: "=C2:C100" });

Fetching data using proxy

For details on how to use the proxy, please refer to Macro Proxy.

// load the URL in A1 and write the HTML in A2
data.onValueChange['Sheet1!A1'] = function (range) {
  var html = proxy.request(range.getTexts()[0][0]);
  grid.getWorkbook().getSheetByIndex(0).getCell("A2").setText(html);
};

Oct	NOV	Dec
	27
2007	2008	2009

Developers

Macro

Web API

Grid API

Page Operations

Browse Space