3

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?

1
  • This may be a potential solution as well
    – drn614
    Nov 15, 2017 at 23:21

1 Answer 1

2

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;
}
  1. 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>');
} 
  1. 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.

  2. 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.