记录一下wps多维表格公式和js脚本相关

ai提示词均采用gemini2.5pro

https://aistudio.google.com

https://gemini.google.com

其他ai：

gpt和claude都不太行，特别是js脚本

gpt很适合用来精简代码，删除无用的注释，错误输出，字段检查

grok3和上面那俩差不多

qwen3：非要本地化可以试试…也挺笨的

deepseek&腾讯元宝：达咩

文心一言&豆包：不做评价

公式

多维表公式案例共享表：

多维表函数列表https://kdocs.cn/l/cryI4BWCOzis

秒懂智能编号https://kdocs.cn/l/csywN1I7IoGB

多维表字符串提取https://kdocs.cn/l/cn7wbyGrYo1N

多维表TEXT函数技巧https://kdocs.cn/l/cmEIOU8rjJ43

emoji表情https://kdocs.cn/l/cuUbJRWe8UuA

易经学习工具https://kdocs.cn/l/cowtbtZdKBLW

父记录还能这么玩https://kdocs.cn/l/cgryU5KLbi4I

父子级联树https://kdocs.cn/l/cvt7gu4IJOY3

ai提示词：

修改提示词模板，填写函数要求在”编写多维表格函数：“

例如：

IF([@在岗状态]=”在岗”,CONCAT(“剩余”,[@当月在岗人数]*10-[@在岗分数计算],”分”),””)
新增当计算分数为负数时，输出为“分数池不足（-xx 分）”

提示词：

1
2
3
4
5
6
7
8
9
10
编写多维表格函数：

用原字段表示公式，例如[@可分配分数]，不要替代。
[@可分配分数]表示该行字段，[可分配分数]表示整列字段
只能用以下函数类型：

TODAY,MONTH,DATEDIF,DAY,HOUR,MINUTE,NOW,SECOND,WEEKDAY,WEEKDAY,WEEKNUM,WORKDAY,YEAR,DATE,DAYS360,NETWORKDAYS,NETWORKDAYS.PLUS,EDATE,EOMONTH
OR,IF,AND,ISBLANK,NOT,IFERROR,IFS
MID,CONCAT,LEFT,FIND,LEN,REPLACE,RIGHT,SEARCH,SUBSTITUTE,TRIM,CHAR,CONCATENATE,T,UPPER,TEXT,NUMBERSTRING,MIDB,FINDB,SEARCHB,FILTER,UNIQUE,SORT,SORTBY
AVERAGE,AVERAGEIF,AVERAGEIFS,COUNTBLANK,COUNTIFS,LARGE,MAXIFS,SMALL,SUM,SUMIFS,RANK,ROUNDDOWN,ROUND,ROUNDUP,MIN,INT,MOD,MAX,EVEN,ODD,EXP,LOG,POWER,SQRT,VALUE,XLOOKUP

Javascript脚本

新版api：

官方文档：

api字段：https://open.wps.cn/documents/app-integration-dev/guide/dbsheet/Api/Sheet.html

api文档：https://open.wps.cn/documents/app-integration-dev/guide/dbsheet/Api/api-instro.html

https://airsheet.wps.cn/docs/apiV2/overview.html (不是很全)

老版api（很难用，别用）

官方文档：

https://airsheet.wps.cn/docs/api/dbsheet/Field.html

https://open.wps.cn/documents/app-integration-dev/guide/dbsheet/AirScript/AirScript-build-in.html

老版api教程

b站(部分付费)：https://www.bilibili.com/cheese/play/ep1212770

小红书：https://www.xiaohongshu.com/user/profile/5f20562a000000000101c2c0?xsec_token=ABfsbU0Nv267F51svgj85KNNALVBHcuZbgn7mVsDBvRPA=&xsec_source=pc_note

ai提示词：

由于大部分api都是wps私有的，不像公式那样具有通用性，提示词字典目前还在编写中

目前可采用借鉴参考写法，例如：

修改函数，从”@数据表”字段获取数据表信息，”@视图”字段获取视图信息
执行const res=Application.Sheets(“@数据表”).Views(“@视图”).ViewShare.SetEnable(true)后
获取Application.Sheets(“@数据表”).Views(“@视图”).ViewShare.ShareUrl，修改前缀后填入到”@链接”字段

参考以下两个函数，编写查询数据表所有sheets id和views id
在代码前添加本次修改说明

  • 修改说明 :

*/

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
参考以下两个函数，编写查询数据表所有sheets id和views id
在代码前添加本次修改说明
 * 修改说明 :

 */


sheet id查询：
function main() {
  if (Application.Sheets && typeof Application.Sheets.Count === 'number') {
    const totalSheets = Application.Sheets.Count;
    console.log(`Found ${totalSheets} sheets.`);

    for (let i = 1; i <= totalSheets; i++) {
      const sheet = Application.Sheets(i);
      console.log(`Sheet ${i}: Name = "${sheet.Name}", ID = "${sheet.Id}"`);
    }
  }
}

