Referenz: Beispielfeld für CCDS-Skript

Verbindung testen.
Struktur importieren.
Daten importieren.

Beispielskript für Box

function testConnection(context) {

    var url = getCurrentAccountURL(),
        headers = getCurrentAccountHeader();

    //httpsPOST will throw the script, if something large is awry...
    var response = httpsGET(url, headers);

    //However, we interrogate responses here for anything minor!
    if (response.getHttpCode() == '200') {
        var responseBody = JSON.parse(response.getBody());
        ai.log.logInfo(response.getBody());
        ai.log.logInfo('Authorization confirmed for user ' + responseBody.id, responseBody.name + ' with e-mail: ' + responseBody.email);
        return true;
    } else {
        ai.log.logError('Detected an undesired error code: ' + response.getHttpCode());
        throw 'Detected an undesired error code: ' + response.getHttpCode() + '.  Assess logs for details.';
    }

}

/*******************************************************************************
 *
 * functions in this file:
 *
 * addColumn(table, column)
 * addRows(rowset, rows)
 * CSVToArray(strData, strDelimiter)
 * getColumnRemoteIds(columns)
 * getCurrentAccountBody()
 * getCurrentAccountHeader()
 * getCurrentAccountURL()
 * getFileDownloadBody()
 * getFileDownloadHeader(path)
 * getFileDownloadURL()
 * getInternalObjectKey(tableId)
 * getPath(internalTableId)
 * httpsPOST(url, body, headers)
 *
 *******************************************************************************/
function GetFileSearchResponse(searchFileName) {

    var searchURL = getSearchURL(searchFileName);
    ai.log.logInfo('searchURL: ' + searchURL);
    var searchHeader = getSearchFileHeader();
    //ai.log.logInfo('searchHeader: BDW' + searchHeader + '==<');
    var searchResponse = JSON.parse(httpsGET(searchURL,searchHeader).getBody());
    ai.log.logInfo('searchResponse: ' + searchResponse);
    return searchResponse;
}


//Return a URL for File Search.
function getSearchURL(searchFileName) {
        //return 'https://api.box.com/2.0/search?query=' + searchFileName + 'file&scope=user_content&type=file&file_extensions=csv';

   return 'https://api.box.com/2.0/search?query='+ searchFileName;
}

//Return a header for File Search
function getSearchFileHeader() {
    return {
        //"Content-Type": "application/java"
    };
}


//Return a URL for csv downloads.
function getFileDownloadURL(file_id) {
    return 'https://api.box.com/2.0/files/' + file_id+ '/content';
}

//Return a body for csv downloads.
/*
function getFileDownloadBody() {
    return null;
}
*/

//Return a header for csv downloads.
function getFileDownloadHeader() {
    return {};
}


function httpsGET(url, headers) {
    //authorizedRequest is used since Box integrations leverage OAuth2.0 for Authorization
    try {
        response = ai.https.authorizedRequest(url, 'GET', null, headers);
        return response;
    } catch (e) {
        ai.log.logError('GET to Box failed', 'URL: ' + url + '; body: ' + null + '; headers: ' + JSON.stringify(headers)); //headers is an object.  stringify it to hit logs.
        ai.log.logError('Exception caught -->', '' + e);
        throw 'Request to Box failed: ' + e;
    }
}


//Create columns with importStructure
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 "date":
            recordColumn.setDateTimeColumnType();
            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");
}


//Add rows to the staging area during importData.
function addRows(rowset, rows) {
    var columnRemoteIds = getColumnRemoteIds(rowset.getColumns());
    for (var i = 1; i < rows.length; i++) {
        if (rows[i].length == columnRemoteIds.length) {
            var cells = [];
            var row = rows[i];
            for (var x = 0; x < columnRemoteIds.length; x++) {
                var propName = columnRemoteIds[x].id,
                    propType = columnRemoteIds[x].type,
                    colIndex = columnRemoteIds[x].order;
                var value = row[colIndex];
                value = typeof value === 'undefined' || value === 'NaN' ? null : value.replace('$', '');
                cells.push(value);
            }
            rowset.addRow(cells);
        }
    }
}



