Byte Ebi's Logo

Byte Ebi 🍤

每天一小口，蝦米變鯨魚

操作 Google 表單，透過 GAS(Google App Script)

使用 GAS(Google App Script) 服務把 Google 表單當作簡易的資料庫使用

Ray

在管理後台尚未建立完成而前台已經上線的時候，會希望可以讓非程式人員自行調整網頁上的文案
又或是暫時性的紀錄內容不想因此開資料表，通常這時候我們會透過 Google 表單來管理資料

先把話說在前面，這種做法都是權宜之計！
Google 表單本身限制蠻多的，如果你拿去當正式資料庫絕對會爆炸
沒爆炸代表你的服務太小，連三公分都不到
在初期小不是什麼問題，換來的靈活性讓非程式人員在正式編輯介面完成前可以讓自行修改內容
而不用透過技術人員發布新版程式

讀取 Google 表單有兩種方式，一種是將表單發佈在網路上
如此就可以透過網址取得指定輸出類型的表單內容，最常見的就是輸出成 csv 格式

優點：

  • 操作簡單，人人都會
  • 不需要額外寫程式，建立好之後可以直接訪問網址取得資料

缺點：

  • 發佈內容會被 Google 快取：雖然說明寫有修改之後會更新，但是實際更新大概要等個三到五分鐘
    如果你的內容是需要即時存取最新資料做判斷的話會發生問題

至於第二種方式就是為了解決缺點的部分，為此我們要使用 Google 提供的 GAS(Google App Script) 服務

1. 建立一個google 表單

既然都說要「操作 Google 表單」所以建立一個 Google 表單也是合情合理的吧
設為共用可查看就好

share sheet

2. 建立新 Google App Script 專案

可以在 Google App Script 專案頁面 建立
或是用在 google sheet 裡面直接開啟擴充功能

tool bar

3. Google Script 撰寫

現在已經有了 Google 表單，也知道操作用的程式要寫在哪了
接著就是要來寫要執行的程式，撰寫時可以參考：官方參考文件
從文件中找到需要的函數來操作 Google 表單

共用參數說明

有一些很常用的函數先一併說明

e.parameter

取得傳入的所有參數，後續程式可以用來操作
使用方式是

let params = e.parameter

params.sheetTag

google sheet 最下面的工作表名稱，作為變數傳入就可以同時寫或是讀多個不同工作表

sheet tag

使用方式是在取得傳入的所有變數之後呼叫sheetTag

let sheetTag = params.sheetTag

params.sheetId

Google 表單的 ID 用來識別，作為參數傳入就可以區分正式或是測試環境
用自己的就好了，這份文件我已經刪掉了！

sheet id

使用方式是在取得傳入的所有變數之後呼叫sheetId

let sheetId = params.sheetId

讀取資料

接下來是說一下怎麼讀取資料
讀取表單用的 function 名稱一定要叫deGet，所有 get 方法都打到這邊

呼叫的內建方法可以去查上面提到過的官方文件

  1. 取得最後一欄和最後一列的數字
  2. 取得範圍內的所有資料
  3. 回傳 json 格式
function doGet(e) {
  let params = e.parameter

  let sheetTag = params.sheetTag
  let sheetId = params.sheetId

  let SpreadSheet = SpreadsheetApp.openById(sheetId)
  let Sheet = SpreadSheet.getSheetByName(sheetTag)

  let lastRow = Sheet.getLastRow()
  let lastColumn = Sheet.getLastColumn()

  // getSheetValues(startRow, startColumn, numRows, numColumns)
  let values = Sheet.getSheetValues(1, 1, lastRow, lastColumn)

  let jsonString = JSON.stringify(Object.assign({}, values))
  return ContentService.createTextOutput(jsonString)
}

在寫好doGet之後我們來測試一下是否可以達成預期結果
在直接發佈之前可以利用內建的測試工具來測試

在剛剛的 Google App Script 專案下新增一個檔案debugGet.gs

function debug() {
  var e = {
      parameter:{
        sheetId:"1XuNpMRdZasL8TNovZasd13asd41SoEBo0BMzabGqaE1s",
        sheetTag:"todo"
      }
  }

  let result = doGet(e)
  Logger.log(result.getContent())
}

設定好 Google 表單要傳入資料之後，按下執行
回傳結果會透過Logger.log顯示在底下執行結果視窗

寫入資料

傳入 data 是一個 json 字串，將會一筆一筆寫入指定的工作表
比較特別的是假設資料內容有包含網址，在傳入的時候必須跳脫特殊字元
而原生 JavaScript 沒有轉回來的函示
都必須調用 DOM document，但是 GAS 是 server side 的沒得呼叫
所以就直接用替換的

  1. 傳入的資料從字串轉換為 JSON 物件
  2. 遍歷物件將值從第一列根據不同 index 寫入到不同欄位
