Reference: CCDS Script Example SAP

Test Connection.
Import Structure.
Import Data.

SAP Sample Script

/******************

Preview Data

******************/
/* JavaScript Document
function previewData(context)
{
    // not supported
}


/******************

Test Connection

******************/

// These below settings need to be created on the data source for the script to pick up and connect successfully.

function testConnection(context) {
    var dataSource = context.getDataSource();
    var username = dataSource.getSetting("Username").getValue();
    var password = dataSource.getSetting("Password").getValue();
    var endpoint = dataSource.getSetting("Request URL").getValue();
    var client_id = dataSource.getSetting("client_id").getValue();
    var client_secret = dataSource.getSetting("client_secret").getValue();
    
    // Step 1: Create a https request to send to your API
    var url = endpoint + 'client_id=' + client_id + '&client_secret=' + client_secret;
    ai.log.logInfo('url: ', url);
    var method = 'POST';
    // Setup request in body, sample is using xml, but could be json
    var body = '{"companyCode": "SAP Company Code","fiscalYear": "SAP Fiscal Year","period": "SAP Fiscal Period"}';
    var headers = { 'Authorization': 'Basic ' + base64Encode(username + ':' + password),'Content-Type': 'application/json' };
 
    // Step 2: Send request and receive response
    var response = null;
    try {
        response = ai.https.request(url, method, body, headers);
    }
    catch (exception) {
        // Example of logging to the CCDS log using the Error loglevel
        ai.log.logError('Test Connection HTTPS Request failed', ''+exception);
        ai.log.logInfo('http code', response.getHttpCode());
        return false;
    }


    //ai.log.logInfo('http code', response.getHttpCode());
    ai.log.logInfo('response body', response.getBody().toString());

    var parser = ai.xml.createParser();
    var xmlDoc = parser.parse(response.getBody());
    var root = xmlDoc.getRootElement();
    
    //ai.log.logInfo('authorization', base64Encode(username + ':' + password));
    return true;
}

// This is used to encode the username and password with base64
function base64Encode(text){
    if (/([^\u0000-\u00ff])/.test(text)){
        throw new Error("Can't base64 encode non-ASCII characters.");
    } 
    var digits = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/",
        i = 0,
        cur, prev, byteNum,
        result=[];      

    while(i < text.length){

        cur = text.charCodeAt(i);
        byteNum = i % 3;

        switch(byteNum){
            case 0: //first byte
                result.push(digits.charAt(cur >> 2));
                break;

            case 1: //second byte
                result.push(digits.charAt((prev & 3) << 4 | (cur >> 4)));
                break;

            case 2: //third byte
                result.push(digits.charAt((prev & 0x0f) << 2 | (cur >> 6)));
                result.push(digits.charAt(cur & 0x3f));
                break;
        }

        prev = cur;
        i++;
    }

    if (byteNum == 0){
        result.push(digits.charAt((prev & 3) << 4));
        result.push("==");
    } else if (byteNum == 1){
        result.push(digits.charAt((prev & 0x0f) << 2));
        result.push("=");
    }

    return result.join("");
}

/******************

Import Structure

******************/

function importStructure(context) {
    var dataSource = context.getDataSource();
    
    var builder = context.getStructureBuilder();
 
    for(var i=0; i<MatrixDataModel.Tables.length; i++){
        var definedTable = MatrixDataModel.Tables[i];
        var table = builder.addTable(definedTable.name);
        table.setDisplayName(definedTable.displayName);
        
        for(var j=0; j<definedTable.columns.length; j++) {
            addColumn(table, definedTable.columns[j]);
        }
    }
}

