Search code examples
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.

    • You have a sidebar.
    • When you open a sidebar, you want to load the latest items to each input tag.
    • When the "SUBMIT" button is clicked, you want to put the values of "fullName" and "emailAddress" into cells "B7" of "Sheet1" and "A1" of "Sheet2", respectively.

    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>
    
    • When the sidebar is opened, input values to the input tags and click "SUBMIT" button, the values are put into PropertiesService, and the values of "fullName" and "emailAddress" into cells "B7" of "Sheet1" and "A1" of "Sheet2", respectively. When you reopen the sidebar, the latest values are loaded to the input tags.