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