從 Google 試算表 讀取資料至 App Inventor
App Inventor

從 Google 試算表 讀取資料至 App Inventor

2020 年 12 月 1 日 / 50 留言

前天介紹了如何將資料寫入至 Google Sheets(使用 App Inventor 將文字欄位資料寫入 Google 試算表)，今天要來介紹如何讀取 Google Sheets 的資料，並顯示在 App Inventor 上。




2022/11/14 更新

做了一個新的 App「雲端留言板」，欄位只有暱稱和留言內容，做法和文章教學一樣！

點我下載


*2021/08/10 更新

因為原本的表單突然不能用了！(可以讀但不能寫)
所以這邊附上最新版的 aia 檔案
更新的部份只有表單網址和試算表網址，其它都保持不動！

點我下載



要讀取 Google Sheets 的資料很簡單，今天提供兩種做法，給各位參考：

  1. 將 Google Sheets 發布到網路，再透過 Inventor 的網路元件執行 Get 請求，取得回應文字。
  2. 透過 Google 的 Visualization API 簡易呼叫法去讀取資料，使用此方法還能下 SQL 語法，非常得好用！

以下就來各別介紹：

一、將 Google Sheets 發布至網路

1. 首先登入 Google 雲端硬碟 開啟 Google 試算表，點選左上角的檔案，選擇「發布到網路」。

選擇發布到網路

選擇第二個「.csv」後按發布


將網址儲存到記事本等等會用到


2. 開啟 App Inventor 專案，新增一個標籤、按鈕和網路元件。

如果 網路1 是拿來做寫入，那就再拉一個 網路2 來做讀取。

功能為當按下按鈕，就將讀取的資料透過標籤顯示。


3. 開始撰寫程式方塊，新增一個 清單變數 datas，當 讀取按鈕 被點選時，要設定 讀取標籤 的文字、設定 網路2 的網址，和呼叫 網路2 執行Get請求。

網路元件的網址，就是剛剛複製到記事本的，Google Sheets 發布的網址。

4. 當讀取到文字時，判斷回應程式碼是否為 200，如果是就代表讀取成功。讀取成功後，要將回應內容指派給變數 datas，但中間要插入「CSV表格轉清單」的方塊，然後再設定標籤的文字為 datas。


這樣即可成功顯示資料


這樣顯示是一個二維陣列的資料，Inventor 的清單(陣列)是用 “[” 和 “]” 包起來的，各個資料間再用逗號隔開，例如：[1, 3, 5, 7, 8] –> 這是一維陣列；但如果各個資料又分別再是清單的話，例如：[[1, 3, 5, 7], [2, 4, 6, 8]] –> 這樣就是二維陣列。

直接顯示清單資料，這樣在閱讀上十分不便，所以我們要再將資料做拆解，使資料能夠更方便我們進行閱讀。


5. 先建立三個變數清單，名稱分別為：帳號ID、使用暱稱和密碼。


6. 接著要來寫副程式(請拖拉紫色的定義程序，取名：拆解資料)；從流程控制拖拉「對於任意清單項目的清單……執行」，右方要放變數清單 datas，這個方塊的功能類似迴圈，清單變數裡有幾項資料它就會執行幾次！每執行一次，會將變數裡的資料給暫存變數「清單項目」。

datas 現在有三筆資料，所以會執行三次。

現在要將資料個別存到剛剛建立的三個清單變數裡，這裡為了方便稱呼，我將試算表的橫欄稱為 “列“，直欄就稱為 “欄“，以 Google Sheets 來說，第一列的資料為：時間戳記、帳號 ID、使用暱稱和密碼；第三欄(C 欄)的資料為：使用暱稱、神鵰大俠和JBNotes。(清單項目的資料就是一列一列的)


7. 拖拉一個增加清單項目和選擇清單中索引為…的方塊；每一列的第二項資料為帳號 ID、第三項資料為使用暱稱、第四項是密碼，依序新增到清單變數裡，如下：


8. 修改當網路元件取得文字的程式碼，設定變數 datas 的底下拖拉一個呼叫拆解資料副程式，再來放一個迴圈方塊，範圍從 1 到變數 datas 的長度，將設定文字的方塊放在裡面。如下：


到這資料已可正常顯示



如果不太了解上面的程式方塊在幹嘛，可以先記著要怎麼做就好！用自己的方式，嘗試不同的拆解法；說不定哪天走在路上哼著歌，就突然想通了！！



二、使用 Visualization API 簡易呼叫法 讀取資料

最近在 Google 查 Google Sheets 和 SQL 的關鍵字時，無意間發現了這個方法，用 Inventor 測試後真的可以顯示資料，最重要的還是可以下 SQL 語法，非常好用。


