1/12/2017 - 4:56 PM

Get Sheet By ID (via name)-- Google Sheets

Get Sheet By ID (via name)-- Google Sheets


- Gets the ID of the active spreadsheet
- Creates 2 blank arrays, one for the sheet IDs, one for the names
- Pushes the IDs and Names into their respective arrays (at the same positions within each array)
- Defines the IDs and Names of the Template Sheet, Student Intervention Profile, Database, and Lists
- Can return each of the names of these sheets by its ID… This will allow you to get these sheets in particular by their ID through their name
	- … .getSheetByName(templateSheetName) ← Does the above (uses the hard-coded sheet ID to find the name) then you can use the name...


- Modify the name of the function thusly: get ___ SheetByIdFunction() where ___ = the name of the sheet
var templateSheet = ss.getSheetByName(getSheetByIdFunction()); //Gets the current sheet's name, 12/14


function getSheetByIdFunction(){
  var id  = SpreadsheetApp.getActiveSheet().getSheetId();// get the actual id
  //Logger.log(id);// log
  var sheets = SpreadsheetApp.getActive().getSheets();
  //creates blank arrays (outside of for loop)
    var sheetIdArray = [];
    var sheetNameArray = [];
  for(var n in sheets){ // iterate all sheets and compare ids
    sheets[n].getSheetId(); //gets all the sheets ids
    //pushes all values of ids and names into the arrays
    //n = position

  //var currentSheetName = SpreadsheetApp.getActive().getSheets()[n].getName();

  var templateSheetId = sheetIdArray.indexOf(517276627); //id of template sheet
  var sipSheetId = sheetIdArray.indexOf(803006152); //id of Student Intervention Profile
  var databaseSheetId = sheetIdArray.indexOf(1464566783); //id of Database Sheet
  var listsSheetId = sheetIdArray.indexOf(778610877); //id of Lists Sheet
  //Gets the value of the array in the same position as the index
  var templateSheetName = sheetNameArray[templateSheetId].toString();
  var sipSheetName = sheetNameArray[sipSheetId].toString();
  var databaseSheetName = sheetNameArray[databaseSheetId].toString(); 
  var listsSheetName = sheetNameArray[listsSheetId].toString(); 
  //Chose which one to return based on the id you need
  return [templateSheetName, sipSheetName, databaseSheetName, listsSheetName];
  //Use this block in order to get the names of sheets (even if they're renamed)
  var getSheetById = getSheetByIdFunction();
    var getTemplateSheetById = getSheetById[0]; //gets the name of the aforementioned sheet
    var getSipSheetById = getSheetById[1];      //gets the name of the aforementioned sheet
    var getDatabaseSheetById = getSheetById[2]; //gets the name of the aforementioned sheet
    var getListsSheetById = getSheetById[3];    //gets the name of the aforementioned sheet
  //Delete this???
      //Logger.log(sheetIdArray.indexOf(517276627)); //position of template sheet