How to show GroupListView data using AppsScript and Google sheet?
Dear partner, In this article we have described the method that helps me to show data in GroupListView data mode or categorized view based on given criteria.
First look at the sheet that I have used in this project.
In above example sheet, I have used Column E(Village), Column F (Class) and Column H (Status) as the criteria of this proect.
In this project I have used two forEach function:
►First for making the criteria value unique and for use it as group levels.
►Second for laying the data as given criteria under specific level.
►► Both forEach functions are arranging the data in GroupListView . Each group appears as level and the data of the group lays just bellow to the level.
Two design as html output I have used <ol> and <li> element.
►►Let's see the code.gs and index.html.
Please subscribe my channel if you have not subscried yet.
Make a copy of following Code.gs and paste it in your project page. arrange it as your need.
Code.gs
function doGet(e) {
return HtmlService.createTemplateFromFile('index').evaluate();
}
var st = SpreadsheetApp.openById("sheet id").getSheetByName("data");
function iList(crv){
var pl = st.getDataRange().getValues();
var x='';
for(var i =1;i<pl.length;i++){
x+=pl[i][crv]+",";}
var cv = x.split(",").sort();
var rd = new Set(cv);
var result ='<ul>';
rd.forEach(cc => {
if(cc!==""){
result+="<li><b>"+cc+"</b><ol>";}
pl.forEach((pd)=>{
if(pd[0]!==""&&cc==pd[crv]){
result+= "<li>"+pd+"</li>";
}});
result+="</ol></li></ul><ul>";
});
console.log(result)
return result;
}
Copy the following index.html code and paste it in your project html page.
index.html
<!DOCTYPE html>
<html>
<title>GroupListView data - AppsScript</title>
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="https://www.w3schools.com/w3css/4/w3.css">
<link rel="stylesheet"
href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css">
<body onload="list()">
<div class="w3-row-padding w3-border-bottom">
<div class="w3-col s5">
<select id="criteria"onchange="list()"class="w3-select w3-col s8">
<option value="4">Village</option>
<option value="5">Class</option>
<option value="7">Status</option>
</select>
</div>
<div class="w3-col s1 w3-center">
<i id="spin" class="fa fa-refresh fa-0"onclick="list()" style="font-size:20px"></i>
</div>
<div class="w3-col s5 w3-left"id="info"></div>
<div class="w3-col s1 w3-right">
<input type="button" name="btnprint" value="Print"
onclick="pPrint('result')"class="w3-btn w3-teal w3-hover-red">
</div>
</div>
<div style="overflow: auto;height:520px;">
<div id="result"></div></div>
<script>
function list() {
var cri = document.getElementById('criteria');
var crv = cri.options[cri.selectedIndex].value;
var spn = document.getElementById('spin');
var info = document.getElementById('info');
var resp = document.getElementById('result');
function fail(error){
resp.innerHTML = "<span style='color:red'>"+error+"</span>";
spn.classList.remove('fa-spin');
info.innerHTML = "";
};
function pass(res){
resp.innerHTML = res;
spn.classList.remove('fa-spin');
info.innerHTML =" Fetched.";
};
resp.innerHTML = "";
spn.classList.add('fa-spin');
info.innerHTML = "Working on...";
google.script.run.withFailureHandler(fail)
.withSuccessHandler(pass)
.iList(crv);};
</script>
</body>
</html>
>>>TRY TO CHECK OUT , IF ANY ERROR FOUND. PLEASE LET ME KNOW BY COMMENT.
I'LL TRY MY LEVEL BEST TO FIX THE PROBLEM.
THANKS FOR VISITING CODYLAB
Have a nice day! -------------------------- -------------------------