Skip to main content
Solved

Export all products to a Excel Workbook using the API

  • May 10, 2023
  • 1 reply
  • 532 views
P
Apprentice
Forum|alt.badge.img

I am trying to export all products from FreshService to excel using Power Query I can get the first page but we have more than 100 products to return but I am new to Power Query and I cannot find an example of a method to retrieve all the pages until I reach the last one that works with the data from Freshservice. Can anyone point me in the way of a method that would work. 

Best answer by pjg_west

After no response here and a lot of googling I asked ChatGPT and it provided me the below solution, it may be of use to someone else….

 

let
    getPage = (page as number) =>
        try Json.Document(
            Web.Contents(
                "https://XXXXXX.freshservice.com/api/v2/products?per_page=100&page=" & Text.From(page),
                [Headers=[Authorization="Basic XXXXXXXXXX"]]
            )
        ) otherwise null,
    pageList = List.Generate(
        () => 1,
        each not List.IsEmpty(getPage(_)[products]),
        each _ + 1,
        each getPage(_)
    ),
    #"Converted to Table" = Table.FromList(pageList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"products"}, {"products"}),
    #"Expanded products" = Table.ExpandListColumn(#"Expanded Column1", "products"),
    #"Expanded products1" = Table.ExpandRecordColumn(#"Expanded products", "products", {"id", "created_at", "updated_at", "name", "manufacturer", "asset_type_id", "description", "description_text", "depreciation_type_id", "status", "mode_of_procurement"}, {"id", "created_at", "updated_at", "name", "manufacturer", "asset_type_id", "description", "description_text", "depreciation_type_id", "status", "mode_of_procurement"})
in
    #"Expanded products1"  

1 reply

P
Apprentice
Forum|alt.badge.img
  • pjg_west
  • Author
  • Apprentice
  • Answer
  • May 12, 2023

After no response here and a lot of googling I asked ChatGPT and it provided me the below solution, it may be of use to someone else….

 

let
    getPage = (page as number) =>
        try Json.Document(
            Web.Contents(
                "https://XXXXXX.freshservice.com/api/v2/products?per_page=100&page=" & Text.From(page),
                [Headers=[Authorization="Basic XXXXXXXXXX"]]
            )
        ) otherwise null,
    pageList = List.Generate(
        () => 1,
        each not List.IsEmpty(getPage(_)[products]),
        each _ + 1,
        each getPage(_)
    ),
    #"Converted to Table" = Table.FromList(pageList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"products"}, {"products"}),
    #"Expanded products" = Table.ExpandListColumn(#"Expanded Column1", "products"),
    #"Expanded products1" = Table.ExpandRecordColumn(#"Expanded products", "products", {"id", "created_at", "updated_at", "name", "manufacturer", "asset_type_id", "description", "description_text", "depreciation_type_id", "status", "mode_of_procurement"}, {"id", "created_at", "updated_at", "name", "manufacturer", "asset_type_id", "description", "description_text", "depreciation_type_id", "status", "mode_of_procurement"})
in
    #"Expanded products1"  

Terms of UseAccessibility statement