如何透過googlesheets4連結R與google試算表

王俞才


你可能跟我一樣,經常透過Google表單來做一些簡單的訪問調查。但在調查期間裡,你的主管或指導老師可能會希望你能每天即時回報最新的調查訪問情形。於是你就必須在Rgoogle兩者間做著以下重複的事情,完成後,再把資料檔或報表存回雲端硬碟裡。

step1. Google表單中轉出試算表
step2. > dataset <- read.csv="" span="">"some_surveydata.csv")
step3. R軟體中進行資料清理與分析的工作
step4. > write.csv(dataset, "surveydata.csv")
step5. 繼續在Google試算表中完成後續的工作

這一連串看似簡單卻繁瑣的工作,一直都沒有什麼工具可以幫助你簡化流程。不過現在googlesheets4套件可以協助你解決這樣的問題!

googlesheets4提供了一個經由Sheets API v4連結Google試算表的界面。透過googlesheets4你可以直接讀取Google試算表中的資料,並且在雲端硬碟裡更新表單[1]。你可能會問:「為什麼要叫googlesheets4?我是不是錯過了googlesheets13呢?」其實並沒有喔!googlesheets4之所以命名為4,只是因為Sheets API為第4版!我們就直接來看googlesheets4是如何進行操作的。

一、                安裝套件

你可以透過CRAN來下載最新釋出的googlesheets4

> install.packages("googlesheets4")
> library(googlesheets4)

或這經由GitHub下載開發版的googlesheets4

> install.packages("devtools")
> devtools::install_github("tidyverse/googlesheets4")

二、                連結你的Google帳戶

在安裝完成後,接著就要連結到你的Google帳戶。輸入下列程式碼後,R就會要求你的Google帳戶授權,讓你可以透過RGoogle表單進行修改或查詢等操作。

> sheets_auth()

此時瀏覽器會跳出一個視窗,請選擇你要使用的帳戶並輸入密碼。



完成後,Google會向你確認是否允許授權給Tidyverse API Packages,以查看、編輯、建立及刪除雲端硬碟中的試算表。若沒有其他問題,按下「允許」即可。


接著,Google會再一次確認你要使用的帳戶及相關授權內容。如果一樣沒問題,按下「允許」即可。


此時,在Rconsole裡會顯示”Enter authorization code”,你只需要把Google給你的那組授權碼複製貼上即可。


三、                讀取Google試算表

read_sheet()googlesheets4套件裡讀取資料的主要指令,目標是讀取Google試算表。就如同readr::read_csv()是用來讀取csv文件,而readxl::read_excel()是用來讀取Excel表格一樣。在googlesheets4套件裡,大部分的指令都是以sheet_為開頭,因此sheets_read()也有同樣的功能。簡單來說,read_sheet()不過就是sheets_read()的別名罷了。

你可利用試算表的URLIDgoogledrive套件所產生的物件dribble來讀取資料,都可以得到相同的結果。

# 利用試算表的URL
> read_sheet("https://docs.google.com/spreadsheets/d/1LSTNRGhsnnXhHVmFRcK_CvoD4W2J5btOM_AcQA2QUWU/edit#gid=273006016")
Reading from 'srda2020'
 
# A tibble: 103 x 6
      id    a1    a2    a3    a4    a5
        
 1     3     2    61     3     2     7
 2    19     9    21    10     1     6
 3    42     2    61     3     2     3
 4    91     2    59     3     2     4
 5   112     1    51     4     3     1
 6   123     2    46    13     2    10
 7   246     1    36     9     2     5
 8   363     1    37    13     2     1
 9   415     2    33     7     2     4
10   415     2    33     7     2     4
# ... with 93 more rows
 
# 利用試算表的ID
> read_sheet("1LSTNRGhsnnXhHVmFRcK_CvoD4W2J5btOM_AcQA2QUWU")
Reading from 'srda2020'
 
# A tibble: 103 x 6
      id    a1    a2    a3    a4    a5
        
 1     3     2    61     3     2     7
 2    19     9    21    10     1     6
 3    42     2    61     3     2     3
 4    91     2    59     3     2     4
 5   112     1    51     4     3     1
 6   123     2    46    13     2    10
 7   246     1    36     9     2     5
 8   363     1    37    13     2     1
 9   415     2    33     7     2     4
10   415     2    33     7     2     4
# ... with 93 more rows
 
# 利用物件dribble
> library(googledrive)
> drive_get("srda2020")%>%
+ sheets_read()
Reading from 'srda2020'
 
# A tibble: 103 x 6
      id    a1    a2    a3    a4    a5
        
 1     3     2    61     3     2     7
 2    19     9    21    10     1     6
 3    42     2    61     3     2     3
 4    91     2    59     3     2     4
 5   112     1    51     4     3     1
 6   123     2    46    13     2    10
 7   246     1    36     9     2     5
 8   363     1    37    13     2     1
 9   415     2    33     7     2     4
10   415     2    33     7     2     4
# ... with 93 more rows

至於試算表的URLID該如何取的呢?你只要在瀏覽器中打開試算表,就可以在網址列當中取得URL。以上述為例,該試算表的URL即為:
https://docs.google.com/spreadsheets/d/1LSTNRGhsnnXhHVmFRcK_CvoD4W2J5btOM_AcQA2QUWU/edit#gid=273006016
這裡要提醒大家,這串URL包含了試算表(spread Sheet) ID工作表(work sheet)ID,我們使用的是試算表ID。如果你的試算表包含了2個以上的工作表,那麼你可以透過參數sheet來選取要讀取的工作表。

