I realize this is a few years old, but in case anyone is searching for this in the future: it looks like those fields are base-64-encoded JSON. I think you'll need to do the decoding in an Automation, but that should get you the latitude and longitude.
I realize this is a few years old, but in case anyone is searching for this in the future: it looks like those fields are base-64-encoded JSON. I think you'll need to do the decoding in an Automation, but that should get you the latitude and longitude.
based on work done by @apg314 and @Yourapp from this post, https://community.airtable.com/t5/development-apis/base64-conversion/td-p/103578
I was able to modify the code for Base64 decoding rather than encoding.
Just copy past into a script block and execute to give you a long string which contains lat & long
It's a big JSON bundle so you need extra formula fields to strip out lat & long
eg Lat:
MID( JSONOutput, FIND("Australia",JSONOutput)+17, 11)
not 100% as decimals vary, but 2000 records had about 10 errors needing manual adjustment
-----
function btoa(token) {
var Base64={_keyStr:"ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/=",decodeURI:function(e){var t="";var n,r,i,s,o,u,a;var f=0;e=Base64._utf8_encode(e);while(f<e.length){n=e.charCodeAt(f++);r=e.charCodeAt(f++);i=e.charCodeAt(f++);s=n>>2;o=(n&3)<<4|r>>4;u=(r&15)<<2|i>>6;a=i&63;if(isNaN(r)){u=a=64}else if(isNaN(i)){a=64}t=t+this._keyStr.charAt(s)+this._keyStr.charAt(o)+this._keyStr.charAt(u)+this._keyStr.charAt(a)}return t},decode:function(e){var t="";var n,r,i;var s,o,u,a;var f=0;e=e.replace(/[^A-Za-z0-9\+\/\=]/g,"");while(f<e.length){s=this._keyStr.indexOf(e.charAt(f++));o=this._keyStr.indexOf(e.charAt(f++));u=this._keyStr.indexOf(e.charAt(f++));a=this._keyStr.indexOf(e.charAt(f++));n=s<<2|o>>4;r=(o&15)<<4|u>>2;i=(u&3)<<6|a;t=t+String.fromCharCode(n);if(u!=64){t=t+String.fromCharCode(r)}if(a!=64){t=t+String.fromCharCode(i)}}t=Base64._utf8_decode(t);return t},_utf8_encode:function(e){e=e.replace(/\r\n/g,"\n");var t="";for(var n=0;n<e.length;n++){var r=e.charCodeAt(n);if(r<128){t+=String.fromCharCode(r)}else if(r>127&&r<2048){t+=String.fromCharCode(r>>6|192);t+=String.fromCharCode(r&63|128)}else{t+=String.fromCharCode(r>>12|224);t+=String.fromCharCode(r>>6&63|128);t+=String.fromCharCode(r&63|128)}}return t},_utf8_decode:function(e){var t="";var n=0;var r=c1=c2=0;while(n<e.length){r=e.charCodeAt(n);if(r<128){t+=String.fromCharCode(r);n++}else if(r>191&&r<224){c2=e.charCodeAt(n+1);t+=String.fromCharCode((r&31)<<6|c2&63);n+=2}else{c2=e.charCodeAt(n+1);c3=e.charCodeAt(n+2);t+=String.fromCharCode((r&15)<<12|(c2&63)<<6|c3&63);n+=3}}return t}}
return Base64.decode(token)
}
// Change this name to use a different table
let table = base.getTable("Company");
let tableQuery = await table.selectRecordsAsync();
for(let tableRec of tableQuery.records) {
b64 = btoa(tableRec.getCellValue("MapCache"));
// Insert records in "field2paste" table1
await table.updateRecordAsync(tableRec, {
"JSONOutput" : b64,
});
}
One of the easiest ways of doing this would be to use Make's integrations with Google Maps along with Make’s integrations with Airtable.
Make can grab an address from your Airtable system, send it to Google Maps, retrieve geographic coordinates (latitude and longitude), and then place those coordinates into your Airtable system.
If you’ve never used Make before, I’ve assembled a bunch of Make training resources in this thread. For example, here is one of the ways that you could instantly trigger a Make automation from Airtable.
I also give live demonstrations of how to use Make in many of my Airtable podcast appearances. For example, in this video, I show how to work with Airtable arrays in Make.
Alternatively, if you want to grab geolocation coordinates via a form submission, you can use Fillout's gelocation features.
Fillout lets you create new records or update existing records in Airtable, and they have the ability to geotag addresses with their location coordinates feature.
Fillout gives you hundreds of other advanced form features as well, such as the ability to generate PDF files from form submissions, display Airtable lookup fields on forms, control access to a form via SSO or email domains, perform math or other live calculations on your forms, accept payments on forms, collect signatures on a form, create multi-page forms with conditional paths, create new linked records on a form, and more.
I show how to use a few of the advanced features of Fillout on these 2 Airtable podcast episodes:
Hope this helps! If you’d like to hire the best Airtable consultant to help you with anything Airtable-related, please feel free to contact me through my website: Airtable consultant — ScottWorld
