Hi everyone,
So I know Glide is a “no-code” solution for many, but hear me out. I am a developer of 12+ years used to doing apps the “old-fashioned way”, but it’s obvious to me that Glide is the perfect solution for 99.9% of all the apps developers used to build, and I’m happy to use my technical knowledge to focus on that remaining 0.1%, and use Glide along with everyone else for everything else. I think what the Glide team has been able to accomplish already is tremendous, and I can tell that it’s only going to get much, much better. I also appreciate that there is a community built around this, and that there seem to be plenty of people like me, natural tinkerers, who get off on coercing systems like Glide to produce new and wonderful things that maybe aren’t ‘standard’ behavior. The fact is, it only takes a relatively little amount of logic, combined with relations, math, and templating, to pretty much do anything you want.
Having said that, I was recently tasked with a project, for which we decided Glide would be the best solution. I was able to build out most of the requirements for the project in less than an hour (which is crazy). However, there were a few sticking points, and unfortunately, they were core requirements, and yet it didn’t make sense to not use Glide due to its speed. So we could have changed the requirements or the app design, but there was a third option available, since Glide is based on Google Sheets (at the moment), and the Google Sheets API, and that was to extend Glide’s capabilities with Google Apps Script, the native code behind Google Sheets.
First of all, I don’t recommend this for everyone. Glide is a truly capable no-code solution. But if you need a little bit extra functionality where Glide stops short and you have a little experience programming in JavaScript, or you are willing to learn a little bit, extending Glide just a little bit with some Google Apps Script can be really beneficial. If your ‘extension’ is more than just a couple little functions that work seamlessly with Glide, or it’s going to make working in Glide more complicated… I do not recommend this approach. Ok, having said that I want to share a few examples about what I am talking about that I built for the project mentioned above.
A major requirement of the project was that users can create events, and then these events are aggregated on a listing page, grouped, and ordered by date. The event picker is the obvious UI choice for this, it’s a single element (though it needs a little work, as it is doesn’t let users change weeks on the desktop – this will be fixed, from what I hear, next week), very easy to use, and it gives the user context on existing events in the DB at the time the user is scheduling. Way, way better than two date/time picking fields, or a date field and two text time fields or something. Problem is, when you input a date this way, you get a start and end time as output, but no separate date field, and in Glide, even if you change a column to show only the date, while it outwardly only shows the date, the entire timestamp, including any hidden time information, is used to group items. So using the event picker, which is clearly the superior UI choice, presented me with a problem – how do I extract just the date the user picked, and put it in another column in Glide, without any time information associated with it, so that we can group events by day in the list view? At first I tried to do this with computed columns, date math, templating, etc. However, there was nothing that got me the desired result and retained the date in a format which could be correctly ordered, chronologically.
The next requirement that ran into a wall with Glide was that the items in this list view needed to be sorted by more than one field. Events needed to be grouped and ordered by date, then by start time, and then by end time, so that if two events on the same day started at the same time, the shorter one would display first. Durations are calculated and formatted, in one of 3 user-friendly formats depending on their values, completely in glide using computed columns - perhaps I will share those with the community as well.
As I could not accomplish this with Glide natively, but really liked what we had built, and I knew enough about Google Apps Script to know this was a restrained enough use case that it just might play well with Glide, I wrote some simple code to accomplish these two things, which I will share below, with annotations added. The two ways in which I extended Glide were 1.) use Apps Script to sort on multiple columns in the sheet itself, every time a relevant column is changed, making use of Glide’s existing “sheet order” feature to make sure that order is displayed consistently in Glide, and 2.) use Apps Script to extract just the date from a user’s event picker input and generate data for a new column with this. The key insight for extracting the date is if you just copy start time column to a new column, but either remove or set the time part of that timestamp to midnight, they can then be grouped in Glide as having the same date, without having to add a redundant field along with the event picker. This value then needs to be formatted to only show the date part of the timestamp.
If you don’t know where to find Google Apps script, just open the Google Sheet for which you want to script extended functionality, go to the Tools menu, and click Script Editor. A full-featured IDE will open up, connected to your Google Sheet.
Apps Script Code
/**
* Utility function for iterating through and performing a function on
* each cell in a given range, one column at a time.
*
* @param {Range} range - Google Apps Script Spreadsheet Range Class)
* @param {function} f - do f() for each cell
*
* Note: if you need to do any value checking in this function, the best
* way to do so is by storing the result of the Google Apps Script API
* method getValue() - performed on the entire range - in an array before
* you start looping, and then when you iterate on an individual cell,
* check its corresponding array value, rather than calling getValue() on
* the cell. This makes it so that you only have to call getValue() once,
* instead of for every cell, of which could be very many. This will result
* in orders of magnitude better performance. Unfortunately I couldn't
* avoid doing it the 'bad way' here, due to my requirements, but if you
* can, I'd recommend it. In general, you want to minimize any API calls
* to Google in your functions. For more see: https://tinyurl.com/y3o8kzjc
*/
function forEachRangeCell(range, f) {
// What are the dimensions of the given range?
const numRows = range.getNumRows();
const numCols = range.getNumColumns();
// Loop through each row, one column at a time.
for ( i = 1; i <= numCols; i++){
for ( j = 1 ; j <= numRows; j++){
// Select the cell as a range object and do something to it.
const cell = range.getCell(j, i);
// 'something' is a function, f, passed as an arg to this utility.
f(cell);
}
}
}
/**
* The function that will actually run when the sheet is changed.
*
* @param {Event} e - the actual event that causes this function to run
*/
function updateSheet(e) {
// Apps Script's method to get the sheet object being edited
const sheet = e.source.getActiveSheet();
// Grab the name if you need to make sure it's the right sheet w/o ID#s
const name = sheet.getName();
/**
* Define ranges here in A1 notation, but leave the last row# off - this
* will change as rows are added, and it needs to be dynamic. My ranges
* begin at row 2 because I have a header row in my sheets.
*/
const tableRange = 'A2:J';
const startTimeColRange = 'B2:B';
const endTimeColRange = 'C2:C';
const dateColRange = 'D2:D';
// These two API methods find the last filled row in the sheet
const rangeData = sheet.getDataRange();
const lastRow = rangeData.getLastRow();
/* The conditions below prevent any of the below code from executing if
the wrong sheet is edited, or there is no data other than the header. */
if (name == 'Sessions' && lastRow != 1) {
// Grab entire table, even empty rows, to be used for sorting
const table = sheet.getRange(tableRange);
// Grab start/end time, and new date columns, up to the last filled row
const startTimeCol = sheet.getRange(startTimeColRange+lastRow);
const endTimeCol = sheet.getRange(endTimeColRange+lastRow);
const dateCol = sheet.getRange(dateColRange+lastRow);
/**
* The way this is going to work is the script needs to iterate through
* every row in the startTime column, pushing each value to an array,
* after which it will then iterate through each row in the date column,
* setting the value of that cell with the value in that array, after
* applying a transformation on that value to just get the date part.
*/
// Set up blank array to be populated with startTime values.
let newDates = [];
// Set up a counter to by which the array can be iterated
let i = 0;
/* Now invoke the utility loop above, iterating on each cell of the
startTime column, and perform given function on each cell. */
forEachRangeCell(startTimeCol, (cell) => {
/** This is what you shouldn't do for each cell in a loop, if you can
* avoid it. In my case I have to check there is a value, so that
* a date can be generated for only each legitimate start time.
*/
cellValue = cell.getValue();
if (cellValue) {
/* Strip the time from the startTime, by building a date using
only the day, month, & year - this makes all times 0:00AM. */
let origDate = new Date(cellValue);
let year = origDate.getFullYear();
let month = origDate.getMonth();
let day = origDate.getDate();
let date = new Date(year, month, day);
// Add the newly transformed date to the array created above.
newDates.push(date);
}
});
/* Then iterate through each cell of date column and replace with
the corresponding value in the array that was just populated. */
forEachRangeCell(dateCol, (cell) => {
// Set the value to the corresponding array item and format date
cell.setValue(newDates[i]).setNumberFormat('dddd, MMMM d, yyyy');
// Increment iteration index for the next row
i++;
});
// Sort the entire table by columns, 4, 2, and 3, in that order.
table.sort([4,2,3]);
// Make sure all times are formatted correctly.
startTimeCol.setNumberFormat('h:mm am/pm');
endTimeCol.setNumberFormat('h:mm am/pm');
}
}
That’s it for the code. You might notice I am running the script over every row, even if though existing rows would have already been dealt with previously - this redundancy just makes sure things are always in the right format, even if people directly edit the sheet instead of adding things from Glide, and in my case the sheet will never have that many rows, so this is OK with me. I could have set this up to only run on the row that was directly edited or newly added. There are many different things that can be done, and this just shows a few, so look through the Apps Script documentation. I used the simple syntax for sorting a range on multiple columns above, where every column specified is sorted ascending by default. For descending, you need to use the full syntax, which would look like this:
// Sort the entire table by col 4 ASC, then 2 and 3 DESC, in that order
table.sort([4, true, 2, false, 3, false]);
Also, another way to get rid of the time part of the date is with some simple date math. A date in Google Sheets is just a number of the form: 00000.000000000, where the digits before the decimal represent day/mo/yr, and the digits after represent the time. If we just subtract the start time in this form modulo 1 (which gives the remainder, or just the time part of the date), from the start time itself, we will end up with the date. However, I don’t recommend this if people will be looking at the sheet while the app is used, though, because they would see the values being formatted and reformatted as edits occur.
/* This would replace the first forEachRangeCell() above...
// ...
startTimeCol.setNumberFormat('00000.000000000');
forEachRangeCell(startTimeCol, (cell) => {
let cellValue = cell.getValue();
if (cellValue) {
cellValue -= cellValue % 1;
newDates.push(cellValue);
}
});
//...
// Don't forget then to format it back, preferably after any sorting with:
startTimeCol.setNumberFormat('dddd, MMMM d, yyyy');
Now to hook it up to your sheet, you have to create a Trigger in Google Apps Script, or it will not run. There are ways to make scripts that run automatically without Triggers, these are called Simple Triggers, but they only allow events that are performed directly on the sheet, in the Google Sheets UI, and only a small selection of event types. To work with Glide or any API, an Installable Trigger must be created. You can do this in your Apps Script code editor by going to the Edit menu and selecting Current project’s triggers. Click the big blue button that says “Add trigger”, choose your function from the first dropdown (in my example it’s updateSheet(), not the utility function I created for looping over ranges, make sure the 3rd dropdown says “From spreadsheet” (although the “Time-driven” option here lets you create a trigger that runs periodically, instead of on a user-driven event), and then on the bottom-most dropdown “Select event type”, select “onChange”. This will make it so the script runs every time a something is edited through the API, like Glide does, every time a row is added, or really any change from outside or inside the sheet. There are ways to further restrict different subtypes of events that fall within the onChange event itself, for instance if you wanted a different behavior when rows are added compared to when rows are edited - look for that in the documentation. Lastly, choose how often to receive failure notifications, and then click save. If you don’t have any errors in your script, it will run now on every Change event your sheet fires. You can check each execution of your Triggers on the Apps Scripts Home page, and you will quickly see if they were executed successfully or resulted in an error, and you can drill down to learn more about any errors, or add an instance of the class and method Logger.log() in your script to log and debug internally, in the code editor.
That’s all for now. I realize this isn’t for everyone, and I definitely would recommend you completely understand any code you get from other people online before you try to copy and paste it in your own sheets’ App Scripts. Also this is really just the tip of the iceberg in terms of what you can do with Apps Scripts, but I could see this easily being abused in ways that make it harder for Glide to manage and sync your data on your Glide App. In my example above, the functionality is limited to essentially setting the value of a column that would otherwise be empty, and sorting the sheet. Yet with this little bit of Apps Script, I have a really simple user interface that almost feels magical to the user, and just works the way it should, despite any limitation I might have run into with Glide.
Glide + Apps Script = 
?
This is my first real post in this community, so this is also an introduction of sorts - probably will not be my last… here’s hoping someone finds this topic as interesting as I do!
. I was told that was going to be updated this week, so I am holding off on any changes to the UI to see if that happens, and if it doesn’t I will have to dump the event picker… not ideal for usability/context/data consistency, but neither is an event picker limited to a single week.
