用 Google Sheets 紀錄表單資料

/

本篇要解決的問題

之前 August 寫過一篇〈完全客製 Google 表單,美化表單樣式〉,主要是用 Google Forms 把資料寫入到 Google Sheets 中。最近因為接手了一個要把抓下來的資料寫入到 Google Sheets 的需求,但需要先清空 Sheets 原有的資料後,再覆蓋新資料上去,用 Google Forms 的方式就不適合。

看了一下文件,只需要透過 Google Apps Script 的簡單幾行程式碼,就可以把資料直接寫入到 Google Sheets 中,因此整理成這篇,紀錄一下作法。


製作一個表單頁面

August 用 Simple.css 作了一個簡單的表單頁,這個頁面也是本篇的 Demo:https://letswritetw.github.io/letswrite-sheets-post-data/

製作一個表單
製作一個表單

一共四題,在製作頁面的時候,就要把題目的順序給記下來,下一步建立 Google Sheets 時的表頭就要按順序填入。


建立一個 Google Sheets 檔案,開啟 GAS 功能

進到 Google Drive 後新增一個 Google Sheets 的檔案,表頭的部份就照我們表單題目的順序填上:

建立 Google Sheets
建立 Google Sheets

第一個「填表時間」並不在我們表單頁的題目裡,因為要使用者自己填時間有點怪,這題會在送出資料時用 JS 把當下時間給計算出來,一併送出。

有了 Google Sheets,我們接著就是要開啟 Google Apps Script 的功能,之後才能把收到的資料寫入到 Google Sheets 中。

點擊上排導覽列的「擴充功能 > Apps Script」:

點擊 擴充功能 > Apps Script
點擊 擴充功能 > Apps Script

開啟了 Apps Script 的檔案後,把檔名取好,刪掉裡面原有的對 function myFunction,存檔:

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

建立 Apps Script 檔案
建立 Apps Script 檔案

Google Apps Script 的使用說明,之前有寫過筆記文了,請大家直接點擊連結觀看,本篇不會說明太多:

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

GAS 收到 POST 後把資料寫進試算表

要用 Google Apps Script 寫入資料到 Google Sheets 真的非常簡單,官方文件 上就寫了三行:

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];
sheet.appendRow(["a man", "a plan", "panama"]);

SpreadsheetApp.getActiveSpreadsheet():返回當前的 Google Sheets,由於我們是直接在 Google Sheets 開啟的 Apps Script,因此這邊返回的就是我們剛剛建立的 Google Sheets。

ss.getSheets()[0]:返回第幾張試算表,[0] 就是第一張。

sheet.appendRow([xxx, xxx, xxx]):這行就是把資料寫到指定的試算表上,要給的值是陣列。

把這段範例程式碼,修改成我們需要的,最後如下:

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("工作表1");
function doPost(e) {
  var param = e.parameter;
  var today = param.today;
  var nickname = param.nickname;
  var toy = param.toy;
  var user = param.user;
  var subscribe = param.subscribe;
  sheet.appendRow([today, nickname, toy, user, subscribe]);
  return ContentService.createTextOutput('success');
}

ss.getSheetByName("工作表1"):這邊 August 改成直接給試算表的名稱,來抓要寫到哪張試算表上,因為通常我們開了 Google Sheets 後,會有很多人進來協作,如果有狂人移動了試算表的順序,資料就 GG 了。(如果連表單名稱都亂給,請賜他飛踢乙次)

doPost:這邊就是當 Apps Script 收到 POST 時要執行什麼事情,裡面的內容就是把收到的參數取出來,寫進試算表,最後返回「success」這段文字。

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

清空試算表

開頭有說,August 除了寫入資料,還得先把試算表上的資料給清除,清空試算表的 function 如下:

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("工作表1");
sheet.clear();

清試算表的動作,August 是寫在 doGet 中,跟 doPost 分開來,並且清空後再 appendRaw 表頭進去。

部署 Apps Script,取得應用程式 URL

程式碼完成後,我們要部署出去,才可以對外使用。

點擊右上角的「部署 > 新增部署作業」:

點擊 部署 > 新增部署作業
點擊 部署 > 新增部署作業

新增部署作業的視窗,點擊左上角的齒輪,選擇「網頁應用程式」:

齒輪 > 網頁應用程式
齒輪 > 網頁應用程式

網頁應用程式的視窗裡,「說明」的部份就填寫自己看得懂的說明,「執行身分」選「我」,「誰可以存取」選「所有人」:

