Quando ficheiros de excel são utilizados para fornecer dados à plataforma a criação de um UUIDv4 permite manter um link entre uma linha do excel e uma entrada na plataforma. Assim quando uma linha é alterada ou removida a plataforma consegue encontrar a entrada que deve alterar.

O código que se segue é um script VBA que adiciona um UUID na primeira coluna desde que algum valor dessa linha esteja preenchido

Ainda há optimizações possíveis na detecção de ids repetídos.

Function CheckRowEmptyExceptFirstColumn(rowToCheck) As Boolean

    Dim ws As Worksheet
    Dim lastColumn As Long

    Dim i As Long
    Dim isRowEmpty As Boolean
    
    ' Set the worksheet and row number to check
    Set ws = ActiveSheet
    
    ' Find the last column with data in the sheet
    lastColumn = ws.Cells(rowToCheck, ws.Columns.Count).End(xlToLeft).Column
    
    ' Assume the row is empty except for the first column
    isRowEmpty = True
    
    ' Loop through all cells in the row from the second column to the last column
    For i = 2 To lastColumn
        If ws.Cells(rowToCheck, i).Value <> "" Then
            isRowEmpty = False
            Exit Function
            Exit For
        End If
    Next i
    CheckRowEmptyExceptFirstColumn = isRowEmpty
    
End Function

Function HasDuplicateId(row As String) As Boolean


Dim myArray As Variant
Dim x As Integer
Dim dup As Boolean
Dim idToTest As String


 ' MsgBox ("Checking until row " & row)


idToTest = Cells(row, 1).Value


dup = False
For x = 1 To row - 1 Step 1
    If idToTest = Cells(x, 1).Value Then
       ' MsgBox ("Found Dup")
        dup = True
      Exit For
      End If
      
Next x

HasDuplicateId = dup
End Function


Function GenerateUUIDv4() As String
    Dim uuid As String
    Dim random As Long
    Dim i As Integer

    
    ' Generate a UUID in the format 8-4-4-4-12 (32 hex characters + 4 hyphens)
    uuid = ""
    
    ' Generate the first 8 characters (8 random hex digits)
    For i = 1 To 8
        random = Int(Rnd() * 16) ' Generate a random hex digit (0-15)
        uuid = uuid & Hex(random)
    Next i
    
    uuid = uuid & "-"
      
    For i = 1 To 4
        random = Int(Rnd() * 16) ' Generate a random hex digit (0-15)
        uuid = uuid & Hex(random)
    Next i
    
    uuid = uuid & "-4"
    
    
    ' Generate the next 3 characters (3 random hex digits)
    For i = 1 To 3
        random = Int(Rnd() * 16)
        uuid = uuid & Hex(random)
    Next i
    
    uuid = uuid & "-"
    
    ' Generate the next 3 characters (3 random hex digits)
    ' The 1st character of this block must be one of 8, 9, A, or B
    random = Int(Rnd() * 4) + 8 ' Ensure the first character of the variant is in range 8, 9, A, or B
    uuid = uuid & Hex(random)
    
    ' Generate the remaining 2 characters (2 random hex digits)
    For i = 1 To 3
        random = Int(Rnd() * 16)
        uuid = uuid & Hex(random)
    Next i
    
    uuid = uuid & "-"
    
    ' Generate the final 12 characters (12 random hex digits)
    For i = 1 To 12
        random = Int(Rnd() * 16)
        uuid = uuid & Hex(random)
    Next i
    
    ' Convert the UUID to lowercase
    GenerateUUIDv4 = LCase(uuid)
End Function


Private Sub Worksheet_Change(ByVal Target As Range)
  Randomize ' Set seed based on system timer
  startRow = 3
  Application.EnableEvents = False
   
    Dim cell As Range
    
    ' Check if more than one cell was changed or if the change occurred in column A
    ' If Not Intersect(Target, Me.Columns("A")) Is Nothing Then
    '  Application.EnableEvents = True
   '   Exit Sub
   ' End If
    
     If Target.Rows.Count > 5000 Then
         MsgBox ("fimdomeio: Por questões de performance, não é possivel procurar duplicados na coluna de ids quando são alteradas mais de 5000 linhas. Serão gerados novos ids. Para nmanter ids copie menos de 5000 de cada vez.")
        For Each rng In Target.Rows
          If rng.row > startRow Then
            Cells(rng.row, 1).Value = ""
          End If
        Next rng
     End If
    
    For Each cell In Target
      If Not (cell.Column = 1) And cel.row > startRow Then
    
        ' Insert timestamp in the first column (column A) of the changed row
        If Not IsEmpty(cell.Value) And IsEmpty(Cells(cell.row, 1).Value) Then
            Cells(cell.row, 1).Value = GenerateUUIDv4() ' Insert UUID v4
        End If
      End If
    Next cell
    
    If Target.Rows.Count <= 5000 Then
    For Each rng In Target.Rows
        If HasDuplicateId(rng.row) Then
         ' MsgBox ("Found dup changing value")
          Cells(rng.row, 1).Value = GenerateUUIDv4()
        End If
    Next rng
    End If
    
    
         
  For Each rng In Target.Rows
    If cel.row > startRow Then
         If CheckRowEmptyExceptFirstColumn(rng.row) Then
            Cells(rng.row, 1).Value = ""
          End If
        End If
     Next rng

        Application.EnableEvents = True
End Sub