0 基础学脚本：项目管理表的维护及通过脚本管理员工和物资「04-02」

阅读次数 265
share

4.5 封装查询数据函数

function 获取指定表ID (指定表表名) {
  const 所有表数据 = Application.Sheet.GetSheets();

  let 指定表ID;
  for (let i = 0; i < 所有表数据.length; i++) {
    const 本次查询表信息 = 所有表数据[i];
    
    const 表名称 = 本次查询表信息.name;
    const 表ID = 本次查询表信息.id;

    if (表名称 === 指定表表名) {
      指定表ID = 表ID;
    }
  }

  return 指定表ID;
}

function 获取指定表的所有数据 (指定表ID) {
  let 所有信息 = [];

  let 下一条记录 = null;
  let start = 1;
  const 缓存JSAPI = Application.Record;
  while (start || 下一条记录) {
    start--;
    本次数据查询 = 缓存JSAPI.GetRecords({
      SheetId: 指定表ID,
      Offset: 下一条记录,
    });
    下一条记录 = 本次数据查询.offset;

    const 本次总数据 = 本次数据查询.records;
    // 这里的 ...数组，即解耦这个数组，拆分成一条又一条的对象数据
    所有信息 = [...所有信息, ...本次总数据];
  }

  return 所有信息;
}

// 获取到 jsliang 这张表的数据
const 指定表ID = 获取指定表ID('jsliang');
const 指定表数据 = 获取指定表的所有数据(指定表ID);
console.log('指定表数据', 指定表数据);

这里封装了 2 个函数：

  • 根据对应表的名称，获取到它的 ID
  • 根据对应表的 ID，获取到它的数据

详细代码也很简单，这里不详细介绍，小伙伴可以自行查看。

当然，在上面我们仅【粗略】获取了所有信息，如果我们要获取指定信息呢？

比如，通过 字段名 和 字段值 去获取信息：

function 获取指定表ID(指定表表名) {
  const 所有表数据 = Application.Sheet.GetSheets();

  let 指定表ID;
  for (let i = 0; i < 所有表数据.length; i++) {
    const 本次查询表信息 = 所有表数据[i];

    const 表名称 = 本次查询表信息.name;
    const 表ID = 本次查询表信息.id;

    if (表名称 === 指定表表名) {
      指定表ID = 表ID;
    }
  }

  return 指定表ID;
}

function 根据表ID和指定数据查询该行数据(指定表ID, 查找字段, 查找内容) {
  let 下一条记录 = null;
  let start = 1;
  const 缓存JSAPI = Application.Record;
  while (start || 下一条记录) {
    start--;
    本次数据查询 = 缓存JSAPI.GetRecords({
      SheetId: 指定表ID,
      Offset: 下一条记录,
    });
    下一条记录 = 本次数据查询.offset;

    const 本次总数据 = 本次数据查询.records;
    for (let i = 0; i < 本次总数据.length; i++) {
      const 本次查询数据 = 本次总数据[i];
      if (查找字段 && 查找内容 && 本次查询数据.fields[查找字段] === 查找内容) {
        return 本次查询数据;
      }
    }
  }
}

function 根据表ID和指定数据修改该行数据(表ID, 行ID, 修改字段, 修改内容) {
  Application.Record.UpdateRecords({
    SheetId: 表ID,
    Records: [{
      id: 行ID,
      fields: {
        [修改字段]: 修改内容,
      }
    }],
  })
}

const 指定表ID = 获取指定表ID('jsliang');
const 查询结果 = 根据表ID和指定数据查询该行数据(指定表ID, '姓名', 'jsliang');
console.log('查询结果: ', 查询结果);

// 每次执行将更新【jsliang】这一行对应的年龄值
const 指定行ID = 查询结果.id;
根据表ID和指定数据修改该行数据(指定表ID, 指定行ID, '年龄', Math.floor(Math.random() * 100) + 1);

4.6 封装新增数据函数

function 获取指定表ID (指定表表名) {
  const 所有表数据 = Application.Sheet.GetSheets();

  let 指定表ID;
  for (let i = 0; i < 所有表数据.length; i++) {
    const 本次查询表信息 = 所有表数据[i];
    
    const 表名称 = 本次查询表信息.name;
    const 表ID = 本次查询表信息.id;

    if (表名称 === 指定表表名) {
      指定表ID = 表ID;
    }
  }

  return 指定表ID;
}

