I created a Web App with Apps Script from a Google Sheet. It gets the user's location and inserts a new row to a Sheet successfully. But after successfully submitting the form and adding a new row to the sheet, it doesn't return a success message to the user, instead it returns Forbidden Error 403.
I cleared the cache of the browser. I used different browsers and different devices. None of these worked
Here is a link to the file: https://docs.google.com/spreadsheets/d/1TwtY9mdBvYqIp-u57Wk059FWwuI0Bbfpg3LeTktSBFw/edit?usp=sharing
Here is the gs file:
const sheetName = 'Sheet1'
const scriptProp = PropertiesService.getScriptProperties()
function initialSetup () {
const activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet()
scriptProp.setProperty('key', activeSpreadsheet.getId())
}
function doPost (e) {
const lock = LockService.getScriptLock()
lock.tryLock(10000)
try {
const doc = SpreadsheetApp.openById(scriptProp.getProperty('key'))
const sheet = doc.getSheetByName(sheetName)
const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0]
const nextRow = sheet.getLastRow() + 1
const newRow = headers.map(function(header) {
return header === 'Date' ? new Date() : e.parameter[header]
})
sheet.getRange(nextRow, 1, 1, newRow.length).setValues([newRow])
return ContentService
.createTextOutput(JSON.stringify({ 'result': 'success', 'row': nextRow }))
.setMimeType(ContentService.MimeType.JSON)
}
catch (e) {
return ContentService
.createTextOutput(JSON.stringify({ 'result': 'error', 'error': e }))
.setMimeType(ContentService.MimeType.JSON)
}
finally {
lock.releaseLock()
}
}
function doGet(e) {
const userEmail = Session.getActiveUser().getEmail();
var htmlOutput = HtmlService.createTemplateFromFile('Form');
htmlOutput.email = userEmail;
return htmlOutput.evaluate();
}
And here is the HTML file:
!DOCTYPE html>
<html>
<head>
<meta charset="UTF-16">
<title>Google Sheet Form</title>
<style>
form {...
</style>
<script>
function getLocation() {
// Check if the browser supports geolocation
if (navigator.geolocation) {
// Get the current position of the user
navigator.geolocation.getCurrentPosition(showPosition);
} else {
alert("Geolocation is not supported by this browser.");
}
}
function showPosition(position) {
// Get the latitude and longitude values from the geolocation
data
var latitude = position.coords.latitude;
var longitude = position.coords.longitude;
// Populate the input field with the latitude and longitude
values
document.getElementById("Latitude").value = latitude;
document.getElementById("Longitude").value = longitude;
}
// Function to show the success message
function showSuccessMessage() {
const successMessage =
document.getElementById('successMessage');
successMessage.style.display = 'block';
}
const responseText = '{"result": "success"}'; // Replace this
with the actual response
const response = JSON.parse(responseText);
if (response.result === 'success') {
// Display the success message to the user
showSuccessMessage();
}
</script>
</head>
<body>
<form
action="..." method="post">
<span>Logged In: <?= email ?></span>
<p> <input size="20" name="Email" type="email"
placeholder="Email" required value="<?= email ?>" readonly> </p>
<p> <input size="20" type="text" id="Latitude" name="Latitude"
placeholder="Latitude"readonly> </p>
<p> <input size="20" type="text" id="Longitude" name="Longitude"
placeholder="Longitude"readonly> </p>
<p> <button size="20" type="button" onclick="getLocation()">Get
Location</button> </p>
<p> <button size="20" type="submit">Sign In/Sign Out</button>
</p>
<!-- Add a div to display the success message -->
<div id="successMessage" style="display: none;">Form submitted
successfully!</div>
<!-- Add a div to display the error message -->
<div id="errorMessage" style="display: none;">An error
occurred. Please try again.</div>
</form>
</body>
</html>
From your showing Google Apps Script and HTML, I guess that your HTML is included in the same Google Apps Script project of your Google Apps Script. If my understanding is correct, how about sending the values of the HTML form using google.script.run? When this is reflected in your script, how about the following modification?
Code.gsIn this modification, the function sample2 is added.
const sheetName = 'Sheet1';
const scriptProp = PropertiesService.getScriptProperties();
function initialSetup() {
const activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
scriptProp.setProperty('key', activeSpreadsheet.getId());
}
function doPost(e) {
const lock = LockService.getScriptLock();
lock.tryLock(10000);
try {
const doc = SpreadsheetApp.openById(scriptProp.getProperty('key'));
const sheet = doc.getSheetByName(sheetName);
const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
const nextRow = sheet.getLastRow() + 1;
const newRow = headers.map(function (header) {
return header === 'Date' ? new Date() : e.parameter[header];
})
sheet.getRange(nextRow, 1, 1, newRow.length).setValues([newRow]);
return ContentService
.createTextOutput(JSON.stringify({ 'result': 'success', 'row': nextRow }))
.setMimeType(ContentService.MimeType.JSON);
} catch (e) {
return ContentService
.createTextOutput(JSON.stringify({ 'result': 'error', 'error': e }))
.setMimeType(ContentService.MimeType.JSON);
} finally {
lock.releaseLock();
}
}
function doGet(e) {
const userEmail = Session.getActiveUser().getEmail();
var htmlOutput = HtmlService.createTemplateFromFile('Form');
htmlOutput.email = userEmail;
return htmlOutput.evaluate();
}
// I added this function.
const sample2 = e => doPost({ parameter: e });
Form.htmlIn this modification, the function sample1 is added. And, the HTML form is modified for sending the values with google.script.run.
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-16">
<title>Google Sheet Form</title>
<style>
</style>
<script>
function getLocation() {
if (navigator.geolocation) {
navigator.geolocation.getCurrentPosition(showPosition);
} else {
alert("Geolocation is not supported by this browser.");
}
}
function showPosition(position) {
var latitude = position.coords.latitude;
var longitude = position.coords.longitude;
document.getElementById("Latitude").value = latitude;
document.getElementById("Longitude").value = longitude;
}
function showSuccessMessage() {
const successMessage = document.getElementById('successMessage');
successMessage.style.display = 'block';
}
// I added this function.
function sample1(e) {
console.log(e)
google.script.run.withFailureHandler(err => console.log(err))
.withSuccessHandler(f => {
const responseText = '{"result": "success"}';
const response = JSON.parse(responseText);
if (response.result === 'success') {
showSuccessMessage();
}
}).sample2(e);
}
</script>
</head>
<body>
<form>
<span>Logged In: <?= email ?></span>
<p> <input size="20" name="Email" type="email" placeholder="Email" required value="<?= email ?>" readonly> </p>
<p> <input size="20" type="text" id="Latitude" name="Latitude" placeholder="Latitude"readonly> </p>
<p> <input size="20" type="text" id="Longitude" name="Longitude" placeholder="Longitude"readonly> </p>
<p> <button size="20" type="button" onclick="getLocation()">Get Location</button> </p>
<p> <button size="20" type="submit" onclick="sample1(this.parentNode.parentNode); return false;">Sign In/Sign Out</button>
</p>
<!-- Add a div to display the success message -->
<div id="successMessage" style="display: none;">Form submitted successfully!</div>
<!-- Add a div to display the error message -->
<div id="errorMessage" style="display: none;">An error occurred. Please try again.</div>
</form>
</body>
</html>
When you modified the Google Apps Script of Web Apps, please modify the deployment as a new version. By this, the modified script is reflected in Web Apps. Please be careful about this.
You can see the detail of this in my report "Redeploying Web Apps without Changing URL of Web Apps for new IDE (Author: me)".
About your following reply,
I added your additions to the code and I deployed the Web App as a new Web App but it still returns Forbidden Error 403 after submitting successfully. Here's a link to the file if you would like to have a look docs.google.com/spreadsheets/d/…
When I saw your provided Spreadsheet, I noticed that you are not correctly using my proposed script. I think that the reason for your new issue is due to this.
Please correctly use my proposed modified script.
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-16">
<title>Google Sheet Form</title>
<style>
form {
display: flex;
flex-direction: column;
align-items: center;
gap: 1rem;
}
label {
font-weight: bold;
}
input[type="text"],
input[type="email"] {
padding: 0.5rem;
border: 2px solid gray;
border-radius: 0.5rem;
font-size: 2rem;
width: 100%;
}
/* Adjust Get Location button size */
button[type="button"] {
padding: 0.5rem;
border: none;
border-radius: 0.5rem;
font-size: 5rem; /* Change this value to adjust the font size */
background-color: green;
color: white;
cursor: pointer;
}
button[type="submit"] {
padding: 0.5rem;
border: none;
border-radius: 0.5rem;
font-size: 5rem;
background-color: blue;
color: white;
cursor: pointer;
}
button[type="submit"]:hover {
background-color: darkblue;
}
</style>
<script>
function getLocation() {
// Check if the browser supports geolocation
if (navigator.geolocation) {
// Get the current position of the user
navigator.geolocation.getCurrentPosition(showPosition);
} else {
alert("Geolocation is not supported by this browser.");
}
}
function showPosition(position) {
// Get the latitude and longitude values from the geolocation data
var latitude = position.coords.latitude;
var longitude = position.coords.longitude;
// Populate the input field with the latitude and longitude values
document.getElementById("Latitude").value = latitude;
document.getElementById("Longitude").value = longitude;
}
// Function to show the success message
function showSuccessMessage() {
const successMessage = document.getElementById('successMessage');
successMessage.style.display = 'block';
}
// Assuming you have an XMLHttpRequest or fetch request to submit the form
// After successful form submission, you can handle the response like this:
// Replace `responseText` with the actual response you receive from the server
// The response should be a JSON string containing the 'result' property ('success' or 'error')
const responseText = '{"result": "success"}'; // Replace this with the actual response
const response = JSON.parse(responseText);
if (response.result === 'success') {
// Display the success message to the user
showSuccessMessage();
}
// I added this function.
function sample1(e) {
console.log(e)
google.script.run.withFailureHandler(err => console.log(err))
.withSuccessHandler(f => {
const responseText = '{"result": "success"}';
const response = JSON.parse(responseText);
if (response.result === 'success') {
showSuccessMessage();
}
}).sample2(e);
}
</script>
</head>
<body>
<form action="https://script.google.com/macros/s/AKfycbzRro7Xv_P3k7alhHpk-pTRLHJm4E7FYQ7LtaD7iFr884NS6eqPIpblf7OuBqzjtvZH/exec" method="post">
<span>Logged In: <?= email ?></span>
<p> <input size="20" name="Email" type="email" placeholder="Email" required value="<?= email ?>" readonly> </p>
<p> <input size="20" type="text" id="Latitude" name="Latitude" placeholder="Latitude"readonly> </p>
<p> <input size="20" type="text" id="Longitude" name="Longitude" placeholder="Longitude"readonly> </p>
<p> <button size="20" type="button" onclick="getLocation()">Get Location</button> </p>
<p> <button size="20" type="submit">Sign In/Sign Out</button> </p>
<!-- Add a div to display the success message -->
<div id="successMessage" style="display: none;">Form submitted successfully!</div>
<!-- Add a div to display the error message -->
<div id="errorMessage" style="display: none;">An error occurred. Please try again.</div>
</form>
</body>
</html>
Unfortunately, you don't modify both <script>,,,</script> and <body>,,,</body> using my proposed script. This is the reason for your current issue. Please modify this as follows.
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-16">
<title>Google Sheet Form</title>
<style>
form {
display: flex;
flex-direction: column;
align-items: center;
gap: 1rem;
}
label {
font-weight: bold;
}
input[type="text"],
input[type="email"] {
padding: 0.5rem;
border: 2px solid gray;
border-radius: 0.5rem;
font-size: 2rem;
width: 100%;
}
/* Adjust Get Location button size */
button[type="button"] {
padding: 0.5rem;
border: none;
border-radius: 0.5rem;
font-size: 5rem; /* Change this value to adjust the font size */
background-color: green;
color: white;
cursor: pointer;
}
button[type="submit"] {
padding: 0.5rem;
border: none;
border-radius: 0.5rem;
font-size: 5rem;
background-color: blue;
color: white;
cursor: pointer;
}
button[type="submit"]:hover {
background-color: darkblue;
}
</style>
<script>
function getLocation() {
if (navigator.geolocation) {
navigator.geolocation.getCurrentPosition(showPosition);
} else {
alert("Geolocation is not supported by this browser.");
}
}
function showPosition(position) {
var latitude = position.coords.latitude;
var longitude = position.coords.longitude;
document.getElementById("Latitude").value = latitude;
document.getElementById("Longitude").value = longitude;
}
function showSuccessMessage() {
const successMessage = document.getElementById('successMessage');
successMessage.style.display = 'block';
}
// I added this function.
function sample1(e) {
google.script.run.withFailureHandler(err => console.log(err))
.withSuccessHandler(f => {
const responseText = '{"result": "success"}';
const response = JSON.parse(responseText);
if (response.result === 'success') {
showSuccessMessage();
}
}).sample2(e);
}
</script>
</head>
<body>
<form>
<span>Logged In: <?= email ?></span>
<p> <input size="20" name="Email" type="email" placeholder="Email" required value="<?= email ?>" readonly> </p>
<p> <input size="20" type="text" id="Latitude" name="Latitude" placeholder="Latitude"readonly> </p>
<p> <input size="20" type="text" id="Longitude" name="Longitude" placeholder="Longitude"readonly> </p>
<p> <button size="20" type="button" onclick="getLocation()">Get Location</button> </p>
<p> <button size="20" type="submit" onclick="sample1(this.parentNode.parentNode); return false;">Sign In/Sign Out</button>
</p>
<!-- Add a div to display the success message -->
<div id="successMessage" style="display: none;">Form submitted successfully!</div>
<!-- Add a div to display the error message -->
<div id="errorMessage" style="display: none;">An error occurred. Please try again.</div>
</form>
</body>
</html>