← Back to Skills Marketplace
bitable-formula-generator
by
chengfen1987
· GitHub ↗
· v1.1.0
· MIT-0
113
Downloads
0
Stars
0
Active Installs
2
Versions
Install in OpenClaw
/install bitable-formula-generator
Description
飞书多维表格字段公式生成器。通过导出Excel分析字段结构，利用Excel公式逻辑生成多维表格兼容公式，并写入多维表格字段。
README (SKILL.md)
\r \r
飞书多维表格字段公式生成器\r
\r
目的\r
根据用户描述的业务逻辑，自动生成可直接用于飞书多维表格公式字段的公式。\r 公式语法与 Excel 类似，但只使用多维表格支持的函数，且用字段名（而非列字母）引用数据。\r \r ---\r \r
完整工作流程\r
\r
第 1 步：获取多维表格结构\r
\r 优先通过以下方式获取字段信息：\r
- API 自动获取（推荐）：使用
scripts/feishu-bitable-api.js脚本自动列出表和字段。\r - 导出为 Excel：用户导出多维表格为
.xlsx，读取表头行获取字段名和数据类型。\r - 用户直接提供：用户列出字段名及数据类型。\r \r
使用 API 脚本获取字段信息\r
\r
脚本路径：scripts/feishu-bitable-api.js\r
\r
前置条件：需设置环境变量 FEISHU_APP_ID 和 FEISHU_APP_SECRET。\r
\r
列出所有表：\r
node scripts/feishu-bitable-api.js list-tables \x3Capp_token>\r
```\r
\r
**列出指定表的所有字段**（公式字段会显示当前公式预览，`⚠️ 0` 表示公式未设置）：\r
```bash\r
node scripts/feishu-bitable-api.js list-fields \x3Capp_token> \x3Ctable_name>\r
```\r
\r
**查看指定字段的完整 property**（公式字段显示完整公式，单选字段显示所有选项）：\r
```bash\r
node scripts/feishu-bitable-api.js get-field \x3Capp_token> \x3Ctable_name> \x3Cfield_name>\r
```\r
\r
> `app_token` 可从多维表格 URL 中获取，格式如 `https://xxx.feishu.cn/base/\x3Capp_token>`。\r
\r
从 Excel 读取字段信息时，提取第 1 行（表头）作为字段名列表。\r
\r
---\r
\r
### 第 1b 步：确认依赖字段的公式正确性（重要！）\r
\r
**如果目标公式依赖其他公式字段（中间字段），必须先确认中间字段的公式是否正确，再继续。**\r
\r
```bash\r
# 查看中间字段的当前公式\r
node scripts/feishu-bitable-api.js get-field \x3Capp_token> \x3Ctable_name> \x3C中间字段名>\r
```\r
\r
⚠️ 常见陷阱：中间字段可能是硬编码的 `0` 或空公式，会导致最终结果全为 0。\r
\r
---\r
\r
### 第 2 步：生成公式（Excel 风格 → 多维表格风格）\r
\r
#### 2a. 先构造 Excel 公式\r
\r
借鉴 `excel-formula-generator` 技能的公式模式，基于字段映射（A列=字段1, B列=字段2 ...）构造 Excel 公式原型。\r
\r
#### 2b. 替换列字母为字段名\r
\r
将公式中的列引用（如 `B2`、`C2`、`B:B`）替换为对应字段名：\r
\r
| Excel 引用 | 多维表格写法 |\r
|---|---|\r
| `B2` | `字段名` |\r
| `C2` | `另一个字段名` |\r
| `"固定文本"` | 保持不变 |\r
| `数字常量` | 保持不变 |\r
\r
示例：\r
- Excel: `=(B2-C2)/B2` \r
- 多维表格: `(销售额-成本)/销售额`\r
\r
#### 2c. 函数替换规则（仅使用多维表格支持的函数）\r
\r
不使用 Excel 专有函数，严格只用多维表格支持的函数（见下方函数列表）。\r
\r
**常见替换：**\r
| Excel 函数 | 多维表格替代 |\r
|---|---|\r
| `VLOOKUP` | `LOOKUP` |\r
| `XLOOKUP` | `LOOKUP` |\r
| `CONCAT` / `&` | `CONCATENATE` |\r
| `SUMPRODUCT` | 无直接等价，用 `SUM`+`FILTER` 组合 |\r
| `COUNTIFS` | `COUNTIF`（单条件）|\r
| `AVERAGEIF/AVERAGEIFS` | 用 `SUM`/`COUNTA` 手工计算 |\r
| `STDEV` | 不支持，提示用户 |\r
| `PERCENTRANK` | 不支持，提示用户 |\r
| `XLOOKUP/INDEX/MATCH` | `LOOKUP` |\r
\r
---\r
\r
### 第 3 步：将公式写入多维表格\r
\r
#### 方法 A：API 脚本写入（推荐）\r
使用 `scripts/feishu-bitable-api.js` 脚本直接写入公式字段，支持自动创建新字段或更新已有字段：\r
\r
```bash\r
node scripts/feishu-bitable-api.js set-formula \x3Capp_token> \x3Ctable_name> \x3Cfield_name> \x3Cformula> [formatter]\r
```\r
\r
**参数说明：**\r
- `app_token`：多维表格的 app_token\r
- `table_name`：表名（支持中文，如「客户档案表」）\r
- `field_name`：目标字段名（如「热爱得分」）\r
- `formula`：公式内容（建议用引号包裹）\r
- `formatter`（可选）：数字格式，默认 `0.0`（一位小数）。可选 `""`（整数）、`0.00`（两位小数）、`#,##0.0`（千分位）\r
\r
**示例：**\r
```bash\r
# 写入公式到「热爱得分」字段\r
node scripts/feishu-bitable-api.js set-formula MIASw72fgiRMCxkyqhtcuSwVnPf 客户档案表 "热爱得分" "REGEXEXTRACT(时间投入, \"（([0-9]+)分）\")"\r
\r
# 写入公式，保留两位小数\r
node scripts/feishu-bitable-api.js set-formula MIASw72fgiRMCxkyqhtcuSwVnPf 客户档案表 "毛利率" "(销售额-成本)/销售额" "0.00"\r
```\r
\r
> ⚠️ **公式含 `\x3C`、`>`、`|`、`&` 等特殊字符时，不能通过命令行传入**，PowerShell 会截断参数导致 `FormulaFieldPropertyError`。请改用方法 C（脚本文件 PUT 写入）。\r
\r
> 脚本会自动：通过环境变量获取 access_token → 通过表名查找 table_id → 检查字段是否存在 → 创建或更新字段。\r
\r
#### 方法 B：手动粘贴\r
直接输出公式，让用户复制到多维表格公式字段。\r
\r
#### 方法 C：直接调用 API\r
使用飞书开放平台 API 写入公式字段（`PUT` 方法）：\r
\r
```\r
PUT https://open.feishu.cn/open-apis/bitable/v1/apps/{app_token}/tables/{table_id}/fields/{field_id}\r
Authorization: Bearer {access_token}\r
Content-Type: application/json\r
\r
{\r
"field_name": "目标字段名",\r
"type": 20,\r
"property": {\r
"formatter": "0.0",\r
"formula_expression": "={公式内容}",\r
"type": {\r
"data_type": 2,\r
"ui_property": { "formatter": "0.0" },\r
"ui_type": "Number"\r
}\r
}\r
}\r
```\r
> - 字段类型 `20` 对应公式字段（Formula）\r
> - 更新已有字段用 `PUT`，创建新字段用 `POST`\r
> - `data_type: 2` 表示数字类型\r
\r
---\r
\r
## 多维表格支持的函数列表\r
\r
### 日期函数\r
`DATE` `DATEDIF` `DAY` `DAYS` `EDATE` `EOMONTH` `HOUR` `MINUTE` `SECOND` `MONTH` `NETWORKDAYS` `NOW` `TODAY` `WEEKDAY` `WEEKNUM` `WORKDAY` `YEAR` `DURATION` `TODATE`\r
\r
### 逻辑函数\r
`AND` `CONTAIN` `FALSE` `IF` `IFBLANK` `IFERROR` `IFS` `ISBLANK` `ISERROR` `ISNUMBER` `ISNULL` `MAP` `NOT` `OR` `RANK` `RECORD_ID` `SWITCH` `TRUE` `CONTAINSALL` `CONTAINSONLY` `RANDOMBETWEEN` `RANDOMITEM`\r
\r
### 文本函数\r
`CHAR` `CONCATENATE` `CONTAINTEXT` `FIND` `HYPERLINK` `LEFT` `RIGHT` `MID` `LEN` `LOWER` `UPPER` `REPLACE` `SUBSTITUTE` `TEXT` `SPLIT` `TRIM` `ENCODEURL` `FORMAT` `REGEXEXTRACT` `REGEXEXTRACTALL` `REGEXMATCH` `REGEXREPLACE`\r
\r
### 数字函数\r
`ABS` `ACOS` `ASIN` `ATAN` `ACOSH` `ASINH` `ATANH` `ATAN2` `AVERAGE` `CEILING` `COS` `SIN` `TAN` `COSH` `SINH` `TANH` `COUNTA` `COUNTIF` `FLOOR` `INT` `ISODD` `MAX` `MIN` `MEDIAN` `MOD` `PI` `POWER` `QUOTIENT` `ROUND` `ROUNDDOWN` `ROUNDUP` `SUM` `SUMIF` `VALUE` `SEQUENCE`\r
\r
### 位置函数\r
`DISTANCE`\r
\r
### 列表/查找函数\r
`ARRAYJOIN` `FILTER` `FIRST` `LAST` `LIST` `LISTCOMBINE` `LOOKUP` `NTH` `SORT` `SORTBY` `UNIQUE`\r
\r
---\r
\r
## 公式语法要点\r
\r
1. **字段引用**：直接用字段名，如 `销售额`、`时间投入`，字段名有空格也直接使用。\r
2. **函数调用**：与 Excel 一致，如 `IF(状态="完成", 1, 0)`。\r
3. **字符串**：用英文双引号 `""`。\r
4. **四则运算**：`+` `-` `*` `/` `^`。\r
5. **比较运算**：`=` `\x3C>` `>` `\x3C` `>=` `\x3C=`。\r
6. **不支持数组公式**（Ctrl+Shift+Enter 那种）。\r
\r
---\r
\r
## 示例交互\r
\r
### 示例 1：计算毛利率\r
**用户：** 字段有"销售额"和"成本"，要计算毛利率\r
\r
**输出：**\r
```\r
IFERROR((销售额-成本)/销售额, 0)\r
```\r
> 说明：毛利除以销售额，除零时返回0。将目标字段类型设为"公式"，格式设为百分比。\r
\r
---\r
\r
### 示例 2：根据分数评级\r
**用户：** 字段"分数"，90以上优秀，70以上良好，60以上合格，否则不合格\r
\r
**输出：**\r
```\r
IFS(分数>=90,"优秀",分数>=70,"良好",分数>=60,"合格",TRUE,"不合格")\r
```\r
\r
---\r
\r
### 示例 3：计算工龄（年）\r
**用户：** 字段"入职日期"，计算到今天的工龄\r
\r
**输出：**\r
```\r
DATEDIF(入职日期, TODAY(), "Y")\r
```\r
\r
---\r
\r
### 示例 4：条件拼接文本\r
**用户：** 字段"姓名"和"部门"，生成"姓名(部门)"格式\r
\r
**输出：**\r
```\r
CONCATENATE(姓名,"(",部门,")")\r
```\r
或简写：\r
```\r
姓名&"("&部门&")"\r
```\r
\r
---\r
\r
### 示例 5：蔬菜农场-计算总产值\r
**用户：** 字段"亩产"、"种植面积"、"销售单价"，计算总产值\r
\r
**输出：**\r
```\r
亩产*种植面积*销售单价\r
```\r
\r
---\r
\r
### 示例 6：蔬菜农场-计算亩均收入\r
**用户：** 字段"总收入"、"总面积"，计算亩均收入\r
\r
**输出：**\r
```\r
IFERROR(总收入/总面积, 0)\r
```\r
> 说明：防除零保护，总面积为空或0时返回0。\r
\r
---\r
\r
### 示例 7：检查中间字段公式是否正确\r
**场景：** 目标公式依赖 `学习投入分数` 字段，先确认它的公式\r
\r
```bash\r
node scripts/feishu-bitable-api.js get-field MIASw72fgiRMCxkyqhtcuSwVnPf 客户档案表 学习投入分数\r
```\r
\r
**输出示例（有问题）：**\r
```\r
字段 ID : fldZTgCSLa\r
类型 : 20（Formula）\r
公式 : ⚠️ "0"（未设置有效公式！）\r
```\r
> 发现公式为 `0`，需先修复该字段，再使用它参与计算。\r
\r
---\r
\r
## 注意事项\r
\r
- ⚠️ **中文括号不需要转义**：`（` 和 `）` 可以直接写在正则中，无需转义。\r
- ⚠️ **提取括号内分数的写法**：`VALUE(REGEXEXTRACT(字段名, "（\d+)分）"))`\r
- ⚠️ **不支持的 Excel 函数**：若用户需求涉及 Excel 专有函数（如 `XLOOKUP`、`SUMPRODUCT`、`STDEV`），需告知用户并给出多维表格的替代方案。\r
- ⚠️ **字段名区分大小写**：请与实际字段名完全一致。\r
- ⚠️ **关联字段**：引用"关联字段"中的子字段，用 `关联字段名.子字段名` 格式。\r
- ⚠️ **多选字段**：多选字段返回的是数组，搭配 `ARRAYJOIN`、`CONTAIN` 等函数使用。\r
- ⚠️ **API 写入时 PowerShell 中文问题**：在 Windows PowerShell 中执行包含中文的 API 调用可能出错，建议使用脚本文件（`.js`）而非直接命令行执行。\r
- ⚠️ **公式含特殊字符时禁止命令行传参**：公式中含 `\x3C`、`>`、`|`、`&` 等字符时，PowerShell 会将其解析为重定向/管道符导致参数被截断，**必须**将公式写入 `.js` 脚本文件后执行，不能直接通过 `set-formula` 命令行传入。\r
- ⚠️ **单选字段 IF 匹配前必须检查实际选项**：用 `get-field` 查看字段的真实选项列表，不要假设选项文本唯一——历史录入可能存在前置空格、引号写法差异等脏数据，需用 `OR` 兼容所有变体写法。\r
Usage Guidance
Before installing or running this skill:
- Recognize the main mismatch: the SKILL.md and the included script require FEISHU_APP_ID and FEISHU_APP_SECRET (sensitive credentials) but the registry metadata does not declare them. Ask the publisher to update metadata to list required env vars.
- The skill will call open.feishu.cn and can create/update formula fields in your bitable app_token. That is expected given its purpose, but it means it can change remote data—back up important formulas/tables or test on a non-production app first.
- Provide least-privileged credentials: create a dedicated Feishu app with minimal scopes and use its FEISHU_APP_ID / FEISHU_APP_SECRET, not your organization-wide admin credentials.
- Inspect the included script yourself (scripts/feishu-bitable-api.js) — it is readable and only communicates with open.feishu.cn, but you should confirm there are no unexpected endpoints or credential exfiltration. The script obtains tenant_access_token and uses it for API calls; it does not persist tokens to disk.
- Run the script in an isolated environment (container/VM) if you are unsure, and avoid running it on machines that hold other cloud credentials.
- Consider asking the author to: (1) declare required env vars in registry metadata; (2) document required OAuth scopes/permissions; and (3) add explicit confirmation steps before modifying fields to reduce the risk of accidental overwrites.
If the author cannot justify the metadata omission or provide clearer scope/permission information, treat the package with caution and prefer manual formula generation or a vetted alternative.
Capability Analysis
Type: OpenClaw Skill
Name: bitable-formula-generator
Version: 1.1.0
The skill bundle is a legitimate tool for generating and applying formulas to Feishu (Lark) Bitable fields. It includes a Node.js script (scripts/feishu-bitable-api.js) that interacts with the official Feishu Open Platform API (open.feishu.cn) to manage table structures and field properties. The script requires standard environment variables (FEISHU_APP_ID and FEISHU_APP_SECRET) for authentication, and the instructions in SKILL.md provide a clear, task-oriented workflow for the AI agent without any evidence of malicious intent, data exfiltration, or prompt injection attacks.
Capability Assessment
Purpose & Capability
The name/description (Feishu / 多维表格 formula generator) align with the included script and SKILL.md: the skill enumerates tables/fields and can create or update formula fields via Feishu bitable APIs. This capability legitimately requires access to a Feishu app (app_id/app_secret) and an app_token parameter. The incoherence: registry metadata lists no required env vars or primary credential, but SKILL.md and the included script explicitly require FEISHU_APP_ID and FEISHU_APP_SECRET. That mismatch should be resolved before trusting the package.
Instruction Scope
The runtime instructions explicitly instruct the agent/user to fetch tenant access tokens (via FEISHU_APP_ID/FEISHU_APP_SECRET), call open.feishu.cn APIs, read Excel exports or use provided field lists, and then write formulas back to the bitable (or instruct the user to paste). All actions are within the stated purpose (discover fields and update formula fields). However, these instructions include destructive actions (create/update fields) on remote data—so users should expect the skill to modify remote resources and take backups/confirmations.
Install Mechanism
No install spec; this is instruction-only with a single clear JS utility script. There is no download-from-arbitrary-URL behavior, no obfuscated code, and the script uses Node's https module and plain JSON. This is low install risk. The presence of a code file increases the surface compared with pure prose, but the code is readable and interfaces only with open.feishu.cn.
Credentials
The skill requires FEISHU_APP_ID and FEISHU_APP_SECRET (sensitive credentials) to obtain tenant_access_token and perform API calls, but the registry metadata lists no required env vars or primary credential. Requesting app credentials is proportionate to the stated function, but failing to declare them in metadata is a notable mismatch and a transparency problem. The script also requires the user-supplied app_token (from a bitable URL) which is meaningful for access. These credentials grant the skill the ability to list and modify tables/fields, so least-privilege credentials and explicit documentation are necessary.
Persistence & Privilege
The skill is not marked always:true and does not request permanent system-wide presence. The script does not modify other skills or system settings. It obtains a transient tenant_access_token at runtime and stores it in process/global memory only; there is no code that writes credentials to disk or to other services.
How to Use
- Make sure OpenClaw is installed (local or Docker)
- Run the install command in chat:
/install bitable-formula-generator - After installation, invoke the skill by name or use
/bitable-formula-generator - Provide required inputs per the skill's parameter spec and get structured output
Version History
v1.1.0
Update to 1.1.0
v1.0.0
Initial release
Metadata
Frequently Asked Questions
What is bitable-formula-generator?
飞书多维表格字段公式生成器。通过导出Excel分析字段结构，利用Excel公式逻辑生成多维表格兼容公式，并写入多维表格字段。 It is an AI Agent Skill for Claude Code / OpenClaw, with 113 downloads so far.
How do I install bitable-formula-generator?
Run "/install bitable-formula-generator" in the OpenClaw or Claude Code chat to install it in one step — no extra setup required.
Is bitable-formula-generator free?
Yes, bitable-formula-generator is completely free, licensed under MIT-0. You can download, install and use it at no cost.
Which platforms does bitable-formula-generator support?
bitable-formula-generator is cross-platform and runs anywhere OpenClaw / Claude Code is available (cross-platform).
Who created bitable-formula-generator?
It is built and maintained by chengfen1987 (@chengfen1987); the current version is v1.1.0.
More Skills