用 Google Apps Script 取得 Google Sheets / Excel 資料

/

未發佈到網路也能取得資料

之前寫過一篇「如何用 Google Sheets / Excel 當作資料庫」,知道 Google Sheets 的 id,並且有發佈到網路的狀況下,可以直接用網址取得這份 Google Sheets 的 JSON。

那一篇適合的情境,通常是那個 Google Sheets 是我們自己所擁有,可以執行發佈到網路這個步驟。

那如果要抓的是別人 Google Sheets 的資料呢?

最近新冠肺炎的消息飛來飛去,下了班不太想去人太多的地方,就只好躲在家裡追劇寫 code,又看到有一個站整理了確診之類的資料:Coronavirus 2019-nCoV,想說不然結合之前寫的 Google Maps API,來做個熱圖(Heat Map)。

然後,在第一步取得資料悲劇就發生了,Augustus 無法直接用網址的方式取得 Google Sheets 裡的資料,因為對方沒有選擇發佈到網路上,只有單純的檢視權限。

幸好這個時代有偉大的 Google,爬了一下文後,找到了另一種取得 Google Sheets 資料的方法,就是用 Google Apps Script 的 API 來取得。(Google Apps Script 以下簡稱 GAS、Google Sheets 以下簡稱 Sheet)


取得資料的事前工作

在用 GAS 取得 Sheet 的資料前,有兩樣東西必備,一個就是要有一份 Sheet,一個就是要有一個 GAS……嗯,寫完這句後有點在講廢話的感覺。

Sheet:取得 id、設定共用

Sheet 的 id,直接從網址上看,我們進到 Sheet 的頁面後,網址會像這樣:

https://docs.google.com/spreadsheets/d/abc1234567/edit#gid=0

Sheet 的 id 就是:abc1234567

這是廣告,點擊一下可以幫本站多個一點點的廣告收入,謝謝

像新冠肺炎 Coronavirus 2019-nCoV 上面寫的資料來源是這個:

https://docs.google.com/spreadsheets/d/1UF2pSkFTURko2OvfHWWlFpDFAr1UxCBA4JLwlSP6KFo/htmlview?usp=sharing&sle=true#

id 就是:1UF2pSkFTURko2OvfHWWlFpDFAr1UxCBA4JLwlSP6KFo

2020.02.11補 充:根劇這個 Sheet 的公告,他們的資料將會更新在 GitHub 上,Sheet 就不再更新了,因此這個 id 的更新日期只到 2/10。本篇單純成為如何用 GAS 取得 Sheet 資料的筆記。

設定共用也很簡單,先按下右上角的「共用」:

按下右上角的「共用」
按下右上角的「共用」

會出現一個燈箱,按下右上角的「開啟連結共用設定」:

按下右上角的「開啟連結共用設定」
按下右上角的「開啟連結共用設定」

會看到燈箱變成一段分享用的網址,就代表共用設定成功:

共用設定成功
共用設定成功

這個步驟一定要執行,一定要設定成可以被檢視,不然就會拿不到資料。

Coronavirus 2019-nCoV 的資料來源我們點開來是看得到資料的,就代表的共用設定是設成可以檢視。

GAS 要部署成網路網路應用程式

GAS 要當成 API 來用,之後才能回傳抓到的 Sheet 資料,這部份直接看之前寫過的筆記文:

這是廣告,點擊一下可以幫本站多個一點點的廣告收入,謝謝

Google Apps Script 基本使用:跨網域 AJAX、接 Firebase


GAS 上的程式碼部份

一開始是搜尋 GAS sheet 的說明文件,看到官方說明長這樣:

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

可以看到每種 type 又是一個文件的連結,整個加起來就要比萬里長城還長了。

還好,我們這邊只需要做取資料的動作,之前在寫 GAS 接 Firebase 時,看到的 說明文件 就有提供,只需要以下程式就可以取得 Sheet 的資料:

發佈後用 Postman 看有沒有取得 Sheet 資料:

用postman取得Sheet資料
用 Postman 取得 Sheet 資料(點擊看原圖)

可以看到成功取得資料了,而且原 Sheet 裡的一行就是一個陣列,知道這個規則後,可以在 GAS 回傳資料前先做個整理。

因為之後會拿來接 Google Maps API,在整理時也要保留經緯度,用一個 for 迴圈來整理需要的資料:

2020.02.10 補充:今天發現對方的 Sheet 改了欄位,移掉了原本的第三欄,因此取得的欄位實際上有做修改,在此補充。

再次發佈後,用 Postman 就可以看到整理過的資料:

整理過的資料
整理過的資料(點擊看原圖)

以上,就是用 GAS 取得 Sheet 資料的方式,完整程式碼如下:

最後拿資料畫在 Google Map 的部份寫在這篇:畫新冠肺炎分佈圖

Summary
用 Google Apps Script 取得 Google Sheets / Excel 資料
Article Name
用 Google Apps Script 取得 Google Sheets / Excel 資料
Description
本篇大綱:未發佈到網路也能取得資料。取得資料的事前工作。Sheet:取得 id、設定共用。GAS 要部署成網路網路應用程式。GAS 上的程式碼部份。一種取得 Google Sheets 資料的方法,就是用 Google Apps Script 的 API 來取得。
Augustus
Let's Write
Let's Write
https://letswrite.tw/wp-content/uploads/2020/08/logo_512.jpg

隨選筆記文

PWA

PWA 學習筆記 – 4:manifest.json

API Front-End

用 Google Apps Script 寫一個 LINE 登入功能:下篇 – 三大步驟

Front-End Vue

用 Vue.js 製作圖片版 EDM 生成器

Firebase Google

Firebase Dynamic Links API 做一個自己的縮網址生成器

Google Others

用 reCAPTCHA v3 來做非機器人驗證

Front-End

如何用 Netlify CMS 製作電子報生成器

Firebase Google

Firebase Authentication 第三方登入 – GitHub

Front-End

一些有趣的 CSS 選擇器

Bot Telegram

Telegram Login Widget,製作用 Telegram 登入功能

Google Others

Google Material Design 表單標籤效果

以下是留言,但關於留言的部份必需先讓你們知道:

本站的文章都是 Augustus 因為覺得有趣,才會實作並整理成筆記文而後進行發表。

如果留言是希望把 Demo 改成「你想要」的樣子,或是把功能改成「符合你需求」的樣子,

Sorry~ 除非那修改是 Augustus 也有興趣的,不然不會幫你們寫程式去面對工作或是交作業。

未來這類的留言不會再主動回覆。😎

另外,公開信箱是為了讓金流驗證用,

因為之前遇過幾次回信協助解決問題後,對方卻一聲謝謝也沒有,就這樣拿去幫工作交差。

因此決定不再回覆信件,有疑問就利用留言功能囉。

訂閱
通知
guest
0 Comments
Inline Feedbacks
看所有留言