Implement Reverse Geocoding in Google Sheets

Other Questions Google docs
1

How can I develop a script that, given a latitude and longitude pair, quickly retrieves and displays the corresponding city and country in adjacent columns?

function initializeGeoMenu() {
  const activeSheet = SpreadsheetApp.getActiveSpreadsheet();
  activeSheet.addMenu('Geo Tools', [{
    name: 'Reverse Lookup',
    functionName: 'reverseLookup'
  }]);
}

function reverseLookup() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getActiveSheet();
  const range = sheet.getActiveRange();
  const values = range.getValues();

  for (let i = 0; i < values.length; i++) {
    const lat = values[i][0];
    const lng = values[i][1];
    const response = Maps.newGeocoder().reverseGeocode(lat, lng);
    let city = '', country = '';
    if (response.status === 'OK' && response.results.length > 0) {
      response.results[0].address_components.forEach(comp => {
        if (comp.types.indexOf('locality') !== -1) city = comp.long_name;
        if (comp.types.indexOf('country') !== -1) country = comp.long_name;
      });
    }
    sheet.getRange(range.getRow() + i, range.getColumn() + 2).setValue(city);
    sheet.getRange(range.getRow() + i, range.getColumn() + 3).setValue(country);
  }
}
2

hey, i had similar probs; i added a timeout in my script and a simple try-catch. makes things smoother when the geocoder fails. experimenting with lat & lng pairs can also reveal if the api returns proper data.

3

In my experience, when implementing a reverse geocoding workflow in Google Sheets, careful handling of API responses is essential. I noticed that verifying not just the status but also the existence of certain address components helps avoid issues when encountering incomplete data. It is useful to monitor API quotas and consider incorporating a delay between requests to manage rate limits. I also added logging to capture unexpected values or errors, which proved beneficial during troubleshooting. Combining these techniques led to a more robust script that responded gracefully to variations in geocoder returns.