What Do We Want To Achieve?
Let's say we want to allow people in our neighborhood offer some thing for sale or to give away, or we just want to create a map that let's people know where there are fruit trees in our area, and let people add their own points. Now I'm only going to show how to use point data, since it only requires you to send a single set of coordinates, given some time and a need, this could be done with lines and polygons too, but it would require a lot more work to be done efficiently.
 |
| It's always better to map together |
A small explanation before I get started with the actual code, what I'm going to do is use Google Apps Script to make our Google Sheets document into a small very crude database, that when contacted in HTTP GET or POST will either return the data in our sheet as a GeoJson, or add a row to the table. If any of this sounds confusing, read up on HTTP methods and how they work, that should clear most of the confusion.
I used this process before and it works pretty good, just remember it's still a spreadsheet and has limitations (for example, a single google sheets file can only hold 5 million cell values), but should work well for most simple purposes. I based this process loosely on Stene Bennett's presentation about "Lightweight community mapping sites, powered by spreadsheets" from FOSS4G SotM Oceania 2019, it's a pretty good presentation and worth a watch, although some of it is a bit outdated by now.
Let's get started. As indicated by the name of this post, we have two sides for our code, the first is the Apps Script part which is connected to our sheet, and the second is our map, the actual user interface. We're gonna start with the Apps Script part of the work.
The "DataBase" and API Side
We'll start with opening a new blank sheet, in it we're only going to add the names for our columns, I'm gonna add columns for time (in which I'll add current time when adding rows), type (for type of tree), longitude and latitude (for the coordinates) and the last column is show which I'll use to determine whether I want that point to be shown or not.
Now that we have our table, let's build the Apps Script code that will be our API, basically, this will be the code that allows the table to answer HTTP GET and POST requests. Apps Script allows us to build very basic APIs this way, but ones that work fine for simple purposes.
You would also note that I'm not adding any type of security checks, this can be done by limiting access to the sheet and adding Google sign in to your app, but that's a lot more complicated and while it's a good idea, it would make this post a lot longer than I am willing to write.
To create a new Apps Script we'll click on the Tools menu and than on script editor, and we'll get a new tab with the Apps Script window, where we'll start a new project.
When we want to use Apps Script as an API we need to create functions to run when it receives a HTTP request, we use a function named doGet for HTTP GET and doPost for HTTP POST.
Each Apps Script deployed project can support only one set of these functions, but we can use parameters in our requests to activate other functions within the script. Let's start by writing our doGet function:
What this function does is get the vlaues of our specific sheet, then use them to create a geojson feature for each row that has 1 in its fifth column (Indexing for arrays starts at 0), It then creates a response object and defines it as a JSON response type before returning it to the user. A good Idea by the way is to remove all the empty rows from your table so the script can run a bit faster.
So that's the function for getting the data done, now let's write one for appending new rows.
This function is just as simple, it gets the parametrs we send in the request and appends a row with the current time and defines show as 0 so we'd have a chance to review the data before allowing other users access to it.
After appending the new row, it returns a simple response.
Now when we'll save our script and get a warning, asking us to trust this app with access to our sheets data, that's alright since we want this "app" to both read and append data to our sheet.
after approving the sheet as it is, we'll deploy our app, with the Deploy button in the top right corner of the window, since this is our first deployment, we'll select New Deployment.
This is the confusing part, but since we are keeping thing simple, we are going to select Web App and not API Executable. What it means is that our app stays simple and runs our functions as an API when it gets requests to the address it offers. I can choose to run the script as myself, since I have access and write control to our sheet, or any user that uses it, since I'm creating a public sheet and want only me to have editing acces for the table, I'm allowing anyone to run the script, that means that users don't need to log in to their google accounts and we don't have to send credentials when sending requests.
Now Let's test our API, we'll click on our web app's url and see what we get in a new window, I added one row to my table for this reason.
We get something back, but I'll use geojson.io to see if the geojson object is valid.
Looks like it is, thats exactly the point I took the coordinates for and it has all the attributes we added, including the attributes geojson.io adds for features added to the map.
The address for testing the doPost function is the same one, I recommend using something like postman to test it without having to write the request yourself, it might take a few minutes for google to refresh between deployments, so be patient when changing your functions to do different things.
The Front End side, Our Map
Now that we have a functional back end for our collaborative mapping project, lets build a front end to both show and allow users to easily send their current location to our app so we can add it to the table.
So I'm not gonna assume you know a lot about web development or web mapping so all of the code is gonna be right here, and I'm only adding the bare minimum to get a working map.
Our "front end" is made up of two parts, a HTML file, which is the structure of the website, and a JavaScript file which is going to tell the map what to do.
My project folder is small, only containing two files, one for HTML, the second our JS script, performing all the actions needed. If you working with Visual Studio Code you can create a new index.html file and when you start editing it simply type ! (exclamation mark) and it would suggest Emmet abbreviation which if chosen will create a simple HTML document, or you can copy the code for mine below.
 |