function doPost(e) {
  let params = e.parameter

  // 初始化試算表
  let sheetTag = params.sheetTag
  let sheetId = params.sheetId
  let SpreadSheet = SpreadsheetApp.openById(sheetId)
  let Sheet = SpreadSheet.getSheetByName(sheetTag)
  
  let data = JSON.parse(params.data)

  let lastRow = 0

  // 寫入試算表
  data.forEach(function(value){
    with(value) {
      content = content.replace(/&amp;/g, "&").replace(/&lt;/g, "<").replace(/&gt;/g, ">").replace(/&quot;/g, "\"").replace(/&#039;/g, "'")
      Sheet.getRange(lastRow + 1, 1).setValue(article_id)
      Sheet.getRange(lastRow + 1, 2).setValue(content)
    }
    lastRow = lastRow + 1
  })

  return ContentService.createTextOutput(JSON.stringify(true)).setMimeType(ContentService.MimeType.JSON)
}

測試方法和 Get 的時候很像，我們新增一個檔案debugGet.gs

function debugPost(){
  var processing = {
    parameter:{
      sheetId:"1XuNpMRdZasL8TNovZS09Aing41SoEBo0BMzabGqaE1s",
      sheetTag:"processing",
      data: '[{"action":"鐵人賽","date":"2021/09/12"}]',
    }
  }

  var done = {
    parameter:{
      sheetId:"1XuNpMRdZasL8TNovZS09Aing41SoEBo0BMzabGqaE1s",
      sheetTag:"done",
      data: '[{"action":"還書","date":"2021/10/09"}]',
    }
  }

  let result1 = doPost(processing)
  let result2 = doPost(done)
  Logger.log(result1.getContent())
  Logger.log(result2.getContent())
}

可以看到我們測試了兩種傳入參數：processing 和 done
兩者的差異就只有傳入的 sheetTag，代表不同工作表

下一個範例比較複雜，屬於延伸應用

  1. 輸入邀請碼和使用者 email
  2. 檢查該邀請碼是否還有可使用額度(相同邀請碼的後一欄均已經被填入資料，換言之沒有可填入資料的欄位)
  3. 檢查該使用者是否登記過邀請碼
  4. 寫入資料 / 回傳結果
function doPost(e) {
  let params = e.parameter
  let sheetTag = params.sheetTag
  let sheetId = params.sheetId
  let SpreadSheet = SpreadsheetApp.openById(sheetId)
  let Sheet = SpreadSheet.getSheetByName(sheetTag)

  let invitation_code = params.invitation_code
  let email = params.email

  // email 已存在
  let user_target_range = Sheet.createTextFinder(email).findNext()
  if(user_target_range !== null) {
    return ContentService.createTextOutput(false)
  }

  // 撈取全部 invitation_code
  let ranges = Sheet.createTextFinder(invitation_code).findAll()

  // 遍歷所有 invitation_code 的結果
  let target_row = 0
  ranges.every(function(range){
    let row_num = range.getRowIndex()
    let column_value = Sheet.getRange(row_num, 2).getValue()

    // 取得當前目標的 B 欄內容，如果為空就設定寫入行數
    if(column_value == '') {
      target_row = row_num
      return false
    }

    return true
  })

  if(target_row !== 0) {
    Sheet.getRange(target_row, 2).setValue(email)
    return ContentService.createTextOutput(true)
  }
  
  return ContentService.createTextOutput(false) // 邀請碼額度已經用完
}

測試用的 function

function debugPost(){
  let params = {
    parameter: {
      sheetId:"1XuNpMRdZasL8TNovZS09Aing41SoEBo0BMzabGqaE1s",
      sheetTag:"done",
      invitation_code: "5XdxPDdv",
      email: '[email protected]'
    }
  }

  Logger.log(doPost(params).getContent());
}

從後端呼叫

在測試沒問題之後，就可以按下發佈
就可以拿到一串 API 的網址，這邊沒有截圖的原因是 Google 整天在改版
搞不好截圖過幾天就又改位置了

接著示範怎麼在 PHP 發送請求到 GAS 的 API

private function updateSheet(string $invitation_code): void
{
    $auth_user = auth()->user();
    $user_email = $auth_user->account;

    $client = new \GuzzleHttp\Client();
    $response = $client->request('POST', $this->gas_url, [
        'form_params' => [
            'sheetId'         => $this->sheetId,
            'sheetTag'        => $this->sheetTag,
            'invitation_code' => $invitation_code,
            'email'           => $user_email,
        ]
    ]);

    if ($response->getStatusCode() != Response::HTTP_OK) {
        throw new AppException(Response::HTTP_INTERNAL_SERVER_ERROR, 'The Sheet Die');
    }
}

可以看到使用的是form_params來帶入變數，其他就沒什麼需要別注意的了
最後再說一遍，只有在服務還小的時候可以這樣做
除了 ACID 這類的考量外，你永遠不知道什麼時候會踩中 Google 的敏感帶
取回資料後最好就快取在自己的伺服器，不要讓每個請求都去執行 GAS
因為過於頻繁觸發 GAS 有可能會造成 Google 表單無法存取的情形發生

最新文章

Category

Tag