Streamlit建置GoogleSheet簡易資料庫

發表於 分類於 閱讀次數：

🙌前言

Streamlit是使用python語言的前端套件，他最大的好處是就能透過套件本身程式碼生成一篇篇還算能看的使用者介面，對於Html、CSS、Javascript等前端語言感到不舒服的開發者可以妥善利用，在部署上難度也不高，幾乎可以一鍵達成。

除了python本身內鍵已經有本地端sqlite可以進行資料存儲，如果資料需要上雲的話就會需要額外找尋雲端資料庫來進行資料存放，標準的關聯式有MySQL、postgreSQL、SQLserver等等比較常見的工具、非關聯式資料庫則可以透過mongoDB、firestore等等工具進行挑選。

本篇要介紹的內容是針對比較簡易的前端介面，可以用Google提供的GoogleSheet來進行資料存放，並且透過GoogleAppScripts來做後端語言進行操作。

基本API概念

所謂前端與後端分離開發，就是透過API的方式進行資料交換，API就是一串網址(URL)外加一些夾帶的參數，參數可以是純文字、也可以是物件，然而物件的部分又可分為XML或JSON，這兩項是最常被拿來使用於API的資料交換，包含輸入到後端存放資料或者是從資料庫取出資料到前端進行渲染。

公共工程常用的估價系統(PCCES)便是以XML進行資料交換，詳情可見ExcelVBA@PCCES契約項目後處理，在此不多做說明

JSON

大多數的第三方API都是採用JSON格式進行傳輸，這也是本篇主要資料格式，關於JSON的格式通常是以鍵-值對的方式儲存，也就是所謂的key-value，要進行資料取用或組裝時特別的方便，這也是為什麼會選用他來做為我在streamlit及GAS中的交換媒介。

JSON資料可以分為GAS對象、JSON字串、python對象，每個資料都可以互相轉移，JSON字串主要是在API中所夾帶的字串，其餘python對象、GAS對象則是依照不同語言解析成相對應的物件。

萬一遇見處理多巢階層的過程可以藉由扁平化(將巢狀鍵值對處理到頂層)的方法方便資料取用，也可以透過反扁平化的方法將原本的資料恢復原狀。

在GAS中關於JSON格式的轉換

JSON.parse()

JSON.parse() 方法用於將 JSON 字串解析為 JavaScript 物件。它接受一個合法的 JSON 字串並返回解析後的 JavaScript 值。

JSON.stringify()

JSON.stringify() 方法用於將 JavaScript 物件轉換為 JSON 字串。它接受一個 JavaScript 物件並返回相應的 JSON 格式字串。

在Python中關於JSON格式的轉換

json.dumps()

json.dumps() 方法用於將 Python 物件轉換為 JSON 字串。它接受一個 Python 物件並返回相應的 JSON 格式字串。

json.loads()

json.loads() 方法用於將 JSON 字串解析為 Python 物件。它接受一個合法的 JSON 字串並返回解析後的 Python 值。

json.load()>目前沒用到

json.load() 方法用於從檔案中讀取 JSON 資料並將其解析為 Python 物件。它接受一個可讀取的檔案物件，並返回解析後的 Python 值。

Python對象操作

單層

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
data = json.loads(json_str)

# 存取一層結構
name = data['person']['name']
age = data['person']['age']

# 存取多層結構
city = data['person']['address']['city']
zipcode = data['person']['address']['zipcode']

print(name)     # Output: John
print(age)      # Output: 30
print(city)     # Output: New York
print(zipcode)  # Output: 10001


多層

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
import json

json_str = '''
{
  "person": {
    "name": "John",
    "age": 30,
    "address": {
      "city": "New York",
      "zipcode": "10001"
    }
  }
}
'''

def process_json(data):
    if isinstance(data, dict):
        for key, value in data.items():
            print(f'Key: {key}, Value: {value}')
            process_json(value)  # 遞迴處理值
    elif isinstance(data, list):
        for item in data:
            process_json(item)  # 遞迴處理列表中的元素

data = json.loads(json_str)
process_json(data)

GAS對象操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

var obj = {name: "John", age: 30};
var jsonString = JSON.stringify(obj);

//取值

var name = obj.name;
var age = obj['age'];

//設置

obj.name = 'Alice';
obj['age'] = 25;

//循環

for (var key in obj) {
    if (obj.hasOwnProperty(key)) {
        Logger.log(key + ': ' + obj[key]);
    }
}

綜合操作

streamlit

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
import streamlit as st
import json
from datetime import date, datetime
import requests

