// Jsonpage for Excel (Microsoft 365): Power Query script.
//
// Install: format your customer list as a table named Clients with a column
// "Entreprise" (company name, SIREN, SIRET, VAT number or identifier such as FR-552032534).
// Data > Get Data > From Other Sources > Blank Query > Advanced Editor, replace the
// content with this script, paste your API key on the ApiKey line, then Done and
// "Close & Load". Refresh with Data > Refresh All whenever the list changes.
//
// Added columns: Official name, SIREN, Status, VAT number, VAT status, Address.
// Each row counts as two requests of your plan (search by name, then the company record;
// a SIREN, SIRET or identifier skips the search).
let
    ApiKey = "PASTE_YOUR_JSONPAGE_API_KEY",
    Base = "https://api.jsonpage.com/v2",

    Get = (path as text, query as record) as record =>
        let
            Response = Web.Contents(Base, [
                RelativePath = path,
                Query = query,
                Headers = [#"X-API-Key" = ApiKey],
                ManualStatusHandling = {404}
            ]),
            Status = Value.Metadata(Response)[Response.Status],
            Body = if Status = 200 then Json.Document(Response) else [data = null]
        in
            Body,

    ToIdentifier = (value as nullable text) as nullable text =>
        let
            Text = Text.Upper(Text.Remove(Text.Trim(value ?? ""), {" ", ".", "-"})),
            Digits = Text.Select(Text.Trim(value ?? ""), {"0".."9"}),
            IsGlobal = Text.Length(Text.Trim(value ?? "")) > 3 and Text.At(Text.Trim(value ?? ""), 2) = "-",
            Result =
                if IsGlobal then Text.Upper(Text.Trim(value))
                else if Text.Length(Text) = 14 and Text.Length(Digits) = 14 then "FR-" & Text.Start(Digits, 9)
                else if Text.Length(Text) = 9 and Text.Length(Digits) = 9 then "FR-" & Digits
                else if Text.StartsWith(Text, "FR") and Text.Length(Text) = 13 then "FR-" & Text.End(Text, 9)
                else null
        in
            Result,

    Resolve = (value as nullable text) as nullable text =>
        let
            Known = ToIdentifier(value),
            Found = if Known <> null then Known
                else if value = null or Text.Length(Text.Trim(value)) < 3 then null
                else
                    let Hits = Get("autocomplete", [q = Text.Trim(value), limit = "1"])[data]
                    in if Hits <> null and List.Count(Hits) > 0 then Hits{0}[id] else null
        in
            Found,

    Company = (value as nullable text) as record =>
        let
            Id = Resolve(value),
            Data = if Id = null then null else Get("companies/" & Id, [include = "address,vat"])[data],
            Empty = [Name = null, SIREN = null, Status = "NOT_FOUND", Vat = null, VatStatus = null, Address = null],
            Result = if Data = null then Empty else [
                Name = Data[name],
                SIREN = Data[registry_id],
                Status = Data[status],
                Vat = try Data[vat][number] otherwise null,
                VatStatus = try Data[vat][status] otherwise null,
                Address = try Data[address][formatted] otherwise null
            ]
        in
            Result,

    Source = Excel.CurrentWorkbook(){[Name = "Clients"]}[Content],
    WithRecord = Table.AddColumn(Source, "Jsonpage", each Company(Text.From([Entreprise]))),
    Expanded = Table.ExpandRecordColumn(WithRecord, "Jsonpage",
        {"Name", "SIREN", "Status", "Vat", "VatStatus", "Address"},
        {"Official name", "SIREN", "Status", "VAT number", "VAT status", "Address"})
in
    Expanded