How can I speed up my Google Spreadsheet Script?

Question

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();
  }
}

Answer 1

A first thing to think about would be to grab all the data at once with range.getValues(), then grab the bits you need.

so

 var twoWeapons = AttackMath.getRange("C8").getValue()
 var twoWeaponTable = AttackMath.getRange("A30:A48")

would become

var attackMathRange = AttackMath.getRange("A1:C48").getValues()

// note that arrays are zero based
// so C maps to 2 and 8 maps to 7
var twoWeapons = attackMathRange[2][7] 

// slice to get a range of values
// slice gets all values from first index up to
// but NOT including the second index
var twoWeaponTable = attackMathRange[0].slice(29, 48)

Every call you can reduce will speed up the script. You could even just get the entire range of values on a sheet, make adjustments appropriately, then use range.setValues once all changes are complete.