//CSVToArray - convert the response body to an array to store it in Adaptive tables.
//This function needs try-catch checks...
function CSVToArray(strData, strDelimiter) {


    // Check to see if the delimiter is defined. If not, then default to comma.
    strDelimiter = (strDelimiter || ",");


    // Create a regular expression to parse the CSV values.
    var objPattern = new RegExp(
        (
            // Delimiters.
            "(\\" + strDelimiter + "|\\r?\\n|\\r|^)" +
            // Quoted fields.
            "(?:\"([^\"]*(?:\"\"[^\"]*)*)\"|" +
            // Standard fields.
            "([^\"\\" + strDelimiter + "\\r\\n]*))"),
        "gi");


    // Create an array to hold our data. Give the array a default empty first row.
    var arrData = [
        []
    ];


    // Create an array to hold our individual pattern matching groups.
    var arrMatches = null;


    // Keep looping over the regular expression matches until we can no longer find a match.
    while (arrMatches = objPattern.exec(strData)) {

        // Get the delimiter that was found.
        var strMatchedDelimiter = arrMatches[1];

        // Check to see if the given delimiter has a length (is not the start of string) and if it matches
        // field delimiter. If id does not, then we know that this delimiter is a row delimiter.
        if (strMatchedDelimiter.length && strMatchedDelimiter !== strDelimiter) {

            // Since we have reached a new row of data, add an empty row to our data array.
            arrData.push([]);
        }

        //Now that we have our delimiter out of the way, let's check to see which kind of value we captured (quoted or unquoted).
        var strMatchedValue;

        if (arrMatches[2]) {

            // We found a quoted value. When we capture this value, unescape any double quotes.
            strMatchedValue = arrMatches[2].replace(new RegExp("\"\"", "g"), "\"");
        } else {
            // We found a non-quoted value.
            strMatchedValue = arrMatches[3];
        }
        // Now that we have our value string, let's add it to the data array.
        arrData[arrData.length - 1].push(strMatchedValue);
    }
    // Return the parsed data.
    return (arrData);
}


//Takes rowset.getColumns() and returns an array of objects.
function getColumnRemoteIds(columns) {
    var columnIds = [];
    for (var i = 0; i < columns.length; i++) {
        columnIds[i] = {};
        columnIds[i].id = columns[i].getId();
        columnIds[i].type = columns[i].getColumnType();
        columnIds[i].order = columns[i].getDisplayOrder() - 1; //order - 1 to account for the initialization of 1.  Adaptive returns undesirable values when display order starts at 0, yet, js needs the 0 so we offset the delta.
    }
    return columnIds;
}


//Return a body for testing connections
function getCurrentAccountBody() {
    return '';
}


//Return a header for testing connections
function getCurrentAccountHeader() {
    return {
        "Accept" : "application/json",
        "Content-Type" : "application/json"
    };
}


//Return a URL for testing connections.
function getCurrentAccountURL() {
    return 'https://api.box.com/2.0/users/me';
}





/*******************************************************************************
 * Our ultimate construction goal is this:
 * {"Dropbox-API-Arg" : "{"path" : "/path/2/file.csv"}""}
 *
 * Hardcoding the path is straightforward, yet making it variable and dependent
 * on the ExternalSystem object literal needs some subtle application of JSON
 * functions.  Here, we stringify an object, introducing escape characters, that
 * we remove with regex later.
*******************************************************************************/
function getFilePath(internalTableId) {
    return JSON.stringify({path : ExternalSystem.Tables[internalTableId].path});
}


//Return ExternalSystem object's index for a given rowset.getTableId().
function getInternalObjectKey(tableId) {
    var rowsetId;
    for (var i = 0; i < ExternalSystem.Tables.length; i++) {
        if (ExternalSystem.Tables[i].name == tableId) {
            rowsetId = i;
            break;
        }
    }
    return rowsetId;
}





function httpsPOST(url, body, headers) {
    //authorizedRequest is used since Box integrations leverage OAuth2.0 for Authorization
    try {
        response = ai.https.authorizedRequest(url, 'POST', body, headers);
        return response;
    } catch (e) {
        ai.log.logError('POST to Box failed', 'URL: ' + url + '; body: ' + body + '; headers: ' + JSON.stringify(headers)); //headers is an object.  stringify it to hit logs.
        ai.log.logError('Exception caught -->', '' + e);
        throw 'Request to Box failed: ' + e;
    }
}

