Hello, and, On Extending Glide with Google Apps Script

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 = :rocket:?

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!

5 Likes

Hi Avana, thanks for the sharing, and first, welcome to the community!

For your original problems:

You can do this by having a math column to take only the date, then a template column on that math column to lock the format in.

I think we can get around this by having a column that uses an arrayformula to “rank” the events group-wise, with each group having all the events that starts at the same time. Thne use a template column to join the start time and the “rank” to sort by it.

I believe some people have had some weird issues with sorting the sheet, it caused problems with the rowID.

The problem is, that would coerce the data into a String, and would result in all sorts of craziness, such as Fridays coming before Mondays, in a list that is supposed to be Chronological. Human-readable dates were an absolute requirement in this case, so something like 01-January-1900-Friday wasn’t possible. Dates needed to be rendered strictly in the dddd, mmmm d, yyyy format.

That’s actually where I started off with this. However, I found that both =ARRAYFORMULA() and =SORT(col, 0, true) resulted in all sorts of weirdness, for example, when you hand over the app to the client and the client deletes all the rows in the spreadsheet. An early iteration of this app used a hidden sheet in Google Sheets that copied the entire sheet that received original input from Glide with =SORT('OriginalSheet!Range, 4 true, 2 true, 3 true) and this actually worked sort of well… except for the fact that it then made editing/deleting items on the original table really complicated, since the hidden sheet then was generating the list view, while only the original sheet could be edited. I had an hour-long chat convo with an excellent Glide support person on this. Using formulae resulted in a bunch of other unexpected behavior and weird inconsistencies between GS and Glide, that I can’t specifically remember in enough detail to relate here, but suffice to say, until Google Sheets adds “Column Formulae”, a la Glide, Airtable, Notion, and Smartsheet now, this is not a stable solution. Adding formulae into cells is a bad idea when using Google Sheets with Glide.

In the end, formulae in cells is an ugly hack with unpredictable behavior, while Apps Script is a tailor-made solution that does only what you need it to do, leaves the cell values as pure data, and works well with Glide. Especially if you have a Pro plan and keep your app on constant sync.

Another major issue that I was having before deciding to employ Apps Script was that the formats would not stick, no matter what I did in the Google Sheets UI. As soon as a new row was added from Glide, the new row would arrive in the wrong format, even though I had previously added a specific custom number format to the column. Then, trying to format it back in Glide, I was running into the problem that grouping by date only worked if the ‘default’ column type and format were retained in the Glide Data Sheet Editor. Changing the column type or format destroyed the group-ability of the column by converting it to a timestamp, even if only the date was showing, two items that “looked” like Friday, October 30, 2020 would be grouped separately, as long as their “hidden” times were different. In the end, the best way to make this operable and consistent in the long run after handing over to the client, was just to hard-code the behavior into the sheet itself, with some simple Apps Script.

I have had no such issue. In fact the rowID is necessary for this to work, and with a rowID it works beautifully, combined with the “Sheet Order” sorting option Glide offers.

I want to reiterate what I said at the beginning of my OP - I think Glide is a really powerful solution, and that the vast majority of issues can be solved with clever engineering of Glide native functionality. I think I made it clear that I don’t recommend this approach for 99.9% of situations, and particularly for those who don’t already have this skillset, unless they are willing and able to afford experimenting with it enough to learn. The last thing I’d recommend is for someone having problems to copy some of my code here and slap it on their Google Sheet without knowing what they are doing.

In my specific case though, I found extending Glide’s capabilities with Apps Script was to my benefit, and it allowed me to simplify the whole system, making the whole thing more manageable after handing it off to the client, and for me it took only 30m or so to write the code that did what I had previously spent several hours trying to accomplish in Glide natively. You might argue that this means Glide wasn’t the right fit, but I’d challenge that. Developing the back end and front end of an app from scratch or even with a framework would have made this project much more complicated and time-consuming. The client is particularly happy that the data is all in Google Sheets, and they don’t have to manage any formulae or understand anything beyond it just works. With Glide I don’t have to write my own back end to use Google Sheets as a DB. It took all of an hour or two to set up 99% of the app, back end/data model, and front-end UI in GS and Glide’s editor, and I get something that is constantly deployed, cross-platform, PWA, and reasonably beautiful. I could have spent 2-4 weeks developing something from scratch with similar functionality on my own, compared to 2 days doing it with Glide + a little Apps Script, allowing me to focus on my core development and design work. So I think Glide was the perfect fit, despite my need to extend it a bit.

1 Like

I do what @ThinhDinh suggests. The template is a string but only used for grouping. The sorting still happens with the original date format column. Examples here:

Complicated sorts involving date and other values is a different story. If glide provided an ISO format option, then you could template that with other values, such as calculated duration to get the sort order you want. However, you could possibly subtract a seed datetime from the current datetime to get a numeric value, add the difference in event duration, then sort on that numeric value. It better yet, calculate the seed datetime from event datetime, then separately calculate duration, then template them both together. May or may not run into issues with number if digits, so might be best to add something like 10000 to each calculated result to get a consistent number of digits to the left of the decimal.

I’m not knocking the use of scripts. I use then too, but Glide can be surprisingly powerful and much more responsive if you can accomplish the same thing exclusively within glide.

If you want to play around with a concept and throw some random events at it, feel free here. It’s not perfect but I haven’t worked on it for awhile.

1 Like

That would work in most cases, but I needed to sort on multiple columns.

I agree, and I would labor to do everything in Glide natively before even thinking about scripting.

2 Likes

Have to make an update to this thread:

It’s known that the event picker widget, while providing an undeniably superior UX compared to, say, two date/time pickers (event picker has smaller chance for mistakes in user input, users can see context of other events when inputting data, one less form field, provides ‘confirmation’ or ‘preview’ of correct input, etc), is still quite buggy. Glide have assured me that these bugs are to be fixed in an update that will drop next week.

The major bug that desktop users can’t change the week (which I’m sure has something to do with the fact that Safari and IE don’t support the HTML5 date input type yet), is pretty annoying (again, Glide have assured me that they will have this fixed next week, I assume with some kind of polyfill), but during testing today with my client, we discovered another bug with the event picker:

It appears that the ‘style’ of timestamp generated by the event picker widget varies with browser/OS. Basically I had a user add an event with the event picker, and instead of saving the start and end timestamps in the usual format, this person’s device was sending their input as ISO-8601 dates of the format yyyy-MM-ddThh:mm:ss.sssZ, and despite the fact that the columns for both start/end time in the Google Sheet were set to a specific date/time format, these ISO-8601 dates weren’t being formatted according to the column format.

Hopefully this too will be fixed in the upcoming updates to the Event Picker widget, but in the meantime I added a few lines of code to the above script to handle incoming data formatted in ANY possible ISO-8601 format. Everything above is the same except for the functions in the code below:

// Regex expression to match and capture pieces of any ISO-8601 timestamp
const iso8601Format = /^([0-9]{4})-?([01][0-9])-?([0-3][0-9])T([0-2][0-9]):?([0-5][0-9]):?([0-6][0-9])(?:\.0{3}|\.0{6})?(?:Z|(([+-])([01][0-9]):?([0-5][0-9])?))$/;

// Process the start time column
forEachRangeCell(startTimeCol, (cell) => {
  cellValue = cell.getValue();

  // Check whether exists, so to only create a date when start time is given
  if (cellValue) {

    // Check whether the cell data matches an ISO-8601 format
    if (iso8601Format.test(cellValue)) {

      // Destructuring assignment of regex capture group into several vars
      // " || []" is added to avoid attempting destructuring of null vals
      let [, year, month, day, hours, min, sec, offset, offsetSign, offsetHours, offsetMin] = iso8601Format.exec(cellValue) || [];

      // Create JS datetime obj - JS stupidly 0-indexes only the month
      let t = new Date(year, month-1, day, hours, min, sec);

      // Check whether the cell data includes an ISO-8601 timezone offset
      if (offset) {

        // If so, normalize according to the offset sign and magnitude
        t.setHours(t.getHours() + (offsetHours * (offsetSign === "+" ? 1 : -1)));
        offsetMin && t.setMinutes(t.getMinutes() + (offsetMin * (offsetSign === "+" ? 1 : -1)));
      }

      // Strip time data from timestamp for grouping by date (see above code)
      newDates.push(new Date(t.getFullYear(), t.getMonth(), t.getDate()));

      // Replace cell data with correct data and format
      cell.setValue(t).setNumberFormat('h:mm am/pm');
    } else {

      // Earlier code, but simplified, for data in the expected format
      let d = new Date(cellValue);
      newDates.push(new Date(d.getFullYear(), d.getMonth(), d.getDate()));
    }
  }
});

// Now, process the end time column
forEachRangeCell(endTimeCol, (cell) => {
  cellValue = cell.getValue();

  // Again test whether cell data is in an ISO-8601 format
  if (iso8601Format.test(cellValue)) {

    // Destructuring assignment of regex capture groups into several vars
    // " || []" is added to avoid attempting destructuring of null vals
    let [, year, month, day, hours, min, sec, offset, offsetSign, offsetHours, offsetMin] = iso8601Format.exec(cellValue) || [];

    // Create JS datetime obj - JS stupidly 0-indexes only the month
    let t = new Date(year, month-1, day, hours, min, sec);

    // Check whether the cell data includes an ISO-8601 timezone offset
    if (offset) {

      // If so, normalize according to the offset sign and magnitude
      t.setHours(t.getHours() + (offsetHours * (offsetSign === "+" ? 1 : -1)));
      offsetMin && t.setMinutes(t.getMinutes() + (offsetMin * (offsetSign === "+" ? 1 : -1)));
    }
    
    // Replace cell data with correct data and format
    cell.setValue(t).setNumberFormat('h:mm am/pm');
  }
});

Again, hopefully Glide normalizes both the UI/UX and the output format of the Event picker widget by next week as I was told would be the case. Also looking forward to some other embellishments on this widget, like the ability to navigate/select from a mini-month calendar instead of just a week.

Oh also, because it’s not letting me edit my OP, I wanted to share a version of the utility function for looping through ranges one cell, by cell, one column at a time, which performs conditional checks the “good” (ie performant) way. This won’t work for every situation’s requirements, but it is much faster when you can use it, since it only calls the API method getValue() once, instead of for each cell in the range, which could be huge depending on your dataset.

const forEachRangeCell = (range, f) => {
  // Copy the values of all cells in the range into a JS array
  let rangeValues = range.getValues();

  // 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++){

      // Your condition goes here; arrays are 0-indexed, hence the -1s
      if (rangeValues[j-1][i-1]) {
        
        // Select the cell as a range object
        const cell = range.getCell(j, i);

        // Perform function f, passed as an arg here, on each cell
        f(cell);
      }
    }
  }
}
1 Like