# 模拟一些 session state 数据
st.session_state.current_page = "render_page0"
st.session_state.submitted = False
st.session_state.costs = {
    "open_channel": {"name": "渠道工程", "unit_cost": 0, "length": 0, "total_cost": 0},
    "bridge": {"name": "版橋工程", "unit_cost": 0, "quantity": 0, "total_cost": 0},
    "wall": {"name": "擋土牆", "unit_cost": 0, "length": 0, "total_cost": 0},
    "road": {"name": "道路工程", "unit_cost": 0, "quantity": 0, "total_cost": 0},
    "falsework": {"name": "版樁工程", "unit_cost": 0, "quantity": 0, "total_cost": 0},
}
st.session_state.totalcost = 0
st.session_state.inf = {
    "work_place": "",
    "work_place2": "",
    "work_station": "",
    "work_name": "",
    "work_benefit": "",
    "work_place_detail": "",
    "work_water_check": False,
    "work_start_date": date.today(),
    "work_end_date": date.today(),
}

# 自定义 JSON 序列化器，用于处理日期字段
class DateTimeEncoder(json.JSONEncoder):
    def default(self, obj):
        if isinstance(obj, (datetime, date)):
            return obj.isoformat()
        return super().default(obj)

# 定义一个函数，将 st.session_state 转换为 JSON
def session_state_to_json():
    selected_fields = ["costs", "inf"] # 指定需要转换的字段
    session_state_dict = {key: value for key, value in st.session_state.items()if key in selected_fields}
    json_data = json.dumps(session_state_dict, cls=DateTimeEncoder)
    return json_data

# 使用函数并展示结果
json_result = session_state_to_json()
st.write(st.session_state)
st.write(json_result)

if st.button("Submit"):

    # 設置 Google Apps Script Web 應用程式的 URL
    url = "your_gas_url"

    # 發送 POST 請求並傳遞 JSON 資料
    response = requests.post(url, data=json_result)

    # 檢查請求是否成功
    if response.status_code == 200:
        st.write("Request successful!")
    else:
        st.write("Error:", response.status_code)

GAS

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
// 定义Google Sheets的ID
const SHEET_ID = 'your_sheet_id'; // 替换为您的Google Sheets ID

function doPost(e) {
  // 从 POST 请求中获取 JSON 字符串
  var jsonString = e.postData.contents;
  
  // 解析 JSON 字符串为对象
  var dataOrigin = JSON.parse(jsonString);
  
  var data = flattenObject(dataOrigin);

  // 获取活动工作表
  var ss = SpreadsheetApp.openById(SHEET_ID);
  var sheet = ss.getActiveSheet();
  
  // 获取最后一行，以便在末尾添加新数据
  var lastRow = sheet.getLastRow();
  
  // 将数据写入工作表的下一行
  for (var key in data) {

    var columnIndex = getColumnIndex(sheet, key);

    if (columnIndex === -1) { // 如果列不存在，则创建新列
      columnIndex = sheet.getLastColumn() + 1;
      sheet.getRange(1, columnIndex).setValue(key);
    }

    if (lastRow===0 ){
      sheet.getRange(lastRow + 2, columnIndex).setValue(data[key]);
    }else{
      sheet.getRange(lastRow + 1, columnIndex).setValue(data[key]);
    }
  }
  
  // 返回回应给客户端，这是可选的
  return ContentService.createTextOutput('Success').setMimeType(ContentService.MimeType.TEXT);
}

// 获取列的索引，如果不存在，则返回-1
function getColumnIndex(sheet, columnName) {
  var maxColumns = sheet.getMaxColumns(); // 获取工作表的最大列数
  if (maxColumns < 1) {
    return -1; // 如果没有列存在，则直接返回 -1
  }
  var headersRange = sheet.getRange(1, 1, 1, maxColumns);
  var headersValues = headersRange.getValues()[0]; // 只获取第一行数据
  for (var i = 0; i < headersValues.length; i++) {
    if (headersValues[i] === columnName) {
      return i + 1;
    }
  }
  return -1; // 如果找不到，则返回 -1
}

// 定义一个扁平化嵌套对象的函数
function flattenObject(obj, parentKey = '') {
  // 检查 obj 是否存在且不为 undefined 或 null
  if (!obj || typeof obj !== 'object') {
    return {};
  }
  
  let result = {};
  for (const [key, value] of Object.entries(obj)) {
    const newKey = parentKey ? `${parentKey}.${key}` : key;
    if (typeof value === 'object' && value !== null) {
      const flattened = flattenObject(value, newKey);
      result = { ...result, ...flattened };
    } else {
      result[newKey] = value;
    }
  }
  return result;
}

