How can I speed up my Google Spreadsheet Script?

2018-07-19 02:01:12

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.g

  • 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.

    2018-07-19 02:12:22