【Power Query】使用Excel抓取淘宝天猫所有类目分类和cateId对应关系

先看成果吧，分位多列排版和单列排版，根据你的运营需求选择合适排版


Power Query（M语音）是一个非常强大数据清洗工具，我在日常的运营中，也是经常用到（比如抓取生意参谋、合并文件、文本处理等等）直接上源码吧

// 一级类目--多列排版
let
    url = "https://router.publish.taobao.com/router/asyncOpt.htm?optType=categorySelectChildren",
    query = [
        optType = "categorySelectChildren"
    ],
    headers = [
        #"user-agent"="Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/75.0.3770.142 Safari/537.36",
        cookie = Excel.CurrentWorkbook(){[Name="cookie"]}[Content]{0}[cookie]
    ],
    web = Json.Document(Web.Contents(url, [Headers=headers,Query=query]))[data][dataSource],
    转换为表 = Table.FromList(web, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"展开的“Column1”" = Table.ExpandRecordColumn(转换为表, "Column1", {"groupName", "children"}, {"groupName", "children"}),
    #"展开的“children”" = Table.ExpandListColumn(#"展开的“Column1”", "children"),
    #"展开的“children”1" = Table.ExpandRecordColumn(#"展开的“children”", "children", {"id", "name"}, {"id", "name"}),
    已添加自定义 = Table.AddColumn(#"展开的“children”1", "二级", each fx([id])),
    #"展开的“二级”" = Table.ExpandTableColumn(已添加自定义, "二级", {"id", "name"}, {"二级id", "二级name"}),
    已添加自定义1 = Table.AddColumn(#"展开的“二级”", "三级", each fx([二级id])),
    #"展开的“三级”" = Table.ExpandTableColumn(已添加自定义1, "三级", {"id", "name"}, {"三级id", "三级name"}),
    已添加自定义2 = Table.AddColumn(#"展开的“三级”", "一级类目", each [groupName]&">"&[name]),
    已添加自定义3 = Table.AddColumn(已添加自定义2, "二级类目", each [一级类目]&">"&[二级name]),
    已添加自定义4 = Table.AddColumn(已添加自定义3, "叶子类目", each [二级类目]&">"&[三级name]),
    重排序的列 = Table.ReorderColumns(已添加自定义4,{"groupName", "id", "一级类目", "name", "二级id", "二级类目", "二级name", "三级id", "叶子类目", "三级name"}),
    删除的列 = Table.RemoveColumns(重排序的列,{"groupName", "name", "二级name", "三级name"})
in
    删除的列

// fx
(x)=>
let
    url = "https://router.publish.taobao.com/router/asyncOpt.htm?optType=categorySelectChildren",
    query = [
        optType = "categorySelectChildren",
        catId = Text.From(x)
    ],
    headers = [
        #"user-agent"="Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/75.0.3770.142 Safari/537.36",
        cookie = Excel.CurrentWorkbook(){[Name="cookie"]}[Content]{0}[cookie]
    ],
    web = Json.Document(Web.Contents(url, [Headers=headers,Query=query]))[data][dataSource],
    结果 = try Table.ExpandRecordColumn(Table.FromList(web, Splitter.SplitByNothing(), null, null, ExtraValues.Error), "Column1", {"id", "name"}, {"id", "name"}) otherwise null
in
    结果

下面这个是单列排版方式

// 所有类目(一列)
let
    源 = Excel.CurrentWorkbook(){[Name="表4"]}[Content],
    删除的副本 = Table.Distinct(源)
in
    删除的副本

下面是成品Excel文件
https://download.csdn.net/download/qq980469001/11415810