What to use in place of Excel Shapes


Note that this article uses the Apps Script UI service, which has since been deprecated. I leave it here for interest.

The equivalent of inserting a shape in Google Docs is to insert a drawing, into which you have inserted shapes. Unfortunately it is not at this time possible to script that. It is possible (I think likley) that this will be possible in the future, so for the interim, I want to abstract away the shapes so that the method can be modified in the future.

The only thing that can work for the moment is to use the UI creator, along with an absolutepanel, and some kind of object like a label. This is not that great because
  • It's on a pop up box as opposed to being built into the sheet
  • I dont have a choice of shapes, such as arrows, chevrons and so on
  • It a screwy kind of workaround to have to create a UI in order to display shapes, and the UI API itself is a bit funky. Essentially its like doing this on an Excel Form with Label Controls.

In any case it will do for now. So far I have this for the From VBA to Google Apps Script conversion of the Roadmap Generation project. You can find the code (its a work in progress) and test data in the VBA to Google Apps Script Roadmapper project download


It sucks a bit compared to the Excel version, but I'll keep tweaking.


Implemenation of cShape class

Since I want to abstract the shapes, I created my own cShape class, which is currently implemented using Google UI services, but since this is just a hack, I will want to be able to use Google Drawings if they ever become accessible via scripting. This abstraction will mean that I can switch over with minimum effect later.

Creating the shape

var s = new cShape();

Since this is actually using the UI, what in fact is needed behind the scenes is to create a one off UiApp, and a single AbsolutePanel, and a Label for each roadmap shape required.  Since I dont want the management of all that to be the responsibility of the caller, this is all handled automatically, like this, with the whole structure being managed through the public variable, shapePanel.

var shapePanel;
function usePanel() {
  return shapePanel ? shapePanel : shapePanel = new cUiPanel();
}

function showPanel() {
  usePanel().app().setHeight(usePanel().xMaxHeight).setWidth(usePanel().xMaxWidth);
  ActiveSheet().show(usePanel().app());
  return shapePanel;
}

var cUiPanel = function(){
  this.xApp = UiApp.createApplication();
  this.app = function(){
    return this.xApp;
  };
  this.xAbsolutePanel = this.app().createAbsolutePanel();
  this.absolutePanel = function(){
    return this.xAbsolutePanel;
  };
  this.app().add(this.absolutePanel());
    // keep track of the biggest assigned and tweak the panel later
  this.xMaxHeight = 0;
  this.xMaxWidth = 0;
};
// a shape will be placed on the absolute panel defined by usePanel().xAbsolutePanel
var cShape = function  (isaPanel) {
  this.xIsaPanel = fixOptional(isaPanel,false);
  this.xShapePanel = usePanel();
  this.xBox = this.xIsaPanel ?  this.app().createHorizontalPanel() : this.app().createLabel();
  this.panel().add(this.box(),0,0);
  this.xLeft = this.xTop = this.xHeight = this.xWidth =0;
  this.xVisible = true;
  return this;
};

Shape properties and methods

To enable abstraction, and also because the UIPanel doesnt actually provide access to properties to such as current height etc, any properties of interest are managed through useful properties in the cShape class. In fact, the actual setting of width position and so on are only actually committed to the underlying shape once when complete. This means that the z-order becomes a function of when you commit the shape, not when you create it. The other benefit of this is that I can minimize the number of calls I make to the API.

Mimic the Excel addshape. Note that for the moment, the shapeType is ignored, since i can only do rectangles using the UIApp.

cShapeContainer.prototype.addShape =
      function (shapeType,shapeLeft,shapeTop,shapeWidth,shapeHeight){
      return (new cShape(shapeType == SHAPETYPES.stPanel))
              .setWidth(shapeWidth)
              .setHeight(shapeHeight)
              .setTop(shapeTop)
              .setLeft(shapeLeft)
              .setRounded(this.isRounded(shapeType));
};
Here are a few examples of setting positions etc.
  self.shape()
    .setText(self.text())
    .setVisible (self.paramShapeType() != SHAPETYPES.stNone);
    
  if (self.isData()){
    self.shape()
      .setLeft(self.myLeft())
      .setWidth(self.myWidth());
  }
  self.shape().commit();

Note the use of the .commit() method, which does the below. In other words, all the shape attributes aregenerally all  set before committing. An annoying characteristic of the UIApp and the AbsolutePanel, is that they do not self size. Part of the function of the commit method is to automatically tweak the AbsolutePanel so that it is always big enough to show all the shapes.

cShape.prototype.commit = function() {
  // position and size the box
  this.box()
    .setHeight (this.height())
    .setWidth(this.width())
    .setStyleAttribute('backgroundColor',this.xBackgroundColor)
    .setStyleAttribute('color',this.xColor)
    .setStyleAttribute('textAlign',this.xTextAlign)
    .setStyleAttribute('verticalAlign',this.xVerticalAlign)
    .setStyleAttribute('fontSize',this.xFontSize)
    .setStyleAttribute('border',this.borderCss())
    .setStyleAttribute('borderRadius',this.borderRadiusCss());
   
  if(!this.xIsaPanel)this.box().setText(this.text());   
  this.panel().setWidgetPosition(this.box(), this.left() , this.top() );
  this.box().setVisible(this.visible());
 
  // adjust the absolute panel so its always big enough with a small % border
  var smallPercent = 1.05;
  var x= (this.height() + this.top() ) * smallPercent ;
  if ( x > this.shapePanel().xMaxHeight)  {
    this.panel().setHeight(this.shapePanel().xMaxHeight =  x);
  }
 
  x= (this.width() + this.left())* smallPercent;
  if ( x > this.shapePanel().xMaxWidth)  {
    this.panel().setWidth(this.shapePanel().xMaxWidth =  x);
  }

  return this;
};

the show() method finally will display all the committed shapes, and tweak the UiApp so that it is big enough for the AbsolutePanel

function showPanel() {
  usePanel().app().setHeight(usePanel().xMaxHeight).setWidth(usePanel().xMaxWidth);
  ActiveSheet().show(usePanel().app());
  return shapePanel;
}

Full code of the cShape class

You can find the code (its a work in progress) and test data in the VBA to Google Apps Script Roadmapper project download 

What next?

For more like this, see  From VBA to Google Apps Script . Why not join our forum,follow the blog or follow me on twitter to ensure you get updates when they are available. Much of  the code referred to here is the mcpher library. You can find out how to include it in your project here.

Code


Take a look at  From VBA to Google Apps Script for more like this.. In the meantime why not join our forum,follow the blog or follow me on twitter to ensure you get updates when they are available.  

Transitioning is covered more comprehensively in my my book, Going Gas - from VBA to Apps script, available All formats are available now from O'Reilly,Amazon and all good bookshops. You can also read a preview on O'Reilly.





Comments