Running a script from a hyperlink in a cell

Question

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

Finegan

Answer 1

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
}

Eleonoraeleonore

I've implemented to call a script that opens a sidebar. Oddly I get the error that Exception: You do not have permission to call Ui.showSidebar.. When I call the script that opens sidebar directly, it works fine. – Dagan

Follow up to my previous comment, looks like onEdit being a simple trigger can't cannot acess a service for which authorization is needed. developers.google.com/apps-script/guides/triggers/#restrictions – Dagan

Answer 2

You cannot run a google apps script function directly by URL, if that's what you're thinking.

• You could implement a script as a webapp, with a doPost() method that modifies your spreadsheet. The hyperlink would need to include a parameter indicating where you were in the spreadsheet, since the webapp would have no context information from the spreadsheet.

• You could insert a picture of a button, and link your script to it. If this script is container-bound to the spreadsheet, it would be able to access context info such as .activeCell() determine where the user is.

• You could add a custom menu function that calls an embedded script.

Nightwalker

Answer 3

If all you want is a doodad on the sheet that runs script, but you don't want to have to draw a button every time, or you want the doodad copy-able across many rows, you can use a checkbox. Then you can handle the onEdit and set the value of the cell back to false. Or set it to true while the script is running, and then back to false.

Natterjack

You can also add a protection to the cell afterwards (I need a Send Email button for every row.. So when it's sent - you can see it, but can't easily uncheck it. But if you need you can bypass the warning, and send the email again). – Nob

Answer 4

I know this is an old thread, but I had the same question and came up with a workaround so I figured it may help others.

As Mogsdad mentioned, you can insert a button and link your script to it. If you want the illusion of clicking on a hyperlink in a cell, you can make the button the same size as your cell or text and make both the fill and line colors transparent. It helps to have some color, put the button in place, then edit it to remove the color.

To the user, it appears that the function runs when they click on the text, but really it's an invisible button. It's not a hyperlink, but I believe it achieves the goal of clicking on "text" to launch a script.

Ogrady

Answer 5

A possible solution is to have a checkbox, and let edits of the sheet (including checking the checkbox) trigger (via onEdit()) a call to your function if the checkbox is checked, and programmatically uncheck it. That way, the checkbox acts as a clickable button. Here's the code:

let sheet = SpreadsheetApp.getActiveSheet();

function onEdit() {
  let checkBox = sheet.getRange('A1');
  if(checkBox.getValue()) {
    sheet.getRange('B1').setValue('Running...');
    myFunction();
    sheet.getRange('B1').setValue('Done.');
    checkBox.setValue(false);
  }
}

function myFunction() {
  // your code here
}

Murdocca