It is not possible for apps script to read the value of a cell that is still currently being edited. You might want to consider the following workarounds.
(Option 1)
Replace your Add Data button with a checkbox and use onEdit(e) simple trigger to call your function. When a checkbox is being checked/unchecked, it makes its cell active. Hence your 5th data entry can be read successfully.
Sample Code:
function onEdit(e){
var ss = e.source;
var cell = e.range
if(ss.getActiveSheet().getName() == "Form" && cell.getA1Notation() == "B9" && cell.isChecked() == true){
//Add data to form
Adddataform();
}
}
function Adddataform() {
var ActiveSheet = SpreadsheetApp.getActiveSpreadsheet();
var FormSheet = ActiveSheet.getSheetByName("Form"); //Form Sheet
var DataSheet = ActiveSheet.getSheetByName("Data"); //Data Sheet
//Input Values
var values = [[FormSheet.getRange("B4").getValue(),
FormSheet.getRange("B5").getValue(),
FormSheet.getRange("B6").getValue(),
FormSheet.getRange("B7").getValue(),
FormSheet.getRange("B8").getValue()]];
DataSheet.getRange(DataSheet.getLastRow()+1, 1, 1, 5).setValues(values);
SpreadsheetApp.getUi().alert("Data add successfull")
ResetForm ()
}
// function resetByRangeList_(FormSheet,Formcells){
// FormSheet.getRangeList(Formcells).clearContent()
// }
//............................
//Clear Form
function ResetForm() {
var ActiveSheet = SpreadsheetApp.getActiveSpreadsheet();
var FormSheet = ActiveSheet.getSheetByName("Form"); //Form Sheet
var Formcells = ["B4","B5","B6","B7","B8","B9"];
resetByRangeList_(FormSheet,Formcells);
}
function resetByRangeList_(FormSheet,Formcells){
FormSheet.getRangeList(Formcells).clearContent();
}
What it does?
- Whenever a cell is being modified in your spreadsheet, onEdit() will be called. Check if the modified cell is
Form!B9 and if the checkbox was checked.
- If the checkbox was checked, Add the data to the Data sheet by calling
Adddataform()
Note:
- Removed the undefined
saveData() inside Adddataform()
- Include Cell B9 in the
resetByRangeList to reset the checkbox
Output:
(Option 2)
You can use Custom sidebars or Custom dialogs as your form.
Sample Code:
(Code.gs)
//CREATE CUSTOM MENU
function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu("My Menu")
.addItem("Sidebar Form","showFormInSidebar")
.addItem("Modal Dialog Form","showFormInModalDialog")
.addToUi();
}
//OPEN THE FORM IN SIDEBAR
function showFormInSidebar() {
var form = HtmlService.createTemplateFromFile('form').evaluate().setTitle('Form');
SpreadsheetApp.getUi().showSidebar(form);
}
//OPEN THE FORM IN MODAL DIALOG
function showFormInModalDialog() {
var form = HtmlService.createTemplateFromFile('form').evaluate();
SpreadsheetApp.getUi().showModalDialog(form, "Form");
}
//PROCESS FORM
function processForm(formObject){
Logger.log(formObject);
var ActiveSheet = SpreadsheetApp.getActiveSpreadsheet();
var DataSheet = ActiveSheet.getSheetByName("Data");
Logger.log(formObject.invoiceNumber);
Logger.log(typeof formObject.invoiceNumber);
var values = [[formObject.fleetNumber,
formObject.date,
formObject.invoiceNumber,
formObject.jobNumber,
formObject.operation
]];
DataSheet.getRange(DataSheet.getLastRow()+1, 1, 1, 5).setValues(values);
SpreadsheetApp.getUi().alert("Data add successfull");
}
(form.html)
<!doctype html>
<html lang="en">
<head>
<!-- Required meta tags -->
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
<!-- Bootstrap CSS -->
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@4.5.3/dist/css/bootstrap.min.css" integrity="sha384-TX8t27EcRE3e/ihU7zmQxVncDAy5uIKz4rEkgIXeMed4M0jlfIDPvg6uqKI2xXr2" crossorigin="anonymous">
<title>Sample Form</title>
</head>
<body>
<!-- Option 1: jQuery and Bootstrap Bundle (includes Popper) -->
<script src="https://code.jquery.com/jquery-3.5.1.slim.min.js" integrity="sha384-DfXdz2htPH0lsSSs5nCTpuj/zy4C+OGpamoFVy38MVBnE+IbbVYUew+OrCXaRkfj" crossorigin="anonymous"></script>
<script src="https://cdn.jsdelivr.net/npm/bootstrap@4.5.3/dist/js/bootstrap.bundle.min.js" integrity="sha384-ho+j7jyWK8fNQe+A12Hb8AhRq26LrZ/JpcUGGOn+Y7RsweNrtN/tE3MoK7ZeZDyx" crossorigin="anonymous"></script>
<form id="myForm" onsubmit="handleFormSubmit(this)">
<div class="form-group">
<label for="fleetNumber">Fleet Number</label>
<select class="form-control form-control-sm" id="fleetNumber" name="fleetNumber" required>
<option value="" selected>Choose...</option>
<option value="Number1">Number 1</option>
<option value="Number1">Number 2</option>
</select>
</div>
<div class="form-group">
<label for="date">Date</label>
<input class="form-control form-control-sm" type="text" class="form-control" id="date" name="date" placeholder="mm/dd/yyyy">
</div>
<div class="form-group">
<label for="invoiceNumber">Invoice Number</label>
<input class="form-control form-control-sm" type="text" class="form-control" id="invoiceNumber" name="invoiceNumber">
</div>
<div class="form-group">
<label for="jobNumber">Job Number</label>
<input class="form-control form-control-sm" type="text" class="form-control" id="jobNumber" name="jobNumber">
</div>
<div class="form-group">
<label for="operation">Operation</label>
<input class="form-control form-control-sm" type="text" class="form-control" id="operation" name="operation">
</div>
<button type="submit" class="btn btn-primary">Submit</button>
</form>
<script>
// Prevent forms from submitting.
function preventFormSubmit() {
var forms = document.querySelectorAll('form');
for (var i = 0; i < forms.length; i++) {
forms[i].addEventListener('submit', function(event) {
event.preventDefault();
});
}
}
window.addEventListener('load', preventFormSubmit);
function handleFormSubmit(formObject) {
google.script.run.processForm(formObject);
document.getElementById("myForm").reset();
}
</script>
</body>
</html>
What it does?
- Create a custom menu which will be displayed whenever you open your Google Sheets
Create an html form, use google.script.run to allow HTML-service pages to call server-side Apps Script functions. In this example we will call processForm(formObject) using this google.script.run.processForm(formObject); syntax once the form was submitted.
Add new row data based on the parameters provided during form submission.
Output:
Additional Reference:
