背景

1.业务需求，需要读取飞书的数据整理后打包成excel发给运营。

2.表格背景：在同事的知识库中的在线表格

3.参考：飞书api文档-写入：https://open.feishu.cn/document/server-docs/docs/sheets-v3/data-operation/append-data

4. 技术栈：nestjs

全程大概半个小时搞定，大家可以在看下飞书的api文档。
同时也练下全栈技术

好了，废话不多说，下边进入正题，放入核心代码，dto啥的可忽略

读取飞书表格数据

import { Injectable } from '@nestjs/common';
import axios from 'axios';
import { defaultPlatforms, formatError, formatPlatformName, formatSuccess, getExcelCellText } from 'src/util';
import { WhiteExcelDto } from './dto/whiteExcel.dto';

const feiShuUrl = 'https://mcndvais4o02.feishu.cn/wiki/Fp9ZwDLTsi2kSxkP40jc536YnWe'; // 飞书表格链接'
@Injectable()
export class FeiShuExcelService {
  private readonly appId = '你的id';
  private readonly appSecret = '你的appSecret';
  private accessToken: string;
  private tokenExpireTime: number;

  async getExcel({ url, platforms, columns }: { url: string; platforms?: string; columns?: string }) {
    const _url = url || `${feiShuUrl}?sheet=IDmK34`; // 飞书表格链接
    const wikiRes: any = await this.extractWikiTokens(_url);

    // 查知识库
    const zskRes = await this.getWikiNodes(wikiRes.spaceId);

    // 查表格
    const sheet = _url.split('?sheet=')[1];
    const inputColumns = (columns || '').trim().split(',').filter(Boolean);
    const columnNames = inputColumns?.length ? inputColumns : ['创建时间', '监测时间', '短剧BID', '剧单信息-短剧名', '平台', '侵权链接', '账号id', '账号名'];
    const excelRes = await this.getSheetInfo(zskRes?.obj_token, sheet, platforms, columnNames);

    return formatSuccess(excelRes);
  }

  // 获取表格元数据
  async getSheetInfo(spreadsheetToken: string, sheetId?: string, platforms = defaultPlatforms, columns?: string[]) {
    const token = await this.getAccessToken();
    // 示例值："Q7PlXT!A1:B2" 不加范围是整个表数据
    const url = `https://open.feishu.cn/open-apis/sheets/v2/spreadsheets/${spreadsheetToken}/values/${sheetId}`;
    const headers = { Authorization: `Bearer ${token}` };
    const response: any = await axios.get(url, { headers });
    const datas = response?.data?.data?.valueRange?.values || [];
    // console.log('==== response?.data: ', response?.data);
    const theader = datas[0].map(getExcelCellText);
    const tbody = datas.slice(1);
    const res: any = {
      len: tbody?.length,
      list: tbody,
    };
    const selectedColumnsIndexes = [];
    theader.forEach((item: any, index: number) => {
      if (columns?.length) {
        if (columns?.includes(item)) {
          selectedColumnsIndexes.push(index);
        }
      } else {
        selectedColumnsIndexes.push(index);
      }
    });
    const platformList = platforms.split(',');
    const platformIndex = theader.findIndex((item: any) => item === '平台');
    // console.log('datas', datas.length, platformList);
    const list = datas
      .filter((item: any, i) => (platformList.length ? platformList.includes(formatPlatformName(item[platformIndex])) : true))
      .map((item: any) => selectedColumnsIndexes.reduce((prev, next) => ({ ...prev, [theader[next]]: getExcelCellText(item[next]) }), {}));
    res.list = list;
    res.len = list.length;
    return res;
  }

  // 获取访问令牌
  private async getAccessToken(): Promise<any> {
    if (this.accessToken && Date.now() < this.tokenExpireTime) {
      return this.accessToken;
    }
    const response: any = (
      await axios.post('https://open.feishu.cn/open-apis/auth/v3/tenant_access_token/internal', {
        app_id: this.appId,
        app_secret: this.appSecret,
      })
    )?.data;
    console.log('==获取访问令牌-response: ', response.data);
    if (response.code !== 0) return false;
    this.accessToken = response.tenant_access_token;
    this.tokenExpireTime = Date.now() + (response.expire - 60) * 1000; // 提前60秒刷新
    return this.accessToken;
  }

  // 从知识库URL中提取知识库token和节点token
  private extractWikiTokens(url: string): { spaceId: string; nodeToken?: string } {
    const spaceMatch = url.match(/wiki\/([a-zA-Z0-9]+)/);
    const nodeMatch = url.match(/[?&]sheet=([a-zA-Z0-9]+)/);
    return {
      spaceId: spaceMatch ? spaceMatch[1] : null,
      nodeToken: nodeMatch ? nodeMatch[1] : null,
    };
  }

  // 获取知识库空间下的所有节点
  async getWikiNodes(wikeToken: string) {
    const token = await this.getAccessToken();
    try {
      const response = await axios.get(`https://open.feishu.cn/open-apis/wiki/v2/spaces/get_node`, {
        params: { obj_type: 'wiki', token: wikeToken },
        headers: { Authorization: `Bearer ${token}` },
      });
      // console.log('response.data: ', response.data);
      if (response.data.code !== 0) {
        return `获取知识库节点失败: ${response.data.msg}`;
      }
      const { obj_type, obj_token } = response?.data?.data?.node;
      return { obj_type, obj_token };
    } catch (error) {
      console.log('---- error: ', error);
      return error;
    }
  }
}

在飞书表格中写入数据

在上述的基础上增加如下代码即可

  // 从知识库URL中提取知识库token和节点token
  private extractWikiTokens(url: string): { spaceId: string; nodeToken?: string } {
    const spaceMatch = url.match(/wiki\/([a-zA-Z0-9]+)/);
    const nodeMatch = url.match(/[?&]sheet=([a-zA-Z0-9]+)/);
    return {
      spaceId: spaceMatch ? spaceMatch[1] : null,
      nodeToken: nodeMatch ? nodeMatch[1] : null,
    };
  }

  // 获取知识库空间下的所有节点
  async getWikiNodes(wikeToken: string) {
    const token = await this.getAccessToken();
    try {
      const response = await axios.get(`https://open.feishu.cn/open-apis/wiki/v2/spaces/get_node`, {
        params: { obj_type: 'wiki', token: wikeToken },
        headers: { Authorization: `Bearer ${token}` },
      });
      // console.log('response.data: ', response.data);
      if (response.data.code !== 0) {
        return `获取知识库节点失败: ${response.data.msg}`;
      }
      const { obj_type, obj_token } = response?.data?.data?.node;
      return { obj_type, obj_token };
    } catch (error) {
      console.log('---- error: ', error);
      return error;
    }
  }

至此大工告成，看下写入的效果

如果有帮到你请关注点赞哈，为我创作增加动力