Accessing to sheet with gid

Question

sorry if my question is answered somewhere else, I've been looking for answers on google all afternoon but I'm still too newbie.

I'm trying to use Google spreadsheets' scripts to access to a different spreadsheet. The only information I have is the spreadsheet's URL, where it has the key and the gid (some kind of chronological index for multi-sheet spreadsheets - only information i could find is here).

The sheet URL is something like https://docs.google.com/spreadsheet/ccc?key=abc123#gid=178 And the sheet it links to is the first sheet in the spreadsheet.

How do I find the sheet that maches the gid?

The following doesn't work, since it's based on the sheets' order, not the time they are created:

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

var sheet = ss.getSheets();
Browser.msgBox(sheets[178].getIndex());

Answer 1

I would do something simple like this:

var refSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("SHEET NAME HERE");
var refSheetId = refSheet.getSheetId().toString();

And then append the refSheetId to the end of the getUrl string

var ssUrl = SpreadsheetApp.getActiveSpreadsheet().getUrl()+"#gid="+refSheetId;

Hope this helps!

Answer 2

This is how i do it:

function getSheetByGid(spreadsheet, gid){
    gid = +gid || 0;
    var res_ = undefined;
    var sheets_ = spreadsheet.getSheets();
    for(var i = sheets_.length; i--; ){
        if(sheets_[i].getSheetId() === gid){
            res_ = sheets_[i];
            break;
        }
    }

    return res_;
}

var sheet = SpreadsheetApp.openById("YOUR_SHEET_ID_HERE");
var seetWithGid = getSheetByGid(cpSheet, "YOUR_GID_HERE");

Answer 3

I know this is late, and may not be useful to original requester anymore. But I saw this question as I was working on the functionality, but ended up solving it myself later. You can look up a sheet by gid, and the fact that it doesn't change when you delete the sheet is a good thing.... if that's how you intend to use it. Basically I get all sheets, and loop through sheet names and sheet gid until the gid matches, and then I call the sheet by name.

Here's part of some code I use to take a row, from a aggregated sheet on edit, and push to the sub sheet as part of a two way sync. If you have just the gid and key stored, you can skip the steps i showed, and just reference those values. It seems to work very quick, it's a more complicated script then running from the workbook with the broken out sheets but both scripts take almost exactly 2 seconds to push, which is acceptable for me.

function Stest() 
 {
  SpreadsheetApp.flush();
  var sheet = SpreadsheetApp.getActiveSheet();
  var r = sheet.getActiveRange();
  var lastColumnRow = sheet.getLastColumn(); 
  var activeRow = r.getRow();
  var dataRange = sheet.getRange(activeRow,1,1,lastColumnRow);  
  var data = dataRange.getValues();

   var sskeytemp = data[0][10].split("=")[1]; //the sheet url is in column k
   var sskey = sskeytemp.split("#")[0]; //these first two steps get the sheet key
   var ssid = data[0][10].split("gid=")[1]; //this last step gets the gid
   var wrkbk = SpreadsheetApp.openById(sskey).getSheets(); //this gets the sheets from the workbook

   for (var i = 0 ; i < wrkbk.length ; i++ ) {
     if( ssid == wrkbk[i].getSheetId() ){ //this is to say if the spreadsheet gid, which is the gid of the sheet i know, matches the gid in the workbook from the link, thats the sheet i'm looking for
     var ssname= wrkbk[i].getName();
     var ss = SpreadsheetApp.openById(sskey).getSheetByName(ssname); //give me that sheet by name

    var sslastColumn = ss.getLastColumn();
    var sslastRow = ss.getLastRow();
    var dataRange = ss.getRange(1,1,sslastRow,sslastColumn);  
    var data2 = dataRange.getValues(); //here's your data range, you can proceed freely from here