发表Tech / FrontEnd8 分钟读完 (大约1240个字)  次访问

使用 HTTP API 从 WPS 在线表格中增删改查数据示例

在 使用 HTTP API 从 WPS 在线表格中获取数据 中，我介绍了如何查询数据，本篇将介绍如何利用 AirScript 实现完整的增删改查。

首先还是在金山文档中创建一个智能表格，在智能表格中创建一个数据表，这里以 “汇率表” 为例。

点 “高级功能” - “高级开发” - “AirScript 脚本编辑器”，在文档共享脚本中，点击 + 号右边的下拉按钮，创建一个 AirScript 1.0 脚本。

AirScript 2.0 脚本暂时还不支持数据表

输入以下代码。

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
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
/**
 * WPS 智能表格数据表 AirScript 增删改查示例
 * @author iMaeGoo <hello@imaegoo.com>
 * @copyright iMaeGoo 2024
 * @license MIT
 */

// 请求参数
const request = {
  // 操作 ("create" | "read" | "update" | "delete")
  action: Context.argv.action,
  // 数据表名
  sheet: Context.argv.sheet,
  // 筛选条件，写法参考 https://airsheet.wps.cn/docs/api/excel/databook/%E9%99%84%E5%BD%95.html#%E9%99%84%E5%BD%95-3-%E7%AD%9B%E9%80%89%E6%9D%A1%E4%BB%B6%E8%AF%B4%E6%98%8E
  filter: Context.argv.filter,
  // 更新的字段信息
  fields: Context.argv.fields,
};

// 响应参数
const response = {
  success: false,
  data: undefined,
  message: "",
};

if (!request.action) {
  response.message = "action 参数不能为空";
  return response;
}

if (!request.sheet) {
  response.message = "sheet 参数不能为空";
  return response;
}

// 获取工作表
const sheet = Application.Sheets.Item(request.sheet);

switch (request.action) {
  case "create":
    response.data = create();
    response.success = true;
    response.message = "创建成功";
    break;
  case "read":
    response.data = read();
    response.success = true;
    response.message = "查询成功";
    break;
  case "update":
    response.data = update();
    response.success = true;
    response.message = "更新成功";
    break;
  case "delete":
    response.data = del();
    response.success = true;
    response.message = "删除成功";
    break;
  default:
    response.message =
      'action 参数错误，应为 "create" | "read" | "update" | "delete"';
}

// 增
function create() {
  return sheet.Record.CreateRecords({
    Records: [
      {
        fields: request.fields,
      },
    ],
  });
}

// 查
function read() {
  let all = [];
  let offset = null;
  while (all.length === 0 || offset) {
    let records = sheet.Record.GetRecords({
      Offset: offset,
      Filter: request.filter,
    });
    offset = records.offset;
    all = all.concat(records.records);
  }
  return all;
}

// 改
function update() {
  const records = read();
  const updateRecords = records.map((record) => {
    return {
      id: record.id,
      fields: request.fields,
    };
  });
  return sheet.Record.UpdateRecords({
    Records: updateRecords,
  });
}

// 删
function del() {
  const records = read();
  const deleteIds = records.map((record) => record.id);
  return sheet.Record.DeleteRecords({
    RecordIds: deleteIds,
  });
}

return response;

按 Ctrl + S 保存脚本，然后点击工具栏上的 “脚本令牌” 按钮，生成一个脚本令牌，复制这个令牌保存（以后将无法再次查看），令牌有效期是半年，但放心，令牌可以随时延期，延期后令牌不变。

点击脚本名称旁边的菜单，点击 “复制脚本 webhook” 并保存。

接下来只需 POST 这个 webhook 地址，在请求头中带上 AirScript-Token: 脚本令牌，即可实现增删改查功能。

查询

查询美元的汇率

请求参数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
{
  "Context": {
    "argv": {
      "action": "read",
      "sheet": "汇率表",
      "filter": {
        "mode": "AND",
        "criteria": [
          {
            "field": "货币名称",
            "op": "Contains",
            "values": [
              "美元"
            ]
          }
        ]
      }
    }
  }
}

响应参数

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
{
  "data": {
    "logs": [
      {
        "filename": "<system>",
        "timestamp": "15:42:07.539",
        "unix_time": 1725867727539,
        "level": "info",
        "args": [
          "脚本环境(1.0)初始化..."
        ]
      },
      {
        "filename": "<system>",
        "timestamp": "15:42:07.544",
        "unix_time": 1725867727544,
        "level": "info",
        "args": [
          "已开始执行"
        ]
      },
      {
        "filename": "<system>",
        "timestamp": "15:42:07.678",
        "unix_time": 1725867727678,
        "level": "info",
        "args": [
          "执行完毕"
        ]
      }
    ],
    "result": {
      "data": [
        {
          "fields": {
            "中行折算价": 709.89,
            "发布日期": "2024/09/09",
            "发布时间": "15:25:28",
            "现汇买入价": 710.11,
            "现汇卖出价": 712.94,
            "现钞买入价": 704.34,
            "现钞卖出价": 712.94,
            "货币名称": "美元"
          },
          "id": "VN"
        }
      ],
      "message": "查询成功",
      "success": true
    }
  },
  "error": "",
  "status": "finished"
}

