File Not Found error in Powershell when trying to update SharePoint from Excel - Null Url

Question

I have a script that updates the filename and title on SharePoint from an excel file that lists the names, titles, and url. However, when trying to test run it says it cannot find the file. I have confirmed the URL and filename are correct so I'm a bit stumped - any help is appreciated!

*Error updating file 'EXAMPLE.PDF' with URL 'https://ABC.sharepoint.com/sites/DEF/GHI/JK/LMNO/PQ/RS/EXAMPLE.PDF': File Not Found.
*
Error updating file '' with URL '': Cannot bind argument to parameter 'Url' because it is null.

Import-Module SharePointPnPPowerShellOnline -DisableNameChecking
# Set variables for the SharePoint Online site and the Excel file
$siteUrl = "https://ABC.sharepoint.com/sites/DEF"
$excelFile = "C:\Users\ABC\OneDrive\Documents\FileRename.xlsx"
# Set up error logging
$errorLogPath = "C:\Users\ABC\OneDrive\Documents\ErrorlogRename.txt"
if (Test-Path $errorLogPath) {
    Clear-Content $errorLogPath
} else {
    New-Item -ItemType File -Path $errorLogPath -Force | Out-Null
}
# Connect to SharePoint Online
Connect-PnPOnline -Url $siteUrl
# Load the Excel file
$excel = New-Object -ComObject Excel.Application
$workbook = $excel.Workbooks.Open($excelFile)
$worksheet = $workbook.Worksheets.Item(1)
# Loop through each row in the Excel file and update the corresponding SharePoint items
for ($i = 2; $i -le $worksheet.UsedRange.Rows.Count; $i++) {
    # Get the values from the Excel file
    $existingFilename = $worksheet.Cells.Item($i, 1).Value2
    $newFilename = $worksheet.Cells.Item($i, 2).Value2
    $existingTitle = $worksheet.Cells.Item($i, 3).Value2
    $newTitle = $worksheet.Cells.Item($i, 4).Value2
    $documentUrl = $worksheet.Cells.Item($i, 5).Value2
    # Get the SharePoint item ID from the document URL
    try {
        $file = Get-PnPFile -Url $documentUrl -ErrorAction Stop
        $itemId = $file.ListItemAllFields.Id
        $listName = $file.List.Title
        # Update the SharePoint item
        $itemProperties = @{}
        if ($newFilename) {
            $itemProperties.Add("FileLeafRef", $newFilename)
        }
        if ($newTitle) {
            $itemProperties.Add("Title", $newTitle)
        }
        Set-PnPListItem -List $listName -Identity $itemId -Values $itemProperties
        # Rename the file
        if ($newFilename) {
            Rename-PnPFile -ServerRelativeUrl $documentUrl -TargetFileName $newFilename
        }
    }
    catch {
        # Log the error
        $errorMessage = "Error updating file '$existingFilename' with URL '$documentUrl': $($_.Exception.Message)"
        Add-Content -Path $errorLogPath -Value $errorMessage
    }
}
# Close the Excel file and quit Excel
$workbook.Close()
$excel.Quit()

Answer 1

There' only two palaces in your code that would produce that error: $file = Get-PnPFile -Url $documentUrl -ErrorAction Stop and Rename-PnPFile -ServerRelativeUrl $documentUrl -TargetFileName $newFilename, and only one place that sets the value of the variable: $documentUrl = $worksheet.Cells.Item($i, 5).Value2. I'd bet that the spreadsheet cells' value does not contain the correct value, or the code that extracts the value is incorrect.

What troubleshooting/debugging have you done?

Answer 2

Why not add this to your code, right before the comment "# Get the SharePoint item ID from the document URL"?

if ($null -eq $worksheet.Cells.Item($i, 5).Value2 -OR 
    $documentUrl.Trim().Length -lt 10 -OR 
    $documentUrl.Trim().Length -ne $documentUrl.Length){
        Write-Host "Spreadsheet cell at '$i,5' (Value2) is empty, too short, has leading or trailing whitespace, or is all spaces"
}