Google-sheets – Run script when clicked on picture in published Google spreadsheet

google sheetsgoogle-apps-script

I used the script underneath in Google Spreadsheet and added an image that when clicked upon runs the GOTOSHEETONE function to navigate to sheet: SHEETONE.

So far so good. However, when I publish my document this function is not executed anymore. A link to a Google form, which is also in my document, does work when published. Is there a way to make this function also run in published document mode?

function showSheetByName(Name) {
   var ss = SpreadsheetApp.getActiveSpreadsheet();
   var sheet = ss.getSheetByName(Name);
   SpreadsheetApp.setActiveSheet(sheet);
}

function GOTOSHEETONE() {
   showSheetByName("SHEETONE");
}

Best Answer

Is there a way to make this function also run in published document mode?

The short answer is, unfortunately, no. The published version of a spreadsheet has many functional limitations, and this is one of them.

Related Solutions

Google-sheets – How to automatically create a templated Google sheet

Try this:

This function get called when 'Create Sheet'!B1 gets edited with a new name.

function onEdit(e) {
  var rg=e.range;
  var sh=rg.getSheet();
  if(sh.getName()=="Create Sheet" && rg.columnStart==2 && rg.rowStart==1){
    var name=sh.getRange('B1').getValue();
    var ss=e.source;
    if(name){
      var ns=ss.insertSheet(name);//create new sheet with name from Create Sheet B1
      var osh=ss.getSheetByName('Original');
      var org=osh.getRange('A1:B3');
      org.copyTo(ns.getRange('A1:A3'));//copy original content to new sheet
      var d=Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "MM dd,yyyy");
      var t=Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "HH:mm");
      ns.getRange('B1:B3').setValues([[name],[d],[t]]);//Insert some new values into new sheet
      sh.getRange(1,2).clear();//Clears the name that was originally typed into Create Sheet B1
    }
  }  
}

The Sheet Named 'Create Sheet' Looks Like this:

And you type the name of the new sheet into Create Sheet B1

The Original Sheet Looks like this:

The contents of the Original Sheet get copied into the new sheet contents and format.

The New Sheet is given the name that was typed into Create Sheet B1. This is what the new sheet looks like:

Google Sheets – Run Script When a Sheet is Added

Use the following function, coupled with a trigger "From Spreadsheet > On Change"

function newSheet(e) {
  if (e.changeType == 'INSERT_GRID') {
    Browser.msgBox('New sheet was added');  // or whatever you want to do
  }
}

Best Answer

Related Solutions

Google-sheets – How to automatically create a templated Google sheet

Google Sheets – Run Script When a Sheet is Added

Related Topic