0

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.

enter image description here

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

Error Message

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

Access is not an issue

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?!?

enter image description here

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?

1 Answer 1

0

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.

1
  • That makes sense :)I shall try....
    – Maxcot
    Sep 6, 2022 at 3:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.