How to use Google Properties Service to store data and refer them for various purpose?

user_18139009 0
Viewed 87

I need help with Google Properties Service. Actually, I have created a sidebar in google sheet to store the data into the sheet and used that data to send an email and set up a trigger, but now I want to store that multiple data directly into the Properties Service and refer them to send an email (with stored data) and set up the trigger.

For reference, I am attaching my code. If there is any easy way to code then kindly suggest it.

Any help is highly appreciated.

Code.js

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

var SHEET = SpreadsheetApp.getActiveSheet();
var emailFrom = Session.getActiveUser().getEmail();
var fullName = SHEET.getRange('B1').getValue();
var emailAddress = SHEET.getRange('B2').getValue();
var mobileNumber = SHEET.getRange('B3').getValue();
var city = SHEET.getRange('B4').getValue();
var weekDay = SHEET.getRange('B5').getValue();
var triggerTime = SHEET.getRange('B6').getValue();

function showSidebar() {
  var ui = SpreadsheetApp.getUi();
  var tmp = HtmlService.createTemplateFromFile('index');
  tmp.fullNameValue = fullNameValue();
  tmp.emailAddressValue = emailAddressValue();
  tmp.mobileNumberValue = mobileNumberValue();
  tmp.cityValue = cityValue();
  tmp.dayValue = dayValue();
  tmp.timeValue = timeValue();

  var html = tmp.evaluate();
  html.setTitle('Sidebar');
  ui.showSidebar(html);
}

function include(File) {
  return HtmlService.createHtmlOutputFromFile(File).getContent();
};


function fullNameValue() { return SHEET.getRange(1, 2).getValue(); }
function setfullName(inputfullName) { return SHEET.getRange(1, 2).setValue(inputfullName); }

function emailAddressValue() { return SHEET.getRange(2, 2).getValue(); }
function setEmailAddress(inputEmailAddress) { return SHEET.getRange(2, 2).setValue(inputEmailAddress); }

function mobileNumberValue() { return SHEET.getRange(3, 2).getValue(); }
function setmobileNumber(imputmobileNumber) { return SHEET.getRange(3, 2).setValue(imputmobileNumber); }

function cityValue() { return SHEET.getRange(4, 2).getValue(); }
function setCity(inputCity) { return SHEET.getRange(4, 2).setValue(inputCity); }

function dayValue() { return SHEET.getRange(5, 2).getValue(); }
function setDay(inputDay) { return SHEET.getRange(5, 2).setValue(inputDay); }

function timeValue() { return SHEET.getRange(6, 2).getValue(); }
function setTime(inputTime) { return SHEET.getRange(6, 2).setValue(inputTime); }

function sendEmail() {
  var subject = 'Your request has been received.';
  var body = 'Dear ' + fullName + ', We have received your request. Your details are, name: ' + fullName + ', email address: ' + emailAddress + ', mobile number: ' + mobileNumber + ', city: ' + city;

  GmailApp.sendEmail(emailAddress, subject, body, {
    from: emailFrom,
    htmlBody: body
  });
};

function setTrigger() {
  ScriptApp.newTrigger('sendEmail').timeBased().everyDays(1).atHour(triggerTime).create();
  ScriptApp.newTrigger('sendEmail').timeBased().onWeekDay(ScriptApp.WeekDay.MONDAY).atHour(triggerTime).create();
};

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 class="container">



    <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="time">
<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" 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>

  <script>
    var fullNameBox = <?= fullNameValue ?>;
document.getElementById('fullName').value = fullNameBox;
document.getElementById('btn').addEventListener('click',function(){
var inputfullName = document.getElementById('fullName').value;
google.script.run.setfullName(inputfullName);
});
  </script>

  <script>
    var emailAddressBox = <?= emailAddressValue ?>;
document.getElementById('emailAddress').value = emailAddressBox;
document.getElementById('btn').addEventListener('click',function(){
var inputEmailAddress = document.getElementById('emailAddress').value;
google.script.run.setEmailAddress(inputEmailAddress);
});
  </script>

  <script>
    var mobileNumberBox = <?= mobileNumberValue ?>;
