Running a script from a hyperlink in a cell

google-apps-script, google-sheets

Solution

A solution for this that I have implemented successfully is to use onEdit in combination with a data validation for the target cell. Not exactly clicking a hyperlink, but it does the trick:

The data validation will insert a dropdown list into that cell, from which the user can select the appropriate script to run or the cell value selected can be grabbed via onEdit and passed as a parameter.

For example, if I have a sheet called "sheetToWatch" with a column "columnWithDataValidations" and that column has a data validation in each row so it is either blank or 'RUN SCRIPT', you could control running the script as such:

function onEdit(e) {
  var aSheet = SpreadsheetApp.getActiveSheet();
  var aCell = aSheet.getActiveCell();
  var aColumn = aCell.getColumn();
  var aRow = aCell.getRow();

  if (aSheet == "sheetToWatch" && aColumn == "columnWithDataValidations" && aCell.getValue() == 'RUN SCRIPT') {
    runTheScript()
  } else {
    //do nothing
  }

  return
}

Problem

I have failed to find documentation for running a script by clicking on a value with hyperlink in a cell in google spreadsheets. Is it possible? Thanks

Original source