??? Glide has been saving dates in ISO format long before the event picker was around. I guess to me that’s normal as the underlying date format and the visible format set for the column in either the data editor or Google sheets changes what you see. Couple of questions. What happens to the ISO date in Google sheets if you reset the format on the column? Is glide inadvertently saving the date as a string? I’ve only ever seen this I believe in the App: Logins sheet, where the ISO date has a single quote to convert it to a string. In either case, it should be coming in as a date column. Is the column in the data editor showing as a date column or a text column?

1 Like

I’m not sure what was going on here, other than I had multiple people inputting data from different devices, and for a few of these people, the data was coming in from Glide as a raw ISO-8601 time stamp, while from others it was not. Obviously lots of software uses this standard as an underlying format, and Glide seems to recognize this format, however Google Sheets does not, so it screws up any formulas or scripts or formatting you might want in your Google Sheet.

Google Sheets treats all dates internally as numbers, of the format 00000.000000000, where the integer part is the date, and the decimal is the time.

If you input an ISO-8601 date into a cell in Google Sheets and then try to apply a date format to it, you will see it does nothing.

Below: Google can’t interpret ISO-8601 datetime input: (all bold expressions in this image are equivalent)

Below: As you can see, changing format does nothing.

Below: Glide appears to recognize and successfully formats ISO-8601 timestamps, even though Google Sheets does not
Screen Shot 2020-10-31 at 4.26.38 AM

