google-apps-scriptsidebar

Sidebar script to keep user input but also send responses to specific cells on various pages


Learning google apps scripts and I'm stuck. I'm trying to combine 2 solutions I've found on other posts. The first is referenced here: Prefill Google Sheet sidebar with Apps Script Properties Service values where I'm trying to keep the last user responses in the sidebar. The second is referenced here: Google Sheets sidebar form to pass data to specific sheets and specific cells where I'm sending responses to specific cells. I've tried a variety of solutions but can't get the 2 to work together.

Code.gs
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Sidebar')
    .addItem('Show sidebar', 'showSidebar')
    .addToUi();
}

function showSidebar() {
  var userName = Session.getActiveUser().getEmail();
  var scriptProperties = PropertiesService.getScriptProperties();
  const html = HtmlService.createTemplateFromFile('index');
  html.data = {
    'fullName': scriptProperties.getProperty('fullName'),
    'emailAddress': scriptProperties.getProperty('emailAddress'),
    'mobileNumber': scriptProperties.getProperty('mobileNumber'),
    'city': scriptProperties.getProperty('city'),
    'day': scriptProperties.getProperty('weekday'),
    'selectTime': scriptProperties.getProperty('triggerTime')
  };

  var popFullName = scriptProperties.getProperty('fullName');
  var evaluatHTML = html.evaluate().setTitle('Sidebar')
  SpreadsheetApp.getUi().showSidebar(evaluatHTML)
};

function setfullName(fullName) {
  var scriptProperties = PropertiesService.getScriptProperties();
  scriptProperties.setProperty('fullName', fullName);
};

function setEmailAddress(emailAddress) {
  var scriptProperties = PropertiesService.getScriptProperties();
  scriptProperties.setProperty('emailAddress', emailAddress);
};

function setMobileNumber(mobileNumber) {
  var scriptProperties = PropertiesService.getScriptProperties();
  scriptProperties.setProperty('mobileNumber', mobileNumber);
};

function setCity(city) {
  var scriptProperties = PropertiesService.getScriptProperties();
  scriptProperties.setProperty('city', city);
};

function setday(day) {
  var scriptProperties = PropertiesService.getScriptProperties();
  scriptProperties.setProperty('weekday', day);
};

function setselectTime(selectTime) {
  var scriptProperties = PropertiesService.getScriptProperties();
  scriptProperties.setProperty('triggerTime', selectTime);
};