新增

创建一条 “萌币” 的汇率记录

请求参数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
{
  "Context": {
    "argv": {
      "action": "create",
      "sheet": "汇率表",
      "filter": {},
      "fields": {
        "中行折算价": 26.05,
        "发布日期": "2024/09/09",
        "发布时间": "15:55:28",
        "现汇买入价": 21.05,
        "现汇卖出价": 22.05,
        "现钞买入价": 23.05,
        "现钞卖出价": 25.95,
        "货币名称": "萌币"
      }
    }
  }
}

响应参数

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
{
  "data": {
    "logs": [
      {
        "filename": "<system>",
        "timestamp": "15:56:57.857",
        "unix_time": 1725868617857,
        "level": "info",
        "args": [
          "脚本环境(1.0)初始化..."
        ]
      },
      {
        "filename": "<system>",
        "timestamp": "15:56:58.650",
        "unix_time": 1725868618650,
        "level": "info",
        "args": [
          "已开始执行"
        ]
      },
      {
        "filename": "<system>",
        "timestamp": "15:56:58.805",
        "unix_time": 1725868618805,
        "level": "info",
        "args": [
          "执行完毕"
        ]
      }
    ],
    "result": {
      "data": [
        {
          "fields": {
            "中行折算价": 26.05,
            "发布日期": "2024/09/09",
            "发布时间": "15:55:28",
            "现汇买入价": 21.05,
            "现汇卖出价": 22.05,
            "现钞买入价": 23.05,
            "现钞卖出价": 25.95,
            "货币名称": "萌币"
          },
          "id": "VP"
        }
      ],
      "message": "创建成功",
      "success": true
    }
  },
  "error": "",
  "status": "finished"
}

修改

修改 “萌币” 的备注

请求参数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
{
  "Context": {
    "argv": {
      "action": "update",
      "sheet": "汇率表",
      "filter": {
        "mode": "AND",
        "criteria": [
          {
            "field": "货币名称",
            "op": "Contains",
            "values": [
              "萌币"
            ]
          }
        ]
      },
      "fields": {
        "备注": "很萌但并不存在的货币"
      }
    }
  }
}

响应参数

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
{
  "data": {
    "logs": [
      {
        "filename": "<system>",
        "timestamp": "16:00:41.894",
        "unix_time": 1725868841894,
        "level": "info",
        "args": [
          "脚本环境(1.0)初始化..."
        ]
      },
      {
        "filename": "<system>",
        "timestamp": "16:00:42.683",
        "unix_time": 1725868842683,
        "level": "info",
        "args": [
          "已开始执行"
        ]
      },
      {
        "filename": "<system>",
        "timestamp": "16:00:42.955",
        "unix_time": 1725868842955,
        "level": "info",
        "args": [
          "执行完毕"
        ]
      }
    ],
    "result": {
      "data": [
        {
          "fields": {
            "中行折算价": 26.05,
            "发布日期": "2024/09/09",
            "发布时间": "15:55:28",
            "备注": "很萌但并不存在的货币",
            "现汇买入价": 21.05,
            "现汇卖出价": 22.05,
            "现钞买入价": 23.05,
            "现钞卖出价": 25.95,
            "货币名称": "萌币"
          },
          "id": "VP"
        }
      ],
      "message": "更新成功",
      "success": true
    }
  },
  "error": "",
  "status": "finished"
}

删除

删除 “萌币” 记录

请求参数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
{
  "Context": {
    "argv": {
      "action": "delete",
      "sheet": "汇率表",
      "filter": {
        "mode": "AND",
        "criteria": [
          {
            "field": "货币名称",
            "op": "Contains",
            "values": [
              "萌币"
            ]
          }
        ]
      }
    }
  }
}

响应参数

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
{
  "data": {
    "logs": [
      {
        "filename": "<system>",
        "timestamp": "16:01:47.265",
        "unix_time": 1725868907265,
        "level": "info",
        "args": [
          "脚本环境(1.0)初始化..."
        ]
      },
      {
        "filename": "<system>",
        "timestamp": "16:01:47.271",
        "unix_time": 1725868907271,
        "level": "info",
        "args": [
          "已开始执行"
        ]
      },
      {
        "filename": "<system>",
        "timestamp": "16:01:47.526",
        "unix_time": 1725868907526,
        "level": "info",
        "args": [
          "执行完毕"
        ]
      }
    ],
    "result": {
      "data": [
        {
          "deleted": true,
          "id": "VP"
        }
      ],
      "message": "删除成功",
      "success": true
    }
  },
  "error": "",
  "status": "finished"
}

使用 HTTP API 从 WPS 在线表格中增删改查数据示例

https://www.imaegoo.com/2024/http-crud-data-from-wps/

作者

iMaeGoo

发布于

2024-09-09

更新于

2024-09-09

许可协议

CC BY 4.0

#

喜欢这篇文章？打赏一下作者吧

评论

GitHub
微信二维码

归档

目录

最新文章

最新评论

×