I’m learning to use bootstraps in google script and I wanted to build a form that could change some values in a sheet.
But my script is not grabbing the data from the form when I press the submit button.
The script is supposed to grab the data from the form, compare it to other data in the sheet and depending on the result of that comparison, change some values in the sheet.
But I keep getting errors that indicate the script is not getting the data from the form.
“Error TypeError: Cannot read properties of undefined (reading ‘State’)”
This is my script
function onOpen(){
SpreadsheetApp.getUi().createMenu('Side Bar').addItem('Actions', "CustomSideBar").addToUi();
}
function CustomSideBar(){
var Html = HtmlService.createTemplateFromFile('Side Bar').evaluate();
Html.setTitle('Sheet Actions');
SpreadsheetApp.getUi().showSidebar(Html);
}
function doGet() {
var SS = SpreadsheetApp.getActive();
var DT = SS.getSheetByName('Data Table');
var DTLR = DT.getLastRow();
var Mails = DT.getRange(5, 3, DTLR - 4, 1).getDisplayValues().flat();
var Options = [...new Set(Mails)];
Options.unshift("")
var htmlOutput = HtmlService.createHtmlOutputFromFile('Side Bar')
.setTitle('Mails')
.setWidth(300);
return htmlOutput;
}
function getOptions() {
var SS = SpreadsheetApp.getActive();
var DT = SS.getSheetByName('Data Table');
var DTLR = DT.getLastRow();
var Mails = DT.getRange(5, 3, DTLR - 4, 1).getDisplayValues().flat();
var Options = [...new Set(Mails)];
Options.unshift("")
return Options;
}
function doGet2() {
var SS = SpreadsheetApp.getActive();
var DT = SS.getSheetByName('Data Table');
var DTLR = DT.getLastRow();
var Date = DT.getRange(5, 8, DTLR - 4, 1).getDisplayValues().flat();
var Options2 = [...new Set(Date)];
Options2.unshift("")
var htmlOutput = HtmlService.createHtmlOutputFromFile('Side Bar')
.setTitle('Date')
.setWidth(300);
return htmlOutput;
}
function getOptions2() {
var SS = SpreadsheetApp.getActive();
var DT = SS.getSheetByName('Data Table');
var DTLR = DT.getLastRow();
var Date = DT.getRange(5, 8, DTLR - 4, 1).getDisplayValues().flat();
var Options2 = [...new Set(Date)];
Options2.unshift("")
return Options2;
}
function ChangeState(Data){
var SS = SpreadsheetApp.getActive();
var DT = SS.getSheetByName('Data Table');
var DTLR = DT.getLastRow();
var Mails = DT.getRange(5, 3, DTLR - 4, 1).getDisplayValues().flat();
var Date = DT.getRange(5, 8, DTLR - 4, 1).getDisplayValues().flat();
Logger.log(Data.State)
for (I = 0; I<Mails.length; I++){
if (Data.State != "" && Data.Reason != "" && Data.Mail == Mails[I] && Data.Date == Date[I]){
var StateRange = DT.getRange(I+5, 15);
StateRange.setValue(Data.State);
var ReasonRange = DT.getRange(I+5, 16);
ReasonRange.setValue(Data.Reason);
}
}
}
and this my html
<!DOCTYPE html>
<html>
<head>
<base target="_top">
</head>
<body>
<form>
<fieldset>
<legend class='Review Requests'>Review Requests</legend>
<div> <label for="Mails">Select Agent's Mail:</label>
<select id="Mails" name="Mails">
<? var options = getOptions(); ?>
<? for (var i = 0; i < options.length; i++) { ?>
<option value="<?= options[i] ?>"><?= options[i] ?></option>
<? } ?>
</select>
</div><br>
<div><label for="Date">Select Date:</label>
<select id="Date" name="Date">
<? var options = getOptions2(); ?>
<? for (var i = 0; i < options.length; i++) { ?>
<option value="<?= options[i] ?>"><?= options[i] ?></option>
<? } ?>
</select>
</div><br>
<div><label for="State">Select State:</label>
<select name="State" id="State">
<option value=""></option>
<option value="APPROVED">APPROVED</option>
<option value="DENIED">DENIED</option>
<option value="PENDING">PENDING</option>
</select>
</div><br>
<div><label for="Reason">Select State:</label>
<select name="Reason" id="Reason">
<option value=""></option>
<option value="Hours Requested">Hours Requested</option>
<option value="Hours Available">Hours Available</option>
<option value="Hours Unavailable">Hours Unavailable</option>
<option value="Country Laws">Country Laws</option>
<option value="Other">Other</option>
</select>
</div><br>
<div class="Submit"><input id="Submit" type="Submit" value="Submit"></div>
</fieldset>
</form>
<script>
MailBox = document.getElementById("Mails");
DateBox = document.getElementById("Date");
StateBox = document.getElementById("State");
ReasonBox = document.getElementById("Reason");
document.getElementById("Submit").addEventListener("click", function(event) {
event.preventDefault(); // Prevent the default form submission behavior
AddData();
});
function AddData() {
// Log values to console for debugging
console.log("Mail:", MailBox.value);
console.log("Date:", DateBox.value);
console.log("State:", StateBox.value);
console.log("Reason:", ReasonBox.value);
Data = {
Mail: MailBox.value,
Date: DateBox.value,
State: StateBox.value,
Reason: ReasonBox.value
};
// Log the entire Data object
console.log("Data:", Data);
google.script.run.ChangeState(Data);
}
</script>
<fieldset>
<legend class='Other Actions'>Other Actions</legend>
<input type="button" value="Move Requests"><br><br>
<input type="button" value="Check Data">
</fieldset>
</body>
</html>