Filter and Search Google Sheet framework

1

Hi,

I’m pulling data from a googlesheet to create a kind of schedule of events. I have manged to do some conditional formatting to colour code the type of event and am happy with the way it works, but I would like to add some filtering and search options. I seem to have managed to create a few buttons to filter by month / event type / and reset back to show all. But currently these run individual js functions. I’m wondering if there is a way to combine them rather than creating a function for each filter.

Ultimately, is it possible to have a search field so when the user starts typing the results get filtered? Not sure where to start with that. Thanks for any pointers…

GoogleSheetSchedule.hype.zip (56.4 KB)

This pulls data from Google Sheet into divs.

    $.getJSON("https://spreadsheets.google.com/feeds/list/1XL5oRKXNwbnWqumaRXbCgf60uxN5mNsN1Byb0aJG7Qg/od6/public/values?alt=json", function(data) { 
   
  var Panel = document.getElementById('panel');
  
  var items = [];
 
 
  for (i = 0; i < data.feed.entry.length; i++) { 
  
  var Start = data.feed.entry[i].gsx$start.$t
  var End = data.feed.entry[i].gsx$end.$t
  var Month = data.feed.entry[i].gsx$month.$t
  var Location = data.feed.entry[i].gsx$location.$t
  var Event = data.feed.entry[i].gsx$event.$t
  var Type = data.feed.entry[i].gsx$type.$t
  var Handling = data.feed.entry[i].gsx$handling.$t
  var Link = data.feed.entry[i].gsx$link.$t
  var Email = data.feed.entry[i].gsx$email.$t
  
   if (Type == "S"){
   
 BigDate = '<div class="wrapper"><div class="bigdate_s"><div class="dateNumber">' + Start + '</div><div class="dateMonth">' + Month + '</div></div>'
 Event = '<div class="eventTitle">' + Event + '</div>'
 Link = '<div class="rightDetails">' + Handling + '&nbsp; <i class="far fa-user"></i><br>' + Email + '&nbsp; <i class="far fa-envelope"></i><br>' + Link + '&nbsp; <i class="far fa-images"></i><br></div>'
 Date = '<div class="leftDetails"><i class="far fa-calendar-alt"></i>&nbsp;&nbsp;' + Start + End + ' ' + Month + '<br>' + '<i class="fas fa-map-marker-alt"></i>&nbsp;&nbsp;' + Location + '<br><i class="fas fa-camera"></i>&nbsp; House </div>'


  }
  
    
   else if ((Type == "E")) {
 
 BigDate = '<div class="wrapper"><div class="bigdate_e"><div class="dateNumber">' + Start + '</div><div class="dateMonth">' + Month + '</div></div>'
 Event = '<div class="eventTitle">' + Event + '</div>'
 Link = '<div class="rightDetails">' + Handling + '&nbsp; <i class="far fa-user"></i><br>' + Email + '&nbsp; <i class="far fa-envelope"></i><br>' + Link + '&nbsp; <i class="far fa-images"></i><br></div>'
 Date = '<div class="leftDetails"><i class="far fa-calendar-alt"></i>&nbsp;&nbsp;' + Start + End + ' ' + Month + '<br>' + '<i class="fas fa-map-marker-alt"></i>&nbsp;&nbsp;' + Location + '<br><i class="fas fa-camera"></i>&nbsp; House </div>'
 
  }
  
   else{

 BigDate = '<div class="wrapper"><div class="bigdate"><div class="dateNumber">' + Start + '</div><div class="dateMonth">' + Month + '</div></div>'
 Event = '<div class="eventTitle">' + Event + '</div>'
 Link = '<div class="rightDetails">' + Handling + '&nbsp; <i class="far fa-user"></i><br>' + Email + '&nbsp; <i class="far fa-envelope"></i><br>' + Link + '&nbsp; <i class="far fa-images"></i><br></div>'
 Date = '<div class="leftDetails"><i class="far fa-calendar-alt"></i>&nbsp;&nbsp;' + Start + End + ' ' + Month + '<br>' + '<i class="fas fa-map-marker-alt"></i>&nbsp;&nbsp;' + Location + '<br><i class="fas fa-camera"></i>&nbsp; House </div>'
 
  }
 
  
   items.push( BigDate + Event + Link + Date + '</div></div>');
   
   Panel.innerHTML = items.join('');
   
}
  
});

And this is the CSS that styles the divs.

.wrapper {
  margin-bottom: 12px;
  padding: 10px;
  background-color: white;
  max-height: 50;
  overflow: hidden;
  display: block;
  width:95%;
  box-shadow: 0 4px 8px 0 rgba(0, 0, 0, 0.2);
}

