Creating Shape with Google Apps Script in Google Sheets

ONLINECAST

2 min read

·

06-10-2024

12

0

Share

Drawing Shapes in Google Sheets with Google Apps Script: A Beginner's Guide

Ever wished you could add custom shapes to your Google Sheets for a more visually appealing presentation? Google Apps Script empowers you to do just that! This article will guide you through the process of creating shapes in Google Sheets, exploring the functionalities and offering examples to get you started.

The Challenge: Adding Visual Flair to Google Sheets

While Google Sheets offers basic drawing tools, the options are limited. Many users crave the flexibility to add custom shapes, diagrams, or even basic illustrations to enhance their spreadsheets. Google Apps Script, a JavaScript-based scripting language for Google Workspace, provides the solution.

Getting Started: The Code Framework

Let's begin with a simple example. This script draws a rectangle in a specified cell:

function drawRectangle() {
  // Get the active spreadsheet and sheet
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getActiveSheet();

  // Define the shape's properties
  var shape = sheet.drawings.addShape();
  shape.setName('My Rectangle');
  shape.setShapeType(SpreadsheetApp.DrawingShapeType.RECTANGLE);
  shape.setPosition(5, 5); // Top-left corner coordinates
  shape.setSize(100, 50); // Width and height

  // Adjust the shape's appearance (optional)
  shape.setLineWidth(2);
  shape.setLineColor('#FF0000'); // Red
  shape.setFillColor('#00FF00'); // Green
}

Breaking Down the Code:

• SpreadsheetApp.getActiveSpreadsheet(): Retrieves the currently active Google Sheet.
• spreadsheet.getActiveSheet(): Obtains the currently active sheet within the spreadsheet.
• sheet.drawings.addShape(): Creates a new shape object.
• shape.setName(...): Assigns a name to the shape (useful for organization).
• shape.setShapeType(...): Defines the shape's type (rectangle, circle, etc.).
• shape.setPosition(...): Sets the top-left corner position of the shape.
• shape.setSize(...): Specifies the width and height of the shape.
• shape.setLineWidth(...): Controls the thickness of the shape's outline.
• shape.setLineColor(...): Sets the color of the outline using a hexadecimal color code.
• shape.setFillColor(...): Applies a fill color to the shape.

Beyond Rectangles: Exploring Shape Types

Google Apps Script supports a variety of shapes, including:

• SpreadsheetApp.DrawingShapeType.RECTANGLE: A standard rectangle
• SpreadsheetApp.DrawingShapeType.ELLIPSE: An ellipse (circle is a special case of an ellipse)
• SpreadsheetApp.DrawingShapeType.TRIANGLE: An equilateral triangle
• SpreadsheetApp.DrawingShapeType.LINE: A straight line
• SpreadsheetApp.DrawingShapeType.POLYGON: A polygon with user-defined vertices

Advanced Techniques:

• Customizing Appearance: Use methods like shape.setTransparency(), shape.setRotation(), and shape.setLinkUrl() to further enhance your shapes.
• Adding Text: Utilize shape.insertText() to add text within the shape.
• Interactive Shapes: Use shape.onSelectionChange() to trigger events when the shape is selected.
• Creating Custom Shapes: Define your own shapes using the shape.setVertices() method for SpreadsheetApp.DrawingShapeType.POLYGON.

Getting Started:

1. Access the Script Editor: Open your Google Sheet and go to "Tools" -> "Script editor."
2. Paste the code: Copy the code example above and paste it into the Script Editor.
3. Run the script: Click "Run" and select the "drawRectangle" function.
4. View your shape: The rectangle will appear in your Google Sheet.

Conclusion:

With Google Apps Script, you can elevate your Google Sheets to the next level by incorporating custom shapes. Unleash your creativity and explore the possibilities of interactive visualizations, dynamic diagrams, and more. Happy coding!