使用 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}

|---------------------------------------------------------|------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| | 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}

|---------------------------------------------------------------------|---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| | 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" : { "备注" : "很萌但并不存在的货币" } } } } |

响应参数 {#响应参数-2}

删除 {#删除}

删除 "萌币" 记录

请求参数 {#请求参数-3}

|------------------------------------------------------------|--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| | 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" : [ "萌币" ] } ] } } } } |

响应参数 {#响应参数-3}

|---------------------------------------------------------------------------------------------------------------------------------------|-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| | 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" } |