[關鍵字]:
google 試算表
[重點摘要]:
網頁廢話版:
http://yichunsung.github.io/r_notebook/notebook_for_goolesheets
因為我工作時有很多工作夥伴不懂coding、資料庫這些東西。
唯一會的就是goole試算表和execl，後來便想到乾脆用R連接google試算表
方便大家需要資料的人自己上去google試算表中抓，省去很多電話打來打去的時間
網頁版的是我寫給一些會用R的工作夥伴的筆記，想說順道分享上來。
1. 前言
直接利用R存取google試算表的資料，簡化一些不必要的操作
2. 套件安裝
利用googlesheets套件可以輕鬆地串接R與存取在雲端上的google試算表。
※ 範例:
install.packages('googlesheets')
library(googlesheets)
3. 連結google帳戶
安裝好套件後，首先第一步驟是要連結你的google帳戶，在此之前你可以先去隨意於網頁
上建立一個測試用資料表，但也可以不用，googlesheets套件好用之處在於你可以完全利
用R去建立、存取和刪除你帳戶中的任何試算表。
利用這個函數googlesheets::gs_auth()建立與google帳戶的授權
※ 範例:
gs_auth(new_user = TRUE) # 與帳戶建立新的受權
gs_user() # 查看user資料
此時會自動幫你開啟瀏覽器，你必須於瀏覽器中登入你的google帳號。登入成功後瀏覽器
會顯示訊息，之後便可回到R的執行環境之下。 gs_user()這個函數可以查看該帳戶的資
訊。
4. 查看google試算表
使用gs_ls()這個函數可以直接查看該帳戶中所有可用的google試算表，如果該帳戶中無
任何可存取的試算表他就會顯示訊息告知你無任何試算表可使用
※ 範例:
gs_ls() # 查看帳戶中的google試算表list
5. 讀取
gs_tittle() : 註冊帳戶中可使用的Spreadsheet進入R，function內的參數為該
Spreadsheet名稱。
gs_read() : 讀取選擇的spreadsheet中的某個Worksheet。
利用gs_title() 註冊帳戶中一個Spreadsheets，再利用gs_read()去讀取Spreadsheet中
的其中一個worksheet，讀取後他將存成一個data frame。
※ 範例:
test_gs <- gs_title('testFile') # 讀取帳戶中名為"testFile"的 Spreadsheet
UBspot <- gs_read(ss=test_gs, ws = "工作表1", skip=0)
# ss = spreadSheet, ws = worksheet
gs_url() : 利用連結註冊，function內的參數為該Spreadsheet的公開連結。
gs_read() : 讀取選擇的spreadsheet中的某個Worksheet。
可利用gs_url()註冊的spreadsheet分為兩類，一種是已發布的公開spreadsheet，另一種
是一般私人spreadsheet。
要利用私人的Spreadsheet，一樣需要取得帳戶授權，步驟如上面相同，只是註冊
spreadsheet時由gs_title()改成gs_url()，而已經發佈且公開的Spreadsheet則不需要帳
戶授權的步驟，因為是公開發佈的資料，所以任何人都可以透過該URL去註冊這個
Spreadsheet。
但通常公開發佈的資料表並不會開放權限給外人寫入，只有讀取功能而已。但相對地，如
果你有一份資料想要提供給超多人使用，這就是一個很好的途徑，不然私人資料表你就得
透過google的共用功能去分享給別人。
註: 網頁版有圖教學如何公開發布goole試算表
http://yichunsung.github.io/r_notebook/notebook_for_goolesheets
發佈成功後，便可以取得連結，在不需要帳戶登入或授權情況下可以註冊該spreadsheet
。除此之外，對R來說使用gs_url()最重要的場合就是讓shinyapps去讀取這個資料表，這
部分後面會有詳盡的範例。
※ 範例:
Purl <-
"https://docs.google.com/spreadsheets/d/1aoXyR7foNOsSergX39cEalNGOJNBMrWbIog0d
ASocpw/edit?usp=sharing"
sibi_spreadsheet <- gs_url(Purl)
sibidf <- gs_read(ss=sibi_spreadsheet, ws = "K3_station_list", skip=0)
6. 寫入新的資料
新增資料
※ 範例:
newdata <- data.frame(name=c("Ulaanbaatar Railway Station",
"Ulaanbaatar Airport"),
eng = c("Ulaanbaatar Railway Station",
"Ulaanbaatar Airport"),
lat = c(47.908592, 47.840388),
lng = c(106.883938, 106.769976)
)
gs_add_row(ss=test_gs, ws = "工作表1", input = newdata) # 新增資料
在你已授權的帳戶中建立一個新的spreadsheet。
※ 範例:
gs_new("CWB-data", ws_title = "Beiliao", input = Beiliao, trim = TRUE,
verbose = FALSE)
在已註冊的Spreadsheet中新增一個新的worksheet。
※ 範例:
CWB_gs <- gs_title('CWB-data')
gs_ws_new(ss = test_gs, ws_title = "Zhudong", input = test2, trim = TRUE,
verbose = FALSE)
7. Shinyapps應用
把google sheets當作資料庫，而shinnyapps的server.R可以連結進去
程式碼在下面，Demo:
https://yichunsung.shinyapps.io/testgooglesheets/
這樣的東西方便性在於，未來你要更新資料，你不必在把server.R打開來重新更新code，
你可以直接上去Google Sheet增加資料就好，介面簡單直觀，對於不用R的工作夥伴而言
也非常輕鬆可以協助更新資料。
另外注意，gs_read()千萬記得放在shinyServer(function(input, output){}裡面，才會
自動更新資料
server.R:
library(shiny)
library(googlesheets)
library(plotly)
GTPurl <-
"https://docs.google.com/spreadsheets/d/11VFUdSCfZahqIuaO5eAvtCyyIjpnnYkG4w7KliiY_fc/edit?usp=sharing"
GTPtest <- gs_url(GTPurl)
shinyServer(function(input, output) {
GTPtestdf <- gs_read(ss=GTPtest, ws = "工作表1", skip=0)
GTPtestdf$date <- as.Date(GTPtestdf$date)
output$the_data <- renderDataTable({
GTPtestdf
})
output$plotlyData <- renderPlotly({
GTPdataplotly <- plot_ly(
data = GTPtestdf,
x = GTPtestdf$date,
y = GTPtestdf$TA_Day,
type = "scatter",
mode = "liners+markers"
)
GTPdataplotly
})
})
ui.R:
library(shiny)
library(googlesheets)
library(plotly)
shinyUI(fluidPage(
titlePanel("Quick demo"),
sidebarLayout(
sidebarPanel(
dateRangeInput("dates",
label = h3("date"),
start = "2017-01-01",
end = "2017-01-01",
format = "yyyy-mm-dd"),
selectInput("title",
label = h3("station"),
choices = c("title1", "title2", "title3"),
selected = "title1"),
submitButton("Submit"),
br()
),
mainPanel(
tabsetPanel(
tabPanel("result",
dataTableOutput("the_data")),
tabPanel("Plot",
plotlyOutput("plotlyData"))
)
)
)
))
Reference
https://shiny.rstudio.com/articles/persistent-data-storage.html#gsheets
https://github.com/jennybc/googlesheets/issues/126
https://www.r-bloggers.com/reading-data-from-google-sheets-into-r/
https://is.gd/t7yHAm