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>
In order to autofill the form page with the row the user has double clicked on, you can modify your existing code to retrieve the row values and pass them to the editForm.html page. Here are the modifications you can make: 1. Modify the fillForm() function to pass the row values to the editForm.html page as a query parameter. You can use the google.script.url.getLocation() method to retrieve the current URL and append the query parameter to it. Here's how you can modify the function:
function fillForm(values){
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Data');
sheet.getRange("k1").setValue(values[0]); // set the value in the sheet to retrieve it on the next page
var uid = sheet.getRange("k1").getValue();
var url = google.script.url.getLocation(); // get the current URL
url = url + '&rowData=' + values.join('|'); // append the row data as a query parameter
window.open(url, '_top'); // open the next page with the query parameter
}
window.open('<?=url?>?page=editForm', '_top');
<script>
window.onload = function() {
var rowData = "<?!= decodeURIComponent(e.parameter.rowData) ?>";
var rowValues = rowData.split('|');
document.getElementById("firstname").value = rowValues[0];
document.getElementById("lastname").value = rowValues[1];
document.getElementById("email").value = rowValues[2];
document.getElementById("place").value = rowValues[3];
}
</script>
This code will retrieve the rowData query parameter from the URL, split it into an array using the pipe (|) character as a separator, and set the form field values to the corresponding array values. Note that the decodeURIComponent() function is used to decode any special characters in the query parameter.