Bulk create Document Sets with attachment from an excel worksheet.

Nandita MEHTA 0 Reputation points
2023-09-04T06:31:40.7366667+00:00

Problem Statement: Bulk create Document Sets with attachment from an excel worksheet.

Summary :

Is there a way to do this via the SharePoint Online UI?

What I have understood is that to do this I need to create a PowerShell script.

After creating the content type manually in SharePoint online, I have been successful in creating the Document Sets __
through a PowerShell script__ from the exel sheet**.**

However, I am unable to update the metadata values through PowerShell after creating the Document Sets.
Also, I cannot add the attachments.

Detailed Description of steps:

I want to automate bulk creation of Document Sets with attachments in SharePoint from an excel worksheet where

  1. Each column in excel worksheet indicates a metadata name, and each row represents one document set
  2. Each row is a document set, where the values in under corresponding columns are the values of metadata.
  3. Values under a column named Name is the Name of the Document Set thus created
  4. Additionally there is a column indicating a sub folder. All files under this sub folder need to go as attachments to the Document Set.
  5. I created a content type in SharePoint online with Site columns(metadata).
  6. I have written a PowerShell script to add a document set for each row in the excel using Add-PnPDocumentSet for the given content type
  7. However, despite setting the values of the metadata the values don't seem to be updated in the Document Sets.
  8. I have tried several ways of doing this, but to no avail.
  9. Tried by using the internal or the external names of the metadata columns( site columns).
  10. Trying to get the subsite, the folder doesn't work,
  11. Likewise unable to find a way toadd attachments

So, I need help in updating the metadata values in the Document Sets through the PowerShell script and also add the attachments from the folder. Plese find snippet from my script below. Some of the ways I tried are commented.

Any cmdlets or code snippets I can look into?

foreach ($row in $excelData) {

# Create a document

$documentSetName = $row.Name

$documentSet = Add-PnPDocumentSet -List $documentLibrary -ContentType "Lab95" -Name $documentSetName

# The above works

#$addedDS = $web.GetFolder($documentSet)

#Write-Host " addedDS: $addedDS"

# Tried getting folder but didn't work

#and the below code doesn't work. Here I am trying with internal names of site columns. Tried with visible names #too

foreach($key in $row.keys)

{

#$documentSet.Metadata[$key] = $row.$key

#$addedDS.SetProperty($key,$row.$key)

....

if ($key -eq "Date Created"){

$newkey = "_DCDateCreated"

}

if ($key -eq "Issuer"){

$newkey = "VccIssuer"

}

...

if ($key -eq "Version"){

$newkey = "_Version"

}

$documentSet.Folder.Properties[$newkey]=$row.$key

}

$documentSet.Folder.Update()

#$list.Update()

#$web.Update()

#$subfolderpath = Join-Path -Path $inputPath -ChildPath $documentSetName

# Get the subfolder path based on the "Name" column value

$subfolderPath = Join-Path -Path $inputPath -ChildPath $documentSetName

# Attach files from the subfolder to the document set instance

$files = Get-ChildItem -Path $subfolderPath

#Tried myriad ways to add attachments and later commented none of them work

#foreach ($file in $files) {

#Add-PnPTaxonomyField -List $documentLibrary -DisplayName "Attachments" -InternalName "Attachments" -IsMulti "True" -Group "Custom Columns"

#$attachmentField = Get-PnPField -List $documentLibrary -Identity "Attachments"

#$attachmentFieldId = $attachmentField.Id

#$attachmentFileInfo = [Microsoft.SharePoint.Client.FieldLookupValue]@{ LookupId = $documentSetInstance.Id }

#$attachmentFileInfo = $attachmentField.GetFieldValueAsHtml($attachmentFileInfo)

#Add-PnPFileToContentType -List $documentLibrary -Id $attachmentFieldId -Folder "/" -Values $attachmentFileInfo -Path $file.FullName -NewFileName $file.Name

#Add-PnPTeamsTab -ListName $documentLibrary -Folder $documentSet["FileDirRef"] -SourceFilePath $file.FullName

#}

 

SharePoint
SharePoint
A group of Microsoft Products and technologies used for sharing and managing content, knowledge, and applications.
10,652 questions
PowerShell
PowerShell
A family of Microsoft task automation and configuration management frameworks consisting of a command-line shell and associated scripting language.
2,489 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Limitless Technology 44,221 Reputation points
    2023-09-05T08:13:15.2066667+00:00 
    Hello there,

Powerautomate has own ways of doing this https://powerusers.microsoft.com/t5/Building-Flows/Add-New-Document-Set-to-Document-Library-Using-HTTP-Request-REST/td-p/1978155

You will need to use the COM object to interact with Excel for using the below script.

using (var clientContext = new ClientContext("http://sp"))
            {                
                List list = clientContext.Web.Lists.GetByTitle("MyDoc");                
                Folder parentFolder = list.RootFolder;                
                ContentType ct = clientContext.Web.ContentTypes.GetById("0x0120D520");
                clientContext.Load(ct);
                clientContext.ExecuteQuery();                
                DocumentSet.Create(clientContext, parentFolder, "MyDocumentSet", ct.Id);
                clientContext.ExecuteQuery();

            }


Hope this resolves your Query !!

--If the reply is helpful, please Upvote and Accept it as an answer–
  2. Burt, Robert 0 Reputation points
    2024-09-27T14:27:36.5933333+00:00

    Hi,

    I have used this as the basis for a process to create this in bulk from a CSV file. You will have to add in some environment specific data. Essentially you should just need to add in your sharepoint site url, the library name, and then the column/s you want to add data to, represented by csv column names.

    I am not sure however, how you will add attachments to columns in Sharepoint? If that is what you're asking.

    # Set the SharePoint Online site URL
$siteUrl = "https://<tenant>.sharepoint.com/sites/<Site>" 

# Connect to the SharePoint Online site
Connect-PnPOnline -Url $siteUrl 

# Define CSV file path
$csvFilePath = "C:\Path\To\File.csv"

# Read the CSV file
$csvData = Import-Csv -Path $csvFilePath

# Define list name
$listName = "<Library>"

# Iterate through each row in the CSV
foreach ($row in $csvData) {
    $CaseYear = $row.CaseYear    # Column Name 
	$ID= $row.ID


    # Set custom column values
    $docsetProperties = @{
        "CaseYear" = $CaseYear    # Sharepoint Column Name
		"ID" = $ID

}      
# Create document set     $newDocumentSet = Add-PnPDocumentSet -List $listName -Name $name -ContentType "Document Set"

    if ($newDocumentSet) {
        # Fetch newly created document set by name
        $documentSetItem = Get-PnPListItem -List $listName -Query "<View><Query><Where><Eq><FieldRef Name='FileLeafRef'/><Value Type='Text'>$name</Value></Eq></Where></Query></View>"

        # Set custom column values for the document set
        Set-PnPListItem -List $listName -Identity $documentSetItem.Id -Values $docsetProperties
        Write-Host "Document set '$name' created and metadata set."
    } else {
        Write-Host "Failed to create document set: $name"
    }
}
    0 comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.