1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
|
let
// Fill in the following values
// Base URL of the Kintone environment
subDomain = "{your kintone subdomain}",
domain = "kintone.com",
// App ID of the Kintone App
appId = {your app id},
// API token of the Kintone environment
apiToken = "{your api token}",
// Field codes of the Kintone App (if you want to specify the field codes, comma-separated string is required. e.g. "fieldCode1,fieldCode2")
// If you want to get all fields, leave the list null
fieldCodes = null,
// End of the values to fill in
baseUrl = "https://" & subDomain & "." & domain,
requestKintoneApi = (path as text, query as record) as record =>
let
headers = [
#"X-Cybozu-API-Token" = apiToken
],
result = Web.Contents(baseUrl, [
Headers = headers,
RelativePath = path,
Query = query
])
in
Json.Document(result),
getRecords = (query as text) as record =>
let
relativePath = "/k/v1/records.json",
query = [
query = query,
// $id is always included in the response to get the next records
app = Text.From(appId),
fields = if fieldCodes = null then {} else List.Combine({{"$id"}, Text.Split(fieldCodes, ",")})
]
in
requestKintoneApi(relativePath, query),
getAllRecords = (lastId as number, rawRecords as list) as list =>
let
LIMIT = 500,
kintoneRestApiResp = getRecords(
"$id > " & Text.From(lastId) & " order by $id asc limit " & Text.From(LIMIT)
),
kintoneRecords = kintoneRestApiResp[records],
allRecords =
if List.Count(kintoneRecords) = LIMIT then
let
newLastId = Number.FromText(List.Last(kintoneRecords)[#"$id"][value])
in
List.Combine({rawRecords, @getAllRecords(newLastId, kintoneRecords)})
else
List.Combine({rawRecords, kintoneRecords})
in
allRecords,
extractValuesFromEachFields = (kintoneRecord as record) as record =>
let
extractFieldValue = (fieldObject as record) as any =>
let
fieldType = fieldObject[type],
fieldValue =
if fieldType <> "SUBTABLE" then
fieldObject[value]
else
let
subtableValue = fieldObject[value],
extractedSubtableFieldValues = List.Transform(
subtableValue, (subtableRow) => @extractValuesFromEachFields(subtableRow[value])
)
in
extractedSubtableFieldValues
in
fieldValue,
kintoneRecordFieldCodes = Record.FieldNames(kintoneRecord),
newRecord = Record.FromList(
List.Transform(
kintoneRecordFieldCodes, (fieldCode) => extractFieldValue(Record.Field(kintoneRecord, fieldCode))
),
kintoneRecordFieldCodes
)
in
newRecord,
createTypeList = (kintoneRecord as record) as list =>
let
kintoneRecordFieldCodes = Record.FieldNames(kintoneRecord),
determinType = (fieldCode as text, optional fieldFormat as text) as type =>
let
fieldType = if fieldFormat = null then Record.Field(kintoneRecord, fieldCode)[type] else fieldFormat,
// Field fomats which are used with calculated field are included in the field type.
textValueFields = {
"RECORD_NUMBER",
"SINGLE_LINE_TEXT",
"MULTI_LINE_TEXT",
"RICH_TEXT",
"LINK",
"RADIO_BUTTON",
"DROP_DOWN",
"STATUS",
"HOUR_MINUTE",
"DAY_HOUR_MINUTE",
"CALC"
},
numberValueFields = {"__ID__", "__REVISION__", "NUMBER", "NUMBER_DIGIT"},
dateValueFields = {"DATE"},
dateTimeValueFields = {"CREATED_TIME", "UPDATED_TIME", "DATETIME"},
timeValueFields = {"TIME"},
listValueFields = {"CHECK_BOX", "MULTI_SELECT", "CATEGORY"},
objectValueFields = {"CREATOR", "MODIFIER"},
objectListValueFields = {
"USER_SELECT",
"ORGANIZATION_SELECT",
"GROUP_SELECT",
"FILE",
"STATUS_ASSIGNEE",
"SUBTABLE"
},
columnType =
if List.Contains(textValueFields, fieldType) then
type text
else if List.Contains(numberValueFields, fieldType) then
type number
// The field type of CALC is determined by the format
else if List.Contains(dateValueFields, fieldType) then
type date
else if List.Contains(dateTimeValueFields, fieldType) then
type datetime
else if List.Contains(timeValueFields, fieldType) then
type time
else if List.Contains(listValueFields, fieldType) then
type any
else if List.Contains(objectValueFields, fieldType) then
type any
else if List.Contains(objectListValueFields, fieldType) then
type any
else
Error.Record("UnsupportedFieldType", "Unsupported field type: " & fieldType)
in
columnType,
kintoneFieldTypes = List.Transform(
kintoneRecordFieldCodes, (fieldCode as text) => {fieldCode, determinType(fieldCode)}
)
in
kintoneFieldTypes,
// Main
allRawRecords = getAllRecords(0, {}),
convertedRecords = List.Transform(allRawRecords, (rawRecord) => extractValuesFromEachFields(rawRecord)),
columnTypeList = createTypeList( allRawRecords{0}),
allRecordsTable = Table.FromRecords(convertedRecords),
convertedTable = Table.TransformColumnTypes(allRecordsTable, columnTypeList)
in
convertedTable
|
