So my code will update certain values in my Google Sheet. The code is working. Basically It is a tracking system of who borrows an item. So the tag is unique for each item so it is my index value. Which ever row my value is located it will update on that same row for whatever I entered in my VB. This information is under Column A to Q in my sheet. Columns I update for example are Columns E, I, J, L, O, P and Q.

So as I mentioned it is working as intended, my only issue is that when ever I trigger the code, it will change the format on some columns, specifically M, N, O and P. They have this ‘text in the cells. My Column M and N has a number format, with M4 containing an array formula. My O and P are in date format, and once the code runs, it changes to ‘text.

I just want the format of my Google sheet to remain unchanged. Or if there is a function or code that can prevent changing the format. I have search through and I have not found anything yet.

This is part of the code that executes the code.

Private Sub SearchButton_Click(sender As Object, e As EventArgs) Handles SearchButton.Click
    Dim searchTag As String = searchTagTextBox.Text
    SearchAndDisplayData(searchTag)
End Sub

Private Sub SearchAndDisplayData(searchTag As String)
    Try
        Dim sheetName As String = GetSheetNameFromTag(searchTag)
        Dim range As String = $"{sheetName}!A2:Z"
        Dim request As SpreadsheetsResource.ValuesResource.GetRequest = service.Spreadsheets.Values.Get(SpreadsheetId, range)
        Dim response As ValueRange = request.Execute()
        Dim values As IList(Of IList(Of Object)) = response.Values

        If values IsNot Nothing AndAlso values.Count > 0 Then
            For Each row As IList(Of Object) In values
                If row.Count > 6 AndAlso row(6).ToString() = searchTag Then
                    brand1.Text = If(row.Count > 2, row(2).ToString(), String.Empty)
                    assettag1.Text = If(row.Count > 6, row(6).ToString(), String.Empty)
                    serialnumber1.Text = If(row.Count > 5, row(5).ToString(), String.Empty)
                    userassigned1.Text = If(row.Count > 11, row(11).ToString(), String.Empty)
                    location1.Text = If(row.Count > 9, row(9).ToString(), String.Empty)
                    Exit For
                End If
            Next
        Else
            MessageBox.Show("No data found.")
        End If
    Catch ex As Exception
        MessageBox.Show("Invalid tag format.")
    End Try
End Sub

Private Sub updateuserassigned_Click(sender As Object, e As EventArgs) Handles updateuserassigned.Click
    Dim searchTag As String = searchTagTextBox.Text
    Dim newUserAssigned As String = textuserupdate.Text
    Dim newLocation As String = textlocationupdate.Text
    UpdateUserAssignedAndLocationAsync(searchTag, newUserAssigned, newLocation)
End Sub

Private Async Sub UpdateUserAssignedAndLocationAsync(tag As String, newUserAssigned As String, newLocation As String)
    Try
        Dim sheetName As String = GetSheetNameFromTag(tag)
        Dim range As String = $"{sheetName}!A2:BS" ' Adjust range to include columns R, S, T, U
        Dim request As SpreadsheetsResource.ValuesResource.GetRequest = service.Spreadsheets.Values.Get(SpreadsheetId, range)
        Dim response As ValueRange = Await request.ExecuteAsync()
        Dim values As IList(Of IList(Of Object)) = response.Values

        If values IsNot Nothing AndAlso values.Count > 0 Then
            For i As Integer = 0 To values.Count - 1
                Dim row As IList(Of Object) = values(i)
                If row.Count > 6 AndAlso row(6).ToString() = tag Then
                    Dim previousUserCol As Integer = 17 ' Column R
                    Dim previousLocationCol As Integer = 18 ' Column S
                    Dim maxPreviousUsers As Integer = 10 ' Number of previous users to handle

                    ' Debugging output
                    Console.WriteLine($"Row index: {i}, Current Row Count: {row.Count}")

                    Dim foundSpace As Boolean = False
                    For j As Integer = 0 To maxPreviousUsers - 1
                        Dim userCol As Integer = previousUserCol + (j * 2)
                        Dim locationCol As Integer = previousLocationCol + (j * 2)

                        ' Extend row length if necessary
                        If row.Count <= userCol Then
                            For k As Integer = row.Count To userCol + 1
                                row.Add(String.Empty)
                            Next
                            ' Debugging output
                            Console.WriteLine($"Extended row to {row.Count} columns.")
                        End If

                        ' Debugging output
                        Console.WriteLine($"Checking columns {userCol} (User) and {locationCol} (Location).")

                        ' Check if the column is available
                        If String.IsNullOrEmpty(row(userCol).ToString()) Then
                            ' Found the next available columns
                            row(userCol) = If(row.Count > 11, row(11).ToString(), String.Empty) ' Move current user
                            row(locationCol) = If(row.Count > 9, row(9).ToString(), String.Empty) ' Move current location

                            ' Update the new user and location
                            row(11) = newUserAssigned
                            row(9) = newLocation

                            Dim valueRange As New ValueRange() With {.Values = values}
                            Dim updateRequest As SpreadsheetsResource.ValuesResource.UpdateRequest =
                            service.Spreadsheets.Values.Update(valueRange, SpreadsheetId, range)
                            updateRequest.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW
                            Await updateRequest.ExecuteAsync()
                            MessageBox.Show("User Assigned and Location updated successfully.")
                            foundSpace = True
                            Exit For
                        End If
                    Next

                    If Not foundSpace Then
                        MessageBox.Show("No available columns for previous users.")
                    End If
                    Exit Sub
                End If
            Next
        Else
            MessageBox.Show("No data found.")
        End If
    Catch ex As Exception
        MessageBox.Show("Invalid tag format.")
    End Try
End Sub