CODEWITHSUNDEEP
user-interfacegoogle-apps-scriptgoogle-sheets
James Moore
James Moore

Reputation: 9027

How do you add UI inside cells in a google spreadsheet using app script?

I'd like to add buttons to specific cells in Google docs spreadsheet. The apps script UI documentation talks about how to add a new panel, but it's not clear how UI in that panel could be attached to specific rows or cells.

Is it possible to add UI to particular cells, or are we limited to adding new panels?

Upvotes: 73

Views: 217515

Answers (6)

Mixchange
Mixchange

Reputation: 1208

I have been unable to attach drawings to cells. Instead, I use a visual illusion to make a cell look similar to a button, then use onSelectionChange() to run a script when that cell is clicked. You can't click twice in a row, though. You have to click outside the cell and back in again.

For the visual 3D shading illusion, I use thick colored borders. I give the "button" cell:

  • A thick bottom and right border that is a darker color than the rest of the table cells
  • A thick top and left border that is lighter than the the bottom/right border and lighter than the rest of the cells

One shaded "button"

If I need multiple buttons next to each other, I put an extra column between them or it looks wrong to me because the border between them can only have one color. It's a bit annoying to work with. Here they are without an extra column:

Two "buttons" next to each other

Here they are with an extra column:

Two "buttons" with one column in between them

App script can't listen for someone to click on a cell. It can listen for someone changing which cell is selected, though. When you do that, it's important to check that the click is in the right cell:

function onSelectionChange(event) {
  // Act based on text content
  var activeCell = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getActiveCell();
  var text = activeCell.getValue();
  console.log("text", text);  // for the debugger

  if (text === "thing 1") { function1(); }
  else if (text === "thing 2") { function2(); }
}

I had a bunch of these "buttons" and I found it annoying to interact with those cells when I wanted to edit them, so I also used a "property" to activate and deactivate that interaction:

function onSelectionChange(event) {
  var isActive = PropertiesService.getScriptProperties().getProperty("active")
  console.log("Cells are active", JSON.stringify(isActive));
  if ( isActive !== "true" ) { return; }
  
  // Act based on text content
  var activeCell = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getActiveCell();
  var text = activeCell.getValue();
  console.log("text", text);  // for the debugger

  if (text === "thing 1") { function1(); }
  else if (text === "thing 2") { function2(); }
}

function activate() {
  // I think these get saved as strings even if you give
  // a boolean value, so I just gave a string
  PropertiesService.getScriptProperties().setProperty("active", "true");
  console.log("After activated", JSON.stringify(PropertiesService.getScriptProperties().getProperty("active")));
}

function deactivate() {
  PropertiesService.getScriptProperties().setProperty("active", "false");
  console.log("After deactivated", JSON.stringify(PropertiesService.getScriptProperties().getProperty("active")));
}

I did use drawings as buttons with the activate and deactivate functions instead of cells because that was less annoying to program. I did have a lot of cell-type "buttons", so it was worth it.

I also changed the colors of the cells when I activated and deactivated them. This part is a bit untested. My code was much more specific to my situation. I was coloring bigger ranges and using different colors.

function activate() {
  PropertiesService.getScriptProperties().setProperty("active", "true");
  setStyle("#cccccc", "#000000", "A1");
}

function deactivate() {
  PropertiesService.getScriptProperties().setProperty("active", "false");
  setStyle("#b7b7b7", "#999999", "A1");  // Maybe not enough contrast for some people
}

function setStyle(backgroundColor, fontColor, cellAddress) {
  var range = SpreadsheetApp.getActiveSheet().getRange(cellAddress);
  range.setBackground(backgroundColor);
  range.setFontColor(fontColor);
}

Upvotes: 0

Wael Galal El Deen
Wael Galal El Deen

Reputation: 136

There is a silly trick to do something that might help you :

  1. You can make the drawing object as tall as your sheet (To appear to every row in the sheet).

  2. You can make the script affects the current cell value by the following code:

    SpreadsheetApp.getActiveSpreadsheet().getActiveCell().setValue(cellValue);

Upvotes: 0

TheMaster
TheMaster

Reputation: 50383

Use checkboxes(say, in F1) instead of buttons/Images. This is a intermediate to a full ui inside cells. Then hook your function, that is supposed to run on button click to onEdit() trigger function.

Sample script:

function onEdit(e){
  const rg = e.range;
  if(rg.getA1Notation() === "F1" && rg.isChecked() && rg.getSheet().getName() === "Sheet1"){
    callFunctionAttachedToImage();
    rg.uncheck();
  }
}

References:

Upvotes: 7

Jpsy
Jpsy

Reputation: 20852

Status 2018:

There seems to be no way to place buttons (drawings, images) within cells in a way that would allow them to be linked to Apps Script functions.

This being said, there are some things that you can indeed do:

You can...

You can place images within cells using IMAGE(URL), but they cannot be linked to Apps Script functions.

You can place images within cells and link them to URLs using:
=HYPERLINK("http://example.com"; IMAGE("http://example.com/myimage.png"; 1))

You can create drawings as described in the answer of @Eduardo and they can be linked to Apps Script functions, but they will be stand-alone items that float freely "above" the spreadsheet and cannot be positioned in cells. They cannot be copied from cell to cell and they do not have a row or col position that the script function could read.

Upvotes: 67

VLBaindoor
VLBaindoor

Reputation: 324

Buttons can be added to frozen rows as images. Assigning a function within the attached script to the button makes it possible to run the function. The comment which says you can not is of course a very old comment, possibly things have changed now.

Upvotes: 4

Eduardo
Eduardo

Reputation: 22824

The apps UI only works for panels.

The best you can do is to draw a button yourself and put that into your spreadsheet. Than you can add a macro to it.

Go into "Insert > Drawing...", Draw a button and add it to the spreadsheet. Than click it and click "assign Macro...", then insert the name of the function you wish to execute there. The function must be defined in a script in the spreadsheet.

Alternatively you can also draw the button somewhere else and insert it as an image.

More info: https://developers.google.com/apps-script/guides/menus

enter image description here enter image description here enter image description here

Upvotes: 125

Related Questions