Here's how you can use Google's script editor to get the timezoneName and timeZoneId inside a gsheet.
Step 1. Get an API key for Google's timezone API
Step 2. Create a new gsheet. Underneath the 'tools' menu click 'script editor'. Add the following code:
function getTimezone(lat, long) {
var apiKey = 'INSERTAPIKEYHERE'
var url = 'https://maps.googleapis.com/maps/api/timezone/json?location=' + lat + ',' + long + '×tamp=1331161200&key=' + apiKey
var response = UrlFetchApp.fetch(url);
var data = JSON.parse(response.getContentText());
return data["timeZoneName"];
}
Step 3. Save and publish your getTimezone()
function and use it as shown in the image above.