function 根据指定表ID新增数据 (指定表ID, 新增内容) {
  const 类型 = Object.prototype.toString.call(新增内容);
  console.log('类型: ', 类型);

  // 新增一条
  if (类型 === '[object Object]') {
    Application.Record.CreateRecords({
      SheetId: 指定表ID,
      Records: [{
        fields: 新增内容,
      }]
    });
    return;
  }

  // 新增多条
  Application.Record.CreateRecords({
    SheetId: 指定表ID,
    Records: 新增内容,
  });
}

// 往 jsliang 这张表新增一条记录
const 指定表ID = 获取指定表ID('jsliang');
const 要新增的数据 = {
  "姓名": String(new Date()),
  "年龄": Math.floor(Math.random() * 100) + 1,
}
根据指定表ID新增数据(指定表ID, 要新增的数据);

// 往 梁峻荣 这张表新增多条记录
const 梁峻荣表ID = 获取指定表ID('梁峻荣');
const 本次新增的数据 = [
  {
    fields: {
      "文本": String(new Date()),
      "数字": Math.floor(Math.random() * 100) + 1,
    }
  }, {
    fields: {
      "文本": String(new Date()),
      "数字": Math.floor(Math.random() * 100) + 1,
    }
  }
];
根据指定表ID新增数据(梁峻荣表ID, 本次新增的数据);

在 根据指定表ID新增数据 这个脚本中，我们通过 Object.prototype.toString.call() 来判断新增的数据，是一条还是多条。

如果是 1 条，那么这个类型是 [object Object]；

如果是多条，则是 [object Array]。

这样我们就很方便的新增数据了！

4.7 封装修改数据函数

function 获取指定表ID(指定表表名) {
  const 所有表数据 = Application.Sheet.GetSheets();

  let 指定表ID;
  for (let i = 0; i < 所有表数据.length; i++) {
    const 本次查询表信息 = 所有表数据[i];

    const 表名称 = 本次查询表信息.name;
    const 表ID = 本次查询表信息.id;

    if (表名称 === 指定表表名) {
      指定表ID = 表ID;
    }
  }

  return 指定表ID;
}

function 根据表ID和指定数据查询该行数据(指定表ID, 查找字段, 查找内容) {
  let 下一条记录 = null;
  let start = 1;
  const 缓存JSAPI = Application.Record;
  while (start || 下一条记录) {
    start--;
    本次数据查询 = 缓存JSAPI.GetRecords({
      SheetId: 指定表ID,
      Offset: 下一条记录,
    });
    下一条记录 = 本次数据查询.offset;

    const 本次总数据 = 本次数据查询.records;
    for (let i = 0; i < 本次总数据.length; i++) {
      const 本次查询数据 = 本次总数据[i];
      if (查找字段 && 查找内容 && 本次查询数据.fields[查找字段] === 查找内容) {
        return 本次查询数据;
      }
    }
  }
}

function 根据表ID和指定数据修改该行数据(表ID, 行ID, 修改字段, 修改内容) {
  Application.Record.UpdateRecords({
    SheetId: 表ID,
    Records: [{
      id: 行ID,
      fields: {
        [修改字段]: 修改内容,
      }
    }],
  })
}

const 指定表ID = 获取指定表ID('jsliang');
const 查询结果 = 根据表ID和指定数据查询该行数据(指定表ID, '姓名', 'jsliang');
console.log('查询结果: ', 查询结果);

// 每次执行将更新【jsliang】这一行对应的年龄值
const 指定行ID = 查询结果.id;
根据表ID和指定数据修改该行数据(指定表ID, 指定行ID, '年龄', Math.floor(Math.random() * 100) + 1);

修改数据的函数，相对于新增数据的函数，无非是增加了 根据表ID和指定数据修改该行数据 这个方法而已。

所以这里就不详细介绍了。

4.8 封装删除数据函数

function 获取指定表ID(指定表表名) {
  const 所有表数据 = Application.Sheet.GetSheets();

  let 指定表ID;
  for (let i = 0; i < 所有表数据.length; i++) {
    const 本次查询表信息 = 所有表数据[i];

    const 表名称 = 本次查询表信息.name;
    const 表ID = 本次查询表信息.id;

    if (表名称 === 指定表表名) {
      指定表ID = 表ID;
    }
  }

  return 指定表ID;
}

