Optimize code to put "Default" values taken from a spreadsheet into HTML form fields
I have put a form in a Google Sheets sidebar with HTML. The form loads quickly when I select the form from my custom menu. This form is used once at the beginning of the day by one user. However, there are instances when someone makes a mistake on the form and decides to update it later in the day. For such instances, I have created an identical form that grabs the last value from the spreadsheet in each column that corresponds to the form field. The form opens with the values already displayed in the form field. That way the editor can see the last inputs and just fix any numbers that need fixing.
In my form, there are approximately 30 form fields that get pre-filled. The problem is that when the user selects "Update Form", it can take up to 30 seconds for the form to retrieve the values and the form to display with values in the fields. Is there a way to optimize this script so that the values are grabbed all at once and passed to the proper field in the form.
Here is a sample of my Google Apps Script:
function todayLValue(){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var scheduleSheet = ss.getSheetByName("Scheduled");
var lValue = scheduleSheet.getRange(scheduleSheet.getLastRow(),2).getValues();
return lValue;
}
function todayFTValue(){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var scheduleSheet = ss.getSheetByName("Scheduled");
var fTValue = scheduleSheet.getRange(scheduleSheet.getLastRow(),3).getValue();
return fTValue;
}
function todayBSValue(){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var scheduleSheet = ss.getSheetByName("Scheduled");
var bSValue = scheduleSheet.getRange(scheduleSheet.getLastRow(),4).getValue();
return bSValue;
}
function todaySPValue(){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var scheduleSheet = ss.getSheetByName("Scheduled");
var sPValue = scheduleSheet.getRange(scheduleSheet.getLastRow(),5).getValue();
return sPValue;
}
function todayCMValue(){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var scheduleSheet = ss.getSheetByName("Scheduled");
var cMValue = scheduleSheet.getRange(scheduleSheet.getLastRow(),6).getValue();
return cMValue;
}
function todayITValue(){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var scheduleSheet = ss.getSheetByName("Scheduled");
var iTValue = scheduleSheet.getRange(scheduleSheet.getLastRow(),7).getValue();
return iTValue;
}
function todayStdTValue(){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var scheduleSheet = ss.getSheetByName("Scheduled");
var stdTValue = scheduleSheet.getRange(scheduleSheet.getLastRow(),8).getValue();
return stdTValue;
}
function todaySCValue(){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var scheduleSheet = ss.getSheetByName("Scheduled");
var sCValue = scheduleSheet.getRange(scheduleSheet.getLastRow(),9).getValue();
return sCValue;
}
And here is a sample of my HTML file:
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<link
rel="stylesheet"
href="https://ssl.gstatic.com/docs/script/css/add-ons1.css"
/>
<style>
.container {
margin: 5px 5px 5px 5px;
}
</style>
</head>
<body>
<div class="container">
<form id ="inputForm " onkeydown="return event.key != 'Enter'"/>
<h1 style="color:#cc0000;text-align:center;font-weight:bold;">Scheduled for Today</h1>
<label for="L_Scheduled">Label1</label>
<input type="text" name="L_Scheduled" id="L_Scheduled" style="width: 50px; text-align: center; float: right" value=<?=todayLValue()?> />
<br />
<br />
<label for="F_T_Scheduled">Label2</label>
<input type="text" name="F_T_Scheduled" id="F_T_Scheduled" style="width: 50px; text-align: center; float: right" value=<?=todayFTValue()?> />
<br />
<br />
<label for="B_S_Scheduled">Label3</label>
<input type="text" name="B_S_Scheduled" id="B_S_Scheduled" style="width: 50px; text-align: center; float: right" value=<?=todayBSValue()?> />
<br />
<br />
<label for="S_P_Scheduled">Label4</label>
<input type="text" name="S_P_Scheduled" id="S_P_Scheduled" style="width: 50px; text-align: center; float: right" value=<?=todaySPValue()?> />
<br />
<br />
<label for="C_M_Scheduled">Label5</label>
<input type="text" name="C_M_Scheduled" id="C_M_Scheduled" style="width: 50px; text-align: center; float: right" value=<?=todayCMValue()?> />
<br />
<br />
<label for="I_T_Scheduled">Label6</label>
<input type="text" name="I_T_Scheduled" id="I_T_Scheduled" style="width: 50px; text-align: center; float: right" value=<?=todayITValue()?> />
<br />
<br />
<label for="STD_T_Scheduled">Label7</label>
<input type="text" name="STD_T_Scheduled" id="STD_T_Scheduled" style="width: 50px; text-align: center; float: right" value=<?=todayStdTValue()?> />
<br />
<br />
<label for="S_C_Scheduled">Label8</label>
<input type="text" name="S_C_Scheduled" id="S_C_Scheduled" style="width: 50px; text-align: center; float: right" value=<?=todaySCValue()?> />
<br />
<br />
In my Apps Script, I use getValue() for each cell I want to get a value for. (I assume this makes a separate call for each time this function is used.) Then in my HTML file, I return the value desired by assigning each field the respective value called in the script.
I am not sure how to make one call getValues() and then get the correct value into the correct field in the form.
Answers
To optimize your script and reduce the number of calls to the spreadsheet, you can retrieve all the required values in a single call using getValues(), and then pass these values to your HTML form fields. Here's how you can modify your code:
Google Apps Script:
function getTodayValues() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var scheduleSheet = ss.getSheetByName("Scheduled");
var lastRowData = scheduleSheet.getRange(scheduleSheet.getLastRow(), 2, 1, 8).getValues()[0];
// Return an array containing the values of each column
return lastRowData;
}
HTML:
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css">
<style>
.container {
margin: 5px 5px 5px 5px;
}
</style>
</head>
<body>
<div class="container">
<form id="inputForm" onkeydown="return event.key != 'Enter'"/>
<h1 style="color:#cc0000;text-align:center;font-weight:bold;">Scheduled for Today</h1>
<label for="L_Scheduled">Label1</label>
<input type="text" name="L_Scheduled" id="L_Scheduled" style="width: 50px; text-align: center; float: right" />
<br /><br />
<label for="F_T_Scheduled">Label2</label>
<input type="text" name="F_T_Scheduled" id="F_T_Scheduled" style="width: 50px; text-align: center; float: right" />
<br /><br />
<!-- Repeat for other input fields -->
</form>
</div>
<script>
// Function to set values to form fields
function setFormValues(values) {
document.getElementById("L_Scheduled").value = values[0];
document.getElementById("F_T_Scheduled").value = values[1];
// Set values for other fields
}
// Call Google Apps Script function to get values and set them to form fields
google.script.run.withSuccessHandler(setFormValues).getTodayValues();
</script>
</body>
</html>
In this setup, the getTodayValues() function in Google Apps Script retrieves all the values from the last row in the specified columns (columns B to I), stores them in an array, and returns them. Then, in the HTML file, the setFormValues() JavaScript function receives this array of values as a parameter and sets them to the respective form fields using the value attribute. Finally, the google.script.run.withSuccessHandler() method calls the getTodayValues() function and passes the retrieved values to the setFormValues() function once the values are fetched successfully. This approach minimizes the number of calls to the spreadsheet and improves the loading time of your form.