網頁應用程式設定
網頁應用程式設定

都寫好選好後按下部署,後續會有一系列的視窗跟我們要授權:

點擊 授予存取權
點擊 授予存取權
點擊左下角 進階
點擊左下角 進階
點擊左下角 前往XXX
點擊左下角 前往XXX

因為這段 Apps Script 的程式碼是我們寫上去的,所以不用擔心安全問題,但如果未來大家使用的是別人寫的程式碼,就要注意裡面是寫了什麼內容。

點擊 允許
點擊 允許

點擊允許後,就會看到我們這個 Apps Script 的對外網址:

取得 網頁應用程式 網址
取得 網頁應用程式 網址

我們把「網頁應用程式」的網址給複製下來,這個網址就是我們表單頁上,送出資料時的 URL。


表單頁,送出資料程式碼

最後,我們只需要把表單頁上各個欄位的資料,POST 到我們上一步取得的網頁應用程式網址上,本篇就完成了~

這邊 August 直接用 Vue.js,抓欄位資料簡單快速。

各題的欄位驗證就簡單寫,判斷有沒有填入資料而已。

methods: {
  submit() {
    if(this.nickname.length <= 0) alert('請填寫江湖稱號');
    else if(this.toy.length <= 0) alert('請填寫你的玩具');
    else if(this.user.length <= 0) alert('請選擇你來過的次數');
    else if(this.subscribe.length <= 0) alert('請選擇你是否會訂閱');
    else this.sendData();
  }
}

送出資料時,一併把現在的時間也整理起來後送出:

methods: {
  sendData() {
    const d = new Date(),
          y = d.getFullYear(), mon = d.getMonth() + 1, day = d.getDate(),
          h = d.getHours(), m = d.getMinutes(), s = d.getSeconds();
    const today = `${y}-${mon}-${day} ${h}:${m}:${s}`;
      
    let formdata = new FormData();
    formdata.append('today', today);
    formdata.append('nickname', this.nickname);
    formdata.append('toy', this.toy);
    formdata.append('user', this.user);
    formdata.append('subscribe', this.subscribe);
    const config = { method: 'POST', body: formdata, redirect: 'follow' };
    fetch(this.apiUri, config)
      .then(response => response.text())
      .then(result => {
      	if(result === 'success') {
          alert('資料已成功送出,我們下次見~');
          this.nickname = this.toy = this.user = this.subscribe = '';
        }
      })
      .catch(error => console.log('error', error));
    },
}

完整的程式碼都放在 GitHub 上了。


Demo 及完整程式碼

Demo 跟完整的程式碼都放在 GitHub 上,各位在取用前,麻煩對此篇文章點個讚、分個享,或是 GitHub 專案上給個星星,畢竟,連假還窩在咖啡店裡寫文章,實在是一件很悲摧的事,你的一個小小點擊就是一個小小鼓勵。

GitHub:https://github.com/letswritetw/letswrite-sheets-post-data

Demo:https://letswritetw.github.io/letswrite-sheets-post-data/

Summary
用 Google Sheets 紀錄表單資料
Article Name
用 Google Sheets 紀錄表單資料
Description
本篇大綱:本篇要解決的問題。製作一個表單頁面。建立一個 Google Sheets 檔案,開啟 GAS 功能。GAS 收到 POST 後把資料寫進試算表、清空試算表、部署 Apps Script,取得應用程式 URL。表單頁,送出資料程式碼。Demo 及完整程式碼。
Augustus
Let's Write
Let's Write
https://letswrite.tw/wp-content/uploads/2020/08/logo_512.jpg

隨選筆記文

Front-End

拿 Trello 當資料庫 建一個店家清單 – 上篇:Trello 基本使用

Front-End

OSM + Leaflet 學習筆記2:移動中心點、抓目前地點

Forms Google

Google 表單,提交後系統自動寄送回覆通知 email

Front-End

用 Microsoft Clarity 網站分析工具,觀察使用者行為

Firebase Google

Firebase Cloud Functions 基本使用筆記

Front-End

一個簡單的卡片樣式 Simple Card UI

Vue

Vue.js 用 computed 跟 filter 做一個簡易搜尋功能

Firebase Google

Firebase Dynamic Links Analytics API 取得短網址分析資料

Bot Telegram

Telegram Bot 學習筆記 – 5:取得使用者大頭照

Firebase Google

Firebase Cloud Firestore V8 常用功能筆記

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

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

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

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

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

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

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

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