How to run javascript byc clicking a cell in google sheets?

Question

Google sheets allows you to write scripts in javascript, the same way excel lets you write VBA to automate tasks.

I'd like to make a cell in my google spreadsheet clickable, so that when it is clicked, my function will run.

I've found that it's possible to create custom buttons by inserting a drawing, but I'd really like this to be any time a particular cell is clicked, rather than a button which can move around and looks awkward in the sheet.

Is it possible?

Thanks for any advice.

Answer 1

There is not an onClick event for cells but what you can do is instruct the user to enter your formula/custom function into the cell as described at https://developers.google.com/apps-script/execution_custom_functions?hl=es#using.

Similarly, you could have the cell pre-populated with the formula and simply ask the user to edit it and then press enter to run the function.

Answer 2

What you are searching for, is onSelectionChange(), it runs automatically when a user changes the selection in a spreadsheet.

Try the following code and read the developers link.

/**
 * The event handler triggered when the selection changes in the spreadsheet.
 * @param {Event} e The onSelectionChange event.
 */
function onSelectionChange(e) {
  // Set background to red if a single empty cell is selected.
  var range = e.range;
  if(range.getNumRows() === 1 
      && range.getNumColumns() === 1 
      && range.getCell(1, 1).getValue() === "") {
    range.setBackground("red");
  }
}