Optimize code to put "Default" values taken from a spreadsheet into HTML form fields

Codedabbler
February 23, 2024
156 views
1 vote
2 Answers

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

I would get all of it at one time and I displayed in a sidebar

function todayValues() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  const vs = sh.getRange(sh.getLastRow(),2,1,8).getValues().flat();//get data all at one time
  const ids = [...Array.from(new Array(8).keys(),k => `id${k}`)]
  let html = '<!DOCTYPE html><html><head><base target="_top"></head><body><form>';
  vs.forEach((e,i) => {
   html += `<br><input type="text" id="${ids[i]}" value="${e}">`;
  });
  html += "</form></body></html>";
  SpreadsheetApp.getUi().showSidebar(HtmlService.createHtmlOutput(html));
  Logger.log(html)
}


Execution log
2:46:15 PM  Notice  Execution started
2:46:17 PM  Info    <!DOCTYPE html><html><head><base target="_top"></head><body><form><br><input type="text" id="id0" value="0"><br><input type="text" id="id1" value="9"><br><input type="text" id="id2" value="1"><br><input type="text" id="id3" value="7"><br><input type="text" id="id4" value="19"><br><input type="text" id="id5" value="14"><br><input type="text" id="id6" value="23"><br><input type="text" id="id7" value="3"></form></body></html>
2:46:18 PM  Notice  Execution completed

SUGGESTION:

Same with Cooper’s strategy to fetch all values at once, but tried to do it with fewer modifications from your original code.

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;
      }

      input {

        width: 50px; 
        text-align: center; 
        float: right;
      }
    </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" value=" " />
        <br/>
        <br/>
        <label for="F_T_Scheduled">Label2</label>
        <input type="text" name="F_T_Scheduled" id="F_T_Scheduled" value=" " />
        <br/>
        <br/>
        <label for="B_S_Scheduled">Label3</label>
        <input type="text" name="B_S_Scheduled" id="B_S_Scheduled" value=" " />
        <br/>
        <br/>
        <label for="S_P_Scheduled">Label4</label>
        <input type="text" name="S_P_Scheduled" id="S_P_Scheduled" value=" " />
        <br/>
        <br/>
        <label for="C_M_Scheduled">Label5</label>
        <input type="text" name="C_M_Scheduled" id="C_M_Scheduled" value=" " />
        <br/>
        <br/>
        <label for="I_T_Scheduled">Label6</label>
        <input type="text" name="I_T_Scheduled" id="I_T_Scheduled" value=" " />
        <br/>
        <br/>
        <label for="STD_T_Scheduled">Label7</label>
        <input type="text" name="STD_T_Scheduled" id="STD_T_Scheduled" value=" " />
        <br/>
        <br/>
        <label for="S_C_Scheduled">Label8</label>
        <input type="text" name="S_C_Scheduled" id="S_C_Scheduled" value=" " />
        <br/>
        <br/>

    </div>

  <script>

    function allValues(val){

      var label1 = document.getElementById("L_Scheduled");
      var label2 = document.getElementById("F_T_Scheduled");
      var label3 = document.getElementById("B_S_Scheduled");
      var label4 = document.getElementById("S_P_Scheduled");
      var label5 = document.getElementById("C_M_Scheduled");
      var label6 = document.getElementById("I_T_Scheduled");
      var label7 = document.getElementById("STD_T_Scheduled");
      var label8 = document.getElementById("S_C_Scheduled");

      label1.value = val[0][0];
      label2.value = val[0][1];
      label3.value = val[0][2];
      label4.value = val[0][3];
      label5.value = val[0][4];
      label6.value = val[0][5];
      label7.value = val[0][6];
      label8.value = val[0][7];

    }

    google.script.run.withSuccessHandler(allValues).getAllValues();
  </script> 

  </body>

</html>

Code.gs

function getAllValues(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var scheduleSheet = ss.getSheetByName("Scheduled");
  var values = scheduleSheet.getRange(scheduleSheet.getLastRow(),1,1,8).getValues();
  console.log(values);
  return values;
}

function onOpen() {
  SpreadsheetApp.getUi().createMenu('Options').addItem('Display Form', 'callForm').addToUi()
}

//used to serve the HTML on the sidebar
function callForm() {

  var htmlServe = HtmlService.createHtmlOutputFromFile("Untitled");
  SpreadsheetApp.getUi().showSidebar(htmlServe);
  
}

OUTPUT:

Please signup or login to give your own answer.

Click here to cancel reply.