v7.8.9/Public/Export-Excel.ps1 "Key cannot be null" when using append to add data to an existing worksheet

[simpletechgithub]

Heya dfinke, I'm trying to export some data, and "add" it (append) to the empty row of an existing worksheet

+ FullyQualifiedErrorId : Failed exporting data to worksheet 'Sheet1' to 'C:\Users\%USERNAME%\Downloads\temp\spreadsheet.xlsx': Key cannot be null.
Parameter name: key

Here is the full, sanitised code...

# Ensure the ImportExcel module is installed
if (-Not (Get-Module -ListAvailable -Name ImportExcel)) {
   Install-Module -Name ImportExcel -Scope CurrentUser -Force
}
Import-Module ImportExcel
# Define your email and API key
    $email = "your-email@email.com"
    $apiKey = "your-apikey"
# Combine email and API key with a colon
    $plainText = "${email}:${apiKey}"
# Encode the combined string in base64
    $base64AuthInfo = [Convert]::ToBase64String([Text.Encoding]::ASCII.GetBytes($plainText))
# Set up headers
$headers = @{
   Authorization = "Basic $base64AuthInfo"
   Accept = "application/json"
   ContentType = "application/json"
}
# Define the Jira API endpoints
$group1= "https://your-site.atlassian.net/rest/api/3/group/member?groupname=group1"
$group2= "https://your-site.atlassian.net/rest/api/3/group/member?groupname=group2"
# Define the location of the spreadsheet
$jiragroupsxlsx = "C:\Users\%USERNAME%\Downloads\temp\Jira Groups.xlsx"
$openspreadsheet = "C:\Users\%USERNAME%\Downloads\temp\Jira Groups.xlsx"
# Function to get email addresses from a given Jira group URL
function Get-EmailAddressesFromGroup {
   param (
       [string]$url
   )
   try {
       $response = Invoke-RestMethod -Uri $url -Method Get -Headers $headers
       $emailAddresses = $response.values | Select-Object -ExpandProperty emailAddress
       return $emailAddresses
   } catch {
       Write-Host "Error making API request to ${url}: $_.Exception.Message"
       return @()
   }
}
# Get email addresses from both groups
$group1emails = Get-EmailAddressesFromGroup -url $group1
$group2emails= Get-EmailAddressesFromGroup -url $group2
# Check if the Excel file exists; create it if it does not
if (-Not (Test-Path $jiragroupsxlsx)) {
   New-ExcelWorkbook -Path $jiragroupsxlsx
}
# Initialize the row index
$rowIndex = 2
# Write group email addresses to the spreadsheet
foreach ($email in $group1emails) {
   $data = @(
       @{GroupName = "group1"; EmailAddress = $email}
   )
   $data | Export-Excel -Path $jiragroupsxlsx -WorksheetName 'Sheet1' -StartRow $rowIndex -StartColumn 1 -Append
   $rowIndex++
}
# Write group email addresses to the spreadsheet
foreach ($email in $group2emails) {
   $data = @(
       @{GroupName = "group2"; EmailAddress = $email}
   )
   $data | Export-Excel -Path $jiragroupsxlsx -WorksheetName 'Sheet1' -StartRow $rowIndex -StartColumn 1 -Append
   $rowIndex++
}
# Output the response
Start-Process $openspreadsheet

[dfinke]

hi @simpletechgithub need a simple script that repos the issue

[simpletechgithub]

Trying to think what we could demo it on... essentially 1) hit an API with a get request Invoke-RestMethod -Uri $url -Method Get -Headers $headers 2) store some data in a variable 3) Try and export that to an existing spreadsheet with the "-Append" switch

You don't happen to have your own Jira instance, do you?

[scriptingstudio]

If I got it right there must be a single table. If so and you are trying to append line by line to the excel table your code wont work. First off, build data, then pass them to the final pipeline

$group1 = $group1emails | foreach-object {
   [pscustomobject]@{GroupName = "group1"; EmailAddress = $_}
}
$group2 = $group2emails | foreach-object {
   [pscustomobject]@{GroupName = "group2"; EmailAddress = $_}
} 
$group1 + $group2 | Export-Excel -Path $jiragroupsxlsx

[simpletechgithub]

Thanks for looking into this, awesome.. I'm back on my work laptop tomorrow and will try it if I don't give it a go from a demo jira tonight 👍