How to programmatically (via AppsScript) open a specific Sheet in a Google Spreadsheet via URL-CodePudding

I have a source Sheet with URLs in. Those URLs point to specific target Sheets (tabs) in other Spreadsheets. (If I click the links manually they open to the correct Tab/Sheet.)

The URLs look like this: https://docs.google.com/spreadsheets/d/<spreadsheetID>/edit?resourcekey=<ignored>#gid=<sheetid>. (The "gid=NNN" part identifies the Sheet (tab) in the Spreadsheet to make active.)

I can use const sprd = SpreadsheetApp.openByUrl(url) to open the target Spreadsheet programmatically. But to get a specific Sheet (tab), the only useful API I can find is getActiveSheet() but that is documented as returning the Sheet "shown in the UI". But this Spreadsheet isn't show in the UI. And AFAICT, for programmatic access, the "active" sheet is always just the first sheet in the Spreadsheet, independent of what "gid" was in the URL passed to openByUrl().

As a work-around I can parse the "#gid=NNNN" out of the URL. And then openByUrl() the Spreadsheet, invoke getSheets() and compare the gid pulled out of the URL against the .getSheetId() of each sheet until it is found. AFAICT, there is nothing like an getSheetByID() API on the Spreadsheet.

Is there some other approach that I'm missing?

CodePudding user response：

This function requires you to paste the url into a prompt. It will open up the spreadsheet by url and get all the data on the current sheet and display it in a dialog and at the end it will display the name of the sheet.

function getData() {
  const ui = SpreadsheetApp.getUi();
  const r = ui.prompt('Url Dialog', 'Enter Url', ui.ButtonSet.OK_CANCEL);
  if (r.getSelectedButton() == ui.Button.OK) {
    const url = r.getResponseText()
    const ss = SpreadsheetApp.openByUrl(url);
    let id = url.slice(url.indexOf("=")   1);
    let sh = ss.getSheets().filter(sh => sh.getSheetId() == id)[0];
    let vs = sh.getDataRange().getDisplayValues();
    let s = '<textarea cols="50" rows="12"> ';
    vs.forEach((r, i) => {
      if (i > 0) {
        s  = ['\n'];
      }
      s  = r.join(',');
    })
    s  = `\nSheet Name: ${sh.getName()}</textarea>`
    ui.showModelessDialog(HtmlService.createHtmlOutput(s).setWidth(800), 'Title');
  }
}

Url Dialog:

Data Dialog:

Scroll down to the bottom to see the sheet name.