-2

Is there a way I can add a drawing to a google sheet that when clicked on takes me to another separate Google Sheet? I understand how to insert a button, I just don't understand the apps script to make it work. Thanks!

Blakebacher
  • 1
  • 1

1 Answers1

1

Using this function

function gotoSheet(name) {
  SpreadsheetApp.getActive().getSheetByName(name).activate();
}

you can add buttons to take you to other sheets like this:

Assign this one a button:

function gotoSheet1()  {
  gotoSheet('Sheet1');
}

Assign this to another button:

function gotoSheet2() {
  gotoSheet('Sheet2');
}

If you wish you can now assign buttons to script programmatically using Sheets.getDrawings()

function assignButtons() {
  const ss=SpreadsheetApp.getActive();
  const sh=ss.getSheetByName('Sheet1');
  const btns=sh.getDrawings();
  btns[0].setOnAction('gotoSheet2');
  btns[1].setOnAction('gotoGoogle');
}

You can also set width, height and position of your buttons under program control using the Class Drawing Methods and getContainerInfo();

Answer to your additional question:

function showURL(url){
  var html = HtmlService.createHtmlOutput('<html><script>'
  +'window.close = function(){window.setTimeout(function(){google.script.host.close()},9)};'
  +'var a = document.createElement("a"); a.href="'+url+'"; a.target="_blank";'
  +'if(document.createEvent){'
  +'  var event=document.createEvent("MouseEvents");'
  +'  if(navigator.userAgent.toLowerCase().indexOf("firefox")>-1){window.document.body.append(a)}'                          
  +'  event.initEvent("click",true,true); a.dispatchEvent(event);'
  +'}else{ a.click() }'
  +'close();'
  +'</script>'
  // Offer URL as clickable link in case above code fails.
  +'<body style="word-break:break-word;font-family:sans-serif;">Failed to open automatically. <a href="'+url+'" target="_blank" onclick="window.close()">Click here to proceed</a>.</body>'
  +'<script>google.script.host.setHeight(40);google.script.host.setWidth(410)</script>'
  +'</html>')
  .setWidth( 90 ).setHeight( 1 );
  SpreadsheetApp.getUi().showModalDialog( html, "Opening ..." );
}

The above function copied from here

function gotoGoogle() {
  showURL('http://www.google.com');
}

Remove pop up blocker

Cooper
  • 59,616
  • 6
  • 23
  • 54