Google Apps Script to Geocode a Postal Code and Save the result in Google Sheets

gs-postalcode2coords

function FillLatitudexLongitude() {
  var spreadsheet = SpreadsheetApp.getActive();
  var postalcodes = spreadsheet.getRange('F:F').activate().getValues();

  for(var i = 1; i < postalcodes.length; i++)
  {
    postalcode = postalcodes[i];
    url = 'https://developers.onemap.sg/commonapi/search?searchVal='+postalcode+'&returnGeom=Y&getAddrDetails=N&pageNum=1';
    
    var response = UrlFetchApp.fetch(url, {'muteHttpExceptions': true});
    var json = response.getContentText();
    var data = JSON.parse(json);
    //Logger.log(data);
    
    if(data.found > 0) {
      var lat = data.results[0].LATITUDE;
      var lng = data.results[0].LONGITUDE;
      Logger.log(postalcode+': '+lat+','+lng);
      
      var range = 'G'+(i+1);
      spreadsheet.getRange(range).activate().setValue(lat);
      var range = 'H'+(i+1);
      spreadsheet.getRange(range).activate().setValue(lng);
    }
    
    if(i==250 || i==500 || i==750 || i==1000 || i==1250 || i==1500 || i==1750 || i==2000 || i==2250 || i==2500 || i==2750 || i==3000 || i==3250) {
      Utilities.sleep(70000);
      Logger.log('Slept for 70k ms');
      //break;
    }
    
  }
 
};