Trigger Script in Spreadsheet in Similar Fashion to a Link

Question

I currently have a button on my spreadsheet (a text box image that triggers the script). Unfortunately this button doesn't work when using a tablet. Is there a way of triggering a script to run via a link or the same way you can click on a link on e field in a spreadsheet?

Answer 1

Scripts can be invoked as custom functions (e.g., =myFunction() entered into a cell); but when used in this way, they can only affect the cell into which they're entered. Generally, the recommended way is to use custom menu commands (as I described, e.g., here).

But if you really want a hyperlink, here is a way to do this. Warning: this is a kludge.

1. Add to your script the following function:

function params() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var ssid = ss.getId();
  var sheet = ss.getActiveSheet();
  var sheetName = sheet.getName();
  return '?ssid='+ssid+'&name='+sheetName;
}

2. Rename the function you want to run as doGet and rewrite it as follows:

function doGet(e) {
  var ssid = e.parameters.ssid;
  var sheetName = e.parameters.name;
  var ss = SpreadsheetApp.openById(ssid);
  var sheet = ss.getSheetByName(sheetName);

  //   do whatever you want with this spreadsheet and sheet

  return HtmlService.createHtmlOutput('<p>Script executed. Close this window to return to the spreadsheet.</p>');
} 

3. Deploy the script as a web app. The default parameters (run as you; only you have access) are good for this. You will get a URL of the web app.

4. In the spreadsheet, create a hyperlink with the following:

=HYPERLINK("https://script.google.com/macros/s/ ... /exec"&params())

where the URL is the web app URL that you got in step 3. This is the link that will execute the script when clicked.

Explanation:

• When running as a web app, the script does not know what spreadsheet and what sheet you are working on. This is why the function params() obtains these parameters and adds them to the hyperlink. The script gets them from e.parameters object.

• Unfortunately, clicking the hyperlink will open a new tab. That tab will not have much content (only the message "Script executed. Close this window to return to the spreadsheet.") but I can see this being an annoyance. This is why I called this a kludge.