1. 登入 Google 雲端硬碟 開啟 Google 試算表，複製網址到記事本。右邊點選開啟共用，讓知道連結的使用著可以檢視。


我們需要用到 d/………/edit 之間的試算表代號


2. 複製以下資料到記事本
https://spreadsheets.google.com/tq?

tqx=out:csv
key=填入試算表代號
tq=填入SQL語法

這是網路元件要設定的網址，如下：

https://spreadsheets.google.com/tq?tqx=out:csv&key=填入試算表代號&tq=填入SQL語法


tqx、tq 和 key 之間要用 & 符號連結


3. 開啟 Inventor 專案，新增一個變數，取名為：資料呈現網址，內容為上面的網址資料，一直到 tq= 就好。

用合併文字方便閱讀

4. 將 讀取按鈕被點選時，的方塊裡的網路元件網址，改成變數 資料呈現網址；還要合併網路元件裡的URL編碼方塊，方塊右邊是 SQL 語法。


因為 SQL 語法會用到比較特殊的符號或中文…等等，所以得經過編碼處理。

select B, C, D 是只要取這三個欄項的資料


5. 在網路元件取得文字的方塊裡，將資料結果的文字，設定為回應內容。


這樣就可以讀取到資料了！


6. 這個資料也可以用剛剛的副程式拆解，不過清單項的索引要改成 1、2、3，因為這次沒有時間戳記的資料了！


網路元件取得文字裡的程式，要改成跟剛剛一樣，呼叫副程式、迴圈顯示文字。

至此 整個 App 已製作完成！

如果先讀取後，要再測試寫入和讀取，記得將三個變數清空，清空方式為「設置變數為空清單」即可，否則資料呈現會變成如下所示：


App 完整程式碼：

這裡還包含寫入的程式方塊

App Inventor 專案 aia 檔案下載




App Demo 影片：

下一篇應用教學 –> 將資料使用表格呈現



如果覺得文章內容還不錯的話，麻煩請幫我點個讚！感謝

可以多點幾次喔~~

第一次點讚需使用 Google 或 Facebook 帳號註冊

你也可能喜歡

TQC+ 創意 App 程式設計認證指南 第一類：App Inventor 基礎畫面元件

TQC+ 創意 App 程式設計認證指南 第一類：App Inventor 基礎畫面元件

2021 年 6 月 3 日
TQC+ 創意 App 程式設計認證指南 201 小畫家

TQC+ 創意 App 程式設計認證指南 201 小畫家

2021 年 6 月 14 日
TQC+ 創意 App 程式設計認證指南 309 鍵盤小鋼琴

TQC+ 創意 App 程式設計認證指南 309 鍵盤小鋼琴

2021 年 6 月 29 日