function 根据表ID和指定数据查询该行数据(指定表ID, 查找字段, 查找内容) {
  let 下一条记录 = null;
  let start = 1;
  const 缓存JSAPI = Application.Record;
  while (start || 下一条记录) {
    start--;
    本次数据查询 = 缓存JSAPI.GetRecords({
      SheetId: 指定表ID,
      Offset: 下一条记录,
    });
    下一条记录 = 本次数据查询.offset;

    const 本次总数据 = 本次数据查询.records;
    for (let i = 0; i < 本次总数据.length; i++) {
      const 本次查询数据 = 本次总数据[i];
      if (查找字段 && 查找内容 && 本次查询数据.fields[查找字段] === 查找内容) {
        return 本次查询数据;
      }
    }
  }
}

function 根据表ID和指定数据删除该行数据(表ID, 行ID) {
  Application.Record.DeleteRecords({ 
    SheetId: 表ID, 
    RecordIds: [行ID],
  });
}

const 指定表ID = 获取指定表ID('jsliang');
const 查询结果 = 根据表ID和指定数据查询该行数据(指定表ID, '年龄', 90);
console.log('查询结果: ', 查询结果);

// 每次执行需要查看这个指定的年龄值是否存在
const 指定行ID = 查询结果.id;
根据表ID和指定数据删除该行数据(指定表ID, 指定行ID);

删除函数同样如此~

那么问题来了：

  • 如果我要批量查找对应数据并删除呢？

请小伙伴们自行封装此函数，达到效果：

04-19.jpg

五、实例：合并地址信息

当我们 增删改查样样精通之后，再看我们一些实例，你会发现它竟如此简单：

  • 0 基础学脚本：合并地址信息「04」https://kdocs.cn/l/crMOLbPMBREJ

04-20.jpg

对于上面数据，用户不小心将【省】【市】以及【详细】信息进行了拆分。

而我们需要做的，就是点击脚本，将数据进行合并到【地址】字段上。

function 获取指定表ID (指定表表名) {
  const 所有表数据 = Application.Sheet.GetSheets();

  let 指定表ID;
  for (let i = 0; i < 所有表数据.length; i++) {
    const 本次查询表信息 = 所有表数据[i];
    
    const 表名称 = 本次查询表信息.name;
    const 表ID = 本次查询表信息.id;

    if (表名称 === 指定表表名) {
      指定表ID = 表ID;
    }
  }

  return 指定表ID;
}

function 获取指定表的所有数据 (指定表ID) {
  let 所有信息 = [];

  let 下一条记录 = null;
  let start = 1;
  const 缓存JSAPI = Application.Record;
  while (start || 下一条记录) {
    start--;
    本次数据查询 = 缓存JSAPI.GetRecords({
      SheetId: 指定表ID,
      Offset: 下一条记录,
    });
    下一条记录 = 本次数据查询.offset;

    const 本次总数据 = 本次数据查询.records;
    所有信息 = [...所有信息, ...本次总数据];
  }

  return 所有信息;
}

const 地址表ID = 获取指定表ID('地址表');
const 地址表数据 = 获取指定表的所有数据(地址表ID);

// 遍历数据，读取数据并且将拼接的数据填写到【地址】字段
for (let 行数 = 0; 行数 < 地址表数据.length; 行数++) {
  const 当前行 = 地址表数据[行数];
  const 当前行信息 = 当前行.fields;
  
  const 当前行ID = 当前行.id;
  const 省 = 当前行信息["省"];
  const 市 = 当前行信息["市"];
  const 详细地址 = 当前行信息["详细地址"];

  // 逻辑 1：如果是空白行，跳过
  if (!省 || !市 || !详细地址) {
    continue;
  }

  // 逻辑 2：拼接数据
  const 地址字段数据 = {
    detail: 详细地址,
    districts: [省, 市],
  };

  // 逻辑 3：填写更新行数据
  Application.Record.UpdateRecords({
    SheetId: 地址表ID,
    Records: [{
      id: 当前行ID,
      fields: {
        地址: 地址字段数据,
      },
    }],
  })
}

