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

-
王俞才


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

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？我是不是錯過了googlesheets1到3呢？」其實並沒有喔！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帳戶授權，讓你可以透過R對Google表單進行修改或查詢等操作。

> sheets_auth()

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



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


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


此時，在R的console裡會顯示”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()的別名罷了。

你可利用試算表的URL、ID或googledrive套件所產生的物件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

至於試算表的URL或ID該如何取的呢？你只要在瀏覽器中打開試算表，就可以在網址列當中取得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套件一起用

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

大家是否有發現，我們在利用googlesheets4讀取試算表的資料時，是利用試算表的ID（URL也是由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] 部分功能仍處於開發階段，必須使用「開發版」的套件才能使用。

留言

張貼留言

這個網誌中的熱門文章

SAS、SPSS、STATA 統計軟體檔案格式轉換介紹

-
圖片
陳家玉 [原文刊載於SRDA學術調查研究資料庫通訊第41期，2012.6] 一、   前言 市面上統計套裝軟體不勝枚舉，目前最常被使用的仍以 SAS 、 SPSS 、 STATA 為主流且各有所長。當好不容易申請或取得的資料檔，並非平時熟悉的統計軟體檔案格式時，如何將檔案進行讀取或轉換，是多數人常面臨到的問題。統計軟體檔案格式的轉換，除了利用統計套裝軟體內建的匯入及匯出功能外， Stat/Transfer 是另一個快速且便利的檔案格式轉換工具。本文將統計套裝軟體的匯入 / 匯出功能分為二部分介紹：第一部分以各軟體資料檔間的轉換為主；第二部分介紹匯入 / 匯出最通用的純文字檔及普遍用於數據資料整理的 EXCEL 檔，於下以 SAS 9.2 版、 SPSS 20 版、 STATA 12.1 版逐步分述之。 Stat/Transfer 統計數據轉換軟體則針對介面功能操作進行介紹。 二、   匯入 / 匯出統計軟體資料檔功能 在說明如何轉檔前，先瞭解此三種軟體資料檔的差異性（表 1 ），其中 SPSS 、 STATA 資料檔可以包含變項說明（ variable label ）與選項數值說明（ value label ）兩者，但 SAS 資料檔僅包含變項說明，選項數值說明則是另以格式檔儲存，需套用 format 指令才能取得。 表 1 ： SAS 、 SPSS 、 STATA 資料檔之差異   軟體 差異性 SAS SPSS STATA 說   明 資料與格式分別儲存 資料與格式儲存在同檔案中 資料與格式儲存在同檔案中 資料檔 *.sas7bdat *.sav *.dta 格式檔 *.sas7bcat 數據資料因受限於統計軟體資料檔支援性的問題，較常以純文字檔，搭配語法（ syntax ）撰寫進行檔案讀取。隨著套裝軟體視窗版的不斷發展、更新且越具易用性，可支援多種檔案類型的匯入與匯出，使得程式語法不再是絕對必要，讓使用者省去自行撰寫程式的麻煩，能快速又方便地讀取或轉換資料。 （一）   ...
閱讀完整內容

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

-
圖片
李孟諵 資料整理是資料分析和建模前的準備工作，包括資料的讀取、檢核、修改、重新編碼和合併等。市場上有不少專為量化研究所設計的商業軟體，例如： SPSS 、 SAS 和 Stata 等，都能進行這項任務，但共通問題是價格所貲不菲。因此，有些人轉向使用開源軟體，其中，具資料整理功能的代表軟體就是「 R 」。 近年來，隨著資料科學 [1] （ Data Science ）一詞廣泛被討論，另一套追求開源精神的程式語言 Python ，因第三方函式庫 [2] （ library ） Pandas 日漸成熟，使其在資料整理的功能上也逐漸受到關注。因此，為了使大家更容易了解 Pandas 在調查資料的運用，本文以一個符合調查資料結構的小資料表，藉由調查資料的整理過程，來介紹所使用的函式以及說明程式碼和輸出結果。 一、 Python 簡介 Python 一個開源、簡潔、易讀的程式語言──由 Guido van Rossum 創建，於 1991 年首次發布，設計哲學為「優雅」、「明確」、「簡單」，其直譯式 [3] 、易擴充和跨平台的特性，加上豐富多元的第三方函式庫和社群，使其在各種程式應用上都能看見它的身影，例如：網頁開發、系統管理、資料處理、科學計算，以及近年熱門的機器學習和深度學習等。在近期的 TIOBE 指數 [4] 和 KDnuggets 調查 [5] 都有不錯的熱門度。 二、 Python 環境安裝 由於跨平台的特性， Windows 、 Mac 和 Linux 系統都能安裝。一般從 Python 原生環境加上所需的函式庫到整合開發環境（ Integrated Development Environment ，簡稱 IDE ） [6] ，對初學者來說安裝就是一個挑戰，因此建議直接下載「 Anaconda 」（號稱 Python 資料科學懶人包）來安裝，優點是安裝簡單，只需點選「下一步」即可，缺點是有些函式庫可能用不到，會佔據太多磁碟空間。安裝步驟可參考 官網 說明 ，惟補充兩個安裝時會遇到的問題（以 Windows 為例），第一個是環境變數（ PATH environment variable ）的選擇（如圖一），主要作用在於透過命令提示字元（ cmd.exe ）呼叫 Python 或相關程式時，是否需指引到絕對路徑。 Anacond...
閱讀完整內容

資料整理與檢誤經驗談—以SPSS程式進行不合理值檢誤

-
圖片
蘇婉雯 [原文刊載於SRDA學術調查研究資料庫通訊第2期，2002.8] 數據資料檔的建立，必須包括資料檢誤的工作才能算完成。嚴謹的資料檢誤，可以增進資訊的正確性以及資料的可用性。一般來說，資料發生錯誤的來源可能來自於訪員、督導、過錄者或鍵入資料者。不過，有些時候只是程式語法錯誤，而造成資料錯誤的假象。基本的資料檢誤工作包括：不合理值檢誤及邏輯檢誤兩種。本期以 SPSS 統計軟體介紹不合理值的檢誤工作。 大部份變項都有其合理的值域或分佈，而超出這些值域、或落在合理分佈以外的觀察值，往往起因於建檔過程中發生的錯誤。一般而言，界外值 (Outlier) 亦屬於不合理值。茲將整理工作內容說明於下： 一、類別變項 (Categorical Variables) 類別變項的合理數值應該是幾個固定的類別代碼，包含研究者設計的「跳答碼」、「遺漏值代碼」等。如果類別變項的資料中有任何數值非屬這些固定代碼，即為不合理值。研究者應該查明這些不合理值的來源，並做適當的修改或處理。 例 1 ：【原始問卷】如下： 3.   請問您父親的籍貫是 □   1. 本省閩南人 □   2. 本省客家人 □   3. 大陸各省市 □   4. 原住民 □   5. 其他 表 1-1 【次數分配結果】如下：   從上面的次數分配結果表中，不容易發現是否有不合理值存在，必須核對問卷或過錄編碼簿之內容才能得知。如果能在資料檔或程式中補上選項數值說明 (value label) ，則能夠輕易的找出不合理值。 【自撰語法】 - 選項數值說明 value label   a3   1 " 本省閩南人 "           2 " 本省客家人 "         3 " 大陸各省市 "         4 " 原住民 "            5 ...
閱讀完整內容