CODEWITHSUNDEEP
google-apps-scriptgoogle-docsgoogle-apps
mond007
mond007

Reputation: 109

Open Google Sheet on Client side using Google Apps Script

From an existing GoogleSheet I am trying to create a image button and attach a small script that will open another GoogleSheet using the Id of the target Googlesheet. I have done the image button and know how to attach a function script to it.

I have tried the following : 

function Open2017File() {
  var ss = SpreadsheetApp.OpenById("1mTz8YaH_lu6R3icbSCpgx0Oy-AB4a98thDPP0m1xNhU");
  Logger.log(ss.getName());
}

However upon further reading I have found this only opens the file for server side scripting...

Can someone help me by telling me the equivalent command to open the GoogleSheet on the client side. ?

Thanks in advance

Upvotes: 1

Views: 4732

Answers (3)

René Smeets
René Smeets

Reputation: 1

Because it isn't possible to switch to another sheet (tab) I found a work-arond.

Just hide one or more sheets until you only have the sheet you wanted.

And go back by unhidden sheets.

Upvotes: 0

ScottGutman
ScottGutman

Reputation: 302

You could use this code to open a new sheet:

function openNewSheet() {
  var spreadsheetId = YOUR_ID_HERE;
  var url = "https://docs.google.com/spreadsheets/d/"+spreadsheetId;
  var html = "<script>window.open('" + url + "');google.script.host.close();</script>";
  var userInterface = HtmlService.createHtmlOutput(html);
   SpreadsheetApp.getUi().showModalDialog(userInterface, "Open Sheet");
  }

I got this from this post: https://www.youtube.com/watch?v=2y7Y5hwmPc4

Upvotes: 3

Anton Dementiev
Anton Dementiev

Reputation: 5716

You can achieve this by using the built-in HtmlService and scriptlets. Go to 'File' - 'New' - 'Html file' and add a HTML page to your project. I called mine 'sidebar'. The '<?!= ?>' notation is used to output variables passed to html by '.gs' code. The exclamation mark '!' indicates that anything between these symbols will be printed directly to the page.

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    <a href="<?!= spreadsheet.url ?>"> Open spreadsheet <?!= spreadsheet.name ?> </a>
  </body>
</html>

The code below adds the custom menu to the spreadsheet - clicking on 'Show sidebar' menu option executes the 'showSidebar()' function. Inside this function, you should first get the instance of the HtmlTemplate class for the sidebar. Note that at this point it's just a JS object that we can define custom properties for. That's where the scriptlets come into play.

The next step is to get the url and file name of the 2nd sheet by calling 'getSpreadsheet(id)' function and pass variables to HtmlTemplate.

Finally, calling evaluate() method on the template will convert the template to HtmlOutput object ready to be consumed by showSidebar() method. Properties we passed to the template will be printed to the resulting html:

var ui = SpreadsheetApp.getUi();
var spreadsheetId = YOUR_ID_HERE;

function onOpen() {

ui.createMenu('User options').addItem('Show sidebar', 'showSidebar').addToUi();

}

function showSidebar() {

 var template = HtmlService.createTemplateFromFile('sidebar');
 var spreadsheet = getSpreadsheet(spreadsheetId);

  template.spreadsheet = {      //passing data to the template
    url: spreadsheet.getUrl(), 
    name: spreadsheet.getName()
  };




   ui.showSidebar(template.evaluate()); //evaluate runs the scriptlets 
                                       //and creates the fully-formed DOM tree

}

function getSpreadsheet(id) {


  return SpreadsheetApp.openById(id);


}

Of course, you can create a shortcut by assigning 'showSidebar' function to the button inside the spreadsheet for easier access.

Upvotes: 1

Related Questions