六、实例：维护项目管理表

下面我们来到本篇文章的终极关卡：

  • 项目管理表的维护及通过脚本管理员工和物资

它需要维护的逻辑如下所示：

【物资表】和【材料表】

规则 1：每天 17:00，将入库材料价格填写到当天入库材料中

（这里不能使用公式，因为材料表价格是波动的）

04-22.png

【项目管理表】和【员工表】

规则 2：需要根据【项目状态】，统计本次【物资损耗汇总】和【员工工资汇总】，并顺带更新【已结款】项目

（注意【项目状态】为完结推进员工表的【已结款】标记该项目）

规则 3：需要根据员工【关联：项目管理】和【已结款】字段，生成【未结款】字段

04-21.png

OK，下面我们开始操作脚本，实现这 3 条规则。

6.1 每天维护物资表信息

在我们的物资表中，我们的【入库单价】是需要根据当前价格，进行写死的。

所以，我们需要新增一个脚本，在每次执行的时候，将当天时间对应的【入库单价】进行填写，并汇总到【入库总价】上面。

04-22.png

因此，我们的代码应该是：

function 获取年月日时分秒 (date = new Date()) {
  return {
    "年": date.getFullYear(),
    "月": date.getMonth() + 1,
    "日": date.getDate(),
    "时": date.getHours(),
    "分": date.getMinutes(),
    "秒": date.getSeconds(),
    "星期": date.getDay() === 0 ? 7 : date.getDay(),
  };
};

function 获取指定表ID (指定表表名) {
  const 所有表数据 = Application.Sheet.GetSheets();

  let 指定表ID;
  for (let i = 0; i < 所有表数据.length; i++) {
    const 本次查询表信息 = 所有表数据[i];
    
    const 表名称 = 本次查询表信息.name;
    const 表ID = 本次查询表信息.id;

    if (表名称 === 指定表表名) {
      指定表ID = 表ID;
    }
  }

  return 指定表ID;
}

function 获取指定表的所有数据 (指定表ID) {
  let 所有信息 = [];

  let 下一条记录 = null;
  let start = 1;
  const 缓存JSAPI = Application.Record;
  while (start || 下一条记录) {
    start--;
    本次数据查询 = 缓存JSAPI.GetRecords({
      SheetId: 指定表ID,
      Offset: 下一条记录,
    });
    下一条记录 = 本次数据查询.offset;

    const 本次总数据 = 本次数据查询.records;
    所有信息 = [...所有信息, ...本次总数据];
  }

  return 所有信息;
}

const 物资表ID = 获取指定表ID('物资表');
const 物资表数据 = 获取指定表的所有数据(物资表ID);

const 材料表ID = 获取指定表ID('材料表');
const 材料表数据 = 获取指定表的所有数据(材料表ID);

// 查找当天时间对应的数据
const { 年, 月, 日 } = 获取年月日时分秒();
const 当天时间 = String(年) + '/' + String(月).padStart(2, '0') + '/' + String(日).padStart(2, '0');

// 记录单价
for (let 行数 = 0; 行数 < 物资表数据.length; 行数++) {
  const 当前行信息 = 物资表数据[行数];
  const 当前行ID = 当前行信息.id;
  const 当前行数据 = 当前行信息.fields;

  // 如果不是当天入库，则不统计
  if (当前行数据['入库时间'] !== 当天时间) {
    continue;
  }

  // 如果当天已经更新【入库单价】，则不更新（避免重复更新）
  if (当前行数据['入库单价']) {
    continue;
  }

  // 材料名称关联是唯一的，所以取 [0] 即可
  const 材料名称对应的材料表行ID = 当前行数据['材料名称'][0];

  // 获取对应的材料信息
  const 材料信息 = Application.Record.GetRecord({ SheetId: 材料表ID, RecordId: 材料名称对应的材料表行ID });

  // 将材料表中的【单价】填写到物资表中的【入库单价】
  const 材料单价 = 材料信息.fields["单价"];
  Application.Record.UpdateRecords({
    SheetId: 物资表ID,
    Records: [{
      id: 当前行ID,
      fields: {
        "入库单价": 材料单价,
      }
    }],
  })
}

当然，如果你已经开通【自动化任务】，那么你可以定期执行次脚本，否则需要手动操作。

