Convert Latlong List to Polygon WKT using Google Apps Script
A few days ago, I was curious to search if there is any tool out there that can help me to create Polygon WKT by clicking several pinpoints on the map.
To you who don’t know what Polygon WKT is, WKT stands for “Well-Known Text”. It’s only a text format that can be considered polygon map data, something like “yyyy-mm-dd” for formatting the date data. You can find the detail about WKT here.
Back to the topic of the website with such features I wanted, I desperately found zero of them (despite my poor googling skill -lol-). However, I found some helpful websites — but need additional steps to get the output I needed.
As I mentioned, rather than in Polygon WKT, the output is still in latlong list format.
We need to convert it to Polygon WKT in this format below but with a simple click or only copy-paste the website’s output.
POLYGON((lng1 lat1, lng2 lat2, lng3 lat3, ...))At first, I thought about using a simple web app, but it is easier to use Google Sheet with the help of Google Apps Script. So, here we go!
Creating Google Script
Step by step to create Google Apps Script:
- Open the Google Spreadsheet. (wow, thanks, Capt. Obvious)
- Click “Extension > Apps Script” on the menu bar (as far as I remember, in the old UI of Google Spreadsheet, the menu is “Tools > Apps Script”).
- A new tab will be opened, and you will be directed to this UI with a pre-defined empty “MyFunction()” function.
You can change the project name from “Untitled project” to what you want.
Now, back to the sheet tab, let’s make a user scenario like this.
The user will paste the latlong data from the web to column A, and the result will be shown automatically in cell C2.
Write the Script
Back to the Apps Script tab, let’s write our first line inside the “MyFunction()” function. Here is the complete code of our script. You can find the explanation detail below.
function myFunction() {
var sheet = SpreadsheetApp.getActive().getSheetByName("data");
last_row = sheet.getLastRow()-1;
var data = sheet.getRange(2,1,last_row,1).getValues();
var cell = sheet.getRange(2,3); var result = "POLYGON((";
var i = 1; data.forEach(function (row) {
latlong = row[0].split(" , ");
lat = latlong[0];
lng = latlong[1]; if (i === last_row) {
result += lng + " " + lat + "))";
} else {
result += lng + " " + lat + ", ";
}
i += 1;
}); cell.setValue(result);
}
The next part would be the explanation of each line. If you want to skip to the next subsection about “Triggers”, you can click on this link.
Script Explanation
We want the interpreter to know which sheet containing latlong data that we refer to (and not other sheets, especially if you have multiple of them). This can be identified using the sheet name. By default, the sheet name is “Sheet1”.
var sheet = SpreadsheetApp.getActive().getSheetByName("Sheet1");We define the variable “sheet” in the code above, which refers to Sheet1. Next, as we saw earlier, the data in column A is dynamic. Means, we can have as many rows as we want (along with how many pinpoints we created earlier on the web). So, we have to define our data's “end row”. Then, get all the data in column A.
// Get last row number
last_row = sheet.getLastRow()-1;// Get data from column A
var data = sheet.getRange(2,1,last_row,1).getValues();// Define which cell to show the output
var cell = sheet.getRange(2,3);
Explanation:
When we define the “last_row” variable, we have to minus 1 the result since the first row of column A is a header, so we should exclude that from the calculation (also, we will only get the data from row 2 and so on).
I know what you might ask again. Why the “getRange()” method has difference format between “getRange(2,1,last_row,1)” and “getRange(2,3)”?
In the first line, it will get range from row 2 column 1 (A2) to row “last row” column 1 (last row of column A).
In the second line, it will get the range (single cell) of row 2 column 3 (C2).
Then we have to create a loop and add these latlong data to polygon WKT format for each row and print the format to cell C2.
var result = "POLYGON((";
var i = 1;data.forEach(function (row) {
latlong = row[0].split(" , ");
lat = latlong[0];
lng = latlong[1];
if (i === last_row) {
result += lng + " " + lat + "))";
} else {
result += lng + " " + lat + ", ";
} i += 1;
});cell.setValue(result);
Explanation:
Define result data with “POLYGON((“. Remember the output format we want:
POLYGON((lng1 lat1, lng2 lat2, lng3 lat3, ..., lngN latN))Every latlong in the loop we add “,” in the end, except for the last latlong (which ends in “))”). So we define dummy variable i=1 to know the last latlong, with i representing the row number (that’s why I define it with 1 and not 0 to make it easier to read).
Then we define the loop using the “forEach” method.
data.forEach(function (row) {
...
});This will loop every element in variable “data” and name the element as “row” for every iteration (Note: “row” can be replaced with another name).
Remember the website output that we got earlier?
-6.180703638599647 , 106.8227959734262
We have to split the latitude and longitude by “ , “ using the “split()” method and define the first element as latitude and the second element as longitude.
latlong = row[0].split(" , ");
lat = latlong[0];
lng = latlong[1];You might ask, why do we use “row[0]” instead of “row”? Isn’t every element in the loop already named “row”?
The data that we defined earlier using “sheet.getRange(2,1,last_row-1,1).getValues();” is in array format. Hence, we should refer to the element of the array (and not the array itself), albeit it has only 1 element. (Note: Remember Google Script using 0 as the first index identifier).
We then combine the latlong we got using this script and add the result to the variable “result” we have defined.
result += lng + " " + lat + ", ";Remember, the last line of latlong is exceptional, since we have to close it with “))” rather than “, “. With our dummy variable i, we can use the if-else function to go this way (don’t forget to add 1 to i for every loop, so it will represent the next row number).
if (i === last_row) {
result += lng + " " + lat + "))";
} else {
result += lng + " " + lat + ", ";
}
i += 1;So, if i equals the last row number, we should close the latlong with “))” and we’re done!
We then can print the result in cell C2 (our defined variable “cell”).
cell.setValue(result);Add Trigger to Script
No, we’re not done yet! Lol.
We have defined only the script function. But we have to run this function every time the user paste the data to column A. This is why we will use the “Trigger” feature. You can find the menu in the sidebar.
Step by step to create the trigger:
- Click the “Add Trigger” button on the bottom right of the screen.
- Popup menu will be shown up.
- Leave some options as default, except for “Select event type”, please select “On Edit”. Below is the explanation for each option.
- Choose which function to run: Pick the function that should be run when the trigger fires (as the event).
- Choose which deployment should run: No option if you are not creating “version” of your script, so the choice is always Head.
- Select event source: This is the type of trigger. There are three options: From Spreadsheet (to trigger event from spreadsheet), Time-driven (to trigger periodically), or From Calendar.
- Select event type: When you select “From Spreadsheet” from the event source, there are four options:
a. On open: trigger the event when the user opens the Spreadsheet.
b. On edit: trigger the event when the user edits any value.
c. On change: trigger the event when the user changes the structure of the Spreadsheet (add a column, remove a column, etc.)
d. On form submit: trigger the event when someone submits Google Form, and the result is inputted to Spreadsheet.
If you choose “Time-driven” from the event source, you can pick a date and time to trigger the event at some specific time in the future. - Failure notification settings: Choose how often you want to be notified if your trigger fails to run.
And it’s a wrap. You can test it now. And we will get this result!
Last note from this long article, you actually can use the “OnEdit(e)” function when defining the function to run. This is called “simple trigger”, and you can skip the step to define the trigger manually. You can find the explanation here. It is not explained here since we want to cover many features of Google Script.
That’s all from me. Ciao!
