How to append only new rows with sheet_append in googlesheets within a shiny app

Question

This is not a reproducible example question!

What I am doing: I use library(googlesheets4) to read a sheet from google sheets, entry data to it within a shiny app, and save it back to the sheet on google sheets.

I managed to read the data from google sheets with authentication etc...

So now I am having a google sheet say my_sheet.

I load the data from google sheets with

responses <- read_sheet("https://docs.google.com/spreadsheets/...etc... xx. and so on")

I can see the data in my app table now.

Now I add new data (rows) in my app and create new rows.

So far it is working perfect!

Now I want to update the my_sheet in google sheets.

I do:

# Click "Save" button -> save data
observeEvent(input$save, {
  my_sheet <- gs4_get("https://docs.google.com/spreadsheets/..bla bla bla")
  sheet_append(my_sheet, data = responses)
})

The data is written to the google sheet, but all of the data is appended not only the new entries.

So the data on google sheets in my_sheet is duplicated + the new rows, doubling each time I hit the save button.

How can I keep my workflow and append only the new entries to google sheets.

Answer 1

Untested but something along these lines should work:

Sub Tester()
    Dim rw As Range, clr As Long
    Set rw = ActiveSheet.Range("B4:AN4") 'start row
    
    Do While Application.CountA(rw) > 0 'loop while there's data
        Select Case rw.EntireRow.Columns("AF").Value
            Case "ready": clr = vbGreen
            Case "in progress": clr = vbYellow
            Case "pending": clr = vbRed
            Case Else: clr = -1
        End Select
        
        If clr <> -1 Then
            rw.Interior.Color = clr         'apply fill
        Else
            rw.Interior.ColorIndex = xlNone 'no fill
        End If
        Set rw = rw.Offset(1, 0) 'next row
    Loop
End Sub