这个自动化的操作方式，我们下一期补充~

6.2 统计员工工资汇总和物料损耗汇总

04-21.png

在项目状态为【完工】的条件下，我们应该对【物资损耗汇总】和【员工工资汇总】进行统计，并顺带更新【员工表】中的【已结款】字段对应的项目：

function 获取指定表ID(指定表表名) {
  const 所有表数据 = Application.Sheet.GetSheets();

  let 指定表ID;
  for (let i = 0; i < 所有表数据.length; i++) {
    const 本次查询表信息 = 所有表数据[i];

    const 表名称 = 本次查询表信息.name;
    const 表ID = 本次查询表信息.id;

    if (表名称 === 指定表表名) {
      指定表ID = 表ID;
    }
  }

  return 指定表ID;
}

function 获取指定表的所有数据(指定表ID) {
  let 所有信息 = [];

  let 下一条记录 = null;
  let start = 1;
  const 缓存JSAPI = Application.Record;
  while (start || 下一条记录) {
    start--;
    本次数据查询 = 缓存JSAPI.GetRecords({
      SheetId: 指定表ID,
      Offset: 下一条记录,
    });
    下一条记录 = 本次数据查询.offset;

    const 本次总数据 = 本次数据查询.records;
    所有信息 = [...所有信息, ...本次总数据];
  }

  return 所有信息;
}

const 项目管理表ID = 获取指定表ID('项目管理表');
const 项目管理表数据 = 获取指定表的所有数据(项目管理表ID);

const 员工表ID = 获取指定表ID('员工表');
const 员工表数据 = 获取指定表的所有数据(员工表ID);

const 物资表ID = 获取指定表ID('物资表');
const 物资表数据 = 获取指定表的所有数据(物资表ID);

// 步骤一：遍历项目管理表数据，查找完工状态数据
for (let 行数 = 0; 行数 < 项目管理表数据.length; 行数++) {
  const 当前行信息 = 项目管理表数据[行数];
  const 当前行数据 = 当前行信息.fields;
  const 当前行ID = 当前行信息.id;

  // 步骤二：只有完工的才进行统计
  const 项目状态 = 当前行数据["项目状态"];
  if (项目状态 === '完工') {
    
    // 步骤三：统计员工工资
    let 员工工资汇总 = 0;

    // 步骤四：查找员工表，更新【已结款】项目
    for (let 员工表行数 = 0; 员工表行数 < 员工表数据.length; 员工表行数++) {
      const 员工表行信息 = 员工表数据[员工表行数];
      const 员工表行数据 = 员工表行信息.fields;
      const 员工表行ID = 员工表行信息.id;
      const 员工项目关联 = 员工表行数据["关联：项目管理表"];

      // 步骤 4.1：如果包含关联信息，则进行更新【已结款】
      if (员工项目关联.includes(当前行ID)) {
        const 已结款项目 = 员工表行数据["已结款"];
        已结款项目.push(当前行ID);
        Application.Record.UpdateRecords({
          SheetId: 员工表ID,
          Records: [{
            id: 员工表行ID,
            fields: {
              "已结款": 已结款项目,
            }
          }],
        });

        // 步骤 3.1：统计本项目中员工工资
        const 当前员工工资 = 员工表行数据["员工工资"];
        员工工资汇总 += 当前员工工资;
      }
    }

    // 步骤 3.2：归纳员工总工资
    Application.Record.UpdateRecords({
      SheetId: 项目管理表ID,
      Records: [{
        id: 当前行ID,
        fields: {
          "员工工资汇总": 员工工资汇总
        }
      }],
    });

    // 步骤五：查找物资表，进行【物资损耗汇总】
    let 物资损耗汇总 = 0;
    for (let 物资表行数 = 0; 物资表行数 < 物资表数据.length; 物资表行数++) {
      const 物资表行信息 = 物资表数据[物资表行数];
      const 物资表行数据 = 物资表行信息.fields;
      const 入库项目关联 = 物资表行数据["入库项目"];

      // 步骤 5.1：如果包含关联信息，则获取入库总价
      if (入库项目关联.includes(当前行ID)) {
        const 入库总价 = 物资表行数据['入库总价'];
        物资损耗汇总 += Number(入库总价);
      }
    }

    // 步骤 5.2：归纳员工总工资
    Application.Record.UpdateRecords({
      SheetId: 项目管理表ID,
      Records: [{
        id: 当前行ID,
        fields: {
          "物资损耗汇总": 物资损耗汇总
        }
      }],
    });
  }
}

