I am very new to webapp with appscript. I have the below code. It will filter out the google sheet data with the session.user’s name only and show as a table in html page. once the user double clicks a cell it will open a new form. I want to autofill this form page with the row they have double clicked on.
My code works fine with generating the table, double clicking and opening data. but it does not get the values autofiilled
function doGet(e) {
Logger.log( Utilities.jsonStringify(e) );
if (!e.parameter.page) {
// When no specific page requested, return "home page"
return HtmlService.createTemplateFromFile('homePage').evaluate();
}
// else, use page parameter to pick an html file from the script
return HtmlService.createTemplateFromFile(e.parameter['page']).evaluate();
}
function getScriptUrl() {
var url = ScriptApp.getService().getUrl();
return url;
}
function getData() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Data');
var data = sheet.getDataRange().getValues();
var headerRow = data.shift(); // remove the header row from the data array
var filteredData = [headerRow]; // add the header row to the filtered data array
for (var i = 0; i < data.length; i++) {
var row = data[i];
if (row[6].indexOf(Session.getActiveUser().getEmail()) != -1) {
filteredData.push(row);
}
}
return filteredData;
}
function fillForm(values){
var list = getData();
var newItem = list.filter(v=> v[0] == values);
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Data');
sheet.getRange("k1").setValue(values);
var uid = sheet.getRange("k1").getValue();
console.log("gf"+ values);
return values;
}
Html code for displaying table
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<meta http-equiv="X-UA-Compatible"
content="IE=edge">
<meta name="viewport" content=
"width=device-width, initial-scale=1.0">
<base target="_top">
<script>
function addthisre(values)
{
google.script.run.addrec(values);
}
</script>
</head>
<!-- Add a link to the local Style Sheet for further control over the layout-->
<?!= HtmlService.createHtmlOutputFromFile('DisplayContentCSS').getContent(); ?>
<body>
<h1>Contents</h1>
<table border="1" cellpadding="5px" id="myTable" >
<?var tableData = getData();?>
<?for(var i = 0; i < tableData.length; i++) { ?>
<?if(i == 0) { ?>
<tr >
<?for(var j = 0; j < tableData[i].length; j++) { ?>
<th><?= tableData[i][j] ?></th>
<? } ?>
</tr>
<? } else { ?>
<tr >
<?for(var j = 0; j < tableData[i].length; j++) { ?>
<td><?= tableData[i][j] ?></td>
<? } ?>
</tr>
<? } ?>
<? } ?>
</table>
<div class="container">
<button id="btn" >
<p id="btnText" onclick="sendsheetmail()">Send Mail</p>
<div class="check-box">
<svg viewBox="0 0 50 50">
<path fill="transparent" d="M14.1 27.2l7.1 7.2 16.7-16.8" />
</svg>
</div>
</button>
</div>
<script type="text/javascript">
const btn = document.querySelector("#btn");
const btnText = document.querySelector("#btnText");
btn.onclick = () => {
btnText.innerHTML = "Success!!";
btn.classList.add("active");
};
</script>
</body>
</body>
<?var url = getScriptUrl();?>
<script>
var table = document.getElementById('myTable');
var rows = table.getElementsByTagName('tr');
for (var i = 0; i < rows.length; i++) {
rows[i].addEventListener('dblclick', function(event) {
var cells = event.currentTarget.getElementsByTagName('td');
console.log(cells);
var values = [];
for (var j = 0; j < 12; j++) {
values.push(cells[j].textContent);
window.open('<?=url?>?page=editForm','_top');
}
google.script.run.fillForm(values);
console.log(values); // Output the row values to the console
});
}
</script>
</html>
html content for new form..
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<script>
function addEditedRow()
{
var firstname = document.getElementById("firstname").value;
var lastname = document.getElementById("lastname").value;
var email = document.getElementById("email").value;
var place = document.getElementById("place").value;
google.script.run.AddRecord(firstname, lastname ,place, email);
document.getElementById("firstname").value = '';
document.getElementById("lastname").value = '';
document.getElementById("email").value = '';
document.getElementById("place").value = '';
}
</script>
</head>
<!-- Add a link to the local Style Sheet for further control over the layout-->
<?!= HtmlService.createHtmlOutputFromFile('editFormCSS').getContent(); ?>
<body>
<? var rowvalues = fillForm(); ?>
<div class="form">
<div class="title">Welcome</div>
<div class="subtitle">Subtitle Goes Here</div>
<div class="input-container ic1">
<input id="team" class="input" type="text" placeholder=" " value="<? rowvalues ?>" readonly />
<div class="cut"></div>
<label for="team" class="placeholder">Team</label>
</div>
<div class="input-container ic2">
<input id="lastname" class="input" type="text" placeholder=" " />
<div class="cut"></div>
<label for="lastname" class="placeholder">Last name</label>
</div>
<div class="input-container ic2">
<input id="email" class="input" type="text" placeholder=" " />
<div class="cut cut-short"></div>
<label for="email" class="placeholder">Email</label>
</div>
<input type="button" class="submit" value="Add" onclick="addEditedRow()" />
</div>
</body>
</html>