So it does get changed in Glide, but since it doesn’t work in Google Sheets, if you rely on either:

  • Custom date/time formats done in Google Sheets
  • Sorting from Google Sheets (Sheet Order setting in Glide)
  • Formatting to make your sheet look nice and normalized in Google Sheets
  • The reasonable expectation that your data in Google Sheets acts the same as it does in Glide’s data editor
  • Formulas in your Google Sheet
  • Apps Script

… you’re screwed when Glide sends over datetime input in this format. But the big issue, regardless of whether or not you think Google Sheets should support entering dates in an ISO-8601 format, is the issue that Glide is sending different data to Google Sheets depending on the user’s device/OS/browser settings. When I use the event picker, it sends data in a Google-friendly format. When some others we had testing did this, it sent the data as a raw ISO-8601 string, and screwed up everything in Google. Again, this wouldn’t be an issue if I wasn’t depending on clean data in my Google Sheet for Apps Script, but there are plenty of other reasons you wouldn’t want ISO-8601 strings in your Google Sheets date/time columns, even without any Apps Script. Bottom line is the data should come in the same format, no matter who inputs it, if they are using the same Glide app. Like I said, I hope this is one of the things that will be fixed in the forthcoming Event picker update.

I will update this when I receive more information about the device/OS/browser info that was used.

