1. 新增Google試算表(更改試算表名稱、工作頁命名: sheet1，※工作頁名稱要英文)
2. 點「工具 / <>指令碼編輯器」
3. 貼上 Google Apps Script 程式碼(工作頁名稱，預設為：sheet1)，每次程式碼修改均需手動存檔，並重新部署應用程式(第5、6點)
var sheet1=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("sheet1")
function doPost(e) {
var para = e.parameter, // 存放 post 所有傳送的參數
data1 = para.data1,
data2 = para.data2,
data3 = para.data3;
var timestamp=Utilities.formatDate(new Date(), "GMT+8:00", "MM-dd-yyyy HH:mm:ss");
sheet1.appendRow([timestamp,data1, data2, data3]); // 插入一列新的資料
}
4. 存檔
5. 點右上 「部署 / 新增部署作業 / 點選齒輪圖示 / 網頁應用程式」，將誰可以存取改為：「所有人」
6. 按「部署」鈕，「授予存取權」後，將網址複製起來貼到VBA gasURL變數中。
7. VBA 程式碼：
//工作頁中放一個按鈕(CommandButton1)
Private Sub CommandButton1_Click()
gasURL = "https://script.google.com/macros/s/AKfycbz8MqyZIX0BxD08etNUMDZMI9VnFiWo43jrm2VZHiNRXh50Hj1ErqC6QKNpGtQopk-N/exec"
a = Cells(1, 1) '取得欄位資料
b = Cells(1, 2) '取得欄位資料
c = Cells(1, 3) '取得欄位資料
a = UrlEncode(CStr(a)) '如資料內容有中文，需轉為網頁中文編碼
'將資料使用POST寫進googlesheet
Set WinHttp = CreateObject("WinHttp.WinHttpRequest.5.1")
postData = "data1=" & a & "&data2=" & b & "&data3=" & c
WinHttp.Open "POST", gasURL, False
WinHttp.setRequestHeader "authority", "script.google.com"
WinHttp.setRequestHeader "User-Agent", "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_13_4) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/65.0.3325.181 Safari/537.36"
WinHttp.setRequestHeader "Content-type", "application/x-www-form-urlencoded"
WinHttp.send postData
'Debug.Print WinHttp.responseText
End Sub
//中文轉碼
Public Function UrlEncode(ByRef szString As String) As String
Dim szChar As String
Dim szTemp As String
Dim szCode As String
Dim szHex As String
Dim szBin As String
Dim iCount1 As Integer
Dim iCount2 As Integer
Dim iStrLen1 As Integer
Dim iStrLen2 As Integer
Dim lResult As Long
Dim lAscVal As Long
szString = Trim$(szString)
iStrLen1 = Len(szString)
For iCount1 = 1 To iStrLen1
szChar = Mid$(szString, iCount1, 1)
lAscVal = AscW(szChar)
If lAscVal >= &H0 And lAscVal <= &HFF Then
If (lAscVal >= &H30 And lAscVal <= &H39) Or _
(lAscVal >= &H41 And lAscVal <= &H5A) Or _
(lAscVal >= &H61 And lAscVal <= &H7A) Then
szCode = szCode & szChar
Else
szCode = szCode & "%" & Hex(AscW(szChar))
End If
Else
szHex = Hex(AscW(szChar))
iStrLen2 = Len(szHex)
For iCount2 = 1 To iStrLen2
szChar = Mid$(szHex, iCount2, 1)
Select Case szChar
Case Is = "0"
szBin = szBin & "0000"
Case Is = "1"
szBin = szBin & "0001"
Case Is = "2"
szBin = szBin & "0010"
Case Is = "3"
szBin = szBin & "0011"
Case Is = "4"
szBin = szBin & "0100"
Case Is = "5"
szBin = szBin & "0101"
Case Is = "6"
szBin = szBin & "0110"
Case Is = "7"
szBin = szBin & "0111"
Case Is = "8"
szBin = szBin & "1000"
Case Is = "9"
szBin = szBin & "1001"
Case Is = "A"
szBin = szBin & "1010"
Case Is = "B"
szBin = szBin & "1011"
Case Is = "C"
szBin = szBin & "1100"
Case Is = "D"
szBin = szBin & "1101"
Case Is = "E"
szBin = szBin & "1110"
Case Is = "F"
szBin = szBin & "1111"
Case Else
End Select
Next iCount2
szTemp = "1110" & Left$(szBin, 4) & "10" & Mid$(szBin, 5, 6) & "10" & Right$(szBin, 6)
For iCount2 = 1 To 24
If Mid$(szTemp, iCount2, 1) = "1" Then
lResult = lResult + 1 * 2 ^ (24 - iCount2)
Else: lResult = lResult + 0 * 2 ^ (24 - iCount2)
End If
Next iCount2
szTemp = Hex(lResult)
szCode = szCode & "%" & Left$(szTemp, 2) & "%" & Mid$(szTemp, 3, 2) & "%" & Right$(szTemp, 2)
End If
szBin = vbNullString
lResult = 0
Next iCount1
UrlEncode = szCode
End Function
Google試算表建立篩選器步驟：
a. 點選一個欲篩選欄位的標題，Ex：D欄「學生帳號」
b. 點選「資料 / 篩選器檢視畫面 / 建立新的篩選器檢視畫面」
c. 左上 名稱「篩選器1」 改成班級名稱，Ex: 「901」
d. 下拉標題「學生帳號」右側篩選圖示(倒三角)
e. 選擇「依條件篩選」，將「無」改選為「文字開頭」
f. 「值或公式」位置輸入班級代碼後按「確定」，Ex：「901」
g. 將網址複製下來，此網址為901的篩選器位置
h. 可從「901篩選器」複製篩選器設定值
(1) 點選視窗右側齒輪圖示
(2)選「複製」
(3)將 名稱「901的副本」改為「902」
(4)下拉D欄標題「學生帳號」右側篩選圖示(倒三角)
(6)將 開頭文字「901」改為「902」後按「確定」
(7)將網址複製下來，此網址為902的篩選器位置