document.getElementById('mobileNumber').value = mobileNumberBox;
document.getElementById('btn').addEventListener('click',function(){
var imputmobileNumber = document.getElementById('mobileNumber').value;
google.script.run.setmobileNumber(imputmobileNumber);
});
  </script>

  <script>
    var cityBox = <?= cityValue ?>;
document.getElementById('city').value = cityBox;
document.getElementById('btn').addEventListener('click',function(){
var inputCity = document.getElementById('city').value;
google.script.run.setCity(inputCity);
});
  </script>

  <script>
    var selectDay = <?= dayValue ?>;
document.getElementById('day').value = selectDay;
document.getElementById('btn').addEventListener('click',function(){
var inputDay = document.getElementById('day').value;
google.script.run.setDay(inputDay);
});
  </script>

  <script>
    var selectTime = <?= timeValue ?>;
document.getElementById('time').value = selectTime;
document.getElementById('btn').addEventListener('click',function(){
var inputTime = document.getElementById('time').value;
google.script.run.setTime(inputTime);
});
  </script>

  <script>
    document.getElementById('btn').addEventListener('click',function(){
var setTrigger = document.getElementById('btn').value;
google.script.run.setupTrigger(setTrigger);
});

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

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

</body>

</html>
google-apps-script google-sheets javascript
1 Answers

Sample for getting properties

From

var SHEET = SpreadsheetApp.getActiveSheet();
var emailFrom = Session.getActiveUser().getEmail();
var fullName = SHEET.getRange('B1').getValue();
var emailAddress = SHEET.getRange('B2').getValue();
var mobileNumber = SHEET.getRange('B3').getValue();
var city = SHEET.getRange('B4').getValue();
var weekDay = SHEET.getRange('B5').getValue();
var triggerTime = SHEET.getRange('B6').getValue();

modify to

var scriptProperties = PropertiesService.getScriptProperties();
var emailFrom = Session.getActiveUser().getEmail();
var fullName = scriptProperties.getProperty('fullName');
var emailAddress = scriptProperties.getProperty('emailAddress');
var mobileNumber = scriptProperties.getProperty('mobileNumber');
var city = scriptProperties.getProperty('city');
var weekDay = scriptProperties.getProperty('weekDay');
var triggerTime = scriptProperties.getProperty('triggerTime');

and from

function fullNameValue() { return SHEET.getRange(1, 2).getValue(); }

to

// make sure scriptProperties is a global variable
function fullNameValue() { 
  return scriptProperties.getProperty('fullName'); 
}

Same for your other functions.

Sample for setting properties

From

function setfullName(inputfullName) { return SHEET.getRange(1, 2).setValue(inputfullName); }

modify to

//skip the first line if scriptProperties is already defined as a global vairiable
function setfullName(inputfullName) { 
  var scriptProperties = PropertiesService.getScriptProperties();
  scriptProperties.setProperty("inputfullName", inputfullName); 
}

NOTE:

  • Curently, a big part of your code (the one to get values) is located outside of a function and runs globally
  • If you try to access a script property that does not exist - it will be undefined. Make sure to out the code block accessing values into (a) function(s) that will only be called after you have set the properties frist.
  • PropertiesService converts all dat to strings. If you need to process data as numbers (e.g. mobileNumber), it's best practive to use JSON.stringify() and JSON.parse() when wroking with script properties.

Sample:

function setmobileNumber(mobileNumber) { 
  var scriptProperties = PropertiesService.getScriptProperties();
  scriptProperties.setProperty("mobileNumber", 
  JSON.stringify(mobileNumber)); 
}
function mobileNumberValue() {
  var mobileNumber = scriptProperties.getProperty('mobileNumber'); 
  //check if defined
  if(mobileNumber){
    mobileNumber = JSON.parse(mobileNumber);
    return scriptProperties.getProperty('mobileNumber'); 
   }
}
user_115997890
Related