Clarity

  • 1.  Getting started with Rest API

    Posted May 19, 2016 10:28 AM

    Hi,

     

    I did take a look at the CA PPM REST API and try to figure how it work using VBA in Excel. ( I am starting from very little knowledge in this area (REST API and VBA)).

     

    In the attached 20 min video, you will see how from scratch you can use the CA PPM REST API to get data inside a spreadsheet... I did have a microphone issue recording the video... I used a free version of camtasia to assemble it with a new recorder audio.

     

    I also include 2 files you can play with or modify (very basic).

    In two workbooks you will see how you can:

         -Get a specific project data

         -Get a project list

         -Create a project

     

    I really hope this can help some folks out there... I do not take any responsibility for the file... use it on dev or test environment.

     

    If by any chance someone build something great from these two files... please shared them.

     

    Eric,

    Attachment(s)

    xlsm
    RestAPIExample2.xlsm   152 KB 1 version


  • 2.  Re: Getting started with Rest API

    Posted May 19, 2016 10:41 AM

    This is awesome, thanks!



  • 3.  Re: Getting started with Rest API

    Posted May 19, 2016 10:55 AM

    Thanks for sharing this

     

    Regards,

    NJ



  • 4.  Re: Getting started with Rest API

    Posted May 19, 2016 11:55 AM

    Awesome!



  • 5.  Re: Getting started with Rest API

     
    Posted May 19, 2016 06:13 PM

    Thank you for sharing this Eric!

    Getting started with Rest API



  • 6.  Re: Getting started with Rest API

    Posted Jul 06, 2016 09:42 PM

    Very nice! Thanks for sharing!



  • 7.  Re: Getting started with Rest API

    Posted Oct 27, 2016 02:03 PM

    HI, I was trying to use the same code but was not able to succeed.

     

    here is what I am using.

    Public Function GetSecurityHeader() As String
    '-----------------------------
    'Auhentification
    '-Get Server/username& Password from Security Tab
    '-Encode under Basic Authentification Base 64
    '----------------------------------------
    Dim Wsec As Worksheet: Set Wsec = Sheets("Security")
    Dim username As String: username = Wsec.Cells(2, 2)
    Dim password As String: password = Wsec.Cells(3, 2)
    GetSecurityHeader = "Basic " & Encode64(username & ":" & password)
    End Function


    Private Sub ProjectRefresh_Click()
    'Build Authorization Header
    Dim Wsec As Worksheet: Set Wsec = Sheets("Security")
    Dim APIserver As String: APIserver = Wsec.Cells(1, 2)
    Dim auth_header As String: auth_header = GetSecurityHeader()
    Dim W As Worksheet: Set W = Sheets("GetProjects")
    Dim Last As Integer: Last = W.Range("A5000").End(xlUp).Row
    Dim Id As String
    Dim i As Integer
    Dim URL As String

    Debug.Print auth_header

     

    Dim Auth As New WinHttpRequest

    URL = APIserver & "/testcase/TC649812"
    Debug.Print URL
    Auth.Open "GET", URL, False
    Auth.SetRequestHeader "authorization", auth_header
    Auth.Send

     

     

    after Auth.Send I get an automation error. I am not sure what I was doing wrong.

    here are the references used.

     

    can you please help.



  • 8.  Re: Getting started with Rest API

    Posted Oct 27, 2016 02:50 PM

    At first sight. I would say something is wrong with your URL Variable...URL = APIserver & "/testcase/TC649812"

     

    As example if you try to get one project information back... your URL should look like... https://xxxxx.ca.com/ppm/rest/v1/projects/" & Id

     

     

     

    Try the following:

    Create the String to get project information...

    URL = APIserver & "ppm/rest/v1/projects"

    do the print out:

    Debug.Print URL

    Run your VB

     

    Log in to CA PPM

    Cut and Paste in your browser the Print out... https://xxxxxondemand.ca.com/ppm/rest/v1/projects

     

    Do you have a result?

     

    If no, check that your URL string match what you find in systems option under API URL followed by a "/"  and "Command" as example "/projects" 

    Ensure also that you have the API - Access Right

     

     

     

     

     

     



  • 9.  Re: Getting started with Rest API

    Posted Oct 31, 2016 05:22 PM

    Hi Eric

    sorry for the delayed reply. I have been experimenting. for me it worked with

    Set Http = CreateObject("MSXML2.XMLHTTP.6.0")

    instead of WinHttpRequest

     

     

    I have able to retrieve the project.

    But now I am trying to get the test cases under a project. I was able to get all the releases under a project with

    webservice/v2.0/Project/"& ID &/"Releases

    But not able to get the test cases. is there way you know to get the test cases from a project.

     

    I was also able to get the test cases from the requirements. but not able to get them form the project.



  • 10.  Re: Getting started with Rest API

    Posted Nov 02, 2016 12:00 PM

    Hi,

     

    You cannot retrieve all data with Rest API.

     

    To view which functionalities are available you can view the Rest API documentation.

    You should have the link to Rest API Documentation within Administration Menu / System Options Under API Section.

     

    About the "Test Case" under project, I never heard of this.  If this is a custom object,  you won't be able to retrieve the data.  If It is a custom field within project object you might be able, but you need to configure under the  project Uniqueobject Attribute... the  

     

    Before programming the VB I do recommend you use the API documentation link, you can directly test all function from there and ensure you have proper syntax.