Capturing lat/lon from an Address field type

[rmwarren007]

First post, total Newby in Appsheet, greatly appreciate any assistance anyone can provide.

I'm using the Appsheet database for my data source. There is an Address type field [Full Address] that I use to enter an address, and it's connected to Google to geocode. Separately I have a LatLong field type named [LatLong] in the same table where I'd like to capture the coordinates.

I'm struggling to figure out how to do this. When I enter an address, after a short pause the google geocoded respond shows up, I click that and it populates the Address field. Can I use an expression to in the LatLong field to extract the coordinates from the Address field directly, or is this data not actually created/saved/stored as part of the Address field?

Hope this makes sense, & thanks in advance.

[Steve]

---

@rmwarren007 wrote:

Can I use an expression to in the LatLong field to extract the coordinates from the Address field directly

---

Nope. To get the location from an address, you'd have to use a process outside of AppSheet itself.

[WhoTripathi]

@rmwarren007 

I will suggest you to explore Google App Script to do so, it is not much deficult function to get the Lat- long value from the address.

[mykrobinson]

done with apps script fairly easily as an automation that triggers when a column that contains the address (or a concatenation of columns to create the full address) is entered. No clue how to do it with an Appsheet database though 🤔

In this example, i am assuming i have a full address in column L and i want the script to fetch latlong coordinates and place them in column M. This is done at the data level and not directly in Appsheet by adding the apps script to the google sheet and enabling the Google Maps service

// Function to set up the trigger
function createSpreadsheetEditTrigger() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  ScriptApp.newTrigger('onEdit')
    .forSpreadsheet(ss)
    .onEdit()
    .create();
}

// Main function that runs when spreadsheet is edited
function onEdit(e) {
  const range = e.range;
  const sheet = range.getSheet();
  
  // Only proceed if edit was made in column L
  if (range.getColumn() !== 12) return; // Column L is 12th column
  
  const row = range.getRow();
  const address = sheet.getRange(row, 12).getValue(); // Get value from column L
  
  // If address is empty, clear the coordinates
  if (!address) {
    sheet.getRange(row, 13).clearContent(); // Clear column M
    return;
  }
  
  try {
    const coordinates = getCoordinates(address);
    
    // Only proceed if we got valid coordinates
    if (Array.isArray(coordinates)) {
      const [lat, lng] = coordinates;
      // Format coordinates with 6 decimal places
      const coordString = `${lat.toFixed(6)}, ${lng.toFixed(6)}`;
      sheet.getRange(row, 13).setValue(coordString);
    } else {
      // If coordinates weren't valid, clear the target cell
      sheet.getRange(row, 13).clearContent();
    }
  } catch (error) {
    // If there's any error, clear the target cell
    sheet.getRange(row, 13).clearContent();
    Logger.log('Error processing address: ' + error.toString());
  }
}

// Function to get coordinates from address
function getCoordinates(address) {
  try {
    const geocoder = Maps.newGeocoder();
    const location = geocoder.geocode(address);
    
    if (location.status === 'OK') {
      const lat = location.results[0].geometry.location.lat;
      const lng = location.results[0].geometry.location.lng;
      return [lat, lng];
    }
    return null;
  } catch (e) {
    Logger.log('Geocoding error: ' + e.toString());
    return null;
  }

Keep in mind that this is an example and not necessarily meant to be copy/pasted, though it can pretty easily be modified. Hope this helps