# Iterative Calculation

This Google Sheets script performs an iterative (circular) calculation to find values based on a gross percentage of project cost. Range names are used so that users can add/delete rows/columns and not have to reset the code. The ranges included in copyTo() are part of the total sum of project cost and based on a percentage of project cost. During the iterations, each time ranges are copied and pasted, the overall project cost changes and therefore the values of each range is changed.

The iterations take about two seconds each, which is too slow. How can I improve their speed?

//<!-- FEE, BOND, & INSURANCE CALCULATOR --!>
function feeBondInsCalculator (){
var sheet = ss.getSheetByName("Pretty Sum Form");
sheet.activate();
// Get all variables from named ranges
var bondValue = ss.getRangeByName("bondValue");
var bondCalc = ss.getRangeByName("bondCalc");
var insValue = ss.getRangeByName("insValue");
var insCalc = ss.getRangeByName("insCalc");
var feeValue = ss.getRangeByName("feeValue");
var feeCalc = ss.getRangeByName("feeCalc");
var interCount = ss.getRangeByName("interCount")

// Interative calculation to paste calculated values into the body of the spreadsheet
for (var i = 0; i<11; i++){
feeCalc.copyTo(ss.getRangeByName("feeValue"), {contentsOnly: true});
bondCalc.copyTo(ss.getRangeByName("bondValue"), {contentsOnly: true});
insCalc.copyTo(ss.getRangeByName("insValue"), {contentsOnly: true});
interCount.setValue(i)
}
}

• Please don't put tags in the title. You already have tags for that. – Fund Monica's Lawsuit Feb 14 '16 at 4:41
• Could you link to the spreadsheet? I'm having a hard time understanding the intent. – 200_success Feb 14 '16 at 4:58
• Here is a link to a sample spreadsheet. In order to understand what I am looking for, change the values in column C above row 159, then run the script. docs.google.com/spreadsheets/d/… – Chris MMgr Feb 14 '16 at 6:09
• Your script runs too slowly because you make a lot of getRangeByName calls. If you can, try load all the data at once, or consider using your cached ranges feeValue, bondValue, and insValue instead of getting them again and again in your for loop. – Joshua Dawson Jul 31 '16 at 7:03