72

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?

Rubén
  • 34,714
  • 9
  • 70
  • 166
James Moore
  • 8,636
  • 5
  • 71
  • 90

5 Answers5

125

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

Eduardo
  • 22,574
  • 11
  • 76
  • 94
  • 2
    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:( – fullOfGAS Sep 29 '12 at 09:26
  • 1
    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 – Omar Elfada Mar 06 '13 at 08:34
  • 7
    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. – Jpsy Mar 03 '16 at 09:56
  • This was always like this. The element is not bound to a cell it kinda floats in the air above the spreadsheet. – Eduardo Mar 03 '16 at 14:55
  • 1
    Then this reply does not answer the OP's question, sorry. – Jpsy Mar 08 '16 at 16:37
  • 2
    @Jpsy 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. – CaptainMarvel Jul 21 '17 at 19:55
  • 2
    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 – RobChooses Feb 20 '19 at 16:52
  • 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. – Eduardo Dec 23 '19 at 06:55
  • 1
    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. – clozach Jun 14 '20 at 15:19
67

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.

Jpsy
  • 20,077
  • 7
  • 118
  • 115
  • 1
    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. – gr1zzly be4r Oct 25 '17 at 16:47
  • Anything accessible by builtin functions could be concatenated to the URL, e.g. =CONCATENATE("?row=",row(),"&col=",COLUMN()) – Martin of Hessle May 13 '19 at 13:33
  • 4
    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]() ` – David Aug 18 '19 at 16:35
7

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:

TheMaster
  • 45,448
  • 6
  • 62
  • 85
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.

VLBaindoor
  • 224
  • 3
  • 7
0

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

Wael Galal El Deen
  • 1
  • 1