What's wrong with my code here?

Using various sources, I have compiled the following for use in Powershell (note I have replaced the file paths and API key, but left in where they would be for illustrative purposes). In short, I have an Excel file with company registration numbers, and I’m trying to pull the “Persons entitled to” field for each into a new Excel file. I’m not getting any errors but the output file is blank. Any thoughts?

Import the Excel file containing the company registration numbers

$companies = Import-Excel -Path “C:path_to\file.xlsx”

Create a new array to store the results

$results = @()

Loop through each row in the Excel file

foreach ($company in $companies) {
# Build the API endpoint URL using the company registration number
$url = “https://api.company-information.service.gov.uk/company/$($company.‘Company Number’)”

# Set the authorization header using your API key
$headers = @{
    Authorization = "Basic $( [System.Convert]::ToBase64String([System.Text.Encoding]::ASCII.GetBytes("123-ABC:")))"

# Make the HTTP GET request and store the response in a variable
$response = Invoke-RestMethod -Uri $url -Headers $headers -Method Get

# Retrieve the name of the person entitled from the response and add it to the results array
foreach ($person in $response.persons_entitled) {
    $result = [pscustomobject]@{
        'Company Number' = $company.'Company Number'
        'Person Entitled' = $person.name
    $results += $result


Export the results to an Excel file

$results | Export-Excel -Path “C:path_to\output.xlsx” -AutoSize -BoldTopRow

Welcome. Did you miss something when pasting in your code here? You appear to be requesting the Company Profile endpoint using:

“https://api.company-information.service.gov.uk/company/$company.‘Company Number’”

… but the response to this does not - as far as I’m aware - contain a persons_entitled member. That would be in the chargeDetails resource (or an item with that structure in the charge list resource - see below).

To check whether the company has any charges you could check the has_charges member in the Company Profile resource. (Note I think this is set if the company has ever had any charges and doesn’t necessarily indicate that there are unsatisfied charges). If there have been charges there should also be a links.charges member containing the URL to request to get the charges list. I would likely use that but not necessarily rely on it being present though!

You can obtain the persons entitled by requesting the charges list or an individual charge (for which you’d need its ID - which you’d likely only know if you requested the charge list - read on). As your code stands I assume you’d want to iterate over all the charges in the company (as there can be multiples). For that you would use the List Charges endpoint. Unlike other “lists” this isn’t a “paged” one so you should get all details of all the charges with a single call. You can then iterate over all the charges in the list.

A good example to test would be this company with 40 charges when I ran this (using curl):

curl -u MY_API_KEY_HERE: ‘https://api.company-information.service.gov.uk/company/00502851/charges

If you have access to curl I’d always recommend running the query you think you need / have using that!

Hope this helps.