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

Question

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?

Hilburn

Answer 1

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

Milestone

I know this is an old post but it might save someone time to know that the button you draw cannot be inserted in a cell that is in a frozen row or frozen column:( – Niphablepsia

Be aware that you need to Save And Close the drawing windows and then right click on the button to assign a function WITHOUT the brackets – Arelus

As of today (Feb. 2016) this method does NOT allow to add UI elements within cells. Instead the elements are created stand-alone and will not copy with cells / rows / cols. The cannot access the position in terms of row or col they were placed in and accessed at. – Bargeman

This was always like this. The element is not bound to a cell it kinda floats in the air above the spreadsheet. – Milestone

Then this reply does not answer the OP's question, sorry. – Bargeman

@Bargeman I'd say it does answer the question. The position of the button can be assigned to a hard-coded range or a named range reference in the script however the programmer chooses. This has use case implications, which are obviously different than your interpretation of the question, but it's not perfectly clear what OP means by "attached to specific rows or cells". It's not like a data-bound column in ASP.NET with one button per row, but you can certainly use it to affect certain cell ranges. – Rockey

For anyone in 2019 wondering where the Assign script menu appears, you have to save and close the drawing, and then click the image, and then you will see a "3-dots" menu which has the "Assign Script..." option – Carlicarlick

I just noticed today that when adding an image (not a drawing) in Spreadsheets you can choose to add the image "inside the cell" or "over the cell". This is new, but I don't think when it's inside a cell it can be linked to a script.... I'll update the answer if I find a way to do that. – Milestone

TIP: After the function's been assigned (on a Mac), the 3-dot menu will no longer be available. If you want to reassign or remove the function, ⌘-click on the button first. – Antimere

Answer 2

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.

Bargeman

If Google is watching, it would be a great feature to be able to have a getImagesInSheet() function or something similar that could allow you to name/access images. Then there could be an addScript() function for the Image object where you could add a script to a specific image. – Gearldinegearshift

Anything accessible by builtin functions could be concatenated to the URL, e.g. =CONCATENATE("?row=",row(),"&col=",COLUMN()) – Kurtzman

You can publish the script has a web app and use this HYPERLINK solution to call the script and pass in the function you want to execute as a parameter. then in the doGet(e) function grab the parameter and execute it like this. this[e.parameter.functionName]() – Lissome

Answer 3

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:

• Class Range
• Event Objects

Ambidextrous

Answer 4

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.

Swerve

Answer 5

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);

Carvel