CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
redditor
redditor

Reputation: 4316

Add elements to array inside a loop

I have the following code to convert a series of post codes to long lat by selecting the cells, in this example, A2:C3

It works well but want I want it to do is get the range into an add a long and lat entry to the array, then dump the array back to the sheet on the assumption it would be quicker.

I have tried cells.split but was still only able to print one line back at a time.

Example data, UK post codes with empty cells for long and lat

  |     A    |      B     |      C     |
1 | Postcode | Long       | Lat        |
2 | SW1 1AA  |            |            |
3 | EC1V 9BP |            |            |

Current app script

function getGeocodingRegion() {
  return PropertiesService.getDocumentProperties().getProperty('GEOCODING_REGION') || 'uk';
}

function addressToPosition() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var cells = sheet.getActiveRange();

  var addressColumn = 1;
  var addressRow;

  var latColumn = addressColumn + 1;
  var lngColumn = addressColumn + 2;

  var geocoder = Maps.newGeocoder().setRegion(getGeocodingRegion());
  var location;

  for (addressRow = 1; addressRow <= cells.getNumRows(); ++addressRow) {
    var address = cells.getCell(addressRow, addressColumn).getValue();

    // Geocode the address and plug the lat, lng pair into the 
    // 2nd and 3rd elements of the current range row.
    location = geocoder.geocode(address);

    // Only change cells if geocoder seems to have gotten a 
    // valid response.
    if (location.status == 'OK') {
      lat = location["results"][0]["geometry"]["location"]["lat"];
      lng = location["results"][0]["geometry"]["location"]["lng"];

      cells.getCell(addressRow, latColumn).setValue(lat);
      cells.getCell(addressRow, lngColumn).setValue(lng);
    }
  }
};

Upvotes: 0

Views: 87

Answers (1)

TheWizEd
TheWizEd

Reputation: 8606

Since you are doing this for every row of the spreadsheet you can simply getValues() for all rows, loop through the values and build another 2D array, then use setValues() to place that array next to the address column

function addressToPosition() {
  var sheet = SpreadsheetApp.getActiveSheet();

  // Get all values at once, a 2D array with getLastRow() rows and 1 column
  var cells = sheet.getRange(1,1,sheet.getLastRow(),1).getValues();
  var results = [];  // Will build a 2D array for setValues()
  for( var i=0; i<cells.length; i++ ) {
    var location = geocoder.geocode(cells[i][0]);
    if( location.status === "OK" ) {
      // I'm assuming this works, can't test
      var lat = location["results"][0]["geometry"]["location"]["lat"];
      var lng = location["results"][0]["geometry"]["location"]["lng"];
      results.push([lat,lng]);
    }
    else {
      results.push(["",""]); // if status is not OK
    }
  }
  // Now put the results in columns B and C
  sheet.getRange(1,2,results.length,results[0].length).setValues(results);
};

Upvotes: 1

Related Questions