# How-to: Feature Next Upcoming Event in List

**URL:** <https://community.glideapps.com/t/how-to-feature-next-upcoming-event-in-list/18757>\
**Category:** Project Showcase\
**Created:** [November 22, 2020, 6:42am UTC](https://community.glideapps.com/t/how-to-feature-next-upcoming-event-in-list/18757 "2020-11-22T06:42:19Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![avanavana](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/avanavana/32/15637_2.png) [@avanavana](https://community.glideapps.com/u/avanavana)\
**Post date:** [November 22, 2020, 6:42am UTC](https://community.glideapps.com/t/how-to-feature-next-upcoming-event-in-list/18757/1 "2020-11-22T06:42:19Z")

</div>

### What?

I recently created a glide app for a client that displays the next upcoming event in a database in a special format, to “feature” it on the page. Doing so ended up being a little trickier than I had guessed initially.

The reason this is difficult to do is that Glide doesn’t have the needed datetime comparison feature. You can compare datetimes against “now” and “today”, and in rollups you can find “earliest” and “latest”, but if you want to select the “earliest” out of a rollup of only the upcoming events, you’re out of luck, because you need a conditional column to create a column of only datetimes that are upcoming, and if you try to do a rollup on those, you will find that date comparisons such as “earliest” and “latest” are unavailable - the output of rollups of dates is converted to strings, so you only have the rollup functions “count” and “count unique” available to you. If Glide adds the ability to preserve date type data in rollups and use “earliest” and “latest”, then this will become _very_ easy to do. But until then, it requires a bit of wizardry to pull off.

### Demo

> **[Next Event Demo](https://next-event-demo.glideapp.io)**
>
> Showcases glide wizardry to display the next event in a set of events with a featured style, and the rest below, grouped by day.

### Note

According to my level of present knowledge, this simple behavior cannot be done solely within Glide. This demo uses the tiniest, tiniest bit of Google Apps Script on the source sheet, as I’ll describe below.

### How-to

_Prerequisite Steps_

- First, you must obviously have a datetime field in your sheet.
- Next, this works only if the sheet is ordered, so that the first row is always the oldest event, and the last row the newest. To ensure this, we can use a tiny bit of Google Apps Script in our Google Sheet.
  1. Open your Google Sheet
  2. Go to **Tools** › **Script Editor**
  3. Paste the following code in the editor window (with replacements for your particulars as noted below)

Code:

```
 const sortSheet = (e) => {
     const sheet = e.source.getActiveSheet();
     const name = sheet.getName();
     const sheetRange = 'YOUR_SHEET_RANGE'';
     const lastRow = rangeData.getLastRow();
     if (name == 'YOUR_SHEET_NAME' && lastRow != 1) {
         const range = sheet.getRange(sheetRange + lastRow);
         range.sort(DATE_COLUMN_NUMBER);
     }
};

```

Make sure you replace `YOUR_SHEET_RANGE` with your actual sheet range, for example, if your sheet has columns from A until J, and you have a header row, `YOUR_SHEET_RANGE` would be `A2:J`. Keep the single quotes around it in the code. Replace `YOUR_SHEET_NAME` with the name of the sheet you want to keep sorted, e.g. `Sheet1` (again, don’t delete the quotes in the code, write it between the quotes. Replace `DATE_COLUMN_NUMBER` with the number of the column you want to sort by, in this case, your datetime column. Column B for example would be 2, so it’d say `range.sort(2);` on that line. You can sort by multiple columns by enclosing the numbers in square brackets and separating by commas, e.g. `range.sort([2,3,4]);`. This will sort the columns _ascending_. If you need to sort by _descending_ value, it’s pretty simple, but just [read the docs](https://developers.google.com/apps-script/reference/spreadsheet/range#sortsortspecobj). The setup below is for “Next Event”, however, so we need to sort ascending, or from earliest to latest event. You might notice in the demo above, I am sorting on 3 columns, first by date, then by time, and then by category, all ascending.

- Lastly we need to set up this litle sortSheet() function to be triggered every time the sheet changes. To do this:
  1. With the code editor still open, go to the **Edit** menu and select **Current project’s triggers**.
  2. Click the blue ‘Add trigger’ button in the lower right.
  3. Under ‘Choose which function to run’ choose your ‘sortSheet’ function, if it isn’t already selected.
  4. Under ’ Select event type’ choose ‘On change’.
  5. Click **Save**.

Congratulations, your Google Sheet will now stay automatically sorted, and we can now implement the “Next Event” trick.

_Glide Data Setup_  
The setup in glide is as follows (see screenshot). I’ll describe each column in turn.

 ![Screen Shot 2020-11-22 at 1.07.42 AM](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/4/0/401b62b9c8f04e5259f4f907c9ff5896c74a2ace.png)  
From left to right:

- **Date & Time** _Basic Column_ from your sheet, the one that we sorted on in the Apps Script, the basis for most everything else below
- **Total Events** : _Rollup Column_, configured to **Sheet › Row ID** (note: any column works, as long as every row has a value in the column you select) and **Count**. This will be the total number of events in the database.
- **Is Upcoming** : _Conditional Column_, configured to IF **Sheet › Date & Time** _is after_ **Now** , THEN **1** , ELSE **0**
- **Total Upcoming:** _Rollup Column_, configured to **Sheet › Is Upcoming** and **Sum**. This will be the total number of upcoming events in the database. All future events should have a value of 1 in this column, and all past events a value of 0.
- **Next Event Start Offset:** _Math Column_, configured to **Total Events** _-_ **Total Upcoming** - that is, subtract the number of upcoming events from the number of total events. Because our sheet is always sorted earliest to latest, thanks to Google Apps Script, this number will always represent the number of rows to skip before we find the next event in the database!
- **Next Event:** _Single-value Column_, configured to **From start** , **Next Event Start Offset** , and **Sheet › Date & Time**. This is the key step. We identify the next event by using the single value column, configured with “from start”, in order to tell Glide how many rows to skip (Next Event Start Offset), before picking a value from the column Date & Time, in your sheet. The result is the value of Date & Time for the next event will be shown in all columns. We just need one more column to finish.  
**Is Next:** _Conditional Column_, configured to IF **Sheet › Date & Time** _is_ **Next Event** THEN **1** ELSE **0**. Here we test whether the date and time of each event is equal or not to the date and time of the event we identified as next. Only the very next event will match, and we will get a value of 1 that we can use in the layout.

_Glide Layout Setup_  
Ok, now to make this work on the front end, it’s simply a matter of creating a detail view like below. Ignore the “Rich Text” component – this is simply some special formatting I am using in my demo, but it’s not needed for this how-to, nor is the form button needed (this is just to create new events).

 ![Screen Shot 2020-11-22 at 1.16.16 AM](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/8/7/8728236f6f72015089dff902f0c3f565182e8001.png)

- The key components you need here are just two inline lists. The first inline list will only show one event, the next event, and the second will show all the other upcoming events. To accomplish this:

- Configure the first inline list like this, under “Features”:  
 ![Screen Shot 2020-11-22 at 1.16.35 AM](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/f/5/f593ea7513c76481452f27ebd934a00f25d54093.png)  
(Note that now that you have Google Apps Script sorting your sheet, you don’t need to choose any sort order here besides Sheet Order, unless you’re going for something different). The key here is to filter the inline list to show only items whose **Is Next** column equals **1**. This will always be the next event. You can format this inline list differently, as in my demo, perhaps with an image or different style to make it stand out as “featured”.

- Configure the second inline list like this, under “Features”:  
 ![Screen Shot 2020-11-22 at 1.17.03 AM](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/d/0/d010ffee09973af3f7eccf241cd8a4f466fc0929.png)  
So the key here is to filter this second list by **Is Next** equals **0** AND **Is Upcoming** equals **1** , which will give you all the rest of the upcoming events, besides the one singled out in the first “featured” list. Again, you shouldn’t need to touch the sorting. You can ignore grouping if you aren’t set up for that.

OK, that’s it! Just thought someone might find this useful. If Glide allows for date options in Rollup fields with computed values, this will become MUCH easier to do. All you’d need to do is have a conditional column to show the date & time value if it is after now, and then roll up those values with the earliest of that subset of datetimes. But until then…

* * *

_Note: I have my events grouped by another field “Date”, which is just the date part of the Date & Time field above, so that events in this list are grouped under headings for each day. This won’t work with a field that has both date and time, because Glide will not know they are the same day, because different times make the whole value different. If you don’t want to make your users enter the date twice in two different fields, it becomes harder – trickery in the Data section with Math and Template columns won’t help, because the values you get out won’t be dates and you won’t be able to sort them appropriately. So to do this I also use Google Apps Script to generate a separate date column, without time information. I posted a [thread](https://community.glideapps.com/t/hello-and-on-extending-glide-with-google-apps-script/17751) about this, but be aware that it is more involved._