I am somewhat new to JavaScript and very new to using it in Google Spreadsheets. I have read that fewer calls to Google Spreadsheet API speeds things up, but I feel like I have as few as possible at this point.
I have a spreadsheet for a D&D like role playing game and I have it set up so certain tabs are hidden/shown depending on the class(es) selected. The spreadsheet also hides/shows certain columns/rows depending on if the character has 2 weapons or one and the number of attacks the character has, which is based on class and level.
Spreadsheet is here: https://docs.google.com/spreadsheet/ccc?key=0AtVhz75n7RXqdFJnQ0tDLVpBQmVtZTZxbzJ4OHBoUEE&usp=sharing
I am looking for any tips to speed up this code:
function onEdit(event)
{
var sheetEdited = event.range.getSheet().getName();
var row = event.range.getRow();
var col = event.range.getColumn();
var AttackMath = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Attack Math")
var Stats = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Stats")
var bab1 = Stats.getRange("L46").getValue()
var bab2 = Stats.getRange("E48").getValue()
var bab3 = Stats.getRange("M48").getValue()
var bab4 = Stats.getRange("U48").getValue()
var class1 = Stats.getRange("M5").getValue()
var class2 = Stats.getRange("Y5").getValue()
var class3 = Stats.getRange("AN5").getValue()
var twoWeapons = AttackMath.getRange("C8").getValue()
var twoWeaponTable = AttackMath.getRange("A30:A48")
var mainhand2 = AttackMath.getRange("G1:J1")
var mainhand3 = AttackMath.getRange("K1:N1")
var mainhand4 = AttackMath.getRange("O1:R1")
var offhand1 = AttackMath.getRange("E1:F1")
var offhand2 = AttackMath.getRange("I1:J1")
var offhand3 = AttackMath.getRange("M1:N1")
var offhand4 = AttackMath.getRange("Q1:R1")
var class = [{name : "Alchemist" , isCaster : 1},
{name : "Antipaladin", isCaster : 1},
{name : "Barbarian" , isCaster : 0},
{name : "Bard" , isCaster : 1},
{name : "Cavalier" , isCaster : 0},
{name : "Cleric" , isCaster : 1},
{name : "Druid" , isCaster : 1},
{name : "Fighter" , isCaster : 0},
{name : "Gunslinger" , isCaster : 0},
{name : "Inquisitor" , isCaster : 1},
{name : "Magus" , isCaster : 1},
{name : "Monk" , isCaster : 0},
{name : "Ninja" , isCaster : 0},
{name : "Oracle" , isCaster : 1},
{name : "Paladin" , isCaster : 1},
{name : "Ranger" , isCaster : 1},
{name : "Rogue" , isCaster : 0},
{name : "Samurai" , isCaster : 0},
{name : "Sorcerer" , isCaster : 1},
{name : "Summoner" , isCaster : 1},
{name : "Witch" , isCaster : 1},
{name : "Wizard" , isCaster : 1}]
//******************************************************************************************************
if (sheetEdited == "Stats" && row == 5)
{
//Browser.msgBox("One");
if (bab2 == "--")
{
AttackMath.hideColumn(mainhand2);
AttackMath.hideColumn(mainhand3);
AttackMath.hideColumn(mainhand4);
}
else if (bab3 == "--")
{
AttackMath.unhideColumn(mainhand2);
AttackMath.hideColumn(mainhand3);
AttackMath.hideColumn(mainhand4);
}
else if (bab4 == "--")
{
AttackMath.unhideColumn(mainhand2);
AttackMath.unhideColumn(mainhand3);
AttackMath.hideColumn(mainhand4);
}
else
{
AttackMath.unhideColumn(mainhand2);
AttackMath.unhideColumn(mainhand3);
AttackMath.unhideColumn(mainhand4);
}
}
//******************************************************************************************************
//Browser.msgBox("Zero");
if ((sheetEdited == "Stats" && row == 5) || (sheetEdited == "Attack Math" && row == 8))
{
//Browser.msgBox("One");
if (twoWeapons == "Yes")
{
//Browser.msgBox("Two");
AttackMath.unhideRow(twoWeaponTable);
AttackMath.unhideColumn(offhand1);
if (bab2 != "--")
{
AttackMath.unhideColumn(offhand2);
if (bab3 != "--")
{
AttackMath.unhideColumn(offhand3);
if (bab4 != "--")
{
AttackMath.unhideColumn(offhand4);
}
}
}
}
else
{
//Browser.msgBox("Three");
AttackMath.hideRow(twoWeaponTable);
AttackMath.hideColumn(offhand1);
AttackMath.hideColumn(offhand2);
AttackMath.hideColumn(offhand3);
AttackMath.hideColumn(offhand4);
}
}
//******************************************************************************************************
//Browser.msgBox("Sheet: "+sheetEdited+"\n\n"+"row: "+row+"\n\n"+"col: "+col+"\n\n");
if (sheetEdited == "Stats" && row == 5 && (col == 13 || col == 25 || col == 40))
{
//Browser.msgBox("DAMN");
if (class1)
{
showClass1Sheets();
}
else
{
SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Spells").hideSheet();
}
if (class2)
{
showClass2Sheets();
}
else
{
SpreadsheetApp.getActiveSpreadsheet().getSheetByName("2nd Class Spells").hideSheet();
}
if (class3)
{
showClass3Sheets();
}
else
{
SpreadsheetApp.getActiveSpreadsheet().getSheetByName("3rd Class Spells").hideSheet();
}
//Determine which class sheets to show
for (var classIndex=0; classIndex<class.length;classIndex++)
{
if (class[classIndex].name == class1 || class[classIndex].name == class2 || class[classIndex].name == class3)
{
SpreadsheetApp.getActiveSpreadsheet().getSheetByName(class[classIndex].name).showSheet();
}
else
{
SpreadsheetApp.getActiveSpreadsheet().getSheetByName(class[classIndex].name).hideSheet();
}
}
}
if (sheetEdited == "Stats" && row == 17 && col == 5)
{
var dex = ~~((SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Stats").getRange("E17").getValue()-10)/2);
var maxDex = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Equipment").getRange("X41").getValue();
if (dex > maxDex)
{
SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Stats").getRange("H17").setFontColor("#FF0000");
}
else
{
SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Stats").getRange("H17").setFontColor("#000000");
}
//Browser.msgBox("dex is "+dex+" maxDex is "+maxDex);
}
}
function showClass1Sheets()
{
var class1Index = class.map(function(e) { return e.name; }).indexOf(class1);
// Determine whether or not to show spells sheet
if (class[class1Index].isCaster)
{
SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Spells").showSheet();
}
else
{
SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Spells").hideSheet();
}
}
function showClass2Sheets()
{
var class2Index = class.map(function(e) { return e.name; }).indexOf(class2);
// Determine whether or not to show spells sheet
if (class[class2Index].isCaster)
{
SpreadsheetApp.getActiveSpreadsheet().getSheetByName("2nd Class Spells").showSheet();
}
else
{
SpreadsheetApp.getActiveSpreadsheet().getSheetByName("2nd Class Spells").hideSheet();
}
}
function showClass3Sheets()
{
var class3Index = class.map(function(e) { return e.name; }).indexOf(class3);
// Determine whether or not to show spells sheet
if (class[class3Index].isCaster)
{
SpreadsheetApp.getActiveSpreadsheet().getSheetByName("3rd Class Spells").showSheet();
}
else
{
SpreadsheetApp.getActiveSpreadsheet().getSheetByName("3rd Class Spells").hideSheet();
}
}