Create a SharePoint Intranet
App with Many Items
A PowerShell with SharePoint tutorial
 In
the
previous demo, Peter Kalmström showed how to create
an app with numbers using the PNP.PowerShell Batch functionality.
The items with the numbers 1-100 were easily added, but
if you want to create an app that has more realistic items,
the code gets more complicated. In the demo below, Peter
shows how to use the Excel Fill feature to quickly get multiple
lines of code. He also uses the Replace feature in Word
to facilitate the code creation.
Peter takes his data from an Excel file with sales data.
The Excel sheet has 1481 rows and 9 columns. All this data
should be imported to a new SharePoint list app called "Data".
In the script, all columns are imported to Single line of
text columns in SharePoint, because there are some advantages
to make the change into other column types once the list
has been created. For example, choices in Choice columns
are taken from the items in that column automatically.
The PowerShell command in this example will sometimes be
typed in a row, with semicolon (;) between them. This is
to facilitate copying from Excel into Visual Studio Code.
Create Columns
We have already seen how to create an app, so the steps
below are for the columns. We will only type in the code
for one SharePoint list column in the script, and as the
Title column is created automatically with a new list, that
column will be created from the second column in the Excel
sheet. Then we will use the Excel sheet with the data that
should be imported to create the code for the rest of the
columns. We will also use Word to get the code from Excel
into multiple rows, so that it is easier to read in VS Code.
- In VS Code, add a new line for the Year column after
the line that creates the "Data" list. (Use single quotes
for the names, as the line should be copied to Excel,
which uses double quotes to deliminate text.)
Add-PnPField -List 'Data' -Type Text -AddToDefaultView -InternalName 'Year' -DisplayName 'Year';
- Copy the line and paste it in Excel, in the formula
field for the top cell to the right of the Amount column,
here J1. Add a star sign at the end of the formula,
and put double quotes before and after the code.
- In the formula, change 'Year' into a relative reference
to that cell in Excel, here B1: ="Add-PnPField -List
'Data' -Type Text -AddToDefaultView -InternalName
'"&B1&"' -DisplayName '"&B1&"';*"
(Learn
about Excel references)
- Fill out the (cell J1) to the right so that 9 columns
get the formula. The reference will change accordingly
and pick up the corrent column names.
- Copy all the cells with the formula, here J1 to
R1, and paste it as text in Word, to make it more readable.
- Replace the star sign with a paragraph mark.
- Replace the space before Add-PnPField with nothing.
- Copy the code from Word and paste it in VS Code.
- Run the pasted code to create the list and columns.
Import-Module PnP.PowerShell
connect-pnponline https://m365x61537192.sharepoint.com/sites/BatchImport -Interactive
New-PnPList -Title "Data" -Template GenericList
Add-PnPField -List 'Data' -Type Text -AddToDefaultView -InternalName 'Year' -DisplayName 'Year';
Add-PnPField -List 'Data' -Type Text -AddToDefaultView -InternalName 'Month' -DisplayName 'Month';
Add-PnPField -List 'Data' -Type Text -AddToDefaultView -InternalName 'Week' -DisplayName 'Week';
Add-PnPField -List 'Data' -Type Text -AddToDefaultView -InternalName 'Weekday' -DisplayName 'Weekday';
Add-PnPField -List 'Data' -Type Text -AddToDefaultView -InternalName 'Salesperson' -DisplayName 'Salesperson';
Add-PnPField -List 'Data' -Type Text -AddToDefaultView -InternalName 'Item' -DisplayName 'Item';
Add-PnPField -List 'Data' -Type Text -AddToDefaultView -InternalName 'Distrikt' -DisplayName 'Distrikt';
Add-PnPField -List 'Data' -Type Text -AddToDefaultView -InternalName 'Amount' -DisplayName 'Amount';
|
Create Items
The SharePoint list items are created from the Excel rows
in a similar way as the columns. To get the data into the
correct columns, we will use a hashtable variable named
Val. A hashtable is a data structure that stores a collection
of objects. The data is contained within curly brackets,
and the hashtable is indicated by the @ sign immediately
before the start curly bracket. Hashtables are most often
used for key-value pairs, in this case the column name and
the column value.
We will also use a variable for the Batch funtionality,
MyBatch.
- Declare the variable Val as a hashtable and add
a new hashtable with the Title and its value, 1, from
the Number column in the Excel sheet. The whole code
for creating new list items looks like this:
$Val =@{};$Val +=@{Title='1'}; Add-PnPListItem -List 'Data' -Values $Val -Batch $MyBatch
- Copy the part $Val +=@{Title='1'}; from this line
and paste it into the Excel sheet in the cell on the
first value row after the last column, in this case
J2.
- In the formula, change '1' into a reference to that
cell in Excel, here A2: ="$Val =@{};$Val +=@{Title='"&A2&"'};"
- Fill down the J2 cell so that you get all numbers.
- Fill the J2 cell one step to the right. Now you
will get the year value, because of the Excel eference
change.
- Change Title in the hashtable to the reference for
the 'Year' column. Lock the reference with a dollar
sign: ="$Val +=@{"&B$1 &"='"&B2&"'};"
- Fill the cell with the formula, here K2, to the
right, here to R2.
- Keep the cells selected and fill down. Because of
the references, all column names and values will be
filled out correctly.
- In VS Code, copy the declare variable code,
$Val =@{};
- In Excel, paste the declare variable code in the
formula of the first cell in the first value formula
row, here J2
- In VS Code, copy the last part of the add list item
line:
Add-PnPListItem -List 'Data' -Values $Val
- In Excel, add a paste the copied code last in the
formula of the last cell in the first value row, here
R2.
- Add the Batch parameter and the MyBatch variable
to the formula, so that the last part becomes:
Add-PnPListItem -List 'Data' -Values $Val -Batch $MyBatch
- Fill down the R2 column.
- In VS Code, declare the MyBatch variable to equal
New-PnPBatch.
- Add an AddData function.
- After the function, add a call to the function.
- Invoke the MyBatch variable. Now the code for the
list items looks like this:
$Val =@{};$Val +=@{Title='1'}; Add-PnPListItem -List 'Data' -Values $Val;
$MyBatch = New-PnPBatch
function AddData(){
}
AddData
Invoke-PnPBatch $MyBatch
- In Excel, select the cell J2 and use Ctrl+Shift
+ the down and right arrows to select all the cells
that have formulas. Copy the selected cells.
- In VS Code, paste all the data copied from Excel
into the function, after the start curly bracket.
- To add the data to the Batch, run the code part
$MyBatch = New-PnPBatch
function AddData(){
}
AddData
- Run the last line in the script, Invoke-PnPBatch
$MyBatch, to add the items to the list.
The last step is to change colum type for the columns that
should not be Single line of text columns.
|
 |
 |
|
