4

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();
  }
}
2
  • Why don't you share a doc with us. Rearranging this from the spreadsheet side, could help as well. Sep 5, 2013 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, 2013 at 13:35

1 Answer 1

6

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.

1
  • 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. Feb 27, 2014 at 8:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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