Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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();
  }
}
share|improve this question
    
Why don't you share a doc with us. Rearranging this from the spreadsheet side, could help as well. –  Jacob Jan Tuinstra Sep 5 '13 at 12:59
    
I would consider re-arranging the spreadsheet to be a last resort since it is set up for ease of use for the player. The code is like icing on the cake. If I can't speed it up more I will live with it. A link to the spreadsheet has been added to the original post. I should also note that the code is only triggered when a class, level, or the two weapon field are edited. In standard play this will happen very rarely. –  Shawn Sep 5 '13 at 13:35
add comment

1 Answer

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 and 8 maps to 7
var twoWeapons = attackMatchRange[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 = attackMatchRange[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.

share|improve this answer
    
As Tom says, grab all the data you're going to work with in a single getValues() call. In short, keep the number of API method calls at a minimum. API calls (such as all those getRange()s) is what's making your script slow. I usually call sheet.getDataRange().getValues() and work on the resulting array, then write the data back with sheet.getDataRange().setValues() when I'm done. –  Vidar S. Ramdal Feb 27 at 8:48
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.