I'm working on an interface on Google Sheet with JS & Google App Script.
The purpose is to click on a button, fill a html form (with an inputbox, a dropdown and a dropdown with multiple choices) which appears at the side, add the data in the sheet and write it in an alert box.
I began with this code which works perfectly.
AddFood.gs
// File used
var ss = SpreadsheetApp.getActiveSpreadsheet();
// Sheet used
var sheet = ss.getActiveSheet();
/*
* Main function. Associated to the button
*/
function addFood() {
showFormInSideBarAddFood();
}
/*
* Show a form in a side bar for adding food
*/
function showFormInSideBarAddFood() {
var form = HtmlService.createTemplateFromFile('IndexAddFood').evaluate().setTitle("Add Food");
SpreadsheetApp.getUi().showSidebar(form);
}
function processFormAddFood(formObject) {
sheet.appendRow([
formObject.veggie
]);
}
function include(filename) {
return HtmlService.createHtmlOutputFromFile(filename).getContent();
}
FormAddFood.html
<!DOCTYPE html>
<html>
<head>
<base target="_top">
</head>
<body>
<!-- form -->
<form id="addFoodForm" onsubmit="handleFormSubmitAddFood(this)">
<label for="veggie">Veggie</label><br/>
<input type="text" id="veggie" name="veggie"><br/><br/>
<button type="submit">Submit</button>
</form>
</body>
</html>
JavaScriptAddFood.html
<!-- JavaScriptAddFood.html -->
<script>
/*
* The window will not close when the form is summited
*/
function preventFormSubmitAddFood() {
var formsAddFood = document.querySelectorAll('FormAddFood');
for (var i; i < formsAddFood.length; i++) {
formsAddFood[i].addEventListener('submit', function(event) {
event.preventDefault();
});
}
}
// The page is not refreshed when the button submit is pressed
window.addEventListener('load', preventFormSubmit);
/*
* Calls the form
*/
function handleFormSubmitAddFood(formObject) {
google.script.run.processFormAddFood(formObject);
document.getElementById("addFoodForm").reset();
}
</script>
IndexAddFood.html
<!-- IndexAddFood.html -->
<!-- Links the JavaScript file and the HTML forms -->
<!doctype html>
<html>
<head>
<meta charset="utf-8">
<title>Form Add Fruit</title>
<?!= include('JavaScriptAddFood'); ?>
</head>
<body>
<?!= include('FormAddFood'); ?>
</body>
</html>
At this point, I can add the inputbox value in the sheet. (Don't forget to assign the "addFood" function to a button for tests)
After that, I spent some hours trying to resolve these two issues, following different tutorials :
- Add in the same row the fruit and cake dropdown values (Get the dropdown values and add them to the row)
- Get the values and display them in an alert box (Get the html value for displaying it in an alert)
Here's my modified code (which doesn't work):
AddFood.gs
// File used
var ss = SpreadsheetApp.getActiveSpreadsheet();
// Sheet used
var sheet = ss.getActiveSheet();
/*
* Main function. Associated to the button
*/
function addFood() {
showFormInSideBarAddFood();
}
/*
* Show a form in a side bar for adding food
*/
function showFormInSideBarAddFood() {
var form = HtmlService.createTemplateFromFile('IndexAddFood').evaluate().setTitle("Add Food");
SpreadsheetApp.getUi().showSidebar(form);
}
function processFormAddFood(formObject) {
sheet.appendRow([
formObject.veggie,
// Add the dropdown values
selectedFruit,
selectedCake
]);
SpreadsheetApp.getUi().alert(formObject.veggie + ' ' + selectedFruit + ' ' + selectedCake + "added");
}
function include(filename) {
return HtmlService.createHtmlOutputFromFile(filename).getContent();
}
// Get the value for the alert
function doGet() {
return HtmlService.createHtmlOutputFromFile('FormFood');
}
function getSelectDatas(form) {
var nameBox = form.fruit;
SpreadSheetApp.getUi().alert(nameBox);
}
FormAddFood
<!DOCTYPE html>
<html>
<head>
<base target="_top">
</head>
<body>
<!-- form -->
<form id="addFoodForm" onsubmit="handleFormSubmitAddFood(this)">
<label for="veggie">Veggie</label><br/>
<input type="text" id="veggie" name="veggie"><br/><br/>
<label for="fruit">Fruit</label><br/>
<select name="fruit" id="fruit" value="" onChange="document.getElementById('addFoodForm').submit()">
<option value="Apple"> Apple </option><br/>
<option value="Pear"> Pear </option><br/>
<option value="Banana"> Banana </option><br/>
</select><br/><br/>
<label for="cake">Cake</label><br/>
<select multiple name="cake" id="cake" value="" onChange="document.getElementById('addFoodForm').submit()">
<option value="crumble"> Crumble </option><br/>
<option value="brownie"> Brownie </option><br/>
<option value="cheeseCake"> Cheese Cake </option><br/>
</select><br/><br/>
<button type="button" value="Submit" onClick="formSubmit()">Submit</button>
</form>
<script>
/**
* Allows to select multiple option in a select form without ctrl + click
*/
var multiSelect = {};
function init() {
var s = document.getElementsByTagName('select');
for (var i = 0; i < s.length; i++) {
if (s[i].multiple) {
var n = s[i].name;
multiSelect[n] = [];
for (var j = 0; j < s[i].options.length; j++) {
multiSelect[n][j] = s[i].options[j].selected;
}
s[i].onchange = changeMultiSelect;
}
}
}
function changeMultiSelect() {
var n = this.name;
for (var i=0; i < this.options.length; i++) {
if (this.options[i].selected) {
multiSelect[n][i] = !multiSelect[n][i];
}
this.options[i].selected = multiSelect[n][i];
}
}
window.onload = init;
</script>
</body>
</html>
JavaScriptAddFood.html
<!-- JavaScriptAddFood.html -->
<script>
/*
* The window will not close when the form is summited
*/
function preventFormSubmitAddFood() {
var formsAddFood = document.querySelectorAll('FormAddFood');
for (var i; i < formsAddFood.length; i++) {
formsAddFood[i].addEventListener('submit', function(event) {
event.preventDefault();
});
}
}
// The page is not refreshed when the button submit is pressed
window.addEventListener('load', preventFormSubmit);
/*
* Calls the form
*/
function handleFormSubmitAddFood(formObject) {
google.script.run.processFormAddFood(formObject);
document.getElementById("addFoodForm").reset();
}
function formSubmit() {
var fruitDropdown = document.getElementById("fruit");
var selectedFruit = e.value;
var cakeDropdown = document.getElementById("fruit");
var selectedCake = e.value;
google.script.run.getSelectDatas(document.forms[0]);
}
</script>
No changes in the file IndexAddFood.html
How could I do to resolve these issues ? I hope I made myself clear.
