Google Apps Script - Still struggling with looping through rows

Question

I'm still struggling with understanding loops.

I have a script that calculates the distance in kilometers between two addresses. At this point it only calculates the distance at row 12 and put the result in column C, row 12.

Start           End           Total km's      
address one     address two   16,283           

Code (found on the web):

function GOOGLEMAPS() {

  var start_address = SpreadsheetApp.getActiveSheet().getRange('A12').getValue();
  var end_address = SpreadsheetApp.getActiveSheet().getRange('B12').getValue();

  var mapObj = Maps.newDirectionFinder();
  mapObj.setOrigin(start_address);
  mapObj.setDestination(end_address);
  var directions = mapObj.getDirections();

  var getTheLeg = directions["routes"][0]["legs"][0];

  var meters = getTheLeg["distance"]["value"];

  var keerduizend = meters / 1000;

  SpreadsheetApp.getActiveSheet().getRange('C12').setValue(keerduizend);

}

The final result should be that it can calculate all distances that are put in the sheet:

A               B             C
Start           End           Total km's      
address one     address two   16,283    
address two     address one   16,283    
address three   address four  16,283    
address five    address six   16,283    

                Total        SUM(C12:C15)

It should not matter if there are 4 addresses or 40 and users should be able to add rows (between the last row with an address and the row with Total. As I'm still struggling with understanding loops I don't see how to achieve this.

Can someone explain to me how to achieve this?

Edit

Thanks to Diego the first part of the script works:

function GOOGLEMAPS() {

  var sheet = SpreadsheetApp.getActiveSheet();
  var start_addresses = sheet.getRange('B12:B26').getValues(); // [["address one"], ["address two"], ["address three"], ["address five"]]
  var end_addresses = sheet.getRange('D12:D26').getValues(); // [["address two"], ["address one"], ["address four"], ["address six"]]

  var distances = []; // This will store all of the distances

    for (var i = 0; i < start_addresses.length; i++) {
    var mapObj = Maps.newDirectionFinder();
    if (start_addresses[i][0] != "" && end_addresses[i][0] != "") {
      mapObj.setOrigin(start_addresses[i][0]);
      mapObj.setDestination(end_addresses[i][0]);
      var directions = mapObj.getDirections();

      var getTheLeg = directions["routes"][0]["legs"][0];

      var meters = getTheLeg["distance"]["value"];

      var keerduizend = meters / 1000;

      distances.push([keerduizend]);
    }
  }

  sheet.getRange('E12:E26').setValues(distances);

}

Still get's an error

The number of rows in the data does not match the number of rows in the range. The data has 3, but the range has 15. (line 30, file 'Code')

But I will try to figure this one out by myself first.

Answer 1

To read at all of the values in the range "A12:B15", you should use .getValues(). If you look at the documentation, you'll see that it returns a two-dimensional array. The first element in the array is the row, and the second element is the column. Something like this:

       0      1
0 [ ["A-1", "B-1"] ]
1 [ ["A-2", "B-2"] ]

So the address of A-1 is [0][0]; the address of A-2 is [1][0]; the address of B-1 is [0][1]; and the address of B-2 is [1][1]. You can use a for loop to read the value at each address. Look at the code below and try to figure it out on paper, then run it in the editor.

function test_forLoop() {
  var data = [["A-1", "B-1"], ["A-2", "B-2"]];
  for (var rowIndex = 0; rowIndex < data.length; rowIndex++) {
    for (var columnIndex = 0; columnIndex < data[rowIndex].length; columnIndex++) {
      Logger.log(data[rowIndex][columnIndex]); // Will print: "A-1", "B-1", "A-2", "B-2"
    }
  }
}

That's a very brief introduction, but let's return to your code. You can call .getValues() on the Column A range and the Column B range, and then loop through those values to calculate the various distances. Finally, you can use .setValues() to print the results to Column C.

function GOOGLEMAPS() {

  var sheet = SpreadsheetApp.getActiveSheet();
  var start_addresses = sheet.getRange('A12:A15').getValues(); // [["address one"], ["address two"], ["address three"], ["address five"]]
  var end_addresses = sheet.getRange('B12:B15').getValues(); // [["address two"], ["address one"], ["address four"], ["address six"]]

  var distances = []; // This will store all of the distances

  for (var i = 0; i < start_addresses.length; i++) {
    var mapObj = Maps.newDirectionFinder();
    if (start_addresses[i][0] != "" && end_addresses[i][0] != "") {
      mapObj.setOrigin(start_addresses[i][0]);
      mapObj.setDestination(end_addresses[i][0]);
      var directions = mapObj.getDirections();

      var getTheLeg = directions["routes"][0]["legs"][0];

      var meters = getTheLeg["distance"]["value"];

      var keerduizend = meters / 1000;

      distances.push([keerduizend]);
    } else {
      distances.push([null]);
    }
  }

  sheet.getRange('C12:C15').setValues(distances);

}

There are multiple ways to do this, but in order to help you understand it more easily, I chose to make as few changes to what you wrote. If you understand it, then please continue thinking about how you might improve it. For example, what if you selected columns A and B at the same time (sheet.getRange('A12:B15'))?