.eventTitle{
font-size: 16px;
font-weight: 900;
width: 200px;
background-color:white;
display: inline-block;
margin-bottom:8px;
}

.leftDetails {
  font-size: 14px;
  line-height:21px;
  padding-right: 10px;
  color: gray;
  background-color: white;
  overflow: show;
  display: relative;
}

.rightDetails {
font-size: 14px;
line-height:30px;
font-weight: 600;
text-align: right;
color: gray;
width: 150px;
background-color:white;
float: right;
display: inline-block;

}


.dateNumber {
font-size: 42px;
color: white;
font-weight: 900;
}
.dateMonth {
font-size: 14px;
color: white;
}

.bigdate {
  text-align: center;
  width: 70px;
  height: 70px;
  margin-right: 12px;
  padding: 10px 10px 10px 10px;
  background-color: #12A274;
  overflow: hidden;
  float: left;
  display: inline-block;
}
.bigdate_s {
  text-align: center;
  width: 70px;
  height: 70px;
  margin-right: 12px;
  padding: 10px 10px 10px 10px;
  background-color: #F0B729;
  overflow: hidden;
  float: left;
  display: inline-block;
}
.bigdate_e { 
  text-align: center;  
  width: 70px;
  height: 70px;
  margin-right: 12px;
  padding: 10px 10px 10px 10px;
  background-color: #876BD6;
  overflow: hidden;
  float: left;
  display: inline-block;
}

And this is one of the Filter examples that is fired onclick of a button:

    $.getJSON("https://spreadsheets.google.com/feeds/list/1XL5oRKXNwbnWqumaRXbCgf60uxN5mNsN1Byb0aJG7Qg/od6/public/values?alt=json", function(data) { 
   
  var Panel = document.getElementById('panel');
  
  var items = [];
 
 
  for (i = 0; i < data.feed.entry.length; i++) { 
  
  var Start = data.feed.entry[i].gsx$start.$t
  var End = data.feed.entry[i].gsx$end.$t
  var Month = data.feed.entry[i].gsx$month.$t
  var Location = data.feed.entry[i].gsx$location.$t
  var Event = data.feed.entry[i].gsx$event.$t
  var Type = data.feed.entry[i].gsx$type.$t
  var Handling = data.feed.entry[i].gsx$handling.$t
  var Link = data.feed.entry[i].gsx$link.$t
  var Email = data.feed.entry[i].gsx$email.$t
  
   if (Type == "E"){
   
 BigDate = '<div class="wrapper"><div class="bigdate_e"><div class="dateNumber">' + Start + '</div><div class="dateMonth">' + Month + '</div></div>'
 Event = '<div class="eventTitle">' + Event + '</div>'
 Link = '<div class="rightDetails">' + Handling + '&nbsp; <i class="far fa-user"></i><br>' + Email + '&nbsp; <i class="far fa-envelope"></i><br>' + Link + '&nbsp; <i class="far fa-images"></i><br></div>'
 Date = '<div class="leftDetails"><i class="far fa-calendar-alt"></i>&nbsp;&nbsp;' + Start + End + ' ' + Month + '<br>' + '<i class="fas fa-map-marker-alt"></i>&nbsp;&nbsp;' + Location + '<br><i class="fas fa-camera"></i>&nbsp; House </div>'
  
   }
  
   else{

 BigDate = ''
 Event = ''
 Link = ''
 Date = ''
 
  } 
  
   items.push( BigDate + Event + Link + Date + '</div></div>');
   
   Panel.innerHTML = items.join('');
   
}
  
});
2
  • removed dependency jQuery (no need for that really)
  • cached data in hypeDocument.customData
  • added search
  • cleaned indentation (watch that as it helps coding)
  • there is much more that could be done better but that is not my job :wink:

GoogleSheetSchedule-Max.hype.zip (30,9 KB)

PS: Saw that somewhere your referencing a $t that doesn’t exist. That is why you got one console error. My guess in one of the scene load functions.

4 Likes
3

Hi @MaxZieb
Thank you so much for this. You are right, it’s not your job (although I would hire you in an instant if I could afford you!) Just a quick follow up… The search is being run on the Event only. I have changed the line below to encompass other fields (location and month) and it’s working, but I’m wondering if this is the correct way of doing it, or if there is a more elegant solution?

if (Event.toLowerCase().indexOf(searchString) !== -1 || Location.toLowerCase().indexOf(searchString) !== -1 || Month.toLowerCase().indexOf(searchString) !== -1 || searchString == '') {

Secondly, all the search results are given the same class, (div class=“bigdate_s”) so the color of the date switches to yellow, which breaks the conditional formatting applied via css. Is there an elegant solution to keep the same class as the original? I will try to figure it out and report back here if anyone is interested.

Thanks again,

Gleb