In my spreadsheet script editor, I have the below codes:
Code.gs
function doGet() {
return HtmlService.createTemplateFromFile('checkForm.html')
}
function doPost1(e) {
Logger.log(JSON.stringify(e))
if (!e || !e.parameter) {
return;
}
var lock = LockService.getScriptLock();
lock.tryLock(10 * 1000);
var scriptProp = PropertiesService.getScriptProperties();
try {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var checkForm = ss.getSheetByName("checkForm");
var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
var nextRow = sheet.getLastRow() + 1;
var newRow = headers.map(function(header) {
return header === 'Timestamp' ? new Date() : e.parameter[header]
});
sheet.getRange(nextRow, 1, 1, newRow.length).setValues([newRow]);
var startTime = newRow[1];
var endTime = newRow[2];
var cal = CalendarApp.getCalendarById("ID");
var allEvents = cal.getEvents(new Date(startTime), new Date(endTime));
if (allEvents.length > 0) {
return HtmlService.createTemplateFromFile('calendarAgenda.html')
}else {
return HtmlService.createTemplateFromFile('bookingForm.html')
};
}
catch (e) {
return ContentService
.createTextOutput(JSON.stringify({ 'result': 'error', 'error': e }))
.setMimeType(ContentService.MimeType.JSON)
}
finally {
lock.releaseLock()
}
}
function doPost2(e) {
Logger.log(JSON.stringify(e))
if (!e || !e.parameter) {
return;
}
var lock = LockService.getScriptLock();
lock.tryLock(10 * 1000);
var scriptProp = PropertiesService.getScriptProperties();
try {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var bookForm = ss.getSheetByName("bookForm");
var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
var nextRow = sheet.getLastRow() + 1;
var newRow = headers.map(function(header) {
return header === 'Timestamp' ? new Date() : e.parameter[header]
});
sheet.getRange(nextRow, 1, 1, newRow.length).setValues([newRow]);
return ContentService
.createTextOutput(JSON.stringify('Successfully received. Thank you!'))
.setMimeType(ContentService.MimeType.JSON)
}
catch (e) {
return ContentService
.createTextOutput(JSON.stringify({ 'result': 'error', 'error': e }))
.setMimeType(ContentService.MimeType.JSON)
}
finally {
lock.releaseLock()
}
}
checkForm.html
<!DOCTYPE html>
<body>
<form name="class1Check" id="class1Check" action="ScriptURL" target="_self" method="POST">
Start Date & Time
<input class="w3-input w3-border" type="datetime-local" required name="Start Date & Time">
<br><br>
End Date & Time
<input class="w3-input w3-border" type="datetime-local" required name="End Date & Time">
<button type="submit" onclick="google.script.run.doPost1(this.parentNode)">Check</button>
</form>
<script>
function postData(form) {
google.script.run.withSuccessHandler(postData).doPost1(e);
}
</script>
</body>
</html>
bookingForm.html
<!DOCTYPE html>
<body>
<form name="class1Booking" id="class1Booking" action="ScriptURL" target="_self" method="POST">
<inputs> ..
<button type="submit" onclick="google.script.run.doPost1(this.parentNode)">Check</button>
</form>
<script>
function postData(form) {
google.script.run.withSuccessHandler(postData).doPost2(e);
}
</script>
</body>
</html>
The goGet function returns the "checkForm.html" page that when submitted is supposed to run the doPost1 function to send the data to tab "checkForm" in the spreadsheet and then returns the second page "bookingForm.html" that when submitted is supposed to run the doPost2 function to send the data to tab "bookForm" and then returns a certain text output
When I submit the check form, I receive the error "Script function not found: doPost" and I think I might have some issues with google.script.run that I tried to modify several times with no luck. Any help and thanks in advance
You’re not using google.script.run functions correctly, as it’s stated in the documentation you can only return values of types:
Number, Boolean, String, or null, as well as JavaScript objects and arrays that are composed of primitives, objects and arrays.
An in your case, you’re trying to return a textOutput object, which is not allowed, this class is intended for Web Apps functions (doGet(e) or doPost(e)).
In the documentation about [Client-to-Server communication] (https://developers.google.com/apps-script/guides/html/communication) it’s explained how to work with google.script.run. Here is an example applied to your case so you can understand better how it works:
checkForm.html
<!DOCTYPE html>
<body>
<form name="class1Check" id="class1Check" target="_self" method="POST">
Start Date & Time
<input class="w3-input w3-border" type="datetime-local" required name="Start Date & Time">
<br><br>
End Date & Time
<input class="w3-input w3-border" type="datetime-local" required name="End Date & Time">
<button onclick="postData(this.parentNode)">Check</button>
</form>
<script>
//It’s run when form button is clicked
function postData(form) {
//Calls doSomething function in code.gs with form parameter
google.script.run.withSuccessHandler(handlerFunction).doSomething(form);
}
//Handles the response from doSomething function
function handlerFunction(responseData) {
//Logs ‘It worked!’ in developer console
console.log(responseData);
}
</script>
</body>
</html>
code.gs
//Web App function
function doGet() {
return HtmlService.createTemplateFromFile('checkForm.html')
}
//Apps script function to receive form object and return response
function doSomething(form) {
//Do something
return ‘It worked!’;
}
Apps Script’s Web Apps are designed to be a single page application (one HTML page) and not a multi-page application, although there are different workaround examples you could guide from to achieve a multi-page behavior:
[1] Linking to another HTML page in Google Apps Script
[2] https://sites.google.com/corp/view/googlappsscript/recent-scripts/multiple-page-webapp-example