//This section sends responses from the form to each sheet
function appendRowFromFormSubmit(index) {
  let spread = SpreadsheetApp.getActiveSpreadsheet();
  spread.getSheetByName("Sheet1").getRange("B7").setValue(form.fullName); 
  spread.getSheetByName("Sheet2").getRange("A1").setValue(form.emailAddress); 

index.html

<!DOCTYPE html>
<html>
  <head>
    <base target="_top" />
    <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no" />
    <link href="https://fonts.googleapis.com/icon?family=Material+Icons" rel="stylesheet" />
    <link href="https://fonts.googleapis.com/icon?family=Material+Icons" rel="stylesheet" />
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/materialize/1.0.0/css/materialize.min.css" />
    <script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>
    
    <style>
      .form-row {
        margin-bottom: 15px;
      }
    </style>
  </head>
  
  <body>
    <div class="content-body">
      <div class="row">
        <div class="input-field col s12">
          <input id="fullName" type="text" class="validate" />
          <label class="active" for="fullName">Full Name</label>
        </div>
      </div>

      <div class="row">
        <div class="input-field col s12">
          <input id="emailAddress" type="text" class="validate" />
          <label for="emailAddress">Email Address</label>
        </div>
      </div>

      <div class="row">
        <div class="input-field col s12">
          <input id="mobileNumber" type="number" class="validate" />
          <label for="mobileNumber">Mobile Number</label>
        </div>
      </div>

      <div class="row">
        <div class="input-field col s12">
          <input id="city" type="text" class="validate" />
          <label for="city">City</label>
        </div>
      </div>

      <div class="row">
        <div class="input-field col s12">
          <select id="day">
            <option value="">Choose</option>
            <option value="MONDAY">MONDAY</option>
            <option value="TUESDAY">TUESDAY</option>
            <option value="WEDNESDAY">WEDNESDAY</option>
            <option value="THURSDAY">THURSDAY</option>
            <option value="FRIDAY">FRIDAY</option>
            <option value="SATURDAY">SATURDAY</option>
            <option value="SUNDAY">SUNDAY</option>
          </select>
          <label>Select Day</label>
        </div>
      </div>

      <div class="row">
        <div class="input-field col s12">
          <select id="selectTime">
            <option value="">Choose</option>
            <option value="1">1</option>
            <option value="2">2</option>
            <option value="3">3</option>
            <option value="4">4</option>
            <option value="5">5</option>
            <option value="6">6</option>
            <option value="7">7</option>
            <option value="8">8</option>
            <option value="9">9</option>
            <option value="10">10</option>
            <option value="11">11</option>
            <option value="12">12</option>
            <option value="13">13</option>
            <option value="14">14</option>
            <option value="15">15</option>
            <option value="16">16</option>
            <option value="17">17</option>
            <option value="18">18</option>
            <option value="19">19</option>
            <option value="20">20</option>
            <option value="21">21</option>
            <option value="22">22</option>
            <option value="23">23</option>
          </select>
          <label>Select Time</label>
        </div>
      </div>

      <div class="row">
        <button id="btn" class="btn waves-effect waves-light" onclick="setValueToProperties()" type="submit" name="action">Submit</button>
      </div>

      <div class="row">
        <button class="btn waves-effect waves-light" onClick="google.script.host.close()">Close</button>
      </div>
    </div>

    <input id="test1" type="text" class="validate" />


    <script>
      function setValueToProperties() {
        const fullName = document.getElementById("fullName").value;
        google.script.run.setfullName(fullName);

        const emailAddress = document.getElementById("emailAddress").value;
        google.script.run.setEmailAddress(emailAddress);

        const mobileNumber = document.getElementById("mobileNumber").value;
        google.script.run.setMobileNumber(mobileNumber);

        const city = document.getElementById("city").value;
        google.script.run.setCity(city);

        const day = document.getElementById("day").value;
        google.script.run.setday(day);

        const selectTime = document.getElementById("selectTime").value;
        google.script.run.setselectTime(selectTime);
      }
    </script>

    <script>
      document.addEventListener("DOMContentLoaded", function () {
        var elems = document.querySelectorAll("select");
        var instances = M.FormSelect.init(elems);
      });
    </script>

    <script>
      var data = JSON.parse("<?=JSON.stringify(data)?>");
    </script>

    <script>
      $(document).ready(function(){
        const keys = ["fullName", "emailAddress", "mobileNumber", "city", "day", "selectTime"];
        $.each(keys, function(index, item){
          $("#"+item).val(data[item]);       
        });
      });
    </script>

<script>
     function submitForm(index) {
       google.script.run.appendRowFromFormSubmit(document.getElementById("test1"));
     }
   </script>    

<script src="https://cdnjs.cloudflare.com/ajax/libs/materialize/1.0.0/js/materialize.min.js"></script>
  </body>
</html>

Solution

  • I believe your goal is as follows.

    When I saw your showing script, submitForm in Javascript is not used. And, I thought that PropertiesService could be used one time by including the values in an object. When these are reflected in your script, how about the following modification?

    Google Apps Script:

    Please modify your Google Apps Script as follows.

    function onOpen() {
      var ui = SpreadsheetApp.getUi();
      ui.createMenu('Sidebar').addItem('Show sidebar', 'showSidebar').addToUi();
    }
    
    function showSidebar() {
      const html = HtmlService.createTemplateFromFile('index');
      const scriptProperties = PropertiesService.getScriptProperties();
      const str = scriptProperties.getProperty("obj");
      html.data = str || "{}";
      const evaluatHTML = html.evaluate().setTitle('Sidebar');
      SpreadsheetApp.getUi().showSidebar(evaluatHTML);
    }
    
    function setObj(obj) {
      PropertiesService.getScriptProperties().setProperty('obj', JSON.stringify(obj));
      const spread = SpreadsheetApp.getActiveSpreadsheet();
      spread.getSheetByName("Sheet1").getRange("B7").setValue(obj.fullName);
      spread.getSheetByName("Sheet2").getRange("A1").setValue(obj.emailAddress);
    }
    

    HTML & Javascript:

    Please modify your HTML & Javascript as follows. I didn't modify your HTML part. I modified only Javascript.

    <!DOCTYPE html>
    <html>
      <head>
        <base target="_top" />
        <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no" />
        <link href="https://fonts.googleapis.com/icon?family=Material+Icons" rel="stylesheet" />
        <link href="https://fonts.googleapis.com/icon?family=Material+Icons" rel="stylesheet" />
        <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/materialize/1.0.0/css/materialize.min.css" />
        <script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>
        
        <style>
          .form-row {
            margin-bottom: 15px;
          }
        </style>
      </head>
      
      <body>
        <div class="content-body">
          <div class="row">
            <div class="input-field col s12">
              <input id="fullName" type="text" class="validate" />
              <label class="active" for="fullName">Full Name</label>
            </div>
          </div>
    
          <div class="row">
            <div class="input-field col s12">
              <input id="emailAddress" type="text" class="validate" />
              <label for="emailAddress">Email Address</label>
            </div>
          </div>
    
          <div class="row">
            <div class="input-field col s12">
              <input id="mobileNumber" type="number" class="validate" />
              <label for="mobileNumber">Mobile Number</label>
            </div>
          </div>
    
          <div class="row">
            <div class="input-field col s12">
              <input id="city" type="text" class="validate" />
              <label for="city">City</label>
            </div>
          </div>
    
          <div class="row">
            <div class="input-field col s12">
              <select id="day">
                <option value="">Choose</option>
                <option value="MONDAY">MONDAY</option>
                <option value="TUESDAY">TUESDAY</option>
                <option value="WEDNESDAY">WEDNESDAY</option>
                <option value="THURSDAY">THURSDAY</option>
                <option value="FRIDAY">FRIDAY</option>
                <option value="SATURDAY">SATURDAY</option>
                <option value="SUNDAY">SUNDAY</option>
              </select>
              <label>Select Day</label>
            </div>
          </div>
    
          <div class="row">
            <div class="input-field col s12">
              <select id="selectTime">
                <option value="">Choose</option>
                <option value="1">1</option>
                <option value="2">2</option>
                <option value="3">3</option>
                <option value="4">4</option>
                <option value="5">5</option>
                <option value="6">6</option>
                <option value="7">7</option>
                <option value="8">8</option>
                <option value="9">9</option>
                <option value="10">10</option>
                <option value="11">11</option>
                <option value="12">12</option>
                <option value="13">13</option>
                <option value="14">14</option>
                <option value="15">15</option>
                <option value="16">16</option>
                <option value="17">17</option>
                <option value="18">18</option>
                <option value="19">19</option>
                <option value="20">20</option>
                <option value="21">21</option>
                <option value="22">22</option>
                <option value="23">23</option>
              </select>
              <label>Select Time</label>
            </div>
          </div>
    
          <div class="row">
            <button id="btn" class="btn waves-effect waves-light" onclick="setValueToProperties()" type="submit" name="action">Submit</button>
          </div>
    
          <div class="row">
            <button class="btn waves-effect waves-light" onClick="google.script.host.close()">Close</button>
          </div>
        </div>
    
        <input id="test1" type="text" class="validate" />
    
        <script>
          const keys = ["fullName", "emailAddress", "mobileNumber", "city", "day", "selectTime"];
    
          document.addEventListener("DOMContentLoaded", function () {
            var elems = document.querySelectorAll("select");
            var instances = M.FormSelect.init(elems);
          });
    
          $(document).ready(function(){
            const data = <?!= data ?>;
            $.each(keys, function(index, item){
              $("#"+item).val(data[item]);
            });
          });
    
          function setValueToProperties() {
            const obj = keys.reduce((o, e) => (o[e] = document.getElementById(e).value, o), {});
            google.script.run.setObj(obj);
          }
        </script>
    
        <script src="https://cdnjs.cloudflare.com/ajax/libs/materialize/1.0.0/js/materialize.min.js"></script>
      </body>
    </html>