Forum Discussion
jbrown184
1 year agoHelper I
Safety Culture API Not Pulling all pages
Hey, I have this code and it is not pulling up all the pages. The Relitive path is set up correctly and tested. Below is the next_page token documentation from the API Website. SafetyCulture ...
lbendlin
1 year agoSuper User
() => [data = firstPage[data], nextPage = firstPage[nextPage]],
This will fetch the first page twice. Not a big issue but worth mentioning.
How do you know that not all pages are fetched?
- jbrown1841 year agoHelper I
It is only pulling 10/31/2024.
This one pulls correctly but doesn't have a reletivepath
let // Define parameters BaseUrl = "https://api.safetyculture.io/feed/inspection_items", QueryParams = [ template = "template_895b484e3a48408893666349887a8821", archived = "false", completed = "true", include_inactive = "true", modified_after = "2024-10-28T23:14:23.000Z" ], AuthorizationToken = "Bearer 9d433069cc2691f46d2c2axxxxxx", // Fetch the first page of data FirstPageUrl = BaseUrl & "?" & Uri.BuildQueryString(QueryParams), FirstPage = try Json.Document(Web.Contents(FirstPageUrl, [Headers=[Accept="application/json", Authorization=AuthorizationToken]])) otherwise null, // If the first page fails, return null Items = if FirstPage <> null then FirstPage[data] else {}, NextPage = try FirstPage[metadata][next_page] otherwise null, // Combine all pages iteratively FetchPages = List.Generate( () => [CurrentUrl = FirstPageUrl, Data = Items, NextPage = NextPage], each [NextPage] <> null, each [ CurrentUrl = "https://api.safetyculture.io" & [NextPage], PageData = try Json.Document(Web.Contents("https://api.safetyculture.io" & [NextPage], [Headers=[Accept="application/json", Authorization=AuthorizationToken]])) otherwise null, Data = if PageData <> null then PageData[data] else {}, NextPage = try PageData[metadata][next_page] otherwise null ], each [Data] ), AllItems = List.Combine(FetchPages), // Convert the list to a table #"Converted to Table" = Table.FromList(AllItems, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "item_id", "audit_id", "template_id", "label", "response"}), // Perform transformations (rename, filter, add columns, etc.) #"Removed Columns" = Table.RemoveColumns(#"Expanded Column1", {"id", "item_id", "audit_id", "template_id"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns", {{"label", "Question"}, {"response", "Response"}}), #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each [Question] <> "Title Page"), #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows", {{"Response", type text}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type), #"Added Conditional Column" = Table.AddColumn(#"Added Index", "Custom", each if Text.Contains([Question], "Fecha") then [Index] else null), #"Filled Down" = Table.FillDown(#"Added Conditional Column",{"Custom"}), // Remove unnecessary columns #"Removed Columns1" = Table.RemoveColumns(#"Filled Down",{"Index"}), // Pivot the data based on the 'Question' field #"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[Question]), "Question", "Response"), #"Removed Columns2" = Table.RemoveColumns(#"Pivoted Column",{"Custom"}), // Duplicate and reorder date columns #"Duplicated Column" = Table.DuplicateColumn(#"Removed Columns2", "Conducted on / Fecha", "Conducted on / Fecha - Copy"), #"Duplicated Column1" = Table.DuplicateColumn(#"Duplicated Column", "Conducted on / Fecha - Copy", "Conducted on / Fecha - Copy - Copy"), #"Reordered Columns" = Table.ReorderColumns(#"Duplicated Column1",{"Conducted on / Fecha", "Conducted on / Fecha - Copy - Copy", "Conducted on / Fecha - Copy", "How many items were in the tote? (¿Cuántos artículos había en el tote?)", "Was there an error? (¿Hubo un error?)", "if response is |Yes / Sí|", "LP Number", "User I.D / Nombre de usuario", "What was the issue? (¿Cuál fue el problema?)", "What type of apparel had the error? (¿Qué tipo de prenda tuvo el error?)", "Photo of Error / Foto de error"}), // Split date columns by delimiter 'T' #"Split Column by Delimiter" = Table.SplitColumn(#"Reordered Columns", "Conducted on / Fecha", Splitter.SplitTextByDelimiter("T", QuoteStyle.Csv), {"Conducted on / Fecha.1", "Conducted on / Fecha.2", "Conducted on / Fecha.3"}), #"Removed Columns3" = Table.RemoveColumns(#"Split Column by Delimiter",{"Conducted on / Fecha.2", "Conducted on / Fecha.3"}), // More splits for duplicated columns #"Split Column by Delimiter1" = Table.SplitColumn(#"Removed Columns3", "Conducted on / Fecha - Copy - Copy", Splitter.SplitTextByDelimiter("T", QuoteStyle.Csv), {"Conducted on / Fecha - Copy - Copy.1", "Conducted on / Fecha - Copy - Copy.2", "Conducted on / Fecha - Copy - Copy.3"}), #"Removed Columns4" = Table.RemoveColumns(#"Split Column by Delimiter1",{"Conducted on / Fecha - Copy - Copy.2", "Conducted on / Fecha - Copy - Copy.3"}), #"Split Column by Delimiter2" = Table.SplitColumn(#"Removed Columns4", "Conducted on / Fecha - Copy", Splitter.SplitTextByDelimiter("T", QuoteStyle.Csv), {"Conducted on / Fecha - Copy.1", "Conducted on / Fecha - Copy.2"}), #"Removed Columns5" = Table.RemoveColumns(#"Split Column by Delimiter2",{"Conducted on / Fecha - Copy.2"}), // Renaming columns for clarity #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns5",{{"Conducted on / Fecha - Copy - Copy.1", "Week"}, {"Conducted on / Fecha - Copy.1", "Year"}, {"Conducted on / Fecha.1", "Date"}}), // Transform data types correctly #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Date", type date}, {"Week", type date}, {"Year", type date}}), #"Calculated Week of Year" = Table.TransformColumns(#"Changed Type1",{{"Week", Date.WeekOfYear, Int64.Type}}), #"Extracted Year" = Table.TransformColumns(#"Calculated Week of Year",{{"Year", Date.Year, Int64.Type}}), // Convert quantity to integer #"Changed Type2" = Table.TransformColumnTypes(#"Extracted Year",{{"How many items were in the tote? (¿Cuántos artículos había en el tote?)", Int64.Type}}) in #"Changed Type2"This one only shows 50 rows and one day and does have reletivepath
let // Define parameters baseUrl = "https://api.safetyculture.io", relativePath = "/feed/inspection_items", authorizationToken = "Bearer 9d433069cc2691f46d2c2a6cbcxxxxxxxx", modifiedAfter = "2024-10-27T23:14:23.000Z", template = "template_895b484e3a48408893666349887a8821", archived = "false", completed = "true", includeInactive = "false", // Construct the query string query = [ modified_after = modifiedAfter, template = template, archived = archived, completed = completed, include_inactive = includeInactive ], // Use Web.Contents function fetchData = (relativePath as text, query as record) => let response = try Json.Document(Web.Contents(baseUrl, [ RelativePath = relativePath, Query = query, Headers = [ #"accept" = "application/json", #"authorization" = authorizationToken ] ])) otherwise null, data = if response <> null and Record.HasFields(response, "data") then response[data] else {}, nextPage = if response <> null and Record.HasFields(response, "metadata") and Record.HasFields(response[metadata], "next_page") then response[metadata][next_page] else null in [data = data, nextPage = nextPage], firstPage = try fetchData(relativePath, query) otherwise [data = {}, nextPage = null], allData = List.Generate( () => [data = firstPage[data], nextPage = firstPage[nextPage]], each List.Count([data]) > 0 and [nextPage] <> null, each let nextPageData = if [nextPage] <> null then fetchData(Text.Middle([nextPage], Text.Length(baseUrl)), query) else [data = {}, nextPage = null] in nextPageData, each [data] ), combinedData = List.Combine(allData), #"Converted to Table" = Table.FromList(combinedData, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "item_id", "audit_id", "item_index", "template_id", "parent_id", "organisation_id", "created_at", "modified_at", "type", "category", "category_id", "parent_ids", "primeelement_id", "primeelement_index", "label", "response", "response_id", "response_set_id", "is_failed_response", "comment", "media_files", "media_ids", "media_hypertext_reference", "score", "max_score", "score_percentage", "combined_score", "combined_max_score", "combined_score_percentage", "mandatory", "inactive", "location_latitude", "location_longitude"}, {"Column1.id", "Column1.item_id", "Column1.audit_id", "Column1.item_index", "Column1.template_id", "Column1.parent_id", "Column1.organisation_id", "Column1.created_at", "Column1.modified_at", "Column1.type", "Column1.category", "Column1.category_id", "Column1.parent_ids", "Column1.primeelement_id", "Column1.primeelement_index", "Column1.label", "Column1.response", "Column1.response_id", "Column1.response_set_id", "Column1.is_failed_response", "Column1.comment", "Column1.media_files", "Column1.media_ids", "Column1.media_hypertext_reference", "Column1.score", "Column1.max_score", "Column1.score_percentage", "Column1.combined_score", "Column1.combined_max_score", "Column1.combined_score_percentage", "Column1.mandatory", "Column1.inactive", "Column1.location_latitude", "Column1.location_longitude"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded Column1",{"Column1.id", "Column1.item_id", "Column1.audit_id", "Column1.item_index", "Column1.template_id", "Column1.parent_id", "Column1.organisation_id", "Column1.created_at", "Column1.modified_at", "Column1.type", "Column1.category", "Column1.category_id", "Column1.parent_ids", "Column1.primeelement_id", "Column1.primeelement_index", "Column1.response_id", "Column1.response_set_id", "Column1.is_failed_response", "Column1.comment", "Column1.media_files", "Column1.media_ids", "Column1.media_hypertext_reference", "Column1.score", "Column1.max_score", "Column1.score_percentage", "Column1.combined_score", "Column1.combined_max_score", "Column1.combined_score_percentage", "Column1.mandatory", "Column1.inactive", "Column1.location_latitude", "Column1.location_longitude"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Column1.label", "Question"}, {"Column1.response", "Response"}}), #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([Question] <> "Title Page")), #"Added Index" = Table.AddIndexColumn(#"Filtered Rows", "Index", 0, 1, Int64.Type), #"Added Conditional Column" = Table.AddColumn(#"Added Index", "Custom", each if Text.Contains([Question], "Fecha") then [Index] else null), #"Removed Columns1" = Table.RemoveColumns(#"Added Conditional Column",{"Index"}), #"Filled Down" = Table.FillDown(#"Removed Columns1",{"Custom"}), #"Pivoted Column" = Table.Pivot(#"Filled Down", List.Distinct(#"Filled Down"[Question]), "Question", "Response"), #"Removed Columns2" = Table.RemoveColumns(#"Pivoted Column",{"Custom", "if response is |Yes / Sí|"}), #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns2", "Conducted on / Fecha", Splitter.SplitTextByDelimiter("T", QuoteStyle.Csv), {"Conducted on / Fecha.1", "Conducted on / Fecha.2"}), #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Conducted on / Fecha.1", type date}, {"Conducted on / Fecha.2", type time}}), #"Removed Columns3" = Table.RemoveColumns(#"Changed Type",{"Conducted on / Fecha.2"}), #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns3",{{"Conducted on / Fecha.1", type date}}), #"Renamed Columns1" = Table.RenameColumns(#"Changed Type1",{{"Conducted on / Fecha.1", "Date"}}), #"Duplicated Column" = Table.DuplicateColumn(#"Renamed Columns1", "Date", "Date - Copy"), #"Duplicated Column1" = Table.DuplicateColumn(#"Duplicated Column", "Date - Copy", "Date - Copy - Copy"), #"Renamed Columns2" = Table.RenameColumns(#"Duplicated Column1",{{"Date - Copy", "Week"}, {"Date - Copy - Copy", "Year"}}), #"Calculated Week of Year" = Table.TransformColumns(#"Renamed Columns2",{{"Week", Date.WeekOfYear, Int64.Type}}), #"Extracted Year" = Table.TransformColumns(#"Calculated Week of Year",{{"Year", Date.Year, Int64.Type}}) in #"Extracted Year" - jbrown1841 year agoHelper I
It is only pull 50 rows.