Import Data

Dernière mise à jour
Enregistrer en tant que PDF

Import Data Step 1: Use the passed-in contextual information to create a rowset object.

function importData(context) {
   // Step 1: Make use of passed in contextual information to create a rowset object. Here, I am simply assigning tableId, maxRows and columnNames to variables to be used elsewhere in this script.
   var rowset = context.getRowset();
   rowset.setSmartParsingEnabled(true);
   var tableId = rowset.getTableId();
   var columns = rowset.getColumns();
   var columnRemoteIds = getColumnRemoteIds(columns);

Import Structure Step 2: Create a HTTPS request to return data.

   // Step 2: Create a https request that will return data - in this case, Yahoo


   var url = 'https://finance.yahoo.com/webservice...on&view=detail';
   var method = 'GET';
   var headers = {
       'Content-Type': 'text/json'
   };
   var body = '';

Import Data Step 3: Send HTTPS request and receive a response.

   // Step 3: Send https request and receive response. Normally you would want to check that the response contains a success message first before looking at the rows.
   var response = ai.https.request(url, method, body, headers);

Import Data Step 4: Parse the HTTPS response body.

   // Step 4: Parse the https response body.
   var data = parseData(response.getBody());
   var rows = parseRows(data);




Import Data Step 5: Process each row to extract cell values for each column and add them as an array to the rowset in the expected column order.
   // Step 5: Process each row to extract the cell values for each column and add them as an array to the rowset in the expected column order.
   addRows(rowset, rows);
}


   function getColumnRemoteIds(columns) {
       var columnIds = [];
       for (var i = 0; i < columns.length; i++) {
           columnIds.push(columns[i].getId());
       }
       return columnIds;
   }


   function parseRows(json) {
       return json.list.resources;
   }


   function addRows(rowset, rows) {
       var columnRemoteIds = getColumnRemoteIds(rowset.getColumns());
       for (var i = 0; i < rows.length; i++) {
           var cells = [];
           var row = rows[i];
           for (var x = 0; x < columnRemoteIds.length; x++) {
               var propName = columnRemoteIds[x];
               cells.push(row.resource.fields[propName]);
           }
           rowset.addRow(cells);
       }
   }


   function importStructure(context) {
       var builder = context.getStructureBuilder();
       var progressManager = context.getProgressManager();
       var response = buildAndSendRequest();
       var dataEl = parseData(response.getBody());
       var table = dataEl.list.resources[0];
       var progressIncrement = 100 / 1;
       createTable(builder, table.resource);
       progressManager.setProgress(progressManager.getProgress() + progressIncrement);
   }


   function buildAndSendRequest() {
       var url = 'https://finance.yahoo.com/webservice...on&view=detail';
       var method = 'GET';
       var headers = {
           'Content-Type': 'text/json'
       };
       var body = '';
       return ai.https.request(url, method, body, headers);
   }


   function parseData(responseJSON) {
       return JSON.parse(responseJSON);
   }


   function createTable(builder, tableEl) {
       var tableId = tableEl.classname;
       var tableDisplayName = tableEl.classname;
       var table = builder.addTable(tableId);
       table.setDisplayName(tableDisplayName);
       var i = 0;
       createColumn(table, "change", "Change", i++, false, "number");
       createColumn(table, "chg_percent", "Change Percent", i++, false, "number");
       createColumn(table, "day_high", "Day High", i++, false, "number");
       createColumn(table, "day_low", "Day Low", i++, false, "number");
       createColumn(table, "issuer_name", "Issuer Name", i++, false, "string");
       createColumn(table, "issuer_name_lang", "Issuer Name Lang", i++, false, "string");
       createColumn(table, "name", "Name", i++, false, "string");
       createColumn(table, "price", "Price", i++, false, "number");
       createColumn(table, "symbol", "Symbol", i++, false, "string");
       createColumn(table, "ts", "TS", i++, false, "integer");
       createColumn(table, "type", "Type", i++, false, "string");
       createColumn(table, "utctime", "UTC Time", i++, false, "datetime");
       createColumn(table, "volume", "Volume", i++, false, "integer");
       createColumn(table, "year_high", "Year High", i++, false, "number");
       createColumn(table, "year_low", "Year Low", i++, false, "number");
   }


   function createColumn(table, columnId, columnDisplayName, displayOrder, mandatoryForImports, dataType) {
       var column = table.addColumn(columnId);
       column.setDisplayName(columnDisplayName);
       column.setDisplayOrder(displayOrder);
       column.setMandatoryForImports(mandatoryForImports);
       switch (dataType) {
           case "integer":
               column.setIntegerColumnType();
               break;
           case "string":
           case "base64_encoded":
               column.setTextColumnType(100);
               break;
           case "date":
               column.setDateTimeColumnType();
               break;
           case "datetime":
               column.setDateTimeColumnType();
               break;
           case "boolean":
               column.setBooleanColumnType();
               break;
           case "number":
           case "currency":
               column.setFloatColumnType();
               break;
           default:
               // unknown type
               ai.log.logInfo('Unable to create a column. Its type is unknown.', 'Column ' + columnId + ' could not be created because its data type was not known. Its data type was ' + dataType + '.');
               break;
       }
   }