Unique ID Special Value

Create a special value which is a UUID (or other unique ID feature). This will enable less brittle associations between records. I also think this will work super well with the new data editor.

Copied from Spectrum

7 Likes

I think a related feature request would be to allow separate values and labels like the option tag https://developer.mozilla.org/en-US/docs/Web/HTML/Element/option.

For example you could have ID and Name columns. The value would be the ID but the label would be the name.

1 Like

Yes! I’m really interested in this feature. So, the users will be able to change the names without worry. The issue is: what formula or function of Google Sheet will create automatically this ID? Because it’s not user friendly ask to end users to create a random ID each row.

The proposal, if I’m reading Spectrum correctly, is for Glide to generate the UUID on create. You would add this to your form just like you would add creator and creation time

1 Like

It’s a nice ideia. Not only in forms by button, but in every add function. I agree!

I still keep thinking about how many issues this would solve for relations. I’m really excited if this becomes the approach.

1 Like

Here is a workaround using a script. You would need to create a timed trigger to run the function every minute or so. This would cause a delay in the data being populated in your app but it works for me because the linked sheet items, the ones using that unique ID, don’t get added that fast after the parent row is added. The script in attached to this spreadsheet. Make copy of it and experiment with it.
Here is the script in case the spreadsheet disappears in the future. I put in a lot of comments so hopefully most of you familiar with scripts can follow along. Add a new row to the sheet without the ID and Image columns to see what happens.

/* 
  Run this function in a timed trigger
  The test sheet ("Order" in this example) should have the first column be the ID
  Columns B through .... has the data that the App added
  Another sheet is needed (appdata in the example) to hold a number that represents the next ID
   - For this example it is in cell B2 
*/

function check4NewOrder(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Order");
  var lastRow = sheet.getLastRow();
  var sheetAppdata = ss.getSheetByName("appdata");
  // assumes the ID is going to be in column A
  var data = sheet.getRange(2,1,lastRow-1,1).getValues();
  var countDown = 3; // number of rows to check from the bottom for blank ID's
  
  // walk the data rows from the bottom as the most recent add will be there
  for (var i = data.length-1; i > -1; i--){ 
    if (data[i][0] === ""){
      // if you want to set some default values in columns past 1
      // then bring more rows into the array and set them as you wish
      // this demo is bringing in 6 columns of data for this row
      var rangeRow = sheet.getRange(i+2,1,1,6); 
      var dataRow = rangeRow.getValues();
      // create the new ID
      var sheetAppdata = ss.getSheetByName("appdata");
      var rangeAppdata = sheetAppdata.getRange("B2");
      var dataAppdata = rangeAppdata.getValue();
      // you can tack on some letters in the front to make it easier to spot and know 
      // where it's linked to
      var newID = "ORD" + dataAppdata.toString();
      // increment for the next ID
      rangeAppdata.setValue(dataAppdata+1);
      // put the ID in the data array
      // if you bring in more than the first column into the array you could
      // set some other default values if you wanted to here as well
      dataRow[0][5] = "<image URL here>";
      dataRow[0][0] = newID;
      rangeRow.setValues(dataRow);
    };
    countDown--;
    if (countDown < 0) {break}
  }
}```
1 Like

Yes, Glide would create the Unique ID as a special value, just as Current Time works today.

4 Likes

Is that option around the corner, David? Or is it low on the buildlist?

We are entering a new major product cycle. At the moment the “buildlist” is being determined based on feedback. :slight_smile: I think a UUID solution is likely to be on the short list, but its not being actively worked on yet.

4 Likes

I’ll just throw in my two cents argument for UUID. I have a sheet of students. When a new name is entered, I have a script the takes over and populates 3 other sheets with their name. Their name is the key value which links the sheets together. The problem I have is that if a name is ever changed, or it was misspelled and corrected, then a new entry is created in those other tables and and the link to any previously entered data is broken. For me, UUID would allow one ID and it frees me up to changes the students name without breaking any links to data in other sheets.

2 Likes

I am building a platform where people can ask others to do small jobs for them. They can have the same description though (buy a book, clean car, etc). There it would help to have UUID’s.

1 Like

This would still be a great feature! Any word on if it will come out in a glide release soon?

1 Like

I understand the Gliders can’t promise on timelines, but to me it would help to know if a feature is No2 out of 10 or 10 out of 10 on the roadmap, Now it feels like we have to campaign for features :wink:

We are entering a new major product cycle. At the moment the “buildlist” is being determined based on feedback. :slight_smile: I think a UUID solution is likely to be on the short list, but its not being actively worked on yet.

1 Like

Yeah, it’s hard to tell if we should wait for a feature or not.