Search code examples
google-sheetsgoogle-apps-script

I can't get the second sheet's URL


I can't get the second sheet's URL. Please tell me how to get the URL of the second sheet?

function myFunction() {
const ss = SpreadsheetApp.getActiveSpreadsheet().getSheets()[1]; 

const name =ss.getSheetName();
Logger.log(name); // ok

const value = ss.getRange('C2').getValue();
Logger.log(value);  //  ok

Logger.log(ss.getUrl());   // TypeError: ss.getUrl is not a function. Please help
}

I tried this method to get the second sheet's URL. But I am getting two same first sheet urls.

function myFunction() {
const sl = SpreadsheetApp.getActiveSpreadsheet();
console.log(sl.getUrl())
sl.insertSheet('xyz',1);
const nm = sl.getSheetByName("xyz");
nm.activate();
const spsh = SpreadsheetApp.getActiveSpreadsheet();    
console.log(spsh.getUrl());}

Solution

  • The url of the sheet is of the format:

    SpreadsheetUrl#gid=sheetId
    

    Use .getSheetId() to get the gid:

    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const sh = ss.getSheets()[1]; 
    const url = ss.getUrl() + "#gid=" + sh.getSheetId();
    console.info({url})