Referencia: Dropbox de ejemplo de secuencia de comandos CCDS

Conexión de prueba
Importar estructura
Importar datos

Secuencia de comandos de muestra de Dropbox

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

Preview Data

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


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

Test Connection

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

// The script utilizes OAuth2.0 to connect and retrieve the files from Dropbox. Create OAuth 2.0 credentail and associate it with the CCDS datasource.

function testConnection(context) {

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

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

    //However, we interrogate responses here for anything minor!
    if (response.getHttpCode() == '200') {
        var responseBody = JSON.parse(response.getBody());
        ai.log.logInfo('Authorization confirmed for user ' + responseBody.account_id, responseBody.name.display_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.';
    }

}


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

Import Structure

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

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]);
        }
    }
}

ExternalSystem = {
    Tables: [
        {
            name : "FILE1",
            displayName : "File 1",
            path : "/Captain/FILE1.csv",
            columns : [
                {name : "PERIOD", displayName : "PERIOD", type : "text", length : "25", order : 1, includeByDefault : true, includeInKey : false, mandatoryForImport : true},
                {name : "ACCOUNT", displayName : "ACCOUNT", type : "text", length : "255", order : 2, includeByDefault : true, includeInKey : false, mandatoryForImport : true},
                {name : "LEVEL", displayName : "LEVEL", type : "text", length : "255", order : 3, includeByDefault : true, includeInKey : false, mandatoryForImport : true},
                {name : "SPLIT_LABEL", displayName : "SPLIT_LABEL", type : "text", length : "255", order : 4, includeByDefault : true, includeInKey : false, mandatoryForImport : true},
                {name : "BALANCE", displayName : "BALANCE", type : "float", order : 5, includeByDefault : true, includeInKey : false, mandatoryForImport : true},
            ]
        },
        {
            name : "FILE2",
            displayName : "File 2",
            path : "/Captain/FILE2.csv",
            columns : [
                {name : "PERIOD", displayName : "PERIOD", type : "text", length : "25", order : 1, includeByDefault : true, includeInKey : false, mandatoryForImport : true},
                {name : "ACCOUNT", displayName : "ACCOUNT", type : "text", length : "255", order : 2, includeByDefault : true, includeInKey : false, mandatoryForImport : true},
                {name : "NOT_LEVEL", displayName : "NOT_LEVEL", type : "text", length : "255", order : 3, includeByDefault : true, includeInKey : false, mandatoryForImport : true},
                {name : "SPLIT_LABEL", displayName : "SPLIT_LABEL", type : "text", length : "255", order : 4, includeByDefault : true, includeInKey : false, mandatoryForImport : true},
                {name : "BALANCE", displayName : "BALANCE", type : "float", order : 5, includeByDefault : true, includeInKey : false, mandatoryForImport : true},
            ]
        }
    ]
}

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

Import Data

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


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();

    //Use the tableId, defined in the structure process (and ExternalSystem object) to soon unarchive the appropriate file path.
    var internalTableId = getInternalObjectKey(tableId);

    //Return relative file path from ExternalSystem definition.
    var path = getFilePath(internalTableId);

    //File API specifics, with help from the scripts...
    var url = getFileDownloadURL(),
        body = getFileDownloadBody(),
        headers = getFileDownloadHeader(path);

    //Use script generated values to pass to an authorized POST.
    var response = httpsPOST(url, body, headers);

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

    //an info log...
    ai.log.logInfo('Found ' + rows.length + ' records in ' + path, 'Starting import...');
    addRows(rowset, rows);
}

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


//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 'null';
}


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


//Return a URL for testing connections.
function getCurrentAccountURL() {
    return 'https://api.dropboxapi.com/2/users/get_current_account';
}


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


//Return a header for csv downloads.
function getFileDownloadHeader(path) {
    var regex = /\\/g; //as mentioned at getFilePath(), this line supports the strategic use of JSON functions to refine the path / Dropbox-API-Arg headers.
    var header = {
        "Dropbox-API-Arg" : path.replace(regex, null),
        "Content-Type": "text/plain"
    }; //the replace function here eliminates escape characters like '\'
    return header;
}


//Return a URL for csv downloads.
function getFileDownloadURL() {
    return 'https://content.dropboxapi.com/2/files/download';
}


/*******************************************************************************
 * 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 Dropbox integrations leverage OAuth2.0 for Authorization
    try {
        response = ai.https.authorizedRequest(url, 'POST', body, headers);
        return response;
    } catch (e) {
        ai.log.logError('POST to dropbox 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 dropbox failed: ' + e;
    }
}