Let me start by saying I’ve been really enjoying building with Glide and am excited to see it continue to grow and improve.
For awhile in the process of building my app, I’ve wished that entry fields had more configuration options for inputs (ranging from changing the error message shown for existing min/max violations to automatically formatting phone numbers or currency values on screen). I also wish that there were address specific entry fields so that, for example, state and zip code could be combined on one line as two fields to save space. Though, I’m actually considering trying to implement an auto fill for city and state (US addresses only) based off of an entered zip.
However, I’m making this post because during UAT I recently had a user include a page break at the end of the street entry as well as a trailing space after her name. This caused several display problems down the row, especially because I concatenate the address into one “easy” line.
The documentation for entry fields on the website seems a bit limited, as it wasn’t until digging on the forums here that I discovered email address fields apparently automatically trim whitespace. If anyone knows of a way to get other fields to do this, I’d be excited to hear about it. I really don’t want to have to implement additional columns in google sheets with an array formula trim function because I can only imagine the performance issues that’d cause in addition to the headache of rebuilding so many things.
Glide team, can we please get the ability to set entry fields to trim white space?
Agreed that more functions like this in Glide would be super useful.
A kind of hacky workaround for “post entry data validation” is to use a trigger to fire a function that tidies things up. I’ve done this before - in fact I’ve used it for your exact use case - to remove trailing whitespace.
Sure.
Yes, I use App Scripts that are fired by an ON CHANGE trigger.
Following is an example from one of my projects that gets fired every time a change is made to my Users sheet. I’ve annotated it, but it just does two things:
Removes trailing white space from all user names (this happens a lot when people copy/paste)
Reformats a couple of date/time columns for better readability.
Summary
function tidy_users() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('Users');
// Cols 14/15 are the created_at and updated_at dates. Set to a more readable date format
sheet.getRange(2,14,sheet.getLastRow(),2).setNumberFormat('yyyy-mm-dd hh:mm:ss').setHorizontalAlignment('left');
// Remove any trailing whitespace from user names
for (var row=2; row<=sheet.getLastRow(); row++) {
var name = sheet.getRange(row, 3).getValue();
sheet.getRange(row,3).setValue(name.trim());
}
}
This is awesome and definitely better than nothing. Have you noticed any performance impact? My concern about implementing this in my application is that I’d probably point it at around 10 different columns, and ON CHANGE will start firing pretty frequently for my table in question. I’m worried that it’d cause issues with Glide’s data sync. I’ve also noticed an issue where if the Glide app refreshes with new information from google sheets it seems to reset to the main page and users lose any information in a form they were working on. If that started happening more it’d be troublesome.
Voted—would love an “address component” that will autofill the mailing address of record upon typing the address—most appstore/playstore apps have this functionality when it comes to addresses.
I went and filed that as a separate feature request, since I do think both would be tremendously valuable to my use case.
I actually took a crack at implementing a zip code based city/state autofill, but wasn’t able to because the number of records to reference in a zip code database results in more rows that Glide supports for an app.
No, I haven’t noticed any performance impact at all. I use triggers extensively in almost all of my projects and they are constantly firing when an app is in use (think multiple times per minute).
Bear in mind that ON CHANGE will fire whenever there is a change anywhere in the worksheet, so your triggers need to use some additional logic to determine whether or not to take any action.
Here is another example - this is the script that actually gets called by the ON CHANGE event, and then it calls additional helper scripts as necessary.
Summary
function on_sheet_change(event) {
var sheet = event.source.getActiveSheet();
var sheetname = sheet.getName();
if (sheetname == 'Feedback') {
format_time_cols(sheetname, 2, 1);
}
else if (sheetname == 'Users') {
tidy_users();
}
else if (sheetname == 'User Summaries') {
format_time_cols(sheetname, 17, 1);
}
else if (sheetname == 'Users-Languages') {
check_for_deleted(sheetname);
}
else if (sheetname == 'Users-Ethnicities') {
check_for_deleted(sheetname);
}
else if (sheetname == 'Users-Orgs') {
check_for_deleted(sheetname);
format_time_cols(sheetname, 5, 1);
}
format_sheet(sheetname);
}
Seeing the discussion on API integration in staging… I would think there should be a way to incorporate some sort of address lookup API using this new feature. I have absolutely no idea though as I have zero knowledge or experience with APIs… Just sounds like it should be possible.
I actually use a lot of integrations with Zapier. A more direct API integration would be really awesome.
For my use case, I don’t think I’d be able to use an API for address lookup because my address entry is on a form. I can’t think of a way to get that form entry field to trigger any API calls. I haven’t gone and looked at what they’re building in staging, though.