(Excel) Download SharePoint Lists and JSON Iterate

Downloads the JSON containing the list of SharePoint lists, and iterates to get information for each.

Download Excel Class Modules Chilkat Excel Class Modules

'  This requires the Chilkat API to have been previously unlocked.
'  See Global Unlock Sample for sample code.

'  -------------------------------------------------------------------------
'  The following comments apply to SharePoint Windows classic authentication.
'  -------------------------------------------------------------------------
'  For example, imagine our SharePoint endpoint is https://xyzoffice.mycompany.com/
'  The SHAREPOINT_NTLM_DOMAIN would be "mycompany.com"
'  The SHAREPOINT_HTTPS_DOMAIN would be "xyzoffice.mycompany.com"
'  Also, the SHAREPOINT_USERNAME would be just the name, not a full email address.
'  for example, "chilkat" instead of "chilkat@mycompany.com"

Dim http As Chilkat.Http
Set http = Chilkat.NewHttp

'  If SharePoint Windows classic authentication is used, then set the
'  Login, Password, LoginDomain, and NtlmAuth properties.
http.Login = "SHAREPOINT_USERNAME"
http.Password = "SHAREPOINT_PASSWORD"
http.LoginDomain = "SHAREPOINT_NTLM_DOMAIN"
http.NtlmAuth = True

'  -------------------------------------------------------------------------
'  The more common case is to use SharePoint Online authentication (via the SPOIDCRL cookie).
'  If so, do not set Login, Password, LoginDomain, and NtlmAuth, and instead
'  establish the cookie as shown at SharePoint Online Authentication
'  -------------------------------------------------------------------------

'  Indicate that we want a JSON reply
http.Accept = "application/json;odata=verbose"

Dim sbJson As Chilkat.StringBuilder
Set sbJson = Chilkat.NewStringBuilder

success = http.QuickGetSb("https://SHAREPOINT_HTTPS_DOMAIN/_api/web/lists",sbJson)
If (success <> True) Then
    Debug.Print http.LastErrorText
    Exit Sub
End If

Dim json As Chilkat.JsonObject
Set json = Chilkat.NewJsonObject
success = json.LoadSb(sbJson)
json.EmitCompact = False

'  Iterate over the list of lists and show information for each list.

numLists = json.SizeOfArray("d.results")

i = 0
Do While i < numLists
    json.I = i


    title = json.StringOf("d.results[i].Title")

    uri = json.StringOf("d.results[i].__metadata.uri")

    Debug.Print (i + 1); ": "; title
    Debug.Print "URI: "; uri

    '  To retrieve the contents of a particular list, an app would send a GET request to the list's URI.

    i = i + 1
Loop

'  The output will look similar to this:

'  	1: appdata
'  	URI: https://xyzoffice.mycompany.com/_api/Web/Lists(guid'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx')
'  	2: Briefing 02032017
'  	URI: https://xyzoffice.mycompany.com/_api/Web/Lists(guid'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx')
'  	3: Cache Profiles
'  	URI: https://xyzoffice.mycompany.com/_api/Web/Lists(guid'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx')
'  	4: Composed Looks
'  	URI: https://xyzoffice.mycompany.com/_api/Web/Lists(guid'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx')
'  	5: Content and Structure Reports
'  	URI: https://xyzoffice.mycompany.com/_api/Web/Lists(guid'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx')
'  	6: Documents
'  	URI: https://xyzoffice.mycompany.com/_api/Web/Lists(guid'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx')
'  	7: Form Templates
'  	URI: https://xyzoffice.mycompany.com/_api/Web/Lists(guid'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx')
'  	8: List Template Gallery
'  	URI: https://xyzoffice.mycompany.com/_api/Web/Lists(guid'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx')
'  	9: Master Page Gallery
'  	URI: https://xyzoffice.mycompany.com/_api/Web/Lists(guid'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx')
'  	10: Notification List
'  	URI: https://xyzoffice.mycompany.com/_api/Web/Lists(guid'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx')
'  	11: Project Policy Item List
'  	URI: https://xyzoffice.mycompany.com/_api/Web/Lists(guid'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx')
'  	12: Quick Deploy Items
'  	URI: https://xyzoffice.mycompany.com/_api/Web/Lists(guid'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx')
'  	13: Relationships List
'  	URI: https://xyzoffice.mycompany.com/_api/Web/Lists(guid'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx')
'  	14: Reusable Content
'  	URI: https://xyzoffice.mycompany.com/_api/Web/Lists(guid'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx')
'  	15: Site Collection Documents
'  	URI: https://xyzoffice.mycompany.com/_api/Web/Lists(guid'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx')
'  	16: Site Collection Images
'  	URI: https://xyzoffice.mycompany.com/_api/Web/Lists(guid'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx')
'  	17: Solution Gallery
'  	URI: https://xyzoffice.mycompany.com/_api/Web/Lists(guid'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx')
'  	18: Style Library
'  	URI: https://xyzoffice.mycompany.com/_api/Web/Lists(guid'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx')
'  	19: Suggested Content Browser Locations
'  	URI: https://xyzoffice.mycompany.com/_api/Web/Lists(guid'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx')
'  	20: Tasks
'  	URI: https://xyzoffice.mycompany.com/_api/Web/Lists(guid'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx')
'  	21: TaxonomyHiddenList
'  	URI: https://xyzoffice.mycompany.com/_api/Web/Lists(guid'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx')
'  	22: Theme Gallery
'  	URI: https://xyzoffice.mycompany.com/_api/Web/Lists(guid'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx')
'  	23: Variation Labels
'  	URI: https://xyzoffice.mycompany.com/_api/Web/Lists(guid'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx')
'  	24: Web Part Gallery
'  	URI: https://xyzoffice.mycompany.com/_api/Web/Lists(guid'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx')
'  	25: wfpub
'  	URI: https://xyzoffice.mycompany.com/_api/Web/Lists(guid'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx')
'  	26: Workflow Tasks
'  	URI: https://xyzoffice.mycompany.com/_api/Web/Lists(guid'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx')
'