> read_sheet("1LSTNRGhsnnXhHVmFRcK_CvoD4W2J5btOM_AcQA2QUWU", sheet=2)
Reading from 'srda2020'
Range "'srda2020_2'"
 
# A tibble: 103 x 6
      id    b6    b7    b8    b9   b10
        
 1     3     2     2     5     7     9
 2    19     9     9     1     1     3
 3    42     9     9     5     2     3
 4    91     2     1     5     6     9
 5   112     9     9     5     7     9
 6   123     2     1     5     4     3
 7   246     2     1     2     7     9
 8   363     2     1     1     1     3
 9   415     9     9     3     6     9
10   415     9     9     3     6     9
# ... with 93 more rows
 
> read_sheet("1LSTNRGhsnnXhHVmFRcK_CvoD4W2J5btOM_AcQA2QUWU",
               sheet= "srda2020_2" )
Reading from 'srda2020'
Range "'srda2020_2'"
 
# A tibble: 103 x 6
      id    b6    b7    b8    b9   b10
        
 1     3     2     2     5     7     9
 2    19     9     9     1     1     3
 3    42     9     9     5     2     3
 4    91     2     1     5     6     9
 5   112     9     9     5     7     9
 6   123     2     1     5     4     3
 7   246     2     1     2     7     9
 8   363     2     1     1     1     3
 9   415     9     9     3     6     9
10   415     9     9     3     6     9
# ... with 93 more rows

四、                合併googledrive套件一起用

為什麼要建議你同時使用googlesheets4googledrive套件呢?文章開頭有提到,googlesheets4是透過Sheets API v4.來讀取Google試算表中的資料。但Sheets API比較著重在試算表的資料內容及其詮釋資料的操作,無法提供文件級的操作,例如:刪除、複製或重新命名試算表等。因此,我們就必須透過Drive API來處理,而googledrive套件是目前最好的選擇。

大家是否有發現,我們在利用googlesheets4讀取試算表的資料時,是利用試算表的IDURL也是由ID所組成的),而不是名。這是因為該API只接受試算表的ID。但googledrive套件就比較人性化,接受檔名及ID。這也是為什麼建議你合併googledrive套件一起用的另一個原因。

最後,舉一個例子來幫助大家理解googledrive套件的應用。首先是下載及載入套件。

> install.packages("googledrive")
> library(googlesheets4)
> library(googledrive)

接著是請求Google帳戶授權。

> drive_auth()

完成授權後,我們就可以利用googledrive套件裡的函數,例如:drive_find()來檢視或搜尋google雲端硬碟中所有檔案,並利用drive_get()來啟動檔案,最後再透過googlesheets4將資料載入即可。

> drive_find("srda")
Items so far: 
172 
# A tibble: 2 x 3
  name        id                                                drive_resource   
*                                                               
1 srda2020    1LSTNRGhsnnXhHVmFRcK_CvoD4W2J5btOM_AcQA2QUWU 
2 srda2.csv   1_l9cyf5u5CJojvhxTOG2MFCLYYwr4tja             
 
> ss <- drive_get="" span="">"srda2020")
> sheets_get(ss)
Auto-refreshing stale OAuth token.
  Spreadsheet name: srda2020
                ID: 1LSTNRGhsnnXhHVmFRcK_CvoD4W2J5btOM_AcQA2QUWU
            Locale: zh_TW
         Time zone: America/Los_Angeles
       # of sheets: 2
 
(Sheet name): (Nominal extent in rows x columns)
  srda2020_1: 104 x 6
  srda2020_2: 104 x 6
 
> read_sheet(ss)
Reading from 'srda2020'
 
# A tibble: 103 x 6
      id    a1    a2    a3    a4    a5
        
 1     3     2    61     3     2     7
 2    19     9    21    10     1     6
 3    42     2    61     3     2     3
 4    91     2    59     3     2     4
 5   112     1    51     4     3     1
 6   123     2    46    13     2    10
 7   246     1    36     9     2     5
 8   363     1    37    13     2     1
 9   415     2    33     7     2     4
10   415     2    33     7     2     4
# ... with 93 more rows

在完成資料整理及分析後,最後一個步驟就是利用drive_upload()將本機檔案夾裡的資料檔上傳到Google雲端硬碟。

> write.csv(srda2020, "surveydat.csv")
> drive_upload("surveydat.csv ", "uploadname.csv")

其他函數的用法,可以參考下方參考文獻的內容。同時也再次提醒大家,在做資料整理或分析的過程當中,記得要先保留一份原始檔,以防資料修改錯誤導致無法回復喔!

五、                參考文獻

1.          googlesheets4. (2020-06-01). https://googlesheets4.tidyverse.org/index.html.
2.          googledrive. (2020-06-01). https://googledrive.tidyverse.org/index.html.



[1] 部分功能仍處於開發階段,必須使用「開發版」的套件才能使用。

留言

張貼留言

這個網誌中的熱門文章

使用Python進行資料整理 – 初探Pandas

你看的是資料視覺化還是資訊圖表?—資料視覺化工具介紹

資料整理與檢誤經驗談—以SPSS程式進行邏輯檢查