最近在玩钉钉日志,企业填写简单数据后方便汇总到一起比较实用的工具,但数据填写以后还是需要下载日志报表,比较麻烦。
在做BI的时候,可不可以用pq获取这些数据同步更新呢?查阅钉钉开发文档,日志是可以通过api接口获取。
那么写个pq(power query)自定义函数获取即可。
为了方便演示,新建一个日志填写模板。
标题:测试日志
字段:日期,测试项目,测试值
设计日志模板
填写测试数据
填写好的日志
1、自定义函数Fxdingding
看到这一串不要慌,直接复制到自己的pq中保存为Fxdingding即可。
let fx=(AppKey as text ,AppSecret as text,Month as date,RecodeName as text,NameList as list ) as table=> let token=Json.Document( Text.FromBinary(Web.Contents("https://oapi.dingtalk.com/gettoken?appkey="&AppKey&"&appsecret="&AppSecret)))[access_token], //======================时间戳转北京时间========================================================================= BeijingTime=(x as number) => let t=DateTime.FromFileTime((11644473600000+x)*10000) in t, //======================日期转换为时间戳========================================================================= UnixTimestampStart=(m as date)=> let 源 = ((Number.From(m)-70*365-19)*86400-8*3600)*1000, tf=Text.From(源) in tf, //======================提供日期的月末最后一天转换为时间戳========================================================= UnixTimestampEnd=(m as date)=> let t=Date.EndOfMonth(m), st = ((Number.From(t)-70*365-19)*86400-8*3600)*1000+86399999, tf=Text.From(st) in tf, //======================根据cursor获取每页recode================================================================= fxrl=(n as number,name as text)=> let fx=Json.Document( Web.Contents("https://oapi.dingtalk.com/topapi/report/list?" , [Query=[access_token=token] ,Content=Text.ToBinary("{'start_time':"&UnixTimestampStart(Month)&",'end_time':"&UnixTimestampEnd(Month)&",'template_name':'"&name&"','cursor':"&Text.From(n)&",'size':20}") ] ) )[result] in fx, //======================每页的recode转换成表===================================================================== fxrt = (listr as list)=> let fx=Table.Combine(List.Transform(listr,(R)=> Table.FromRecords({Record.TransformFields( R,{"contents", each Record.FromList(Table.FromRecords(_)[value],Table.FromRecords(_)[key])})}))) in fx, //======================获取corsorlist========================================================================== //其中100是个参数,表2000条数据,多数需求能满足,大约这个数用条数除以20得到向上取整数字替换100 corsorlist={0} & List.Distinct( List.Transform({1..100},(L)=>List.Accumulate({1..L},0,(x,y)=> if y=1 then Json.Document(Web.Contents("https://oapi.dingtalk.com/topapi/report/list?" ,[Query=[access_token=token] ,Content=Text.ToBinary("{'start_time':"&UnixTimestampStart(Month)&",'end_time':"&UnixTimestampEnd(Month)&",'template_name':'"&RecodeName&"','cursor':"&Text.From(0)&",'size':20}")]) )[result][next_cursor] else Json.Document(Web.Contents("https://oapi.dingtalk.com/topapi/report/list?" , [Query=[access_token=token] ,Content=Text.ToBinary("{'start_time':"&UnixTimestampStart(Month)&",'end_time':"&UnixTimestampEnd(Month)&",'template_name':'"&RecodeName&"','cursor':"&Text.From(x)&",'size':20}")]) )[result][next_cursor])) ), //======================获取每页数据的record组成datalist========================================================== datalist = List.Transform(corsorlist,each fxrl(_,RecodeName)[data_list]), //======================datalist转换成表========================================================================= tables = Table.Buffer(Table.Combine(List.Transform(datalist,each fxrt(_)))), //======================判断是否为空表,容错机制=================================================================== TF=Table.IsEmpty(tables),//tables=#table({},{}) //======================用namelist展开recode得到表================================================================ 展开 = if TF then #table(NameList,{}) else Table.ExpandRecordColumn(tables, "contents", NameList), //======================删除其他列得到目标列======================================================================= 删除 = Table.SelectColumns(展开,NameList) in 删除, metadata=[ Documentation.Name="钉钉日志数据获取函数:Fxdingding", Documentation.LongDescription="AppKey:登录钉钉开放平台获得(需要组织管理员权限);AppSecret:登录钉钉开放平台获得(需要组织管理员权限);Month:表示要获取数据的月份的1号填写为:#date(2019,1,1);RecordName:要获取的日志标题名称;NameList:日志的标题名称,使用list形式。" ] in Value.ReplaceType(fx,Value.Type(fx) meta metadata)
2、自定义函数的说明
function(AppKey as text ,AppSecret as text,Month as date,RecodeName as text,NameList as list ) as table
**AppKey:**登录钉钉开放平台获得(需要组织管理员权限);
**AppSecret:**登录钉钉开放平台获得(需要组织管理员权限);
**Month:**表示要获取数据的月份的1号填写为:#date(2019,1,1),为什么要设置这个参数,因为钉钉api获取数据是由日期限制的,好像是180天,所以数据就按照每月获取的;
**RecordName:**要获取的日志标题名称;
**NameList:**日志的标题名称,使用list形式。
自定义函数Fxdingding
3、调用函数得到结果
4、以上,对于pq了解多一些人操作起来没有问题,接下来再把函数和字段名称管理一起封装,让更多对pq不是很了解的人直接拿来就用(详细见附件)
**步骤1:**配置《01名称管理》Excel文件表名、ID、原始名称、统一名称;
**步骤2:**配置好power bi文件4个参数;
步骤3:直接一个简单的函数Fxtable得到结果,是不是非常简单。
1、直接调用自定函数fxdingding输入五个参数,即可得到结果;
2、注意参数的格式,详见上图《结果》
3、AppKey、AppSecret 获取需要管理员权限并登录钉钉开放平台获得;
钉钉开放平台
4、具体步骤见开发文档;
钉钉开发文档
5、服务器公网出口IP名单要提前设置好,不然数据会跑不出来。
6、由于每页20条数据限制,我们设置了获取2000条数据为上限,若数据超过这个限制,请参看自定义函数中说明,自行修改。