How to Control cloudplan API from Excel
=======================================


First of all one needs to install a JSON converter which parses JSON response data into a format which can easily be accessed by Excel VBA.

A solid an reliable converter can be found on GitHub (https://github.com/VBA-tools/VBA-JSON). Please download the respective ZIP file. The file proides a libary which can be imported into any VBA project. It will convert a JSON string (e.g. JSON API response) into a VBA dictionary. The Github side mentioned above also provides examples how the information in the VBA dictionary can be accessed.


Import the libary into your Excel project:
------------------------------------------

- Extract ZIP
- Open new Excel Workbook
- Open VBA Editor
- Menu: <Datei / File > // <Datei importieren / Import File > // Choose >>JsonConverter.bas<< file and press ENTER


Add reference in Excel:
-----------------------

In Excel VBA editor navigate to *Menu* > *Extras / Tools* > *Verweise / References* and: 

- Enable *Microsoft Scripting Runtime*


Example code for creating a new weblink:
----------------------------------------

.. highlight:: VB.net

:: 

    Sub Create_Weblink()

    'Create new PDF documet from Excel sheet "Test" and save document into cloudplan folder!

    Sheets("Test").ExportAsFixedFormat Type:=xlTypePDF, Filename:="F:\cloudplan\Test.pdf"

    Application.Wait (Now + TimeValue("0:00:03"))

    'Create a session ID (e.g. Login to your cloudplan account via cloudplan VBA)

    Dim objHTTP As Object
    Dim Json As String
    Dim result1 As String
    Dim jp1 As Object

    Json = "{""email"":""yourmail@provider.com"", ""pw"":""yourawesomepassword"", ""session_duration"": 9600}" 'JSON strong as seen on https://docs.cloudplan.net/api/user/login.html
    
    Set objHTTP = CreateObject("MSXML2.XMLHTTP.6.0")
    Url = "https://uni.cloudplan.biz/api/user/login/" 'API URL as seen on https://docs.cloudplan.net/api/user/login.html
    
    objHTTP.Open "POST", Url, False
    objHTTP.setRequestHeader "Content-type", "application/json"
    objHTTP.send (Json)
    
    result1 = objHTTP.responseText 'Receive API response as JSON
     
    Set jp1 = JsonConverter.ParseJson(result1) 'Convert JSON string into VBA dictionary
    
    SID = jp1("session_id") 'Assing current session ID to SID variable
       
    'Request file ID for file to which the new weblink shall refer
    
    Dim result2 As String
    Dim jp2 As Object

    Json = "{""node_id"":""5F2596ECD04408D022EF563F8733F374"",""folder_id"":""AD76085F81581B4D734838743A2F31AD""}" 'JSON strong as seen on https://docs.cloudplan.net/api/weblink/create.html
    
    Set objHTTP = CreateObject("MSXML2.XMLHTTP.6.0")
    Url = "https://uni.cloudplan.biz/api/folder/ls" 'API URL as seen on https://docs.cloudplan.net/api/weblink/create.html
    
    objHTTP.Open "POST", Url, False
    objHTTP.setRequestHeader "session_id", SID 'Provide current session ID for new API request
    objHTTP.setRequestHeader "Content-type", "application/json"
    objHTTP.send (Json)
    
    result2 = objHTTP.responseText 'Receive API response as JSON
    
    Set jp2 = JsonConverter.ParseJson(result2) 'Convert JSON string into VBA dictionary
    
    NoF = jp2("folder_content").Count 'How many files are in the respective folder
    
        For i = 1 To NoF 'Loop through all files in folder and identify file ID for file to which the new weblink shall refer
        
            Filename = jp2("folder_content")(i)("name")
            FileID = jp2("folder_content")(i)("cp_id")
                  
                If Filename = "Test.pdf" Then
                
                FileID_X = FileID
                
                End If
        
        Next i

    'Create new Weblink

    Dim result3 As String
    Dim jp3 As Object
    
    Json = "{""folder_id"":""AD76085F81581B4D734838743A2F31AD"",""file_id"":"" & FileID_X & "",""file_name"":""Test.pdf"",""pw"":true,""duration"": 4838400,""max_downloads"": 10}" 'JSON strong as seen on https://docs.cloudplan.net/api/weblink/create.html
    
    Set objHTTP = CreateObject("MSXML2.XMLHTTP.6.0")
    Url = "https://uni.cloudplan.biz/api/weblink/create" 'API URL as seen on https://docs.cloudplan.net/api/weblink/create.html
    
    objHTTP.Open "POST", Url, False
    objHTTP.setRequestHeader "session_id", SID 'Provide current session ID for new API request
    objHTTP.setRequestHeader "Content-type", "application/json"
    objHTTP.send (Json)
    
    result3 = objHTTP.responseText 'Receive API response as JSON
    
    Set jp3 = JsonConverter.ParseJson(result3) 'Convert JSON string into VBA dictionary

    WLID = jp3("link_id") 'Retrive weblink 'link_ID' from VBA dictionary
    WLPW = jp3("password") 'Retrive weblink 'password' from VBA dictionary

    Debug.Print "weblink: https://portal.cloudplan.net/wl/Test.pdf/" & WLID 'Create new weblink based on 'link_ID'
    Debug.Print "Password: " & WLPW

    End Sub

See :download:`this example script <excel_example.xlsm>`.