Converting between different coordinate reference systems or projection is a fairly standard feature in a GIS. There are a number of command line tools also available for performing bulk-conversions. cs2cs program, part of the PROJ.4 library is my favorite.
Recently, someone presented me with an interesting challenge. They were collecting field data in UTM coordinates and needed a way to convert them to Latitude and Longitude to map them. As the data was being constantly updated, any offline solution that require exporting and importing the conversion would be cumbersome.
Luckily, there was an easy solution. I found the excellent open-source YouTim: Javascript UTM Conversion library. I was able to adapt it to be used as a Apps Script in Google Spreadsheets. With this script enabled in your Google Spreadsheet, you have access to 2 custom functions – DegreesToUTM(latitude, longitude) and UTMToDegrees(easting, norting, zone) that you can use in the formula like any other built-in function.
You can give it a try. Just open this spreadsheet , make a copy and see the formulae in action. If you wish to change the behavior if the functions, you can edit the code by going to Tools > Script Editor > utmconv.gs in the spreadsheet you copied from above.
If you are familiar with QGIS, you can use it to bulk convert coordinates between any projections easily. See my video guide below.
Great help!
Any chance this comes available for other cs’s too? like EPSG:28992?
For other CRSs, it will be easier to use site such as https://mygeodata.cloud/cs2cs/ or get them as CSV and use QGIS to import, and export with new CRS.
[…] you are working with coordinates in a spreadsheet, check out this post on converting Lat/Lon coordinates to UTM coordinates dynamically in a […]
It calculates the northing UTM wrong when latitude is negative (south hemisphere). Is it possible to adapt a correction for the code?
Hi Eduardo – Thanks for your note. I have applied a fix (the code for handling negative values was wrongly commented out in the script). Can you make a new copy of the spreadsheet and check if it looks ok?
Hi! Thank you so much! Now it works fine from degress to UTM. But from UTM to degress it still does not. I am testing with lat -26.748012232277716, long -49.55182410316538 and UTM Zone:22, X: 644009.10, Y: 7040655.01
Hello, my UTM data is in Australia, zone 55 and I don’t think the script works well in that region. I am seeing different results from other online tools. I think your spreadsheet would work best with a selector if the data is North or South of the equator. Regardless, thank you for making contributions like this.