Get values of Sharepoint list items - Powershell

Question

Company XYZ has lots of child companies
XYZ charges these child companies for each license count
XYZ is keeping track of licenses via excel document
XYZ would like to automate the process of license assignment and setting up mailbox etc

So far I have been able to automate the process of license assignment and setting up mailbox properties via Azure runbook. Utilizing a runasaccount (service principal) given access and api permission assigned to EXO, AAD, SPO – so far so good

Now XYZ would like to keep a track of which company has consumed “x” number of licenses like they were doing earlier and would like the runbook to access this Excel file and assign the available count to a user only based on the data which is in Excel(CSV).

Is it possible to import csv file stored in sharepoint online like we can do it on our machine “import-csv -path c:/xyz.csv”?? Or, is it possible to store this csv file in any other storage like Azure blob or table so that runbook can access, read & modify the table?

I tried creating sharepoint list but I am unable to fetch the column value in powershell. Is there a command or query that I can run to extract the values? (get-pnplistitem, get-pnpfield)??

Answer 1

Hi @Hitender Singh ,

Try the PowerShell Conmmands below to fetch the values in Count column.

$ListItems = Get-PnPListItem -List License -Fields "Title","Count"  
   
foreach($ListItem in $ListItems)  
{    
    Write-Host "Title:" $ListItem["Title"]  
    Write-Host "Count:" $ListItem["Count"]  
}  

---

If an Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

@Hitender Singh ,

Thanks for your instant response :-)

I guess that you renamed the column "Title" to "Company" in your list. You have to use internal name of a column after the parameter -Fields. You can retrieve the internal name of a column via the URL of Edit column page like this:

Also, the result returned from the PowerShell command will also show the internal name(Title) of a column instead of the display name(Company).

I would suggest using the PS like below to customize the return result on your screen.

Or you can create a new single line of text column named "Company", set "Require that this column contains information" of "Title" column to "No" and you can hide "Title" column from your list view if you don't want it.