Latlong from Address

Skip2MiLu
Participant V

Is it possible to pull the relevant latlong coordinates into the latlong column type from a address column type?

0 16 2,608
  • UX
16 REPLIES 16

MultiTech
Participant V

Unfortunately, Geocoding is not currently available.

Thanks.
Maybe appsheets new connection with Google might bring in some quick added value

We can only hope!

Not sure if this will help anyone but here is an Apps Script you can use to convert Address into LatLong values:

/**

  • Returns latitude and longitude values for given address using the Google Maps Geocoder.
  • @param {string} address - The address you get the latitude and longitude for.
  • @customfunction
    */
    function GEOCODE_GOOGLE(address) {
    if (address.map) {
    return address.map(GEOCODE_GOOGLE)
    } else {
    var r = Maps.newGeocoder().geocode(address)
    for (var i = 0; i < r.results.length; i++) {
    var res = r.results[i]
    return res.geometry.location.lat + ", " + res.geometry.location.lng
    }
    }
    }

Forgive the ignorance, but how do you use this function? Are you running it from Google apps script or from AppSheet? Iโ€™m thinking itโ€™s supposed to be run from the spreadsheet to get the latlong info before pulling it into AppSheet. Is that correct? What is triggering this function? When I try to run it from apps script I get this error:
3X_e_f_ef4ff647a96d90082c19c232014dd792e01cca58.png

@Tom_automated
The script is a custom function in the gSheet. It retrieves the lat/long pair from an address text in the gSheet. Itโ€™s a reverse geo-coding.

I understand what the function does. I do not understand how to execute it. Once upon a time I had this working apps script function

function testgeocode(){ 
        var responses = SpreadsheetApp.openById('####').getSheetByName('Form Responses');
        var range = responses.getRange('AD2:AD');
        var addresses = range.getValues();

        var row = range.getRow();
        var column = range.getColumn();

        var destination = new Array();
        destination[0] = 31; // column + 1;
        destination[1] = 32; // column + 2;

        var geocoder = Maps.newGeocoder();
        var count = range.getHeight();
      
        for(i in addresses) {
            var location = geocoder.geocode(addresses[i]).results[0].geometry.location;
            responses.getRange(row, destination[0]).setValue(location.lat);
            responses.getRange(row++, destination[1]).setValue(location.lng);
            Utilities.sleep(200);
        }
    }

I just checked and that code still works, but seems much less efficient (for one, itโ€™s putting the Lat/Long combo in 2 columns instead of one). Iโ€™d like to use the more efficient code but am getting an error on run.

Are you wishing to put the lat/long pair into a single column? And whatโ€™s the error are you receiving?

The function is executed directly on the gSheet just like any other sheet functions as you can see from my screenshot. Are you wishing to execute that function when a specific data is recorded via AppSheet?

I think what might be missing, unless I missed it, is where to place the function code. I have built scripts and run them on a schedule but have never used the coded functions inside of sheet. So, I assume that we would simply need to copy the function code into the scripts area of the sheet??

Hello John, yes you are correct. The script shall be placed within the script editor of the gSheet. But what @Tom_automated was asking is a bit different. He wants the built-in function to be executed and record data to the gSheet when he records a new row via his app as I have explained in-detail above.

Yes, sorry. I wasnโ€™t meaning to react to his question. It was more of a general comment for anyone new who might have been confused but also confirm for myself.

@WillowMobileSystems
Thanks for the sweet reminder John. May be I shall expand the idea a bit further, though I have a couple of Tips&Tricks posts regarding the use of Google Apps Script with AppSheet.

We can identify Google Apps Script in 2 ways:

  • Standalone (via creating a Google Apps Scipt project under gDrive)
  • Using Google Docs (Sheet, Doc, Slide etc.) as a Container

Any Container can be reached via opening the Script Editor from the Doc Menu: TOOLS

We can execute a Google Apps Script code with various methods:

  • Standalone
    โ€“ Deploying the script project as Web App
    โ€“ Deploying the script project as API Executable
    โ€“ Deploying the script project as Google Doc Add-on
  • Google Document Container
    โ€“ Running the script from the Script Editor directly
    โ€“ Running the script as a Custom Function
    โ€“ Running the script code via Simple Triggers and/or Installable Triggers

Thank you for the explanation. I think I was unclear. My Google Sheet is attached to a Google form. Most of the time new rows are added via the Google form and then subsequently captured into the Google sheet on form submission. I need the apps script to run when this occurs. Iโ€™m assuming I do an onChange() trigger on the function. Sometimes new rows are added directly from AppSheet. In that case, I need to run the function in the lat-long columnโ€™s Initial Value?

@Tom_automated
You can either use an onChange(e) or onFormSubmit() trigger.

You canโ€™t use a custom script function as an Initial Value in AppSheet. However you can use it in the Spreadsheet formula property as I have explained in-detail above.

When I manually run directly from apps script the shorter code, I get this error:
3X_8_b_8bfdd94c78e20a562d8578b1127e79c4c5225508.png

Iโ€™d like to execute the function whenever a new row is added to my sheet.

Also, if I can put the lat/long into one column it would be preferable. Basically the end goal is to be able to compare rows to a row where the lat/long is Here()

@Tom_automated
Assuming that you have 2 columns in your gSheet and your app: [Address], [Location] respectively.
In your spreadsheet, record and save below function:

function GEOCODE(address) {
	if (address.map) {
		return address.map(GEOCODE)
	} else {
		var r = Maps.newGeocoder().geocode(address)
		for (var i = 0; i < r.results.length; i++) {
			var res = r.results[i]
			return res.geometry.location.lat + ", " + res.geometry.location.lng
		}
	}
}

Now in your AppSheet edittor, expand the column structure details for the [Location] column.


Here; in the Spreadsheet formula property, you shall refer to R1C1 format where:
R refers to the same row of the recorded data from AppSheet
C refers to the absolute column
[-n] refers to the reference column nth column to the LEFT of this column in the spreadsheet
[n] refers to the reference column nth column to the RIGHT of this column in the spreadsheet

Lets expand this a bit futher:

Example (1)


IN GSHEET


Row A B C
1 ID ADDRESS LOCATION
2 485EE2C1 1600 Amphitheatre Pkwy. Mountain View, CA 94043 =GEOCODE(B2)

IN APP


GEOCODE(RC[-1]

Example (2)


IN GSHEET


Row A B C
1 ID LOCATION ADDRESS
2 485EE2C1 =GEOCODE(C2) 1600 Amphitheatre Pkwy. Mountain View, CA 94043

IN APP


GEOCODE(RC[1]

When you record a new row with your AppSheet app, this custom function will be recorded to your gSheet, and the value will be returned as a lat/long pair as you expect it. May be remind you that, you may need to sync the app once more if the lat/long value is not seen in the app after the data is recorded or you are using a delayed sync.

Top Labels in this Space