如何在googlesheet中追加新数据

Question

下面我有一个在R中提到的数据，我使用RMySQL在3小时内从数据库中获取数据。

查询：

Select Form_No, Date, Name, Age from Test where Date(Date)>='2020-12-01';

DF：

Form_No      Date                  Name            Age
POS-1        2020-12-01 10:16:12   Alex            29
POS-2        2020-12-01 10:18:34   Mike            31
POS-3        2020-12-01 14:16:22   Paul            21
POS-4        2020-12-01 18:33:14   John            27
POS-5        2020-12-01 20:13:34   Cohe            45

当我运行脚本时，每隔3个小时就会有一些额外的条目在dataframe中创建，而当我使用下面提到的代码上传googlesheet中的数据时，它会覆盖之前的信息并更新新的数据。

问题是，对于每一行，信息的审阅者可能已经捕获了一些观察结果。

我所使用的守则是：

library(dplyr)
library(stringr)
library(RMySQL)
library(googlesheets)

connection<-dbConnect(MySQL(), user='User', password='Password', 
                 dbname='Db name',
                 host='host info')

Query<- paste0("Select Form_No, Date, Name, Age from Test where Date(Date)>='2020-12-01';")
DF <- dbGetQuery(connection,Query)

setwd("/home/path")
write.csv(x = DF, file = "cust_data.csv", row.names = FALSE)

as<-gs_title("TargetSheet")
gs_upload("cust_data.csv", sheet_title = "TargetSheet", overwrite = TRUE)

在上传新的一批信息时，我想保持Form_No的顺序和每一个细节相同。

例如，如果我在早上06:00运行查询，并且有五个条目(如DF中所示)，并且所有条目都上传到google，那么如果我在上午9:00运行脚本，那么在我的查询中，除了这五个条目之外，还有更多的条目。

除了上次上传的五个条目外，我现在还需要在google表中添加这些额外的条目。

Answer 1

假设您已经将查询保存到一个名为“DatabaseOutput”的Google表中：

​

您可以使用googlesheets4::read_sheet读取数据。为了确保每次都得到正确的列类型，我建议提供col_types =参数。read_sheet返回的列类型与查询返回的列类型相同，这一点非常重要。您可以在readr文档页面中找到有关该参数的更多信息。

library(googledrive)
library(googlesheets4)
OldSheet <- read_sheet(drive_get("DatabaseOutput"),
                       col_types = "cTci")
OldSheet
## A tibble: 3 x 4
#  Form_No Date                Name    Age
#  <chr>   <dttm>              <chr> <dbl>
#1 POS-1   2020-12-01 10:16:12 Alex     29
#2 POS-2   2020-12-01 10:18:34 Mike     31
#3 POS-3   2020-12-01 14:16:22 Paul     21

您的SQL查询和从Google工作表中读取的内容可能在具体时间上存在分歧，因此我建议使用lubridate查找Date列。

library(dplyr)
library(lubridate)
DF <- as_tibble(DF) %>% 
  mutate(Date = as_datetime(floor(seconds(Date))))
OldSheet <- OldSheet %>%
  mutate(Date = as_datetime(floor(seconds(Date))))

现在，我们可以用dplyr::anti_join找到旧数据中不存在的行。如果这不起作用，请再次检查列类型是否都相同。

NewRows <- anti_join(DF,OldSheet)
NewRows
## A tibble: 2 x 4
#  Form_No Date                Name    Age
#  <chr>   <dttm>              <chr> <int>
#1 POS-4   2020-12-01 18:33:14 John     27
#2 POS-5   2020-12-01 20:13:34 Cohe     45

现在，您可以用sheet_append将行附加到Google表中。

sheet_append(drive_get("DatabaseOutput"),NewRows)

​

​

Answer 2

*编辑以添加：我现在更理解您的问题；您只想添加新条目，因为其他人可以访问您的google工作表，可能已经对以前上传的条目进行了检查和编辑。我的大部分答案将与以前一样。

尝试使用googlesheets4包而不是googlesheet。

这就是对我起作用的东西。

as = gs4_create("TargetSheet")
DF = as.data.frame(read.csv('cust_data.csv'))
lastRow = nrow(DF)
sheet_write(DF, ss = as, sheet = "TargetSheet")

### 3 Hours later and beyond ###

DF = as.data.frame(read.csv('cust_data.csv'))
newEntries = DF[(lastRow + 1):nrow(DF), ]
lastRow = nrow(DF)
sheet_append(ss = as, data = newEntries, sheet = "TargetSheet")

因此，对于任何额外的条目，每三个小时继续运行最后4行代码。另一个潜在的解决方案是基于上次上传的最后一个已知位置进行新的查询，但我认为我的纯R解决方案应该做得很好。