Creating Colorful Buttons on Google Spreadsheet using Google Apps Script

submit.md

Creating Colorful Buttons on Google Spreadsheet using Google Apps Script

This is a sample script for creating the colorful buttons on Google Spreadsheet on Google Apps Script.

In order to achieve this, I have been looking for the method for creating the PNG image with the alpha channel using Google Apps Script. Recently, finally, I could find it. By this, the goal of this report got to be able to be achieved by the report of "Creating PNG Image with Alpha Channel using Google Apps Script".

Demo

Usage

1. Prepare Google Spreadsheet

Please create new Google Spreadsheet.

2. Install Google Apps Script library of DocsServiceApp.

The library's project key is 108j6x_ZX544wEhGkgddFYM6Ie09edDqXaFwnW3RVFQCLHw_mEueqUHTW. Ref You can see the method for installing the GAS library at the official document. Ref

3. Enable Drive API.

Please enable Drive API at Advanced Google services. Ref

4. Sample script.

Please copy and paste the following sample script to the container-bound script of your Google Spreadsheet. Before you use this script, please set the variables of object and sheetName.

// These functions are the sample functions for checking the buttons.
const sample1 = (_) => SpreadsheetApp.getUi().alert("sample1");
const sample2 = (_) => SpreadsheetApp.getUi().alert("sample2");
const sample3 = (_) => SpreadsheetApp.getUi().alert("sample3");
const sample4 = (_) => SpreadsheetApp.getUi().alert("sample4");
const sample5 = (_) => SpreadsheetApp.getUi().alert("sample5");
const sample6 = (_) => SpreadsheetApp.getUi().alert("sample6");
const sample7 = (_) => SpreadsheetApp.getUi().alert("sample7");

// Please run this function.
function main() {
  //  Please set the following object.
  const object = {
    iconSize: { width: 100, height: 50 }, // This is the button size.
    fontSize: 15, // This is the font size in the button.
    functions: [
      { name: "sample1", color: "#ff0000" }, // Color code is from https://colorswall.com/palette/102/
      { name: "sample2", color: "#ffa500" },
      { name: "sample3", color: "#ffff00" },
      { name: "sample4", color: "#008000" },
      { name: "sample5", color: "#0000ff" },
      { name: "sample6", color: "#4b0082" },
      { name: "sample7", color: "#ee82ee" },
    ],
  };
  const sheetName = "Sheet1"; // Please set the sheet name. The buttons are put to this sheet.

  // 1. Create new Google Slides with the custom page size. This is used as a temporal file.
  const { width, height } = object.iconSize;
  const presentationId = DocsServiceApp.createNewSlidesWithPageSize({
    title: "temp",
    width: { unit: "pixel", size: width },
    height: { unit: "pixel", size: height },
  });

  // 2. Create a sample shape to Google Slides.
  const url = `https://docs.google.com/feeds/download/presentations/Export?id=${presentationId}&exportFormat=png`;
  const headers = { authorization: "Bearer " + ScriptApp.getOAuthToken() };
  const blobs = object.functions.map(({ name, color }) => {
    const s = SlidesApp.openById(presentationId);
    const slide = s.getSlides()[0];
    const shapes = slide.getShapes();
    if (shapes.length > 0) {
      shapes.forEach((s) => s.remove());
    }
    slide.getBackground().setTransparent();
    const obj = slide
      .insertShape(SlidesApp.ShapeType.ROUND_RECTANGLE)
      .setWidth(object.iconSize.width / 1.333)
      .setHeight(object.iconSize.height / 1.333)
      .alignOnPage(SlidesApp.AlignmentPosition.CENTER);
    obj.getBorder().setTransparent();
    obj.getFill().setSolidFill(color);
    const text = obj.getText();
    text.setText(name).getTextStyle().setFontSize(object.fontSize);
    text
      .getParagraphStyle()
      .setParagraphAlignment(SlidesApp.ParagraphAlignment.CENTER);
    s.saveAndClose();
    return UrlFetchApp.fetch(url, { headers: headers }).getBlob();
  });

  // 4. Put the created image to Google Spreadsheet.
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  sheet
    .setRowHeight(2, object.iconSize.height)
    .setColumnWidths(1, object.functions.length, object.iconSize.width);
  blobs.forEach((b, i) =>
    sheet.insertImage(b, i + 1, 2).assignScript(object.functions[i].name)
  );

  // 5. Remove the Google Slides.
  DriveApp.getFileById(presentationId).setTrashed(true);
}

• If you cannot see the buttons on the sheet, please reopen the Spreadsheet or please move other tab and return the tab. By this, the buttons can be seen.
• If an error related to Drive API occurs, please enable Drive API at Advanced Google services.

5. Testing.

When above script is run, the situation shown in above demonstration video is obtained.

References

• Creating PNG Image with Alpha Channel using Google Apps Script
• DocsServiceApp