0

I'm working on building a travelling salesman model for a game I play in a Google Spreadsheet. I'm currently working on a portion of the script to calculate the "travel time" for each arc based on a formula from the game that determines travel time. When I run the script, it fills the formulas properly, but they all show parsing errors until I double click them to edit and hit enter, then they run just fine.

Is there a command in Apps Script to refresh formulas or is there something I can change to prevent this issue?

    function nodeSetup() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Node Listing");
  var calc = ss.getSheetByName("Distance Calculations");


  var numNodes = sheet.getLastRow()-1;
  var i = 1;
  var j = 1;

  while (i<numNodes+1){
   calc.getRange(1,i+1).setValue(i); 
    i++
  }

  while (j<numNodes+1){
   calc.getRange(j+1,1).setValue(j); 
    j++
  }

  var i = 1;
  var j = 1;

  while (i<numNodes+1){

    var j = 1;
    while (j<numNodes+1){

      calc.getRange(i+1,j+1).setFormula("=Max(ABS(index('Node Listing'!$B$2:$C$6,"+(j)+",1)-index('Node Listing'!$B$2:$C$6,"+(i)+",1)),ABS(index('Node Listing'!$B$2:$C$6,"+(j)+",2)-index('Node Listing'!$B$2:$C$6,"+(i)+",2))*2/HyperVal*(1+0.05*PilotSkill)");
      j++
    }


    i++
  }


}
user1495229
  • 29
  • 1
  • The first two while loops set the row and column headers in the "calc" sheet based on the number of nodes actually entered. – user1495229 Jul 24 '15 at 05:09
  • id suggest doing it all from code and not use the sheet or formulas except for input. – Zig Mandel Jul 24 '15 at 13:03
  • Take a look at this question on forcing it to refresh by adding the time in the parameters - http://stackoverflow.com/questions/17341399/refresh-data-retrieved-by-a-custom-function-in-google-spreadsheet – Andrew Roberts Jul 29 '15 at 21:16

1 Answers1

0

my answer is a bit on the side of your question, but there are already several implementations of the TSP in Google Apps Script...

You can check https://github.com/tzmartin/Google-Maps-TSP-Solver for example. It is a fork from the original project on Google Code, now closed. Hope it helps!

davidbourguignon
  • 363
  • 5
  • 8