R 學習日誌#6 - 如何使用 R 語言存取 Google Sheets

發表於 更新於 分類於

通常想要將資料放在一個資料庫方便做存取，一般所知道的資料庫都是常見的 MySQL, MSSQL 或是非結構化的 mongodb 等等的資料庫應用程式。不過有些時候就只是不多的資料，其實也不需要特別去裝這些資料庫，只要使用 Google Sheets 也可以做得到輕量型資料的喔。

學習內容大綱

  1. 套件安裝
  2. 連接 Google 帳戶
  3. 查詢 Google Sheets 試算表
  4. 讀取 Google Sheets 試算表

套件安裝

使用 googlesheets 套件可以在之後串接 Google 試算表。

1
2
install.packages('googlesheets')
library(googlesheets)

連接 Google 帳戶

在安裝套件好之後，第一件事情就是要連接你的 google 帳戶，輸入以下的指令之後，就會開始與帳戶建立一個新的授權，讓你可以透過 R 語言的方式做新增、修改、刪除、查詢等操作。

1
gs_auth(new_user = TRUE)

這個時候會彈出一個視窗開啟瀏覽器，你需要登入你的 google 帳號，以下這個畫面所彈出來的資訊，便是讓你選擇你要登入的帳號。

選好某一個帳戶之後，此時會 google 會向你確認這組套件將會存取 google sheets 的查詢、編輯、建立、刪除所有的雲端應用檔案的授權，若沒什麼問題，就可以按下「允許」。

允許之後，下一個畫面會出現一組授權碼

登入成功之後，你就可以回到 R 語言的環境，這時有顯示一個 Enter authorization code: 要你輸入的驗證碼，就將 google 提供給你的授權碼貼到 R 環境這裏。

1
2
3
4
5
> gs_auth(new_user = TRUE)
Disabling .httr-oauth by renaming to .httr-oauth-SUSPENDED
No token currently in force.
httpuv not installed, defaulting to out-of-band authentication
Enter authorization code:

在你的目錄夾裡面，多了一個 .httr-oauth 檔案

你可以使用 gs_user() 的函數，看看你登入的帳戶資訊

1
gs_user()

登入的帳號資訊如下：

1
2
3
4
5
6
7
> gs_user()
Auto-refreshing stale OAuth token.
          displayName: xxxxxxx
         emailAddress: xxxxxxx@gmail.com
                 date: 2019-04-27 15:51:28 GMT
         permissionId: 1344xxxxxxxxx4887
         rootFolderId: 0AHxxxxxxxxxx9PVA

確認資訊無誤之後，接著就可以做接下來的存取囉

查詢 Google Sheets 試算表

gs_ls() 使用這組函數可以查看帳號內可以使用的試算表

1
2
library(knitr)
kable(gs_ls()[, 1:6])

顯示呈現如下：

sheet_title author perm version updated sheet_key
Amazoncom Inc (AMZN) price history xxxxxx rw new 2019-04-26 21:05:18 1Nx6Yflk-xxxxxxxx47VgbimS
Apple Inc (AAPL) price history xxxxxx rw new 2019-04-26 21:01:33 1c-xxxxxxxx68HWrH9SPJ

讀取 Google Sheets 試算表

就讀取一個檔案來試試，在我們已知 sheet_title 這欄位之後，透過 gs_title() 函數來查詢

1
2
gs <- gs_title('Amazoncom Inc (AMZN) price history')
aws_data <- gs_read(ss=gs, ws = "工作表1", skip=0)

結果如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
> gs <- gs_title('Amazoncom Inc (AMZN) price history')
Sheet successfully identified: "Amazoncom Inc (AMZN) price history"

> aws_data <- gs_read(ss=gs, ws = "工作表1", skip=0)
Accessing worksheet titled '工作表1'.
Downloading: 470 B     Parsed with column specification:
cols(
  `2019-03-08` = col_date(format = ""),
  `Amazoncom Inc` = col_character(),
  AMZN = col_character(),
  `1620.8` = col_double(),
  `https://finance.yahoo.com/q?s=AMZN` = col_character(),
  `2019-03-08_1` = col_date(format = "")
)
Warning message:
Duplicated column names deduplicated: '2019-03-08' => '2019-03-08_1' [6]

如此一來，就可以讀取到試算表的值了。

參考

想要了解更多關於 googlesheets 套件的函數方法，請上查詢 googlesheets: Manage Google Spreadsheets from R 說明文件