BuildRowSetFromJSON Function

Home Page

BuildRowSetFromJSON: Retrieving Access Token via BuildRowSetFromJSON Function

This documentation provides a step-by-step guide on how to retrieve an access token using the AMPScript functions LookupOrderedRows, RowCount, HTTPPost2, and BuildRowsetFromJson. The access token will be obtained by making a request to the Authentication API and parsing the JSON response.

Prerequisites

Before proceeding, ensure that you have the following:

• An understanding of basic AMPScript syntax and functionality.
• Access to the Authentication API and the necessary credentials (client ID, client secret, etc.).
• A working knowledge of the AMPScript functions mentioned above.

Steps

1. Initialize the necessary variables:

   <!-- AMPScript -->
   %%[
   VAR @endpoint,@payload, @callstatus, @response, @clientId, @clientSecret
   VAR @DataExtensionName,@numberOfRows,@property,@value,@dataset,@i
   ]%%

2. Assign variables to Retrieve API Config details via LookupOrderedRows function:

   <!-- AMPScript -->
   %%[
       SET @contentType='application/json; charset=utf-8'
       SET @DataExtensionName='InstallPackages'
       SET @numberOfRows=1
       SET @property='APIConfig'
       SET @value='TransactionalAPI'
       SET @rowset=LookupOrderedRows(@DataExtensionName,@numberOfRows,"ClientID desc,ClientSecret desc",@property,@value)
   ]%%

3. Retrieve rowset and assign cientID, Secret and AUTH API endpoint via FIELD function:

   <!-- AMPScript -->
   %%[
   IF RowCount(@rowset) > 0 THEN
   FOR @i=1 TO RowCount(@rowset) DO
       SET @row=Row(@rowset,@i)
       SET @AuthAPI=FIELD(@row,'AuthAPI')
       SET @clientId=FIELD(@row,'ClientID')
       SET @clientSecret=FIELD(@row,'ClientSecret')
   NEXT @i
   ENDIF
   ]%%

4. Assign variables to Retrieve Method URL details via LookupOrderedRows function:

   <!-- AMPScript -->
   %%[
       SET @DataExtensionName='APIMethods'
       SET @numberOfRows=1
       SET @property='MethodName'
       SET @value='Request SFMC Token'
   
       /* call LookupOrderedRows to Retrieve Method URL details*/
       SET @rowset=LookupOrderedRows(@DataExtensionName,@numberOfRows,"MethodURL desc",@property,@value)
   ]%%

5. Retrieve rowset and assign MethodURL via FIELD function:

   <!-- AMPScript -->
   %%[
   IF RowCount(@rowset) > 0 THEN
   FOR @i=1 to RowCount(@rowset) DO
       SET @row=Row(@rowset,@i)
       SET @MethodURL=FIELD(@row,'MethodURL')
   NEXT @i
   ENDIF
   ]%%

6. Make a POST request to the Authentication API using the HTTPPost2 function:

   <!-- AMPScript -->
   %%[
   SET @endpoint = Concat(@AuthAPI,@MethodURL)
   
   /* define the payload*/
   SET @payload = Concat('{"client_id": "',@clientId,'", "client_secret": "',@clientSecret,'","grant_type": "client_credentials"}')
   
   /* Invoke HTTPPOST */
   HTTPPost2(@endpoint, @contentType, @payload,true,@response, @callstatus)    
   ]%%

7. Retrieve the access token from the response using the BuildRowsetFromJson function. This function converts the JSON response into a rowset, allowing us to extract the desired value.

   <!-- AMPScript -->
   %%[
   SET @rowset=BuildRowsetFromJson(@response, '$.*',1) 
   SET @row=Row(@rowset,1) 
   SET @accessToken=FIELD(@row,'Value')
   SET @row=Row(@rowset,2) 
   SET @tokentype=FIELD(@row,'Value')
   SET @authorization=Concat(@tokentype,' ',@accessToken)
   ]%%

8. The third argument/parameter in BuildRowsetFromJson function returns an empty rowset or error. A value of 0 does not return an empty rowset while 1 returns an empty rowset.
9. You now have the access token stored in the @accessToken variable and can use it for subsequent API calls.

Analysis

Before compiling the full code , lets analyse BuildRowsetFromJson function:

