4

I've got a spreadsheet accessed regularly by a team of non-techies and it would speed up our workflow if I could have clickable buttons auto-generated on certain rows (depending on the row's contents) that launch Google Apps Scripts.

An elegant solution (HTML Service or UI Service) would be nice, but I don't mind using Drawings. The problem is, I don't know how to generate them programmatically.

Am I missing something really obvious, or maybe Google Sheets really just wants you to keep UI stuff in dialogs/sidebars?

Community
  • 1
  • 1
Legume Duprix
  • 43
  • 1
  • 3
  • Button in a Cell. I am looking for problems like this, I want to understand the business process, as I am working on a way to solve this using formulas. I would like "dummy but realistic-looking data" to get a feel for the business process. ... See www.cellmaster.com.au. – eddyparkinson Feb 11 '15 at 04:35

2 Answers2

2

No, you can't put a button into a cell of a Google Sheet. You can put an image of a button into a spreadsheet, with a script attached to it. As far as embedding something into a cell, all you can do is to put a hyperlink into a cell. The link can launch a stand alone Apps Script. And you can programmatically put a link into a cell. There are three other options, have a custom menu, dialog box or sidebar open automatically when the spreadsheet opens.

A hyperlink is a formula, and you can add a formula with code:

 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheets()[0];

 var cell = sheet.getRange("B5");
 cell.setFormula("=hyperlink("www.google.com", "search the web");

Google Documentation - Set Formula

Alan Wells
  • 30,746
  • 15
  • 104
  • 152
  • 1
    Thanks! That's what I thought, I think I'm trying too hard to get slick looking results in Google Sheets. It's ever so slightly annoying that you have to click twice to activate it, but otherwise the Link method is fine. – Legume Duprix Feb 10 '15 at 02:38
  • 1
    we actually can position images ( as buttons ) over a specific cell locations with `SpreadsheetApp.getActiveSheet().insertImage(url, column, row)`, just can't assign a script automatically to them or delete them through code. – Bryan P Feb 10 '15 at 07:34
  • You have to hover over the image ( not a button ) to get the dropdown menu to appear in top right. Assign script option is there but like in my alternative answer here i wouldn't vote for putting this into practice – Bryan P Feb 15 '15 at 12:10
  • I wonder if this is still the case with Google Sheets nowadays... I mean how hard can it be to allow programmatic button creation? – akds May 19 '22 at 14:03
1

I'd vote against using images in Sheets as buttons to trigger scripts altogether.

Minimal html code to trigger a script function from the sidebar... 

function onOpen() {
  showSidebar(); 
}

function showSidebar() {
  return SpreadsheetApp.getUi()
  .showSidebar(HtmlService
               .createHtmlOutput('<button onclick="google.script.run.myFunction()">Run</button>'));
}
Bryan P
  • 5,031
  • 3
  • 30
  • 44
  • 1
    Thanks for the sample code! Unfortunately for this I need buttons (gonna just use links) that actually reside on certain rows depending on the sheet contents. The help is much appreciated though! – Legume Duprix Feb 10 '15 at 02:40