JSON物件特殊處理

資料扁平化

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
function doPost(e) {
  const jsonData = {"name":"John","age":30,"address":{"city":"New York","country":"USA"}};

  // 扁平化对象
  const flattenedObject = flattenObject(jsonData);
  
  // 将扁平化后的对象转换为 JSON 字符串
  const jsonString = JSON.stringify(flattenedObject);
  
  // 返回 JSON 格式的数据
  return ContentService.createTextOutput(jsonString).setMimeType(ContentService.MimeType.JSON);
}

// 定义一个扁平化嵌套对象的函数
function flattenObject(obj, parentKey = '') {
  // 检查 obj 是否存在且不为 undefined 或 null
  if (!obj || typeof obj !== 'object') {
    return {};
  }
  
  let result = {};
  for (const [key, value] of Object.entries(obj)) {
    const newKey = parentKey ? `${parentKey}.${key}` : key;
    if (typeof value === 'object' && value !== null) {
      const flattened = flattenObject(value, newKey);
      result = { ...result, ...flattened };
    } else {
      result[newKey] = value;
    }
  }
  return result;
}

// 使用示例
const nestedObject = {
  name: "John",
  age: 30,
  address: {
    city: "New York",
    country: "USA"
  }
};

const flattenedObject = flattenObject(nestedObject);
console.log(flattenedObject);
console.log(Object.keys(flattenedObject))
console.log(Object.values(flattenedObject))

//[name,age,address.city,address.country]
//[John,30,New York,USA]

資料扁平化回復

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
function unflattenObject(obj) {
  // 创建一个新的对象来存储恢复后的数据
  let result = {};

  // 遍历扁平化后的对象
  for (const key in obj) {
    if (Object.hasOwnProperty.call(obj, key)) {
      // 将键名拆分成各级别的嵌套键名
      const keys = key.split('.');
      let nestedObj = result;

      // 迭代嵌套键名，并在新对象中重新创建嵌套结构
      for (let i = 0; i < keys.length; i++) {
        const nestedKey = keys[i];
        if (i === keys.length - 1) {
          // 如果是最后一级键名，直接将值赋给最后一级键
          nestedObj[nestedKey] = obj[key];
        } else {
          // 如果不是最后一级键名，检查是否需要创建嵌套对象
          nestedObj[nestedKey] = nestedObj[nestedKey] || {};
          nestedObj = nestedObj[nestedKey];
        }
      }
    }
  }

  return result;
}

VBA部分

只是先做紀錄，之後再跟collection的資料格式一起介紹

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
Sub TestDictionary()
    Dim jsonData As Object
    Set jsonData = CreateObject("Scripting.Dictionary")
    
    ' 添加?值?
    jsonData("name") = "John"
    jsonData("age") = 30
    
    ' 添加嵌套的字典?象
    Dim address As Object
    Set address = CreateObject("Scripting.Dictionary")
    address("city") = "New York"
    address("country") = "USA"
    Set jsonData("address") = address
    
    ' ???值?
    Debug.Print "Name: " & jsonData("name")
    Debug.Print "Age: " & jsonData("age")
    Debug.Print "City: " & jsonData("address")("city")
    Debug.Print "Country: " & jsonData("address")("country")
End Sub

VBA-JSON 參考

總結

  1. 使用Streamlit和Google Sheets：
    • Streamlit能快速生成前端介面，對不熟悉前端技術的開發者友好。
    • Google Sheets作為雲端資料庫，結合Google Apps Script (GAS)實現資料操作。
  2. JSON格式的優勢：
    • JSON是API資料交換中常用的格式，方便資料取用和組裝。
    • 文章介紹了在Python和GAS中如何進行JSON資料的解析和生成。
  3. 多層資料結構處理技術：
    • 使用扁平化和反扁平化技術，方便處理和還原多層嵌套的資料結構。
    • 提供了Python和GAS中處理多層資料結構的範例。
  4. 實際應用範例：
    • 展示了如何在Streamlit應用中將資料轉換為JSON格式，並通過HTTP POST請求發送到GAS後端。
    • 在GAS後端接收並解析JSON資料，將其寫入Google Sheets的具體步驟。
  5. Python和GAS中的JSON操作方法：
    • 詳述了在Python中使用json.dumps()和json.loads()進行JSON格式轉換。
    • 說明了在GAS中使用JSON.stringify()和JSON.parse()處理JSON資料。