main();

视图id查询：
function main() {
  // 获取指定工作表的Views对象
  const views = Application.Sheets("张").Views;

  // 获取视图的总数
  const viewsCount = views.Count; // 或者可能是 .count，具体请参考WPS官方文档

  console.log("该工作表的视图总数: " + viewsCount);

  // 遍历所有视图
  for (let i = 1; i <= viewsCount; i++) { // 注意：在VBA和类似的API中，集合的索引通常从1开始
    const view = views.Item(i); // 或者使用 views(i)
    console.log("视图 " + i + " 的名称是: " + view.Name);
    console.log("视图 " + i + " 的id是: " + view.Id);
  }
}

main();

通用公式：

数据表及视图ID查询：
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
function printAllSheetAndViewsIdsFinal() {
  // 步骤 1: 在尝试任何操作前，先对最顶层的 Application.Sheets 对象做最终检查。
  if (!Application || !Application.Sheets || typeof Application.Sheets.Count === 'undefined') {
    console.log("致命错误：无法访问 Application.Sheets 对象。脚本无法执行。");
    return; // 如果连工作表列表都拿不到，直接退出。
  }

  const totalSheets = Application.Sheets.Count;
  console.log(`系统报告发现 ${totalSheets} 个工作表。开始详细遍历...`);
  console.log('==========================================');

  // 步骤 2: 遍历所有工作表
  for (let i = 1; i <= totalSheets; i++) {
    try {
      // **将所有可能出错的操作都放在 try 块中**
      const sheet = Application.Sheets(i);

      // 仍然保留对 sheet 对象的检查，这是一种好习惯
      if (!sheet) {
        console.log(`警告：索引为 ${i} 的工作表对象无效或为空，已跳过。`);
        // 使用 continue 跳出本次循环，进入下一次
        continue; 
      }

      // 打印工作表信息，这步也可能出错（例如 sheet.Name 不存在）
      console.log(`工作表 ${i}: 名称 = "${sheet.Name}", ID = "${sheet.Id}"`);

      // 检查并处理视图
      if (sheet.Views && typeof sheet.Views.Count !== 'undefined') {
        const viewsCount = sheet.Views.Count;

        if (viewsCount > 0) {
          for (let j = 1; j <= viewsCount; j++) {
            const view = sheet.Views.Item(j);
            if (view) {
              console.log(`  └- 视图 ${j}: 名称 = "${view.Name}", ID = "${view.Id}"`);
            } else {
              console.log(`  └- 警告：工作表 "${sheet.Name}" 下索引为 ${j} 的视图无法获取。`);
            }
          }
        } else {
          console.log(`  └- (此工作表下没有视图)`);
        }
      } else {
        console.log(`  └- (无法读取此工作表的视图信息)`);
      }

    } catch (error) {
      // **步骤 3: 捕获在 try 块中发生的任何错误**
      // 如果上面的任何一步（获取sheet、获取Name、获取Views等）失败，代码会立即跳转到这里
      console.log(`!!! 处理索引为 ${i} 的工作表时发生意外错误，已强制跳过。`);
      // 打印具体的错误信息，有助于调试
      console.log(`  └- 错误详情: ${error.message}`);
    } finally {
      // 步骤 4: 无论是否出错，都打印分隔线，保持格式整洁
      console.log('------------------------------------------');
    }
  }

  console.log("所有工作表遍历完成。");
}

// --- 执行函数 ---
printAllSheetAndViewsIdsFinal();
数据表&视图跳转链接生成:

使用要求：

  1. 需要有一个与函数字段名对应的基础的数据表：
数据表 视图 链接 生成状态

“视图”和”生成状态”可以为空，无视图信息，默认获取第一视图链接

默认采用数据表ID索引方式(不受数据表改名影响)

若需采用数据表名称索引方式请将以下代码”.ItemById”部分删除
const entranceSheet = Application.Sheets.ItemById(entranceSheetId);

  1. 配置信息，按需修改

