Resize drawing shape in Sheets

Question

I am looking to resize a rectangle drawing in google sheets using apps script. I would like to resize the drawing to an enlarged width if the width is condensed, or a condensed width if it is enlarged. In both cases, I want to keep the height, and topleft location the same.

Enlarged size (1242) Condensed Size (94)

I have found how to log the drawings width but was unable to figure out how to resize the shape. It is the only drawing on the target (active) sheet.

The purpose of this is to condense the shape when slicers are being used, and enlarge the shape over the slicers for a screen shot after the data has been sliced.

Using the development documentation, I was able to log the width of all drawings on the active sheet, but had trouble with syntax around calling setWidth.

I started with this code, but it doesnt have the logic I require and didn't work.

function setDrawWidth() {
var drawings = SpreadsheetApp.getActiveSheet().getDrawings();
        drawings.setWidth(94);
}

Any help would be greatly appreciated!

Answer 1

Treat the Data from .getDrawings() as an Array

I noticed that you did not use an index when using drawings.setWidth(). You need to do so since the data returned by .getDrawings() is an array.

Setup

I created the following sample test case for this post:

Logging the list of Drawings

If you log the return value of `.getDrawings()' using:

function testDrawing() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var drawings = ss.getDrawings();
  console.log(drawings) //logs all shapes
  console.log(drawings.length) //logs number of shapes
}

You will get an output just like this:

Wherein the two shapes were logged as an array.

Logging the Sizes of the Shapes

To get the height and width of the individual shapes, you may want to loop through the array returned .getDrawings() and then use getLength() and getHeight(). You may do it like this:

  drawings.map((x,i)=>{
    var drawingNumber = i + 1;
    var width = x.getWidth();
    var height = x.getHeight();
    console.log("Drawing#: " +  drawingNumber + " width: " + width + " height: " + height);
  });

This will log the following:

Setting the Size of the Drawings

Setting the Size of the Drawings is similar to the previous process. In this case, you will use .setHeight() and `.setWidth().

  drawings.map((x,i)=>{
    var drawingNumber = i + 1;
    x.setWidth(100);
    x.setHeight(50);
  });

Result:

However, if you wish to only set the sizes of some shapes and not all, you will need to manually use their index.

  drawings[0].setHeight(100);
  drawings[0].setWidth(50);

Whole Code

function testDrawing() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var drawings = ss.getDrawings();
  console.log(drawings) //logs all shapes
  console.log(drawings.length) //logs number of shapes
  drawings.map((x,i)=>{
    var drawingNumber = i + 1;
    var width = x.getWidth(); //gets width
    var height = x.getHeight(); //gets height
    console.log("Drawing#: " +  drawingNumber + " width: " + width + " height: " + height);
  });
  drawings.map((x,i)=>{
    var drawingNumber = i + 1;
    x.setWidth(100); //sets width
    x.setHeight(50); //sets height
  });
}

References:

• Class Drawing