Embedded Files
Built to help the City of Wheat Ridge gather feedback from riders and keep its bus stops clean and well‑maintained, this project pairs QR codes / HTML / CSS / JavaScript, Google Apps Script, and Google Sheets. Anyone in the community can scan a code at the shelter, fill out a short mobile‑friendly form, and submit an issue. No app or account required. The page utilizes the browser’s Geolocation API to automatically capture latitude and longitude. On submission, Apps Script writes the record directly to a protected Google Sheet via the Sheets API, creating a live, filterable maintenance log. Riders enter the stop number, select the issue type (trash overflow, graffiti, damage, snow removal, or other), add comments if desired, and tap Submit. The form emulates the City of Wheat Ridge's colors, fonts, and header layout, so riders feel they have never left an official page. It’s a scalable system that builds on tools anyone can access and is free to use. Most importantly, it strengthens the partnership between residents and city services, making it easier for people to speak up and for the city to respond.
Bus Stop Feedback App Demo.mp4
Code.gs
Code.gs
/**
* This function runs when someone visits the web app URL.
* It loads the form interface (HTML file) for users to submit reports.
*/
function doGet(e) {
return HtmlService.createTemplateFromFile('Index') // Load the form page
.evaluate()
.setTitle('Bus Stop Maintenance Reporting'); // Set the title of the web page
}
/**
* This function saves the submitted report data to a Google Sheet.
* @param {Object} formObject - The form data submitted by the user.
* @returns {string} Success or error message.
*/
function submitForm(formObject) {
try {
// **Step 1: Specify the ID of the Google Sheet where data will be stored**
const SPREADSHEET_ID = 'REPLACE_SPREADSHEET_ID';
// **Step 2: Open the Google Sheet and select the worksheet (tab)**
const spreadsheet = SpreadsheetApp.openById(SPREADSHEET_ID);
const sheet = spreadsheet.getSheetByName('WebApp'); // Update if your sheet name is different
// **Step 3: Append (add) a new row with the submitted data**
sheet.appendRow([
new Date(), // Automatically records the timestamp
formObject.stopId || '', // Bus Stop ID (User input)
formObject.issue || '', // Type of issue (User selects)
formObject.latitude || '', // Location (latitude from GPS)
formObject.longitude || '', // Location (longitude from GPS)
formObject.notes || '' // Additional details (User input)
]);
// **Step 4: Return a success message to the user**
return 'Report submitted successfully. Thank you!';
} catch (error) {
// If something goes wrong, return an error message
return 'Error: ' + error.message;
}
}
Index.html
Index.html
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1, user-scalable=no" />
<title>Bus Stop Maintenance Reporting</title>
<!-- Example font (Open Sans) -->
<link
href="https://fonts.googleapis.com/css2?family=Open+Sans:wght@400;600&display=swap"
rel="stylesheet"
/>
<style>
* {
box-sizing: border-box;
margin: 0;
padding: 0;
}
body {
font-family: 'Open Sans', sans-serif;
background-color: #003865;
color: #ffffff;
}
/*********************************
* HEADER STYLES
*********************************/
header {
position: relative;
min-height: 80px;
display: flex;
align-items: center;
padding: 1rem;
background-color: #ffffff;
}
.logo-area {
display: flex;
align-items: center;
}
.logo-area img {
height: 100px;
margin-right: 0.75rem;
}
.logo-area h1 {
color: #000000;
font-weight: 600;
font-size: 18px;
}
/* Navigation bar (social icons) */
nav {
position: absolute;
bottom: 1rem;
right: 1rem;
display: flex;
gap: 1rem;
}
nav a {
text-decoration: none;
}
.social-icon {
width: 32px;
height: 32px;
}
/*********************************
* MAIN CONTENT AREA
*********************************/
.content {
max-width: 90%;
margin: 2rem auto;
padding: 1rem;
}
.content h2 {
color: #ffffff;
margin-bottom: 1rem;
}
.content p {
margin-bottom: 1rem;
}
.content p strong {
color: #ffffff;
}
/* Status box for success or error messages */
#statusBox {
margin-top: 10px;
padding: 8px;
border-radius: 4px;
display: none;
}
.success {
background-color: #eafce2;
color: #287d00;
border: 1px solid #bedbb0;
}
.error {
background-color: #ffe7e7;
color: #b70000;
border: 1px solid #ffa5a5;
}
label {
display: inline-block;
margin-bottom: 0.5rem;
font-weight: 18px;
}
input[type='text'],
select,
textarea {
width: 100%;
padding: 00.75rem;
font-size: 18px;
border: 1px solid #ccc;
border-radius: 4px;
color: #000000;
}
input[type='button'] {
font-size: 18px;
background-color: #ffffff;
color: #003865;
padding: 12px 16px;
border: none;
border-radius: 4px;
font-weight: 18px;
cursor: pointer;
}
input[type='button']:hover {
background-color: #bed1e0;
}
/*********************************
* FOOTER STYLES
*********************************/
footer {
background-color: #ffffff;
padding: 1rem;
text-align: center;
font-size:18px;
color: #000;
}
footer p {
margin: 0.25rem 0;
}
.contact-info {
margin: 0.5rem 0;
}
footer a {
color: #003865;
text-decoration: none;
}
footer a:hover {
text-decoration: underline;
}
</style>
<script>
// On page load, try to get the user’s location
function getLocation() {
if (navigator.geolocation) {
navigator.geolocation.getCurrentPosition(showPosition, showError);
} else {
displayMessage(
"Geolocation is not supported by this browser.",
true
);
}
}
function showPosition(position) {
document.getElementById("latitude").value = position.coords.latitude;
document.getElementById("longitude").value = position.coords.longitude;
}
function showError(error) {
displayMessage(
"Unable to retrieve your location. Please enable GPS/Location Services.",
true
);
}
// Called after the Apps Script (submitForm) completes
function onSuccess(msg) {
// If the script returns an error string, handle that
if (msg.toLowerCase().startsWith("error:")) {
displayMessage(msg, true);
} else {
// Otherwise, success
displayMessage(msg, false);
document.getElementById("reportForm").reset();
}
}
// Display messages in the #statusBox div
function displayMessage(message, isError) {
const statusBox = document.getElementById("statusBox");
statusBox.innerText = message;
statusBox.style.display = "block";
if (isError) {
statusBox.classList.add("error");
statusBox.classList.remove("success");
} else {
statusBox.classList.add("success");
statusBox.classList.remove("error");
}
}
</script>
</head>
<body onload="getLocation()">
<!-- Header with city logo, name, and social media icons -->
<header>
<div class="logo-area">
<!-- Replace with a direct link to your city’s actual logo if desired -->
<img
src="https://www.ci.wheatridge.co.us/ImageRepository/Document?documentID=35084"
alt="City of Wheat Ridge Logo"
/>
</div>
<nav>
<a
href="https://www.facebook.com/CityofWheatRidgeGovernment/"
target="_blank"
aria-label="Facebook"
>
<img
src="https://cdn-icons-png.flaticon.com/512/733/733547.png"
alt="Facebook"
class="social-icon"
/>
</a>
<a
href="https://www.instagram.com/cityofwheatridge/"
target="_blank"
aria-label="Instagram"
>
<img
src="https://cdn-icons-png.flaticon.com/512/1384/1384063.png"
alt="Instagram"
class="social-icon"
/>
</a>
<a
href="https://twitter.com/citywheatridge"
target="_blank"
aria-label="Twitter"
>
<img
src="https://cdn-icons-png.flaticon.com/512/733/733579.png"
alt="Twitter"
class="social-icon"
/>
</a>
<a
href="https://www.youtube.com/user/wheatridgetv8"
target="_blank"
aria-label="YouTube"
>
<img
src="https://cdn-icons-png.flaticon.com/512/1384/1384060.png"
alt="YouTube"
class="social-icon"
/>
</a>
</nav>
</header>
<!-- Main Content -->
<div class="content">
<h2>Bus Stop Maintenance Reporting</h2>
<p>
<strong>Did you know each city is responsible for bus stop maintenance?</strong>
</p>
<p>
Help us keep Wheat Ridge bus stops clean and safe. Your location will be
captured automatically.
</p>
<!-- Status box for messages -->
<div id="statusBox"></div>
<form id="reportForm">
<!-- Hidden fields for lat/lon -->
<input type="hidden" id="latitude" name="latitude" />
<input type="hidden" id="longitude" name="longitude" />
<label for="stopId">Bus Stop ID:</label>
<input type="text" id="stopId" name="stopId" required />
<label for="issue">Issue Type:</label>
<select id="issue" name="issue" required>
<option value="overflowing">Overflowing/Full Trash</option>
<option value="trash_debris">Trash or Debris</option>
<option value="graffiti">Graffiti</option>
<option value="damage">Damage to Bench/Trash Can</option>
<option value="snow">Snow Removal Needed</option>
<option value="other">Other</option>
</select>
<label for="notes">Additional Notes (optional):</label>
<textarea id="notes" name="notes" rows="3" cols="30"></textarea>
<input
type="button"
value="Submit Report"
onclick="google.script.run
.withSuccessHandler(onSuccess)
.submitForm(document.getElementById('reportForm'))"
/>
</form>
</div>
<!-- Footer with contact info -->
<footer>
<p>Contact Us</p>
<div class="contact-info">
City of Wheat Ridge City Hall<br />
7500 W. 29th Avenue<br />
Wheat Ridge, CO 80033<br />
Phone: <a href="tel:3032345900">303-234-5900</a>
</div>
<p>
Questions? Give us a call or
<a href="https://www.ci.wheatridge.co.us/">visit our website</a>.
</p>
</footer>
</body>
</html>
Google Sites
Report abuse