Address fields & Trimming

Hello Glide Community and Team,

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?

Thanks!
N

6 Likes

+1

Yes! And maybe there could be a button that opens up mapbox and asks them to select the address!

Great idea! Why don’t you create a feature request on the feature requests site?

+1 for this.

1 Like

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.

2 Likes

Continuing the discussion from Address fields & Trimming:

Hm I didn’t realize there was a separate site for feature requests. Do you have the link?

1 Like

that is what am looking for, also with option to filter items for location range

Do you mean to triggering a google apps script, zapier, or something else? Can you walk me through how your hacky approach worked?

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:

  1. Removes trailing white space from all user names (this happens a lot when people copy/paste)
  2. 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());
  }
}
3 Likes

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.

Thanks! I appreciate you.

1 Like

For anyone who wants to give this a +1 in the feature request app, here’s the link: https://features.staging.glideapp.io/dl/ewAiAHQAIgA6ADMALAAiAHMAIgA6ACIAYwBsAGEAcwBzAC0ARgBlAGEAdAB1AHIAZQBzACIALAAiAHIAIgA6ACIAYgByAHIAMABGAGsAMwBzAFEAdABDAEUAQQBuAHAAVQBiAGEANAB2ADMAZwAiACwAIgBuACIAOgAiAFQAcgBpAG0AIABFAG4AdAByAHkAIABWAGEAbABpAGQAYQB0AGkAbwBuACAAZgBvAHIAIABXAGgAaQB0AGUAIABTAHAAYQBjAGUAIgB9AA%3D%3D

1 Like

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.

3 Likes

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.

1 Like

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.

1 Like

It should be available, but those comes with additional cost.

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.