Explain SP authentication please

Question

I operate in an environment that has thousands of SharePoint sites. And I need to get a list of all the site "Titles".

My approach has been to use PowerQuery in an Excel workbook calling the SP rest API /_api/web/title on each site URL.

I have partial success, because of an authentication behaviour I don't understand

My goal is this...

SiteURL	Title
https://myOrg.sharepoint.com/sites/edison/sitename1/_api/web/title	This is Site Named 1
https://myOrg.sharepoint.com/sites/edison/sitename2/_api/web/title	This is Site Named 2
...	...
https://myOrg.sharepoint.com/sites/edison/sitename3/_api/web/title	This is Site Named 3
https://myOrg.sharepoint.com/sites/edison/sitename4/_api/web/title	This site name is different
https://myOrg.sharepoint.com/sites/edison/sitename5/_api/web/title	Test site x
...	...

To get the site title I use

= Table.AddColumn(#"Added Custom1", "getTitle", each Xml.Tables(Web.Contents([SiteURL]))),

As the PowerQuery is initiated, I am asked to authenticate (for the first time) which is expected. So I use the "organizational" option, this being SharePoint and all.

Then the first few sites are loaded, maybe 400 of them out of 2,000 and this error message pops up.

But if I manually type in the address to a browser, there's no issue at all.

So, I acknowledge the errors, and try refresh again. This time we get a little further, and then the error pops up again. It tells me I'm not signed in... to the same environment?!?

So what am I not understanding? PowerQuery randomly fails to get the API results.

But's the same SharePoint "farm" / domain/ whatever it's called. And it's not that I don't have permissions - I do as demonstrated.

What is going on?

Answer 1

You are being throttled. You can't send too many API requests without being throttled.

HTTP 429 indicates the calling application sent too many requests in a time window and exceeded a predetermined limit

If possible, I would recommend getting a list of all SharePoint sites using one of these methods:

1. Manually export the list of sites from the SharePoint Online Admin center
2. Get a list of sites using PnP PowerShell. And Save it to PowerShell.
3. Get a list of sites using Official SharePoint Online PowerShell Module. And Save it to PowerShell

Example

Connect-SPOService -url "https://contoso-admin.sharepoint.com"
Get-SPOSite Limit ALL| Export-CSV -LiteralPath SharePointSites.csv -NoTypeInformation

Then, you can use this CSV file as a data source in your Power BI report. I assume getting a list of all SharePoint sites does not make sense since their names rarely change.