50 則留言

    • JamesBang

      2021 年 3 月 10 日 在 下午 1:29 回覆

      你好，不用重開 App，剛剛測試過連續輸入好幾筆，是可以馬上讀出來的，不過之前我也遇過幾次，資料已經寫進去，卻讀不出來的狀況，不知道是網路還是試算表的問題？但通常我都是等待一段時間後就可以正常輸出了。

          • JamesBang

            2021 年 4 月 23 日 在 下午 12:27

            妳好，讀取的網址我都是用原先的沒有修改，剛剛用新的資料表測試過，抓得到資料，資料表網址 –> 「https://docs.google.com/spreadsheets/d/e/2PACX-1vTNqY5mMt-B8Zbbk81hiK0BVpDOYUJTHS2nJ923mgVvaTWTF90FfO2Kq1xI1OKHh-GrTsTpmUetbpip/pub?output=csv」。

            我順便把 aia 檔案給妳：https://drive.google.com/file/d/10RldLwP0n-AI3p5gLQdSvrgQpWseCchR/view?usp=sharing

  • COKEICEFF

    2021 年 4 月 23 日 在 下午 7:34 回覆

    案子讀取按鈕後跳出以下

    執行錯誤
    Select list item: Attempt to get item number 0, of the list [“時間戳記”, “2021/4/23 上午 7:34:54”, “2021/4/23 上午 7:44:57”, “2021/4/23 上午 7:56:59”, “2021/4/23 下午 7:14:47”]. The minimum valid item number is 1.
    注意： 5秒鐘之內不會再次顯示錯誤訊息。

      • COKEICEFF

        2021 年 4 月 23 日 在 下午 10:01 回覆

        1.被你說重了，我裡面有寫一個偷吃步的FOR迴圈，

        只顯示最近五筆資料的那種，然而我今天的試算表內只有四筆資料，絕對跑錯誤訊息。

        先謝謝了。

        2.我有寫一個HTML的遊戲執行黨都弄好了，我想問除了把檔案放到GOOGLE雲端然後讀取之外

        ，還有哪些國內的免費空間可以使用。(可以執行HTML的免費空間，不單單只是備份)

        • JamesBang

          2021 年 4 月 23 日 在 下午 10:37 回覆

          如果只是單純的 HTML 檔案，其實放在 Google Drive 也可以執行喔！有一個方法可以把雲端硬碟變成網站伺服器，文淵閣的茶米老師有教過，很有趣，給妳參考看看「http://blog.e-happy.com.tw/%e7%b6%b2%e7%ab%99%e6%9e%b6%e8%a8%ad-%e6%89%8b%e6%8a%8a%e6%89%8b%e6%95%99%e4%bd%a0%e6%8a%8a%e7%b6%b2%e7%ab%99%e6%9e%b6%e8%a8%ad%e5%88%b0-google-drive-%e9%9b%b2%e7%ab%af%e7%a9%ba%e9%96%93%e4%b8%8a/」。

          我沒聽過有國內的免費空間，不過國外倒是一堆，像是 000webhost、byethost……等，這兩個都是我用過非常不錯的免費虛擬主機，特別是 000webhost，我現在這個網站是用 hostinger，它的底下就是 000webhost，簡單辦個帳戶就能使用了！會給妳一個網址
          官網：「https://www.000webhost.com/」

  • MOE

    2021 年 6 月 21 日 在 上午 10:22 回覆

    您好，想請問用這樣的方式將資料讀取到APP裡，可以用下拉式選單或輸入的方式去篩選我要的資料嗎？

    例如我想只想要找小明的ID﹑密碼等，能不能在標籤輸入小明（或是下拉式選單選取小明）後，按下讀取，然後只跑出小明的資料呢？

    謝謝您

    • JamesBang

      2021 年 6 月 21 日 在 下午 4:38 回覆

      您好！
      這兩種方法都可以做到，如果是用下拉式選單選擇姓名的話，在網路元件讀取到資料時，就可以先設定下拉式選單的元素字串，各個姓名間用逗號隔開，我有寫一篇「查詢 YouBike 站點資料」，裡面有用到這個方法，可以參考看看！

      在清單的方塊裡，有一個「求對象…在清單…中的位置」方法，利用這個方塊，就可以找到這個人在清單中是第幾號，例如 “小明” 在姓名的清單中是第四號，那麼「ID」和「密碼」，這兩個清單變數的第四項資料，就是小明的，因為當初在新增資料時，都是按照順序的，所以在設定標籤文字那，將「求對象…在清單…中的位置」方法，放到「選擇清單…中索引值為…的清單項」方法的索引值那裡就可以了！

      當然如果對二維陣列熟悉的話，也不用像我這麼麻煩，還把各個資料再存到變數裡！直接用兩個「選擇清單…中索引值為…的清單項」方法就可以輕鬆辦到！

      如果對 SQL 熟悉的話，透過「Visualization API 簡易呼叫法」，其實在一開始就可以只獲取特定人士的資料，而且還不限單一筆，假如小明總共有四筆資料，那這四筆就都能一次抓出來。

      如還有任何問題都歡迎提問！

  • MOE

    2021 年 6 月 23 日 在 下午 3:45 回覆

    您好，我同樣在搜尋時遇到了Select list item: Attempt to get item number 0, of the list [中間省略]. The minimum valid item number is 1.的情況，但我的式子中那邊要出現的應該是您說｛「求對象…在清單…中的位置」方法，放到「選擇清單…中索引值為…的清單項」方法的索引值那裡｝這個部分，不確定是哪裡沒有調整到

    避免我說不清楚，我在下面有附上我正在寫的aia檔案，該式子的位子在Screen3，我希望他呈現出的方式應該是當我選單選定姓名後，第一欄＋選定的那一欄會顯示出來……還是我的清單定義方式錯誤了，不好意思問題有點多！

    https://docs.google.com/spreadsheets/d/1X-XCCeauilNylx2Gu1_JM3FjNoG5SKCc0DWRnE_HoWI/edit#gid=1331193330

    • JamesBang

      2022 年 5 月 5 日 在 下午 1:01 回覆

      使用此方法讀取時，只有少第一直欄的「時間戳記」，所以假設你寫入的資料有二十個項目，那麼修改時就是直接從 1 開始到 20。

      可以使用「迴圈方塊」，範圍從 1 開始，一直到看你有幾個項目，把這個「迴圈方塊」放到「清單迴圈」裡，然後「增加清單項目」的方塊，就拉一個放到「迴圈方塊」裡就好，索引值就設為「迴圈變數」；

      那麼清單空格的部份呢
      以我的為例，我有 帳號ID、暱稱和密碼，我只要把這三個變數再放到一個「清單變數」裡，我就從這個「清單變數」裡抓就好，這個的實作方式有很多。
      (使用二維清單)

      不過最簡單的方式就是一個一個拉出來設定，只是很耗時，如果你有一百個項目，可能會拉到放棄 😂

  • Shrimp

    2022 年 9 月 21 日 在 下午 4:11 回覆

    老師 您好

    　　想請教一個問題，就是我可以用甚麼方式同時讀取及更新某一個試算表?

    　　　例如下表：　　　　　　　
    　　https://docs.google.com/spreadsheets/d/1T4WeZCHxL4m2r4eDZSVqCgQVpuZjN4bt6w9NUTydlhs/edit#gid=0

    　　我想做的是，app每次運行都會下載此表的資料 (A)，並且每當我app操作完得到最新的金額(B) 後上傳，但我想上傳到此表以達到更新目的，下次app運行時，會下載到最新的資料 (B)。

    　　

  • Shrimp

    2022 年 9 月 26 日 在 下午 3:00 回覆

    老師 您好

    　　跟您報告一下，您指導的兩個方法，第一個方法我試過了，是可行的…唯一的缺點是，資料面實際上雖然是馬上更新的，但是用APP抓取資料多多少少會延遲，請看下圖：

    https://imgur.com/a/OT8PUIw

    　　這個問題您的文章有提到過，我之前自己做其他APP抓取資料的時候也有發現，用google sheet當後台的會有這個缺點，影響是不大。

    ———————————-

    　　至於第二個方法，其實我還是不懂原理，為何索引值是填1? 第一個方法原理我懂，其實就是因為我有兩個欄位，實際要抓取金額的在第二個欄位，所以索引值填2，這沒有問題，再來就是為何可以持續抓取最新資料? 原因是因為有先求清單的長度，每次更新後，清單長度就會有變化，清單長度4，在抓取資料的時候索引值2 且長度為4的資料，自然就是最新資料; 不知道我這樣理解正不正確…

    　　其實我本來的打算是11個科目金額各建立一個sheet，各自隨時上傳及抓取資料，但是自己經由你的第一個方法指導之後，其實我可以用一個sheet就好，只要索引值依序加1就好，2、3、4…..

    　　再次感謝老師指導。

    • JamesBang

      2022 年 9 月 26 日 在 下午 5:09 回覆

      你好，其實第二種方法的原理和第一種的一模一樣，差別就在第一種方法會讀取所有欄位的資料，但第二種方法我們可以自己選擇要讀取哪些欄位。

      這兩種方法所讀取到的資料格式都是相同的，都是二維清單，也就是清單裡再放清單，範例如下：
      [[“時間”,”金額”], [“09/26″,”1000”], [“09/27″,”3000”]]

      你的理解大致沒錯！第一個方塊的索引值用「求清單的長度…」，是為了抓到最後一筆資料，因為是二維清單，所以我們抓到的最後一筆資料也是一個清單，以上述範例為例：
      第一個方塊執行後會抓到最後一筆清單資料，如下：
      [“09/27″,”3000”]

      然後你想要顯示的資料，是這個清單的第 2 項的金額，所以我們在第二個方塊的索引值就填 2，最後就會顯示 3000。

      那麼使用第二種方法，因為我只有抓金額欄位的資料，所以讀取到的資料就是：
      [[“金額”], [“1000”], [“3000”]]

      要記住這也是二維清單
      第一個方塊會抓到：
      [“3000”]

      因為這一筆清單只有一個資料，所以我們在第二個方塊的索引填 1，就會抓到 3000。

      —————————————–

      最後……
      如果第一種方法你可以用在專案的話，那麼第二種方法也一定可以，而且第二種更加方便！如果今天你只要抓欄位 B、D 的資料，SQL 語法只要下 select B,D 就可以了！你就不用把所有資料都讀進來再做篩選。

      如果第二種方法熟悉之後，你甚至不需要把所有資料都讀進來再抓最後一筆，你可以在下 SQL 語法時，就直接抓最後一筆資料！！

      • Shrimp

        2022 年 9 月 29 日 在 上午 8:46 回覆

        老師

        　　十分感謝你的指導，我已經成功熟練第一種方法了，第二種方法找時間研究，原本我的專案需求的是11個科目能做到即時顯示最新餘額，現在擴增到專案首頁的公告顯示，請看下圖(兩張)：

        https://imgur.com/a/SLiNdOJ

        　　我的公告最多支援4條，可以新增或刪除，但又不是真正意義上的新增、刪除，套用你教我的方法變通，刪除的話傳空字串上去即可，下載的時候自由會有一條公告是下載到空字串，其實不是作者本來根本看不出來是空字串，跟刪除差不多意思了，哈

發表迴響