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 likely) 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 don’t 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.
Implementation 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.
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
The code is in the mcpher library. See here for how to incorporate it into your project.
// emulate excel shapes by using the UI
// public variable holding the panel to contain the shapes
var shapePanel;
function usePanel() {
return shapePanel ? shapePanel : shapePanel = new cUiPanel();
}
function showPanel() {
usePanel().app().setHeight(usePanel().xMaxHeight).setWidth(usePanel().xMaxWidth);
SpreadsheetApp.getActiveSpreadsheet().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;
};
cShape.prototype.app = function() {
return this.shapePanel().app();
};
cShape.prototype.panel = function() {
return this.shapePanel().absolutePanel();
};
cShape.prototype.shapePanel = function() {
return this.xShapePanel;
};
cShape.prototype.box = function() {
return this.xBox;
};
cShape.prototype.borderCss = function() {
return '1px solid gray';
};
cShape.prototype.borderRadiusCss = function() {
return this.xRounded ? '5px' : '0px';
};
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;
};
cShape.prototype.left = function() {
return this.xLeft;
};
cShape.prototype.visible = function() {
return this.xVisible;
};
cShape.prototype.top = function() {
return this.xTop;
};
cShape.prototype.height = function() {
return this.xHeight;
};
cShape.prototype.width = function() {
return this.xWidth;
};
cShape.prototype.text = function() {
return this.xText;
};
cShape.prototype.box = function() {
return this.xBox;
};
cShape.prototype.setHeight = function(height) {
this.xHeight = height;
return this;
};
cShape.prototype.setWidth = function(width) {
this.xWidth = width;
return this;
};
cShape.prototype.setLeft = function(left) {
this.xLeft = left;
return this;
};
cShape.prototype.setTop = function(top) {
this.xTop = top;
return this;
};
cShape.prototype.setText = function(text) {
this.xText = text;
return this;
};
cShape.prototype.setRounded = function(b) {
this.xRounded = fixOptional(b,true);
return this;
};
cShape.prototype.setVisible = function(b) {
this.xVisible = fixOptional(b,true);
return this;
};
cShape.prototype.setBackgroundColor = function(c) {
this.xBackgroundColor = c;
return this;
};
cShape.prototype.setColor = function(c) {
this.xColor = c;
return this;
};
cShape.prototype.setVerticalAlign = function(c) {
this.xVerticalAlign = c;
return this;
};
cShape.prototype.setTextAlign = function(c) {
this.xTextAlign = c;
return this;
};
cShape.prototype.setFontSize = function(c) {
this.xFontSize = c;
return this;
};