asked on

Google script to get long/lat from address

hi,

I need a google script to lookup longitude & latitude from an address, looks like the followin does it as a macro, but i would like a function

https://github.com/nuket/google-sheets-geocoding-macro

Output should be a list like this:
https://docs.google.com/spreadsheets/d/1whW4nOLDxQPGmzmG2wFyMFnyy-dBb0wKoIICDNKkoJA

Any ideas?

Also looking to split city,state etc but that can be a follow-up q.

Thanks a lot

lenamtl

Hi,

Do you plan to use PHP?
I'm using this PHP script for my web application
https://github.com/geocoder-php/Geocoder

xenium

ASKER

Thanks. I just plan to use Google Sheets.

leakim971

The function is in the code you pointed in your link.
I did just two small updates to go over first line and to automatically use columns A to C to match your sheet.

function addressToPosition() {
	var sheet = SpreadsheetApp.getActiveSheet();
	//var cells = sheet.getActiveRange();
	var cells = sheet.getRange("A:C");

	// Must have selected 3 columns (Address, Lat, Lng).
	// Must have selected at least 1 row.

	if (cells.getNumColumns() != 3) {
		Logger.log("Must select at least 3 columns: Address, Lat, Lng columns.");
		return;
	}

	var addressColumn = 1;
	var addressRow;

	var latColumn = addressColumn + 1;
	var lngColumn = addressColumn + 2;

	var geocoder = Maps.newGeocoder().setRegion(getGeocodingRegion());
	var location;

	for (addressRow = 2; addressRow <= cells.getNumRows(); ++addressRow) {
		var address = cells.getCell(addressRow, addressColumn).getValue();

		// Geocode the address and plug the lat, lng pair into the
		// 2nd and 3rd elements of the current range row.
		location = geocoder.geocode(address);

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

			cells.getCell(addressRow, latColumn).setValue(lat);
			cells.getCell(addressRow, lngColumn).setValue(lng);
		}
	}
};

function getGeocodingRegion() {
	return PropertiesService.getDocumentProperties().getProperty('GEOCODING_REGION') || 'us';
}

Open in new window

xenium

ASKER

Thanks a lot. I get error "You do not have permission to call set value (line 36)" any ideas?

xenium

ASKER

PS i'm not sure if i'm running it right either, i have typed "=addressToPosition()" into cell D2

I would prefer to have the address as an input, and long/lat as 2 cell array output, so i would put into B2 eg "=GetLongLat(A2)"

leakim971

so =getLongLat(A2) put Long AND Lat in the cell you put the formula?...
the two values Long AND Lat in the same cell?

xenium

ASKER

No, the function should return an array of 2 cells.

ASKER CERTIFIED SOLUTION

leakim971

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

xenium