<aside> 💡 VOCÊ JÁ PENSOU EM CRIAR UM GEO-FORMULÁRIO EM FRONT-END PARA COLETAR DADOS PARA SUA PLANILHA GOOGLE?
</aside>
Exemplo:
Exemplo de modelo para tabela:
| Nome | Tipo | Status | Latitude | Longitude |
|---|---|---|---|---|
| Praia da Joaquina | Surf | Alta Temporada | -27.48999941 | -48.38653564 |
| Praia da Daniela | Calma | Cheia | -27.44841931 | -48.53158951 |
| Praia da Tapera | Calma | Suja | -27.60080291 | -48.61038208 |
| Praia de Itaguaçu | Calma | Suja | -27.61591462 | -48.5949862 |
| Praia dos Ingleses | Surf | Cheia | -27.43615551 | -48.39074135 |
| Praia da Pinheira | Calma | Cheia | -27.8661679 | -48.60300064 |
| Praia da Armação | Calma | Temporada | -27.78115133 | -48.51742744 |
Script:
const DATA_ENTRY_SHEET_NAME = "Sheet1";
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(DATA_ENTRY_SHEET_NAME);
const doPost = (request = {}) => {
const { postData } = request;
if (postData && postData.contents) {
var data = parseFormData(postData.contents);
appendToGoogleSheet(data);
return ContentService.createTextOutput("Data submitted successfully!").setMimeType(ContentService.MimeType.JSON);
} else {
return ContentService.createTextOutput("Invalid request data").setMimeType(ContentService.MimeType.JSON);
}
};
function parseFormData(postData) {
var data = [];
var parameters = postData.split('&');
for (var i = 0; i < parameters.length; i++) {
var keyValue = parameters[i].split('=');
data[keyValue[0]] = decodeURIComponent(keyValue[1]);
}
return data;
}
function appendToGoogleSheet(data) {
// Defina a ordem das colunas desejada
var columnsOrder = ['Nome', 'Tipo', 'Status', 'Latitude', 'Longitude'];
// Crie um novo array de rowData apenas com as informações relevantes na ordem correta
var rowData = columnsOrder.map(column => data[column]);
// Inclua latitude e longitude no array rowData
//rowData.push(data['Latitude'] || '', data['Longitude'] || '');
// Adicione o array rowData à planilha
sheet.appendRow(rowData);
}
Front-end:
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Coletas de Dados Praias</title>
<link rel="stylesheet" href="<https://cdn.jsdelivr.net/npm/[email protected]/css/bulma.min.css>" />
<link rel="stylesheet" href="<https://unpkg.com/leaflet/dist/leaflet.css>" />
</head>
<body>
<section class="hero is-primary is-bold">
<div class="hero-body">
<div class="container">
<h1 class="title">Coletas de Dados Praias</h1>
</div>
</div>
</section>
<div class="wrap">
<div class="columns is-centered">
<div class="column is-half">
<form id="form" class="container m-4 pl-4" method="POST" action="<https://script.google.com/macros/s/AKfycbxphedaUW1S7F-tEwRn6xBoqQ4HmPc71B9bdUzgCerqVBB6V3so3McJalxGA_aCrJ8F/exec>">
<div class="field">
<label class="label">Nome</label>
<div class="control">
<input class="input" type="text" placeholder="Nome" name="Nome" />
</div>
</div>
<div class="field">
<label class="label">Tipo</label>
<div class="control">
<input class="input" type="text" placeholder="Tipo" name="Tipo" />
</div>
</div>
<div class="field">
<label class="label">Status</label>
<div class="control">
<input class="input" type="text" placeholder="Status" name="Status" />
</div>
</div>
<!-- Outros campos do formulário... -->
<div class="field">
<label class="label">Latitude</label>
<div class="control">
<input class="input" type="text" placeholder="Latitude" id="latitude" name="Latitude" />
</div>
</div>
<div class="field">
<label class="label">Longitude</label>
<div class="control">
<input class="input" type="text" placeholder="Longitude" id="longitude" name="Longitude" />
</div>
</div>
<div id="map" style="height: 300px; margin-bottom: 20px;"></div>
<div class="field is-grouped">
<div class="control">
<button class="button is-primary" type="submit" id="submit-button">ENVIAR</button>
</div>
</div>
</form>
<div id="message" style="display: none; margin: 20px; font-weight: bold; color: green; padding: 8px; background-color: beige; border-radius: 4px; border-color: aquamarine;"></div>
</div>
</div>
</div>
<script src="<https://unpkg.com/leaflet/dist/leaflet.js>"></script>
<script>
document.getElementById("form").addEventListener("submit", function (e) {
e.preventDefault();
// Additional logic for collecting latitude and longitude
var latitude = document.getElementById('latitude').value;
var longitude = document.getElementById('longitude').value;
// Set the values for latitude and longitude
document.getElementById('latitude').value = latitude;
document.getElementById('longitude').value = longitude;
// Continue with the form submission
document.getElementById("message").textContent = "Submitting..";
document.getElementById("message").style.display = "block";
document.getElementById("submit-button").disabled = true;
// Collect the form data
var formData = new FormData(this);
var keyValuePairs = [];
for (var pair of formData.entries()) {
keyValuePairs.push(pair[0] + "=" + pair[1]);
}
var formDataString = keyValuePairs.join("&");
// Send a POST request to your Google Apps Script
fetch(
"<https://script.google.com/macros/s/AKfycbwCzt2XR17FU8mCY5vueUp98hAzyLy60UfELHPlYBIqEpcQ8jJtq3aAHqxZAoQuNg/exec>",
{
redirect: "follow",
method: "POST",
body: formDataString,
headers: {
"Content-Type": "text/plain;charset=utf-8",
},
}
)
.then(function (response) {
// Check if the request was successful
if (response) {
return response; // Assuming your script returns JSON response
} else {
throw new Error("Failed to submit the form.");
}
})
.then(function (data) {
// Display a success message
document.getElementById("message").textContent =
"Data submitted successfully!";
document.getElementById("message").style.display = "block";
document.getElementById("message").style.backgroundColor = "green";
document.getElementById("message").style.color = "beige";
document.getElementById("submit-button").disabled = false;
document.getElementById("form").reset();
// Clear latitude and longitude fields
document.getElementById('latitude').value = '';
document.getElementById('longitude').value = '';
setTimeout(function () {
document.getElementById("message").textContent = "";
document.getElementById("message").style.display = "none";
}, 2600);
})
.catch(function (error) {
// Handle errors, you can display an error message here
console.error(error);
document.getElementById("message").textContent =
"An error occurred while submitting the form.";
document.getElementById("message").style.display = "block";
});
});
// Map initialization code...
let mapOptions = {
center: [-27.61540601339959, -48.50463867187501],
zoom: 10
}
let map = new L.map('map', mapOptions);
// Default layer (OpenStreetMap)
let defaultLayer = new L.TileLayer('http://{s}.tile.openstreetmap.org/{z}/{x}/{y}.png');
map.addLayer(defaultLayer);
// Satellite layer
let satelliteLayer = new L.TileLayer('<https://server.arcgisonline.com/ArcGIS/rest/services/World_Imagery/MapServer/tile/{z}/{y}/{x}>', {
attribution: 'Tiles © Esri — Source: Esri, i-cubed, USDA, USGS, AEX, GeoEye, Getmapping, Aerogrid, IGN, IGP, UPR-EGP, and the GIS User Community'
});
// Control to switch between layers
var baseMaps = {
"Default": defaultLayer,
"Satellite": satelliteLayer
};
L.control.layers(baseMaps).addTo(map);
let marker = null;
map.on('click', (event) => {
if (marker !== null) {
map.removeLayer(marker);
}
marker = L.marker([event.latlng.lat, event.latlng.lng]).addTo(map);
document.getElementById('latitude').value = event.latlng.lat;
document.getElementById('longitude').value = event.latlng.lng;
});
</script>
</body>
</html>