Latlong from Address

Skip2MiLu · 02-09-2020 09:48 PM

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

MultiTech

Unfortunately, Geocoding is not currently available.

Skip2MiLu

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

MultiTech

We can only hope!

Brady_Lovell

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
}
}
}

Tom_automated

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:

LeventK

@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.

Tom_automated

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.

LeventK

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?

WillowMobileSys

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??

LeventK

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.

WillowMobileSys

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.

LeventK

@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

Tom_automated

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?

LeventK

@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.

Tom_automated

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

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()

LeventK

@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.

timsimpson

Thanks for the explanations above and forgive me, I'm pretty new to using scripts in googlesheets.

I've added the script suggested by @Brady_Lovell , running from an address string and it worked find inside the google sheet with <1000 entries. Then a while later it failed and is not returning #ERROR : "Exception: Service invoked too many times for one day: premium geocode. (line 11)."

I understand the script is being reapplied on each entry but I'm not sure what's driving it and how to stop it doing so. The address of each entry only needs geocoding once as the entry is added via a googleform and then on the rare occasion an entry is edited, but even then the geocoding doesn't have to be immediate.

Is Appsheet applying the script on each line during each sync as implied by the Sreadsheet formula in Appsheet, or is the script being applied on a schedule by Google? Or both??

I've seen that each script has 'Triggers' in its admin panel and am wondering whether these, set to a daily, would alleviate the repeated triggering of the script.

Apologies if I'm asking daft questions but googling the problem hasn't created any clarity.