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?

  function feeBondInsCalculator (){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Pretty Sum Form");
  // 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});
  • \$\begingroup\$ Please don't put tags in the title. You already have tags for that. \$\endgroup\$ – Fund Monica's Lawsuit Feb 14 '16 at 4:41
  • \$\begingroup\$ Could you link to the spreadsheet? I'm having a hard time understanding the intent. \$\endgroup\$ – 200_success Feb 14 '16 at 4:58
  • \$\begingroup\$ 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/… \$\endgroup\$ – Chris MMgr Feb 14 '16 at 6:09
  • \$\begingroup\$ 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. \$\endgroup\$ – Joshua Dawson Jul 31 '16 at 7:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.