| My project folder |
 |
| Select the single exclamation mark |
 |
| The result |
Now what I did, was take that basic HTML document and add a few simple things:
1. I changed the title, that's the text that appears in the tab next to the icon
2. I added links for leaflet's CSS and JS, these are required, they can also be downloaded locally, but then you'd need to grab all the icons and images so using the unpkg.com version is easier for a simple example.
3. I specified in a style tag that my map will have a height and width of the entire screen view (vh and vw are units of the screen view for height and width respectively)
4. In the body tag, I added an element with the id of "map" which is where I'll tell leaflet to create the map, and in it I loaded the script map.js where I build the basic map and the functions for adding and loading our locations.
The HMTL Code
integrity="sha512-xodZBNTC5n17Xt2atTPuE1HxjVMSvLVW9ocqUKLsCC5CXdbqCmblAshOMAS6/keqq/sMZMZ19scR4PsZChSR7A=="
crossorigin="">
integrity="sha512-XQoYMqMTK8LvdxXYG3nZ448hOEQiglfqkJs1NOQV44cWnUrBc8PkAOcXy20w0vlaXaVUearIOBhiXZ5V3ynxwA=="
crossorigin=""></script>
Now let's get on with the main part of the show, most of this you can find in leaflet's excellent tutorials and documentation and my script is a combination of few of them, mainly the tutorial for using GeoJson, and the one for creating custom controls (HTML elements the map uses) other than that, I simply use map events (namely, "click") to start my functions.
For the basic map, with just a basemap, and centering around a point to a specific zoom level we'll use this piece of the code:
I also add straight after that variables I'll need to use later, some I add empty and some will have a specific value (like our Apps Script URL)
Now I'm gonna start with the more difficult stuff, I'm going to create functions for loading (And reloading) my layer, what it does is simple, it use the fetch API to perform a HTTP GET request using our URL, the function then checks if the map already has a trees layer and if so removes it. After checking if the layer exists the function takes the GeoJson data and redefines a trees layer and adds it to the map.
We now have a function to use our HTTP GET endpoint for the Apps Script script, now let's build functions that use the HTTP POST endpoint, one simple to save the clicked point and a second one to sent it with a request to the sheet when the user clicks a button (the button we'll build later).
First let's start by saving the data of each click event of the map, that means a simple function to run whenever a user clicks the map, this shouldn't go off on a double click or a drag event.
The "placeholder" element is something we'll add later to give the user instructions.
"map.on('click'" is the part that binds this anonymous function to the map click event.
Now let's add another function to send the location, and possibly a tree type, if the user knows what tree it is. I'm using the "placeholder" element again, and also using a "treeType" element which I'll soon create as an input element of type text.
The function takes the treeLocation variable which should be a leaflet location and add parameters to the base URL of our script, it also checks if the user entered any text in the type input and if so adds a type parameter to the URL, after that it simply sends the HTTP POST request with the parameters and resets the placeholder text and the variable that forces the user to click again.
Now that we have the functions, we only need to add the HTML elements to the map for user instructions, in this element we'll add the text input and the send button that allows the user to send the data to server.
Again if, you need further explanation about Leaflet controls, I recommend reading the tutorial explaining about them, you can then read this piece of code with a better understanding about what are controls and how to define new ones.
Now this code is a bit long but all it does is define a class extension for the basic leaflet control (you can define your own class, but using extend we don't have to write a lot of CSS), create a container for the input elements, style it a bit, create the input elements themselves and append them to the container before returning it.
After that all I do is create a new control with the new class and add the control to the map.
And that's it, this should be our map done. I also added a row that loads the data with reloadLayer after defining the functions, you can see the full script, with it inside at the GitHub repository I created for this post, it also has the Google Apps Script script and functions. The repository can be found here. Using GitHub Pages I also published this web app pretty easily, and you can play with it at https://bogind.github.io/trees/.
My app looks like this now with all the code added and only one point approved.
You can use this method, that should take you a few hours at most to create a simple collaborative mapping app to share with your friends or neighbors. If you want to implement user location I would recommend reading the Leaflet tutorial about Leaflet on mobile. Have fun and feel free to comment with links to your maps if you found this post helpful.
P.S. Since your API returns a GeoJson, you can also load the data directly (thought not edit it) in QGIS.
I am not able to add a new point (I have tried your solution as well as my deployment on local and both didnt work)
ReplyDeleteHi,
Deletewithout seeing your code or any other information I can only point you to this answer about blocked POST requests https://stackoverflow.com/a/56502262/5874767