6.3 统计员工未结款项目

04-21.png

在上图的第 3 步中，我们的【未结款】字段，需要定期整理未结款项目，所以需要编写脚本：

注：这里的【已结款】在 6.2 中已自动统计

function 获取指定表ID (指定表表名) {
  const 所有表数据 = Application.Sheet.GetSheets();

  let 指定表ID;
  for (let i = 0; i < 所有表数据.length; i++) {
    const 本次查询表信息 = 所有表数据[i];
    
    const 表名称 = 本次查询表信息.name;
    const 表ID = 本次查询表信息.id;

    if (表名称 === 指定表表名) {
      指定表ID = 表ID;
    }
  }

  return 指定表ID;
}

function 获取指定表的所有数据 (指定表ID) {
  let 所有信息 = [];

  let 下一条记录 = null;
  let start = 1;
  const 缓存JSAPI = Application.Record;
  while (start || 下一条记录) {
    start--;
    本次数据查询 = 缓存JSAPI.GetRecords({
      SheetId: 指定表ID,
      Offset: 下一条记录,
    });
    下一条记录 = 本次数据查询.offset;

    const 本次总数据 = 本次数据查询.records;
    所有信息 = [...所有信息, ...本次总数据];
  }

  return 所有信息;
}

const 员工表ID = 获取指定表ID('员工表');
const 员工表数据 = 获取指定表的所有数据(员工表ID);
// console.log('员工表数据: ', 员工表数据);

for (let 行数 = 0; 行数 < 员工表数据.length; 行数++) {
  const 当前行信息 = 员工表数据[行数];
  const 当前行数据 = 当前行信息.fields;
  const 当前行ID = 当前行信息.id;

  const 参与的项目 = 当前行数据['关联：项目管理表'];
  // 如果没有填写【已结款】，那么它是 undefiend，我们需要改成 [] 空数据
  // 从而保证数据的正确性（因为 undefined 没有 includes 内置函数）
  const 完结项目 = 当前行数据['已结款'] || [];
  const 未完结项目 = [];

  // 遍历数据，查找未完结项目的 ID 集合
  for (let 参与项目序号 = 0; 参与项目序号 < 参与的项目.length; 参与项目序号++) {
    const 当前项目编号 = 参与的项目[参与项目序号];
    if (!完结项目.includes(当前项目编号)) {
      未完结项目.push(当前项目编号);
    }
  }
  // console.log('未完结项目: ', 未完结项目);

  // 更新未完结项目
  Application.Record.UpdateRecords({
    SheetId: 员工表ID,
    Records: [{
      id: 当前行ID,
      fields: {
        "未结款": 未完结项目,
      }
    }],
  })
}

OK，到这里这个实例就大功告成啦！

七、总结

感谢小伙伴们每一次赞赏、点赞和转发，这都是我更新的动力，非常感谢！

  • 【如果】你有更多的想法，可以帮助你完善你的项目/需求/业务。

但是，你担心自己写不出来。

那么，来找 jsliang 吧~

  • 【如果】你对于学习脚本过程中，碰到一些问题。

然后，你无法解决。

那么，来找 jsliang 吧！

  • 联系方式：

z-small-wechat.jpeg

WX: Liang123Gogo

我们，下期再见~

不折腾的前端，和咸鱼有什么区别！

觉得文章不错的小伙伴欢迎点赞/点 Star。

如果小伙伴需要联系 jsliang：

个人联系方式存放在 Github 首页，欢迎一起折腾~

争取打造自己成为一个充满探索欲，喜欢折腾，乐于扩展自己知识面的终身学习斜杠程序员。

jsliang 的文档库由 梁峻荣 采用 知识共享 署名-非商业性使用-相同方式共享 4.0 国际 许可协议 进行许可。基于 https://github.com/LiangJunrong/document-library 上的作品创作。本许可协议授权之外的使用权限可以从 https://creativecommons.org/licenses/by-nc-sa/2.5/cn/ 处获得。

jsliang341
0 Answers