function addColumn(table, column){
    var recordColumn = table.addColumn(column.name);
    recordColumn.setDisplayName(column.displayName);
    switch(column.type){
        case "text": recordColumn.setTextColumnType(column.length);break;
        case "int": recordColumn.setIntegerColumnType();break;
        case "float": recordColumn.setFloatColumnType();break;
        case "boolean": recordColumn.setBooleanColumnType();break;
        case "datetime": recordColumn.setDateTimeColumnType();break;
        default: recordColumn.setTextColumnType(20);break;
    }
    recordColumn.setDisplayOrder(column.order);
    recordColumn.setIncludeByDefault(column.includeByDefault || false); 
    recordColumn.setIncludeInKey(column.includeInKey || false); 
    recordColumn.setMandatoryForImports(column.mandatoryForImport || false); 
    recordColumn.setRemoteColumnType(column.remoteColumnType || "String");
}


/******************

Import Data

******************/

function importData(context) {
    
    
    var stagingTable = context.getRowset(); //'rowset' opens up the ability to interact with the current staging table.
    stagingTable.setSmartParsingEnabled(true); //allow AI to try coercing response data into assigned column types.
    
    
    var dataSource = context.getDataSource();
    var username = dataSource.getSetting("Username").getValue();
    var password = dataSource.getSetting("Password").getValue();
    var endpoint = dataSource.getSetting("Request URL").getValue();
    var client_id = dataSource.getSetting("client_id").getValue();
    var client_secret = dataSource.getSetting("client_secret").getValue();
    var companyCode = dataSource.getSetting("companyCode").getValue();
    
    
    //Period Range calculations Start
    var startDate = dataSource.getSetting("Import Period").getValue().getFromPeriodStartDateTime().substring(0, 10);
    var startYear=startDate.substring(0, 4);
    var startMonth=startDate.substring(5, 7);

    
    var url = endpoint + 'client_id=' + client_id + '&client_secret=' + client_secret;
    
    var method = 'POST';
    // Setup request in body, sample is using xml, but could be json
    var body = '{"companyCode": "' + companyCode +'","fiscalYear": "' + startYear + '","period": "' + startMonth +'"}';
    var headers = { 'Authorization': 'Basic ' + base64Encode(username + ':' + password),'Content-Type': 'application/json' };

 
    // Step 2: Send request and receive response
    var response = null;
    try {
        
        response = ai.https.request(url, method, body, headers);
    }
    catch (exception) {
        // Example of logging to the CCDS log using the Error loglevel
        ai.log.logError('Test Connection HTTPS Request failed', ''+exception);
        ai.log.logInfo('http code', response.getHttpCode());
        return false;
    }

    
    var responseBody = response.getBody();
    ai.log.logInfo('responseBody', responseBody);
    //convert JSON response to physical, parseable object.

    var responseObject = JSON.parse(responseBody).CDSViewName;
    
    ai.log.logInfo('responseObjectLength', responseObject.length);
   
    var stagingTableColumns = stagingTable.getColumns();

    for(var rowIndex = 0; rowIndex < responseObject.length; rowIndex++) {
        var cells = [];
        var currentRow = responseObject[rowIndex]; //object
        /***************************************************************************
         *
         * Just as generating the filter leveraged structure design, this nested
         * column loop uses the same logic: staging column IDs map 1:1 with
         * object properties.
         *
        ***************************************************************************/
        for (var columnIndex = 0; columnIndex < stagingTableColumns.length;
          columnIndex++) {
          var currentColumn = stagingTableColumns[columnIndex].getId();
          var val;
          var intJulianDate;
          // Date is Returned as Juliandate. Converting Julian Date to proper date
          if (stagingTableColumns[columnIndex].getColumnType()=='DateTimeColumn'){
              
                intJulianDate=currentRow[currentColumn];
                // Date is retured as /date(2452432442)/. Removing that string for proper parsing
                intJulianDate=intJulianDate.replace("/Date(","");
                intJulianDate=intJulianDate.replace(")/","");
                intJulianDate= parseInt(intJulianDate);
                val = new Date(intJulianDate);
              
              } else {
                  val = currentRow[currentColumn];
              }
              
              cells.push(val);
          
        }

    stagingTable.addRow(cells);
  }
    
    return true;
    
}