// — 配置区域 —
const entranceSheetId = “1”; // “画册入口”工作表的ID
const sheetSourceFieldName = “@数据表”; // 存储数据表名称的字段
const viewSourceFieldName = “@视图”; // 存储视图名称的字段
const targetFieldName = “@链接”; // 需要写入分享链接的字段
const statusFieldName = “@生成状态”; // 用于记录成功或失败状态的字段
const newDomain = “http://10.150.153.153/weboffice/office/“; // 新的目标域名
const oldDomain = “http://localhost/office/“; // 需要被替换的旧域名

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
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
/**
 * ----------------------------------------------------------------
 * 修改说明 :
 * 1. 新增辅助函数 `tryWriteStatus`：
 *    - 此函数专门用于写入状态信息到 "@生成状态" 字段。
 *    - 如果该字段不存在或写入时发生任何错误，它会捕获异常，确保整个脚本不会因此中断。
 * 
 * 2. 调整主逻辑 `main`：
 *    - 字段容错：通过 try-catch 块安全地读取 "@视图" 字段的值。如果该字段不存在，脚本会继续执行而不会报错。
 *    - 默认视图：当 "@视图" 字段为空或不存在时，脚本将自动使用目标数据表的第一个视图作为默认值。
 *    - 状态写入：所有对 "@生成状态" 字段的写入操作都已替换为调用 `tryWriteStatus` 函数，以实现错误隔离。
 *    - 日志优化：更新了控制台输出的日志信息，使其在各种情况下都更清晰。
 * ----------------------------------------------------------------
 */

/**
 * 辅助函数：尝试写入状态到指定单元格。
 * 如果字段不存在或写入出错，该函数会捕获错误，防止脚本中断，并在控制台打印提示。
 * @param {object} entranceSheet - "画册入口"工作表对象。
 * @param {number} recordIndex - 当前处理的记录行号 (从1开始)。
 * @param {string} fieldName - 要写入的字段名称 (例如 "@生成状态")。
 * @param {string} message - 要写入的状态信息。
 */
function tryWriteStatus(entranceSheet, recordIndex, fieldName, message) {
  try {
    // 尝试获取单元格并写入值
    const statusCell = entranceSheet.RecordRange(recordIndex, fieldName);
    if (statusCell) {
      statusCell.Value = message;
    }
  } catch (e) {
    // 如果字段不存在或发生其他错误，在控制台打印一个提示信息，然后继续执行
    console.log(`(提示) 无法向字段 "${fieldName}" 写入状态。错误: ${e.message}。脚本将继续运行。`);
  }
}


/**
 * 主函数：用于在“画册入口”表中，根据指定的“@数据表”和“@视图”字段，生成并回填分享链接。
 * 新增功能：当代码出现找不到@数据表或@视图时，在"@生成状态"中写入该行失败原因。
 * 修改功能：支持"@视图"和"@生成状态"字段不存在的情况；"@视图"为空时使用默认视图；写入状态失败不影响运行。
 */