function importStructure(context) {


    //Initialize variables for structure import.
    var dataSource = context.getDataSource(),
        builder = context.getStructureBuilder();


    //Structure import "info" logging...
    ai.log.logInfo('import structure ... tables = ' + ExternalSystem.Tables.length);


    //This outer for-loop rolls over tables defined in the ExternalSystem object.
    for (var i = 0; i < ExternalSystem.Tables.length; i++) {
        var externalTable = ExternalSystem.Tables[i];

        var table = builder.addTable(externalTable.name);
        table.setDisplayName(externalTable.displayName);
        function importStructure(context) {


            //Initialize variables for structure import.
            var dataSource = context.getDataSource(),
                builder = context.getStructureBuilder();


            //Structure import "info" logging...
            ai.log.logInfo('import structure ... tables = ' + ExternalSystem.Tables.length);


            //This outer for-loop rolls over tables defined in the ExternalSystem object.
            for (var i = 0; i < ExternalSystem.Tables.length; i++) {
                var externalTable = ExternalSystem.Tables[i];

                var table = builder.addTable(externalTable.name);
                table.setDisplayName(externalTable.displayName);


                //Every externalTable in ExternalSystem.Tables[i] has a 'columns' array.
                //This loop iterates over the 'columns' array(s), while addColumn puts actual columns on the staging table.
                for (var j = 0; j < externalTable.columns.length; j++) {
                    addColumn(table, externalTable.columns[j]);
                }
            }
        }

        //Every externalTable in ExternalSystem.Tables[i] has a 'columns' array.
        //This loop iterates over the 'columns' array(s), while addColumn puts actual columns on the staging table.
        for (var j = 0; j < externalTable.columns.length; j++) {
            addColumn(table, externalTable.columns[j]);
        }
    }
}

function importData(context) {

    //Define script variables from context object
    var dataSource = context.getDataSource(),
        rowset = context.getRowset();

    //Allow rowset to coerce values into appropriate datatype defined at structure import
    rowset.setSmartParsingEnabled(true);

    //Refine rowset definitions.
    var tableId = rowset.getTableId(),
        columns = rowset.getColumns();

    var searchFileName = dataSource.getSetting('SearchFileName').getValue();
    //var searchFileName = 'GLData';
    ai.log.logInfo('File',searchFileName);
    var importFiles = GetFileSearchResponse(searchFileName);
    var noOfFiles = importFiles.total_count;

    ai.log.logInfo(+ noOfFiles + ' File(s) to Import');

    var header = getFileDownloadHeader();

    for (var i = 0; i < noOfFiles; i++) {
        var fileid = importFiles.entries[i].id;
        var filename = importFiles.entries[i].name;
        var url = getFileDownloadURL(fileid);
        ai.log.logInfo('The download url is ' + url);
        //Use script generated values to pass to an authorized POST.
        var response = httpsGET(url, header);

        //ai.log.logInfo('Response ' + JSON.stringify(response.getBody()));

        //Convert csv response to an array
        var rows = CSVToArray(response.getBody());

        //an info log...
        ai.log.logInfo('Importing ' + rows.length + ' records from ' + filename);
        addRows(rowset, rows);

    }
}

ExternalSystem = {
    Tables: [
        {
            name : "GLData",
            displayName : "GLData",
            path : "/GLData.csv",
            columns : [
                {name : "Account", displayName : "Account", type : "text", length : "25", order : 1, includeByDefault : true, includeInKey : false, mandatoryForImport : true},
                {name : "Level", displayName : "Level", type : "text", length : "255", order : 2, includeByDefault : true, includeInKey : false, mandatoryForImport : true},
                {name : "SPLIT_LABEL", displayName : "SPLIT_LABEL", type : "text", length : "255", order : 3, includeByDefault : true, includeInKey : false, mandatoryForImport : true},
                {name : "Time", displayName : "Time", type : "datetime", order : 4, includeByDefault : true, includeInKey : false, mandatoryForImport : true},
                {name : "Amount", displayName : "Amount", type : "float", order : 5, includeByDefault : true, includeInKey : false, mandatoryForImport : true}
            ]
        }
    ]
}