Exceeded maximum execution time

[kcsf]

Hello,
First - thank you for sharing this, very useful. We've modified the script to use googleapis for more than 2500 calls per day, and return all fields.

`
/* Usage: Select a continuous column of address and select Add-Ons->Geocode Selected Cells
* Encoding will put the results in the corresponding columns
* Based off of: https://github.com/mlucool/geocode-google-sheets/blob/master/Geocode.gs
*/

function geoAPISelectedCells() {
var sheet = SpreadsheetApp.getActiveSheet();
var addresses = sheet.getActiveRange();

// We expect only the column to be encoded selected
if (addresses.getNumColumns() == 0) {
    Browser.msgBox("Please select a address/location column to encode");
} else if (addresses.getNumColumns() != 1) {
    Browser.msgBox("Please select only one address/location column to encode");
    return;
}

// Find where to put results
var headerRange = sheet.getRange(1, 1, 1, sheet.getLastColumn());
var headerValues = headerRange.getValues();
var pidColumn = -1;
var fadColumn = -1;
var streetNumColumn = -1;
var streetColumn = -1;
var cityColumn = -1;
var unitColumn = -1;
var stateColumn = -1;
var zipColumn = -1;
var countryColumn = -1;  
var latColumn = -1;
var lngColumn = -1;
var neighborhoodColumn = -1;
var countyColumn = -1;
var row = null;
for (row in headerValues) {
    for (var col in headerValues[row]) {
        if (headerValues[row][col].toLowerCase() == "lat") {
            latColumn = parseInt(col) + 1;
        } else if (headerValues[row][col].toLowerCase() == "lng") {
            lngColumn = parseInt(col) + 1;
        } else if (headerValues[row][col].toLowerCase() == "projectid") {
            pidColumn = parseInt(col) + 1;
        } else if (headerValues[row][col].toLowerCase() == "fulladdress") {
            fadColumn = parseInt(col) + 1;
        } else if (headerValues[row][col].toLowerCase() == "streetnumber") {
            streetNumColumn = parseInt(col) + 1;
        } else if (headerValues[row][col].toLowerCase() == "street") {
            streetColumn = parseInt(col) + 1;
        } else if (headerValues[row][col].toLowerCase() == "city") {
            cityColumn = parseInt(col) + 1;
        } else if (headerValues[row][col].toLowerCase() == "state") {
            stateColumn = parseInt(col) + 1;
        } else if (headerValues[row][col].toLowerCase() == "zip") {
            zipColumn = parseInt(col) + 1;
        } else if (headerValues[row][col].toLowerCase() == "country") {
            countryColumn = parseInt(col) + 1;
        } else if (headerValues[row][col].toLowerCase() == "neighborhood") {
            neighborhoodColumn = parseInt(col) + 1;
        } else if (headerValues[row][col].toLowerCase() == "county") {
            countyColumn = parseInt(col) + 1;
        } else if (headerValues[row][col].toLowerCase() == "unit") {
            unitColumn = parseInt(col) + 1;
        } 
    }
}
// Let's Encode
row = 1;
// Skip header if selected
if (addresses.getRow() == 1) {
    ++row;
}

var cell = null;

for (row; row <= addresses.getNumRows(); ++row) {
    cell = addresses.getCell(row, 1);
    var address = cell.getValue();

    // Geocode the address
    if (address == "") {
        continue;
    }
    //var location = geocoder.geocode(address);

    try {

     var location = UrlFetchApp.fetch('https://maps.googleapis.com/maps/api/geocode/json?address=' + address + '&key=YOUR-API-KEY');


   //Logger.log("location: " + location);          

    var json = location.getContentText();
    var data = JSON.parse(json);

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

        var number = "";
        var street = "";
        var unit = "";
        var city = "";
        var state = "";
        var zip = "";
        var country = "";
        var neighborhood = "";
        var county = "";
        var address_comp = data["results"][0]["address_components"];
         for (var i=0; i<address_comp.length; i++) {
          if(address_comp[i].types[0] == "street_number") number = address_comp[i].long_name;
          if(address_comp[i].types[0] == "route") street = address_comp[i].long_name;
          if(address_comp[i].types[0] == "locality") city = address_comp[i].long_name;
          if(address_comp[i].types[0] == "administrative_area_level_1") state = address_comp[i].short_name;
          if(address_comp[i].types[0] == "postal_code") zip = address_comp[i].short_name;
          if(address_comp[i].types[0] == "country") country = address_comp[i].short_name;
          if(address_comp[i].types[0] == "neighborhood") neighborhood = address_comp[i].short_name;
          if(address_comp[i].types[0] == "administrative_area_level_2") county = address_comp[i].short_name;
          if(address_comp[i].types[0] == "subpremise") unit = address_comp[i].short_name;           
         }            
        var ifunit = "";
      if(unit != "") {ifunit = "unit" + unit}
        var prid = number + '-' + street + '-' + ifunit + '-' + city + '-' + state;
        projectid = prid.toLowerCase().replace(/ /g,'-').replace(/[-]+/g, '-').replace(/[^\w-]+/g,'');

      setValue(sheet, cell.getRow(), pidColumn, projectid);
      setValue(sheet, cell.getRow(), fadColumn, fulladr);
      setValue(sheet, cell.getRow(), streetNumColumn, number);
      setValue(sheet, cell.getRow(), streetColumn, street);
      setValue(sheet, cell.getRow(), unitColumn, unit);
      setValue(sheet, cell.getRow(), cityColumn, city);
      setValue(sheet, cell.getRow(), stateColumn, state);
      setValue(sheet, cell.getRow(), zipColumn, zip);
      setValue(sheet, cell.getRow(), countryColumn, country);
      setValue(sheet, cell.getRow(), neighborhoodColumn, neighborhood);
      setValue(sheet, cell.getRow(), countyColumn, county);          
      setValue(sheet, cell.getRow(), latColumn, lat);
      setValue(sheet, cell.getRow(), lngColumn, lng);

      Utilities.sleep(2);
    } 

      } catch(e) {
     // if the script code throws an error,
     // do something with the error here

     }          
}

function setValue(sheet, row, column, val) {
sheet.getRange(row, column).setValue(val);    
}
}

`

Now we are encountering an "Exceeded maximum execution time" error after about 130 rows. What do you think is the best way of handling this?

[mlucool]

Glad this is helpful. I don't have any insight into this error. You do have a sleep in there which is killing time. There is also a max amount of script execution time it looks like: https://developers.google.com/apps-script/guides/services/quotas