Geek asks and answers

Google Spreadsheet - access to a sheet with gid

Sorry if my question is forwarded elsewhere, I have been looking for answers on google all day, but I'm still too new.

I am trying to use Google spreadsheet scripts to access another spreadsheet. The only information I have is the URL of the spreadsheet where it has the key and gid (some kind of chronological index for multi-page spreeadsheets - only the information I could find is here ).

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

How to find a leaf that intercepts gid?

The following does not work, because it is based on the order of the sheets, and not on the time they were created:

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

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

Thanks in advance!

+5
source share
3 answers

I would do something simple:

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

Then add refSheetId to the end of the getUrl line

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

Hope this helps!

+4
source

It’s better not to rely on gid for many reasons.

  • The deleted sheet will not be displayed in your getSheets () method, but will be taken into account in gid For example:

Create Sheet1 (default is gid # 1)

Create Sheet2 (gid # 2)

Delete sheet2

Create Sheet3 (gid # 3)

  • The order will be mixed up if the user decides to move the sheets in the spreadsheet user interface. getSheets returns the sheets in the order in which they are located in the spreadsheet.

, , - . , - , .

0

, , . , , . gid, , , , - ... , . gid , gid, .

Here is part of some code that I use to take a line from an aggregated sheet when editing and clicking on an additional sheet as part of two-way synchronization. If you only have gid and key, you can skip the steps I showed and just refer to these values. It seems to work very fast, this is a more complex script, and then works from a book with broken sheets, but both scripts take almost exactly 2 seconds to click, which is acceptable to 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 your data range, you can proceed freely from here
0
source

More articles:


All Articles