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