1 Like

I’ll have to spend some time thinking about this and reading through old posts. How Glide works with dates has evolved over the past year or so. I’m still convinced that ISO is the deep down underlying value somewhere within Glide’s copy of the database, as demonstrated here.
image
Over a year ago, Glide used to store dates exclusively in MDY format, which caused several issues for international users that use DMY. After that, I believe Glide switched to an ISO format to work more reliably internationally, which cleared up a lot of issues. I think there is also some logic on Glide’s part that will determine how dates are stored in the glide sheet and/or google sheet based on if a column is recognized within Glide and/or Google Sheets as a date column as well as the Region/Locale of the Google Sheet itself. The Region/Locale of the user’s device will also play a part in how a date is displayed to a user (ex. US vs European formats) and will adjust automatically as necessary, but I haven’t been aware of it ever effecting how it is stored internally within Glide.

You will find some posts where people have observed dates that will save as one format, then change a few seconds later.
Just trying it in staging, I observed this after entering a date in edit mode:
image
Then a few seconds later it flips to this:
image
This is a text column and not a date column, so I don’t have any reasonable expectation of it being treated as a date that follows my sheet locale or any formatting set within glide, but it is weird nonetheless.

Admittedly, I don’t know the complete inner workings of how Glide works with dates. I wish I did, because weird stuff like this comes up and it’s hard to figure out and explain, or nobody else can duplicate. I’m just going to throw out my best guess right now, that maybe if their are multiple rapid updates, that maybe glide is temporarily losing region/locale of the google sheet, so it writes the ISO version instead…or maybe somehow the formulas/scripts are interfering and running at the same time that a new update is coming from glide and updates coming and going between Glide/Google are crossing paths. I have personally seen issues in the past, with extensive heavy formula processing while data was incoming and in some cases I had data loss due to it. I’m just thinking that maybe there is a very slim possibility that something is glitching while your script is running (not with the script itself), or glide/google are sending data updates to each other at the same time and maybe overwriting a format that it was trying to set properly.

If I can find more info, I’ll let you know, but yeah, I’d be curious to know if you are seeing this more consistently from a specific device/os/browser. I’m leaning more towards a sync issue.

3 Likes

Yeah, I have no doubt Glide is using ISO-8601… the problem is that is a format that doesn’t work in Google Sheets. Usually, if you have formatting on the column in Glide, this formatting goes into Google Sheets just fine, but it appears that on some users’ devices, the raw ISO format goes through and then Google doesn’t know what to do with it.

I have personally solved this issue with the apps script I posted above, but obviously I would prefer not to have to do it at all, so hopefully when the event picker is updated this issue will be fixed. The issue is likely that the HTML input type=date is not implemented by all browsers, and of those that implement it, it’s not standardized.

Right now the only remaining issue my users are having is that they cannot change weeks in the event picker on Desktop :confused:. 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.

3 Likes