Hello, and, On Extending Glide with Google Apps Script

Ask for Help
6

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