How-to: Feature Next Upcoming Event in List

[avanavana]

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

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. 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.

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).

• 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”:
  
  (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”:
  
  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 about this, but be aware that it is more involved.