Home > Mobile >  Prefill Google Sheet sidebar with Apps Script Properties Service values
Prefill Google Sheet sidebar with Apps Script Properties Service values

Time：02-11

I have written a code to show the form in a sidebar in google sheets and store the entered values such as full name, email address, mobile no. etc, in Apps Script Properties but the only issue I am facing is when I close the form and reopen it, the form does not show the last stored values as well as confirmation message on submit.

I want the form to show the last stored value from the Apps Script Properties and confirmation message on submit.

Please help.

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'),
    'weekday': scriptProperties.getProperty('weekday'),
    'triggerTime': scriptProperties.getProperty('triggerTime')
  };

  var popFullName = scriptProperties.getProperty('fullName');
  console.log({ html })
  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);
};

function testProp() {
  var popFullName = PropertiesService.getScriptProperties().getProperty('fullName');
  var popEmailAddress = PropertiesService.getScriptProperties().getProperty('emailAddress');
  var popMobileNumber = PropertiesService.getScriptProperties().getProperty('mobileNumber');
  var popcity = PropertiesService.getScriptProperties().getProperty('city');
  var popsetday = PropertiesService.getScriptProperties().getProperty('weekday');
  var popsettriggertime = PropertiesService.getScriptProperties().getProperty('triggerTime');
  console.log({ popFullName })
  console.log({ popEmailAddress })
  console.log({ popMobileNumber })
  console.log({ popcity })
  console.log({ popsetday })
  console.log({ popsettriggertime })
};

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">
  <style>
    .form-row {
      margin-bottom: 15px;
    }
  </style>
</head>

<body>
  <div >

    <div >
      <div >
        <input id="fullName" type="text" >
        <label  for="fullName">Full Name</label>
      </div>
    </div>

    <div >
      <div >
        <input id="emailAddress" type="text" >
        <label for="emailAddress">Email Address</label>
      </div>
    </div>

    <div >
      <div >
        <input id="mobileNumber" type="number" >
        <label for="mobileNumber">Mobile Number</label>
      </div>
    </div>

    <div >
      <div >
        <input id="city" type="text" >
        <label for="city">City</label>
      </div>
    </div>

    <div >
      <div >
        <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 >
      <div >
        <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 >
      <button id="btn"  onclick="setValueToProperties()" type="submit" name="action">Submit</button>
    </div>

    <div >
      <button  onClick="google.script.host.close()">Close</button>
    </div>

  </div>

  <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.stringify(data);?>
  </script>

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

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

</body>

</html>

CodePudding user response：

I found some issues in your code:

  1. Missing }); for $(document).ready(function(){
  2. $(document).ready(function(){ and other Jquery won't work without <script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>
  3. In <?!=JSON.stringify(data);?> you are converting the value of data to string instead of parsing it to JSON.
  4. The correct way to access array in Jquery is $.each(function(index, value){ and you should use only the callback value instead of keys[key]
  5. The structure of var data is in Object, data[keys[key]] wont work, it should be data[key]

Try this instead:

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

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 >
      <div >
        <div >
          <input id="fullName" type="text"  />
          <label  for="fullName">Full Name</label>
        </div>
      </div>

      <div >
        <div >
          <input id="emailAddress" type="text"  />
          <label for="emailAddress">Email Address</label>
        </div>
      </div>

      <div >
        <div >
          <input id="mobileNumber" type="number"  />
          <label for="mobileNumber">Mobile Number</label>
        </div>
      </div>

      <div >
        <div >
          <input id="city" type="text"  />
          <label for="city">City</label>
        </div>
      </div>

      <div >
        <div >
          <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 >
        <div >
          <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 >
        <button id="btn"  onclick="setValueToProperties()" type="submit" name="action">Submit</button>
      </div>

      <div >
        <button  onClick="google.script.host.close()">Close</button>
      </div>
    </div>

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


    <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 src="https://cdnjs.cloudflare.com/ajax/libs/materialize/1.0.0/js/materialize.min.js"></script>
  </body>
</html>

Output:

The values are from my previous form submit

enter image description here

Reference:

CodePudding user response：

It is also possible to prefill the form without jQuery as folllows

in gs

function showSidebar() {
  var userName = Session.getActiveUser().getEmail();
  const html = HtmlService.createTemplateFromFile('index');
  var evaluatHTML = html.evaluate().setTitle('Sidebar')
  SpreadsheetApp.getUi().showSidebar(evaluatHTML)
};

function myProperties(){
  var scriptProperties = PropertiesService.getScriptProperties();
  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')
  };
  Logger.log(JSON.stringify(data))
  return JSON.stringify(data)
}

the complete html could be

<!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" />


    <style>
      .form-row {
        margin-bottom: 15px;
      }
    </style>
  </head>
  
  <body>
    <script>
      <? 
        var data = JSON.parse(myProperties());
        var days = ['MONDAY','TUESDAY','WEDNESDAY','THURSDAY','FRIDAY','SATURDAY','SUNDAY']
      ?>
    </script>
    <div >
      <div >
        <div >
          <input id="fullName" type="text"  value="<?= data.fullName ?>" />
          <label  for="fullName">Full Name</label>
        </div>
      </div>

      <div >
        <div >
          <input id="emailAddress" type="text"  value="<?= data.emailAddress ?>" />
          <label for="emailAddress">Email Address</label>
        </div>
      </div>

      <div >
        <div >
          <input id="mobileNumber" type="number"  value="<?= data.mobileNumber ?>" />
          <label for="mobileNumber">Mobile Number</label>
        </div>
      </div>

      <div >
        <div >
          <input id="city" type="text"   value="<?= data.city ?>" />
          <label for="city">City</label>
        </div>
      </div>

      <div >
        <div >
          <select id="day">
            <option value="" disabled selected >Choose ...</option>
              <? for (var i = 0; i < days.length; i  ) { ?>
                <option value="<?=days[i]?>" <?= ((days[i]==data.day && data.day!='')?'selected':'') ?> ><?=days[i]?></option>
              <? } ?>
          </select>
          <label>Select Day</label>
        </div>
      </div>

      <div >
        <div >
          <select id="selectTime">
            <option value="" disabled selected >Choose ...</option>
              <? for (var i = 0; i < 24; i  ) { ?>
                <option value="<?=i?>" <?= ((i.toString()==data.selectTime && data.selectTime!='')?'selected':'') ?> ><?=i?></option>
              <? } ?>
          </select>
          <label>Select Time</label>
        </div>
      </div>

      <div >
        <button id="btn"  onclick="setValueToProperties()" type="submit" name="action">Submit</button>
      </div>

      <div >
        <button  onClick="google.script.host.close()">Close</button>
      </div>
    </div>

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

    <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 src="https://cdnjs.cloudflare.com/ajax/libs/materialize/1.0.0/js/materialize.min.js"></script>

  </body>
</html>

Page link：https//www.codepudding.com/Mobile/296086.html

Prev:Dynamic sheet name change bases on a range
Next:Fill Down Formula while ignoring empty cells- Google Scripts
  • Related

About Us:  Contact Us      Terms of Service       Privacy Policy

Copyright © 2010-2023，Powered By CodePudding