Find and fill the time zone of a user based on their location

[Peter Bodelier]

Do you need the time zone of your users filled correctly, but you do not have any source for it?

Well, if you know the users' location, you can use a Geocode API to find out the right time zone and populate the user record.

In this example I used https://opencagedata.com/

Please note that you will need to sign up and retrieve an API key to use in the script.

Create a Business Rule on the sys_user table, with condition location is not empty AND location changes.

Insert below script and don't forget to add your API Key:

(function executeRule(current, previous /*null when async*/) {

var location = getUserLocation();

var timezone = determineTimezone(location)

setUserTimezone(timezone);


function getUserLocation() {
	return current.location;
}

function determineTimezone(location) {
	var api_key = '<<YOUR API KEY HERE>>';
	
	var query = location.city.getDisplayValue() + ',+' + location.country.getDisplayValue();
	
	var api_url = 'https://api.opencagedata.com/geocode/v1/json'
	
	var request_url = api_url
    + '?'
    + 'key=' + api_key
    + '&q=' + encodeURIComponent(query)
    + '&pretty=1'
	
	
	var request = new sn_ws.RESTMessageV2();
	request.setHttpMethod('get');
	request.setEndpoint(request_url);
	
	var response = request.execute();
	var httpResponseStatus = response.getStatusCode();
	
    if (httpResponseStatus === 200){
		// Success!
		var data = JSON.parse(response.getBody());
		gs.info( data.results[0].annotations.timezone.name + ' for ' + query);
		return data.results[0].annotations.timezone.name;
		
		} else if (httpResponseStatus <= 500){
		// We reached our target server, but it returned an error
		
		gs.error("unable to geocode! Response code: " + httpResponseStatus);
		var data = JSON.parse(response.getBody());
		gs.error('error msg: ' + data.status.message);
		} else {
		gs.error("server error");
	}
	
}

function setUserTimezone(timezone, user) {
	current.time_zone = timezone;
}

})(current, previous);

Of course, you could backfill all existing users' time zone with a background script as well:

var users = new GlideRecord('sys_user');
users.addActiveQuery();
users.addNotNullQuery('location');
users.query();

while (users.next()){
	
	// Retrieve the user's location
	var location = getUserLocation(users);
	
	// Determine the timezone based on the location
	var timezone = determineTimezone(location)
	
	// Set the user's timezone to the determined timezone
	setUserTimezone(timezone, users);
	
}


function getUserLocation(user) {
	return user.location;
}

function determineTimezone(location) {
	var api_key = '<<YOUR API KEY HERE>>';
	
	var query = location.city.getDisplayValue() + ',+' + location.country.getDisplayValue();
	
	var api_url = 'https://api.opencagedata.com/geocode/v1/json'
	
	var request_url = api_url
    + '?'
    + 'key=' + api_key
    + '&q=' + encodeURIComponent(query)
    + '&pretty=1'
	
	// see full list of required and optional parameters:
	// https://opencagedata.com/api#forward
	
	var request = new sn_ws.RESTMessageV2();
	request.setHttpMethod('get');
	request.setEndpoint(request_url);
	
	var response = request.execute();
	var httpResponseStatus = response.getStatusCode();
	// see full list of possible response codes:
    // https://opencagedata.com/api#codes
	
    if (httpResponseStatus === 200){
		// Success!
		var data = JSON.parse(response.getBody());
		gs.info( data.results[0].annotations.timezone.name + ' for ' + query);
		return data.results[0].annotations.timezone.name;
		
		} else if (httpResponseStatus <= 500){
		// We reached our target server, but it returned an error
		
		gs.error("unable to geocode! Response code: " + httpResponseStatus);
		var data = JSON.parse(response.getBody());
		gs.error('error msg: ' + data.status.message);
		} else {
		gs.error("server error");
	}
	
}

function setUserTimezone(timezone, user) {
	user.time_zone = timezone;
	user.update();
}

[Peter Bodelier · Accepted solution]

Not possible to create as article/blog, therefore marking this response as correct to close thread.