通过excel获取百度推广账户余额信息

使用工具 excel365 或 power bi

let 
fn = (账户,密码,秘钥,开始日期 as date ,结束日期 as date, optional 媒介 as text,optional 类别 as text) =>


if 类别="关键词" th犀利士
en

let
        zd = if 媒介="WAP" then 2 else if 媒介="PC" then 1 else 0,
        url="https://api.baidu.com/json/sms/service/ReportService/getRealTimeData",
        content="{
            ""header"":{
                ""username"":"""&账户&""",
                ""password"":"""&密码&""",
                ""token"":"""&秘钥&"""
            },
            ""body"":{
                ""realTimeRequestType"":{
                    ""performanceData"":[
                        ""impression"",
                        ""click"",
                        ""cost"",
                        ""cpc"",
                        ""position"",
                        ""ctr""
                    ],
                    ""levelOfDetails"":11,
                    ""reportType"":14,
                    ""startDate"":"""&Date.ToText(开始日期,"yyyy-MM-dd")&""",
                    ""endDate"":"""&Date.ToText(结束日期,"yyyy-MM-dd")&""",
                    ""device"":"""&Number.ToText(zd)&""",
                    ""untilOfTime"":5,
                    ""number"":10000
                }
            }
        }",





        request=Json.Document(Web.Contents(url,[Content=Text.ToBinary(content)])),
        data = Table.FromRecords(request[body][data]),
        title = {{"展现","点击","消费","平均点击价格","平均排名","点击率"},{"账户","计划","单元","关键词"}},
        kpi = Table.TransformColumns(data,{"kpis",each Record.FromList(_,title{0})}),
        name = Table.TransformColumns(kpi,{"name",each Record.FromList(_,title{1})}),
        expand_kpi = Table.ExpandRecordColumn(name, "kpis", title{0}),
        expand_name = Table.ExpandRecordColumn(expand_kpi, "name", title{1}),
        delete = Table.RemoveColumns(expand_name,{"平均点击价格", "点击率"}),
        types = Table.TransformColumnTypes(delete,{{"展现", Int64.Type}, {"点击", Int64.Type}, {"消费", type number}, {"计划", type text},{"date", type date}}),
        shuju = Table.AddColumn(try types otherwise Table.FromRows({{null, null, null,null, null,null,null, null} },{"账户", "计划", "id","展现","点击","消费","平均排名","date"}), "媒介", each if 媒介="WAP" then "WAP" else if 媒介="PC" then "PC" else "-")
in
        shuju
else

let
        zd = if 媒介="WAP" then 2 else if 媒介="PC" then 1 else 0,
        url="https://api.baidu.com/json/sms/service/ReportService/getRealTimeData",
        content="{
            ""header"":{
                ""username"":"""&账户&""",
                ""password"":"""&密码&""",
                ""token"":"""&秘钥&"""
            },
            ""body"":{
                ""realTimeRequestType"":{
                    ""performanceData"":[
                        ""impression"",
                        ""click"",
                        ""cost"",
                        ""cpc"",
                        ""ctr""
                    ],
                    ""levelOfDetails"":3,
                    ""reportType"":10,
                    ""startDate"":"""&Date.ToText(开始日期,"yyyy-MM-dd")&""",
                    ""endDate"":"""&Date.ToText(结束日期,"yyyy-MM-dd")&""",
                    ""device"":"""&Number.ToText(zd)&""",
                    ""untilOfTime"":5,
                    ""number"":10000
                }
            }
        }",
        request=Json.Document(Web.Contents(url,[Content=Text.ToBinary(content)])),
        data = Table.FromRecords(request[body][data]),
        title = {{"展现","点击","消费","平均点击价格","点击率"},{"账户","计划"}},
        kpi = Table.TransformColumns(data,{"kpis",each Record.FromList(_,title{0})}),
        name = Table.TransformColumns(kpi,{"name",each Record.FromList(_,title{1})}),
        expand_kpi = Table.ExpandRecordColumn(name, "kpis", title{0}),
        expand_name = Table.ExpandRecordColumn(expand_kpi, "name", title{1}),
        delete = Table.RemoveColumns(expand_name,{"平均点击价格", "点击率"}),
        types = Table.TransformColumnTypes(delete,{{"展现", Int64.Type}, {"点击", Int64.Type}, {"消费", type number}, {"计划", type text},{"date", type date}}),
        shuju = Table.AddColumn(try types otherwise Table.FromRows({{null, null, null,null, null,null, null} },{"账户", "计划", "id","展现","点击","消费","date"}), "媒介", each if 媒介="WAP" then "WAP" else if 媒介="PC" then "PC" else "-")
in
        shuju,


fnType = type function (账户 as text ,密码 as text ,秘钥 as text ,开始日期 as date ,结束日期 as date, 

    optional 媒介 as(type text 
            meta [
                    Documentation.FieldCaption = "终端:PC 或 WAP ; 默认为空，不分终端",
                    Documentation.FieldDescription = "终端：填写:PC 或 WAP ; 默认为空，不分终端",
                    Documentation.AllowedAllowedvalues={"PC","WAP","汇总"}
                ]
            ),
    optional 类别 as(type text 
            meta [
                    Documentation.FieldCaption = "类别:关键词 或 计划 ; 默认为计划",
                    Documentation.FieldDescription = "类别:关键词 或 计划 ; 默认为计划",
                    Documentation.AllowedAllowedvalues={"计划","关键词"}
                ]
            )
        ) as list meta[]

in
        Value.ReplaceType(fn ,fnType)

效果视频

• 获取百度推广api
• 使用excel获取百度推广余额等信息
• 使用连接可以实现自动获取百度推广账户余额，在power bi  中使用此函数同样也可达到自动获取账户余额。