function main() {
  // --- 配置区域 ---
  const entranceSheetId = "1"; // "画册入口"工作表的ID
  const sheetSourceFieldName = "@数据表"; // 存储数据表名称的字段
  const viewSourceFieldName = "@视图";   // 存储视图名称的字段
  const targetFieldName = "@链接";   // 需要写入分享链接的字段
  const statusFieldName = "@生成状态"; // 用于记录成功或失败状态的字段
  const newDomain = "http://10.150.153.153/weboffice/office/"; // 新的目标域名
  const oldDomain = "http://localhost/office/"; // 需要被替换的旧域名

  // --- 脚本核心逻辑 ---

  // 1. 获取“画册入口”工作表对象
  const entranceSheet = Application.Sheets.ItemById(entranceSheetId);
  if (!entranceSheet) {
    console.log(`错误：无法找到ID为 "${entranceSheetId}" 的工作表。`);
    return;
  }

  const recordCount = entranceSheet.RecordRange.Count;
  if (recordCount === 0) {
    console.log("“画册入口”表中没有记录，脚本执行完毕。");
    return;
  }
  
  console.log(`开始处理“画册入口”表中的 ${recordCount} 条记录...`);

  // 2. 遍历“画册入口”中的每一条记录
  for (let i = 1; i <= recordCount; i++) {
    // 从 "@数据表" 字段获取名称
    const sheetNameToFind = entranceSheet.RecordRange(i, sheetSourceFieldName).Value;

    // 检查 "@数据表" 字段是否有效，如果无效则跳过
    if (!sheetNameToFind || String(sheetNameToFind).trim() === "") {
        const skipMessage = "已跳过：“@数据表”字段为空。";
        console.log(`跳过第 ${i} 行，因为“@数据表”字段为空。`);
        tryWriteStatus(entranceSheet, i, statusFieldName, skipMessage);
        continue; // 继续处理下一条记录
    }

    try {
        // 安全地尝试获取 "@视图" 字段的值
        let viewNameToFind = null;
        try {
            // 如果@视图字段存在，则读取其值
            viewNameToFind = entranceSheet.RecordRange(i, viewSourceFieldName).Value;
        } catch (e) {
            // 如果@视图字段不存在，则忽略错误，viewNameToFind将保持为null
            console.log(`(提示) 第 ${i} 行：字段 "${viewSourceFieldName}" 不存在，将使用默认视图。`);
        }

        // 3. 获取目标数据表对象
        const targetSheet = Application.Sheets(sheetNameToFind);
        if (!targetSheet) {
            const errorMessage = `失败：找不到名为 [${sheetNameToFind}] 的数据表。`;
            console.log(`处理第 ${i} 行时出现问题：${errorMessage}`);
            tryWriteStatus(entranceSheet, i, statusFieldName, errorMessage);
            continue;
        }
        
        // 4. 获取目标视图对象
        let targetView = null;
        let viewIdentifier = ""; // 用于日志和错误消息

        if (viewNameToFind && String(viewNameToFind).trim() !== "") {
            // 如果提供了视图名称，则按名称查找
            viewIdentifier = `视图 [${viewNameToFind}]`;
            targetView = targetSheet.Views(viewNameToFind);
        } else {
            // 如果视图名称为空或字段不存在，则使用第一个视图作为默认值
            viewIdentifier = "默认（第一个）视图";
            if (targetSheet.Views.Count > 0) {
                targetView = targetSheet.Views.Item(1); // 假设Item(1)获取第一个视图
            }
        }

        // 5. 如果成功获取到视图，则继续处理
        if (targetView) {
          // 启用该视图的分享
          targetView.ViewShare.SetEnable(true);

          // 获取分享URL
          const originalUrl = targetView.ViewShare.ShareUrl;

          if (originalUrl) {
            // 修改 URL 域名
            const newUrl = originalUrl.replace(oldDomain, newDomain);

            // 按照指定格式写入到 "@链接" 字段
            entranceSheet.RecordRange(i, targetFieldName).Value = Application.DBCellValue({
              address: newUrl,
              display: "点击前往"
            });
            
            // 写入成功状态
            const successMessage = `为数据表 [${sheetNameToFind}] 的${viewIdentifier}生成链接成功。`;
            tryWriteStatus(entranceSheet, i, statusFieldName, "生成成功");
            console.log(`成功处理第 ${i} 行：${successMessage}`);
          } else {
             const warningMessage = `警告：无法为数据表 [${sheetNameToFind}] 的${viewIdentifier}获取 ShareUrl。`;
             console.log(`在第 ${i} 行，${warningMessage}`);
             tryWriteStatus(entranceSheet, i, statusFieldName, warningMessage);
          }
        } else {
           const warningMessage = `失败：在数据表 [${sheetNameToFind}] 中未找到${viewIdentifier}。`;
           console.log(`在第 ${i} 行，${warningMessage}`);
           tryWriteStatus(entranceSheet, i, statusFieldName, warningMessage);
        }
    } catch (error) {
       // 捕获处理单行记录时发生的任何意外错误
       const errorMessage = `错误：处理第 ${i} 行时发生意外：${error.message}`;
       console.log(errorMessage);
       tryWriteStatus(entranceSheet, i, statusFieldName, `错误：${error.message}`);
    }
  }

  console.log("脚本执行完成。");
}

// 执行主函数
main();
遍历新增：

遍历源数据表的某一个字段，批量写入到另外表格中：

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
function main() {
    const sourceSheetId = "1"; // 遍历源表
    const summarySheetId = "27"; // 姓名写入表1
    const regularChallengeSheetId = "9"; // 姓名写入表2
    const scoringSheetId = "37"; // 姓名写入表3
    const fieldName = "@姓名";

    const sourceSheet = Application.Sheets.ItemById(sourceSheetId);
    const summarySheet = Application.Sheets.ItemById(summarySheetId);
    const regularChallengeSheet = Application.Sheets.ItemById(regularChallengeSheetId);
    const scoringSheet = Application.Sheets.ItemById(scoringSheetId);

    const recordCount = sourceSheet.RecordRange.Count;
    const employeeData = sourceSheet.RecordRange(`1:${recordCount}`, fieldName).Value;

    const employeesToAdd = [];
    for (let i = 0; i < employeeData.length; i++) {
        const row = employeeData[i];
        if (row && row[0] && String(row[0]).trim() !== "") {
            employeesToAdd.push([row[0]]);
        }
    }

    function addEmployeesToSheet(sheet, field) {
        const currentCount = sheet.RecordRange.Count;
        sheet.RecordRange.Add(undefined, undefined, employeesToAdd.length);

        for (let i = 0; i < employeesToAdd.length; i++) {
            const targetRowIndex = currentCount + 1 + i;
            sheet.RecordRange(targetRowIndex, field).Value = employeesToAdd[i][0];
        }
    }

    addEmployeesToSheet(summarySheet, fieldName);
    addEmployeesToSheet(regularChallengeSheet, fieldName);
    addEmployeesToSheet(scoringSheet, fieldName);

    console.log(`成功同步了 ${employeesToAdd.length} 条员工数据。`);
}

main();
许可协议

本文采用 署名-非商业性使用-相同方式共享 4.0 国际 许可协议，转载请注明出处。