Bulk create Document Sets with attachment from an excel worksheet.

Question

Bulk create Document Sets with attachment from an excel worksheet.

Nandita MEHTA 0

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

Each column in excel worksheet indicates a metadata name, and each row represents one document set
Each row is a document set, where the values in under corresponding columns are the values of metadata.
Values under a column named Name is the Name of the Document Set thus created
Additionally there is a column indicating a sub folder. All files under this sub folder need to go as attachments to the Document Set.
I created a content type in SharePoint online with Site columns(metadata).
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
However, despite setting the values of the metadata the values don't seem to be updated in the Document Sets.
I have tried several ways of doing this, but to no avail.
Tried by using the internal or the external names of the metadata columns( site columns).
Trying to get the subsite, the folder doesn't work,
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

#}

Anonymous

2023-09-04T09:02:15.14+00:00

Thank you for your question.

We are currently looking into this issue and will give you an update as soon as possible.

Thank you for your understanding and support

2 answers

Your answer

Anonymous

2023-09-04T09:02:15.14+00:00

Thank you for your question.

We are currently looking into this issue and will give you an update as soon as possible.

Thank you for your understanding and support

Answer 1

Limitless Technology 44,766

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–

Nandita MEHTA 0 Reputation points

2023-09-07T06:34:53.43+00:00
Hello @Limitless Technology , I do not know PowerAutomate and am pretty new to SharePoint. Just started exploring PowerShell. Within that context maybe some basic questions:

How will the script be called from this code?

Where does the script navigate each row of the excel? I want to create a Document set per row of excel? Or is the script supposed to be executed from excel, and it automatically works for each row?

Where are the metadata values getting Populated? It looks like the script adds Document Set, which is the same thing that PowerShell script has already achieved for me using Add-PnPDocumentSet
$documentSet = Add-PnPDocumentSet -List $documentLibrary -ContentType "Lab95"
What I am unable to do however is to:

Update the created Document Sets with the metadata values.

Add attachments from a given folder to the created Document Sets

Answer 2

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"
    }
}

Share via

Bulk create Document Sets with attachment from an excel worksheet.

2 answers

Your answer