Open Google Spreadsheet via Google Apps Script

Question

Is it possible to open a Google Spreadsheet via Google Apps script?
I'm trying to create a way to link to a certain cell in a spreadsheet, but I can't manage to open a sheet from a script (deployed as a web app).
I want to open the spreadsheet and then set the active sheet and range (I know how to do the latter, when the spreadsheet is opened).

Thanks in advance!

EDIT
Here's my current code:

function doGet(e){
  var ss = SpreadsheetApp.openById("[id]");
  SpreadsheetApp.setActiveSpreadsheet(ss);
  var sheet = ss.getSheetByName("Kunder");
  var row = Number(e.parameter.row); 
  var col = Number(e.parameter.col); 
  var range = sheet.getRange(row, col);

  //OPEN THE SPREADSHEET HERE

  SpreadsheetApp.setActiveSheet(sheet);
  SpreadsheetApp.setActiveRange(range);
}

jondoig · Accepted Answer · 2016-03-12 05:12:49Z

11

You need to use the correct ID for the spreadsheet. One way to find it is:

Open the containing folder in Google Drive
Right-click the spreadsheet
Select Get link
Copy the URL shown

The URL will be in the form: https://drive.google.com/open?id=xoxoxoxoxoxoxoxoxoxoxoxo

Use the id parameter value as the argument to openById, e.g.:

var ss = SpreadsheetApp.openById("xoxoxoxoxoxoxoxoxoxoxoxo");

answered Mar 12, 2016 at 5:12

jondoig

1812 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

TC76 Over a year ago

This doesn't actually open the file though. According to the documentation: "// Note that the spreadsheet is NOT physically opened on the client side."

MC9000 Over a year ago

The open function is completely useless since you can't modify anything on a spreadsheet if it's only "opened" on the server.

TC76 · Accepted Answer · 2020-03-07 16:32:59Z

2

Found this at https://tanaikech.github.io/2018/02/20/open-site-with-new-window-using-google-apps-script/

function myFunction() {
  var js = " \
    <script> \
      window.open('https://tanaikech.github.io/', '_blank', 'width=800, height=600'); \
      google.script.host.close(); \
    </script> \
  ";
  var html = HtmlService.createHtmlOutput(js)
    .setHeight(10)
    .setWidth(100);
  SpreadsheetApp.getUi().showModalDialog(html, 'Now loading.'); // If you use this on Spreadsheet
//  DocumentApp.getUi().showModalDialog(html, 'Now loading.'); //  If you use this on Document
//  SlidesApp.getUi().showModalDialog(html, 'Now loading.'); //  If you use this on Slides
}

I altered the link and the window size so that it would simply open in a new tab (you could probably remove '_blank' as well...)

      <script> \
        window.open('https://docs.google.com/spreadsheets/d/" + custFileID.id + "', '_blank'); \
        google.script.host.close(); \
      </script> \

custFileID is another function that finds the file ID. Just replace that with your own variable or file ID.

answered Mar 7, 2020 at 16:32

TC76

8701 gold badge8 silver badges20 bronze badges

2 Comments

aNewb Over a year ago

what is _blank? Does it require using a special library?

TC76 Over a year ago

That’s, afaik, just HTML to open the browser in a new window. Not library needed. You can also name the window in place of “_blank”.

Collectives™ on Stack Overflow

Open Google Spreadsheet via Google Apps Script

2 Answers 2

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related