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...
**********************************************
PUT THIS CODE IN WHEREVER YOU USE THE TEMPLATE
- 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
Logger.log(templateSheet);
**********************************************/
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
sheetIdArray.push(sheets[n].getSheetId());
sheetNameArray.push(sheets[n].getSheetName());
}
//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
//Logger.log(sipSheetId);
//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();
//Logger.log(sipSheetName);
//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
//Logger.log(sheetIdArray);
//Logger.log(templateSheetName);
}