CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Punchlinern
Punchlinern

Reputation: 754

Open Google Spreadsheet via Google Apps Script

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);
}

Upvotes: 8

Views: 46227

Answers (2)

TC76
TC76

Reputation: 860

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.

Upvotes: 2

jondoig
jondoig

Reputation: 181

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

  1. Open the containing folder in Google Drive
  2. Right-click the spreadsheet
  3. Select Get link
  4. 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");

Upvotes: 11

Related Questions