• An understanding of JSONPath -- XPath for JSON is required. You can refer the documentation here.
• We should focus on Javascript Example for better understanding. And more on the Result noted in A.7 section of the documentation.
• The example clearly states, quote "Please note that the return value of jsonPath is an array, which is also a valid JSON data item. So you might want to apply jsonPath to the resulting data item again or use one of your favorite array methods as sort with it." unquote
• Our Access Token response in JSON Notation looks like

  <!-- JSON -->
  {
  "access_token": "",
  "token_type": "",
  "expires_in": 1051,
  "scope": "",
  "soap_instance_url": "https://{{tenantEndpoint}}.soap.marketingcloudapis.com/",
  "rest_instance_url": "https://{{tenantEndpoint}}.rest.marketingcloudapis.com/"
  }

• Output would be like

  <!-- Rowset -->
  value:
  [ "eyJhbGciOiJIUzI1NiIsImtpZCI6IjQiLCJ2ZXIiOiIxIiwidHlwIjoiSldUIn0.jhggh.etc",
    "Bearer",
     1051,
    "web_read web_write",
    "https://{{tenantEndpoint}}.soap.marketingcloudapis.com/",
    "https://{{tenantEndpoint}}.rest.marketingcloudapis.com/"
  ]

Full Code Base

<!-- AMPScript -->
%%[
    
/* =====================  Define variables ==========================
01. @endpoint - https://{{tenantendpoint}}.auth.marketingcloudapis.com/v2/Token
02. @payload - define the payload for the POST operation to AUTH API
03. @response - Output parameter used to contain returned response body information from the HTTP POST request.
04. @clientId - assign/store client id 
05. @clientSecret - assign/store client secret
06. @DataExtensionName
07. @numberOfRows
08. @property
09. @value
10. @row
11. @rowset
===================================================================*/
                
VAR @endpoint,@payload, @callstatus, @response, @clientId, @clientSecret
VAR @DataExtensionName,@numberOfRows,@property,@value,@dataset,@i
            
                
/* =====================   variables assignment =======================*/
SET @contentType='application/json; charset=utf-8'
SET @DataExtensionName='InstallPackages'
SET @numberOfRows=1
SET @property='APIConfig'
SET @value='TransactionalAPI'
            
/* call LookupOrderedRows to Retrieve API Config details*/
SET @rowset=LookupOrderedRows(@DataExtensionName,@numberOfRows,"ClientID desc,ClientSecret desc",@property,@value)
/* ===================================================================*/
            
IF RowCount(@rowset) > 0 THEN
    FOR @i=1 TO RowCount(@rowset) DO
        SET @row=Row(@rowset,@i)
        SET @AuthAPI=FIELD(@row,'AuthAPI')
        SET @clientId=FIELD(@row,'ClientID')
        SET @clientSecret=FIELD(@row,'ClientSecret')
    NEXT @i
ENDIF
            
/* =====================   variables assignment =======================*/
SET @DataExtensionName='APIMethods'
SET @numberOfRows=1
SET @property='MethodName'
SET @value='Request SFMC Token'
            
/* call LookupOrderedRows to Retrieve Method URL details*/
SET @rowset=LookupOrderedRows(@DataExtensionName,@numberOfRows,"MethodURL desc",@property,@value)
/* ===================================================================*/
            
    IF RowCount(@rowset) > 0 THEN
        FOR @i=1 to RowCount(@rowset) DO
            SET @row=Row(@rowset,@i)
            SET @MethodURL=FIELD(@row,'MethodURL')
        NEXT @i
    ENDIF
                
/* =====================   variables assignment =======================*/
SET @endpoint = Concat(@AuthAPI,@MethodURL)
            
/* define the payload*/
SET @payload = Concat('{"client_id": "',@clientId,'", "client_secret": "',@clientSecret,'","grant_type": "client_credentials"}')
            
/* Invoke HTTPPOST */
HTTPPost2(@endpoint, @contentType, @payload,true,@response, @callstatus)      
            
    SET @rowset=BuildRowsetFromJson(@response, '$.*',1) 
    SET @row=Row(@rowset,1) 
    SET @accessToken=FIELD(@row,'Value')
    SET @row=Row(@rowset,2) 
    SET @tokentype=FIELD(@row,'Value')
    SET @authorization=Concat(@tokentype,' ',@accessToken)
]%%
            
%%=v(@authorization)=%%

Conclusion

In this documentation, we learned how to retrieve an access token using the AMPScript functions LookupOrderedRows, RowCount, HTTPPost2, and BuildRowsetFromJson. By following these steps, you can integrate the Authentication API into your AMPScript-powered applications and also leverage BuildRowsetFromJson function for other REST API purposes.

TOP