如何自動化更新Google Data Studio報表的資料來源為CSV的檔案?

先回答文章標題, 無法達成, 不過我們可以利用其他方式來實際這個需求, 就是利用另外的 Data Connector, 讓我們繼續看下去吧:

之前的COVID-19 Taiwan統計-使用Google Data Studio (https://diary.tw/archives/2207)介紹了使用Google Data Studio來展示每日的COVID-19的個案數量, 十分方便好用, 由於是一次性上傳CSV檔案, 但這個資料是每日更新的, 所以若是有需要自動化這個更新, 要如何進行呢?

在Google Data Studio中, 資料的連接器共有以下這些:

https://datastudio.google.com/data

之前使用的是 File Upload (Connect to CSV files), 由於是自行上傳的檔案, 更新也得再次上傳才能達成, 所以資料是無法自動化更新(當然也和這個File Upload連接器沒有API所以無法進行).

然而要如何達成這個自動化更新的資料來源呢? 上面連結的連結器有許多是資料庫的, 如: MySQL, PostgreSQL, Microsoft SQL Server 等, 還有許多 Google 自己的資料來源, 其中有一項可以利用的是 Google Sheets (也就是 Google Drive 中的像 Excel 的表格資料檔案).

這樣一來, 只需要更新 Google Sheets 的內容, 就可以順利更新 Google Data Studio 中的報表資料了, 所以這次我們就選用這個資料連接器來進行操作囉.

一樣從前一篇的資料來源開始:

https://od.cdc.gov.tw/eic/Day_Confirmation_Age_County_Gender_19CoV.csv

下載後, 於 Google Drive 中上傳這個檔案, 你可以放在任何位置(目錄), 完成後, 對該CSV檔按[右鍵], [選擇開啟工具], 使用 [Google試算表] 開啟這個檔案, Google Sheets 會自動建立一個新的 Google Sheets, 並將檔案匯入. (原CSV檔案仍會存在不會變動, 但我們之後要操作與當做資料來源的是這個產生的 Google Sheets).

上圖會看到該CSV檔已順利轉入Google Sheets中了, 不過有個年齡層的資料有筆 10-14 (10-14歲)的部分, 會自動轉成日期的2022/10/14 這個部分需要處理, 但之後會由程式處理, 這裡先不管它, 我們將上面的試算表名稱先改為自己方便記得的檔名, 當然你用原來的名稱亦可, 這裡先改為 my-covid-19-data.

接下來我們先進行報表的製作, 操作步驟和之前的方式一樣, 只是在選擇資料來源時, 我們就可以選用Google Sheets了, 並指定到這個檔案即可.

先到 Google Data Studio 首頁, 選擇建立資料來源:

然後選用 [Google 試算表]:

找到你剛建立的 Google Sheets:

(若一時找不到, 也可以利用左邊的從Google雲端硬碟開啟來進行亦可), 在這裡就沒有上傳或建立按鈕可點, 取而代之的是右上有個[連結], 點下去, 就會出現如下畫面:

這樣資料來源就建立完成了, 之後可以直接利用右上的建立報表來進行後續的報表建立囉, 動作就與之前一樣(https://diary.tw/archives/2207), 不再詳述.

不過到這裡只是將原本的資料來源由上傳CSV檔案改為Google Sheets, 還未能自動更新, 接下來要介紹的是 APP Script 這個程式工具可以用來自動化這個自動更新的作業, 我們快速地來看一下我們會用到的工具, 第一個部分是 UrlFetchAPP:

https://developers.google.com/apps-script/reference/url-fetch/url-fetch-app

是用來下載外部的資料來源用的, 第二個部分是 Google Sheets Servie:

https://developers.google.com/apps-script/reference/spreadsheet

我們藉由這兩個服務來進行開發, 就可以完成達成自動化下載更新Google Sheets囉.

接下來我們先取得我們在之前建立的Google Sheets FileId, 其實在URL上就可以取得, 回到前面的 my-covid-19-data 檔案開啟:

畫面上 URL:  https://docs.google.com/spreadsheets/d/1nMg97jcZhbVEV_f9PyMcTSOpdzS7lYtojU8RW3NnkfM/edit#gid=1354450251 的 1nMg97jcZhbVEV_f9PyMcTSOpdzS7lYtojU8RW3NnkfM 就是FileId, 先記下來.

然後我們到 Google APP Script 開始寫我們的程式囉:

https://script.google.com/

點[新專案], 將專案命名為 my automation csv to sheets:

程式碼如下, 記得將 fildId 改為你的 fileId 哦:

function myFunction() {
  const DataSourceUrl = 'https://od.cdc.gov.tw/eic/Day_Confirmation_Age_County_Gender_19CoV.csv';
  const fileId = '1nMg97jcZhbVEV_f9PyMcTSOpdzS7lYtojU8RW3NnkfM';

  // get sheet and clear data
  const objSpreadSheet = SpreadsheetApp.openById(fileId);
  let sheet = objSpreadSheet.getSheets()[0];
  sheet.getRange(2,1,sheet.getLastRow(),8).clear();
  
  // get data from source url
  var response = UrlFetchApp.fetch(DataSourceUrl);
  let contentText = response.getContentText();

  // process data
  let lines = contentText.split('\n');
  let arrayLines = [];
  for(var i=1;i<lines.length-1;i++){
    let arrayLine = lines[i].split(',');
    arrayLine[6] = '\'' + arrayLine[6];
    arrayLines.push(arrayLine);
  }
  
  // set data
  sheet.getRange(sheet.getLastRow()+1,1,arrayLines.length,8).setValues(arrayLines);
}

然後點選[執行], 會出現授權的需求如下, 我們逐一來看:

點[審查權限],

選擇剛建立Google Sheets的帳戶,

會需要授權, 點[進階],

點[前往 my automation csv to sheets (不安全)],

這裡會需要提供兩個權限, 一個是 Google 試算表的查看, 編輯, 建立, 刪除權, 另一個是連線至外部服務(也就是download外部檔案)的權限, 點[允許], 程式就會開始執行, 等約5~10秒, 應該就會執行完成囉.

再回頭說明程式的部分, DataSourceUrl 為原始 CSV檔案的連結, fileId 為你的 Google Sheets 檔案的 fileId, 程式碼第6~8行則為取得 Google Sheets 中的第一個 sheet, 並將內容從第2個row開始到最後一個row清空(因為第一個row為title不用再清空了).

程式碼第11~12行則為下載 CSV檔, 並將內容存放在 contextText 變數中.

程式碼第15行將下載的CSV內容, 利用斷行(\n)切開為一個字串陣列 lines, 第16行宣告一個陣列變數 arrayLines , 用來存放要更新至 Google Sheets 中的資料陣列.

程式碼17~21為重要的部分, 將lines逐行進行處理, 其中18行將各單筆資料再利用逗號(,)進行資料切割, 存入 arrayLine 變數, 這時這個 arrayLine 內容如下:

['嚴重特殊傳染性肺炎','2020/01/22','空值','空值','女','是','55-59','1']

記得之前提到第七個欄位年齡層中有個10-14會異常, 被自動解析為2022/10/14, 所以針對第七個欄位, 會利用程式碼19行中, 將該欄位值多增加一個單引號( ‘ ) 在前面, 這樣上面的資料就會變為:

['嚴重特殊傳染性肺炎','2020/01/22','空值','空值','女','是','\'55-59','1']

這個 \’ 為 escape (因為在單引號字串中), 也就是實際的資料為 ’55-59, 讓 Google Sheets填入時, 強迫解析為字串.

所以執行完程式碼第17~21後, 我們就會有個 arrayLines (二維陣列)存放如下資料:

[
['嚴重特殊傳染性肺炎','2020/01/22','空值','空值','女','是','\'55-59','1'],
['嚴重特殊傳染性肺炎','2020/01/24','空值','空值','女','是','\'50-54','1'],
...
['嚴重特殊傳染性肺炎','2022/04/16','空值','空值','男','是','\'50-54','1'],
['嚴重特殊傳染性肺炎','2022/04/16','南投縣','南投市','女','否','\'60-64','1'],
]

接下來的程式碼第24行, 就一口氣將所需要的資料欄位用 getRange() 長x寬取出, 並利用setValue() 將值一次寫入即可.

這樣就是將資料由線上的 CSV 寫入自己的 Google Sheets 中囉, 我們還需要定義一下自動執行的時間, 這時候會用到 APP Script 介面上右邊有個時鐘符號的觸發條件:

進入後, 點擊右下角的[新增觸發條件], 並設定[選取時間型觸發條件類型]為[日計時器], 設定[選取時段]為[下午3點至下午4點]:

並儲存起來就可以了, 接下來就是每日會自動作業囉, 檢查執行記錄可由觸發器下的[執行項目]來調閱執行記錄.

為什麼選擇每日下午3點到4點, 是因為通常該CSV檔是在記者會後會上傳至系統, 我們在該時間執行應該是最即時的資料囉.

大家可以試看看, 並且也想看看還有什麼資料集可以利用這樣的方式來進行自動化更新 Google Data Studio 的報表資料, 這將會是十分有用的工具呢.

參考資料:

https://developers.google.com/apps-script/samples/automations/import-csv-sheets

 

[2022/7/26 15:57] 由於目前資料量過大, 所以會無法順利跑完, 已知的狀況為 Google Spreadsheet 中有兩個限制, 1. 為資料筆數上限為 100萬筆, 2. 為資料格數上限為 1000萬格. 也請參考這裡的限制說明: https://support.google.com/drive/answer/37603