Import Data Step 1: Use the passed-in contextual information to create a rowset object.
function importData(context) {
try{
// Step 1: Make use of passed in contextual information to create a rowset object. Here, I am simply assigning tableId to variables to be used elsewhere in this script.
var rowset = context.getRowset();
rowset.setSmartParsingEnabled(true);
var tableId = rowset.getTableId();
var columns = rowset.getColumns();
Import Data Step 2: Create a HTTPS request to return data.
// Step 2: Create a https request to the google doc (syntax: 'https://drive.google.com/uc?export=downloade&id=' + id of your doc in shared link)
var URL = 'https://drive.google.com/uc?export=d...FJrak1oUVlpQzg';
var method = 'GET';
var body = '';
var headers = null;
Import Data Step 3: Send HTTPS request and receive a response.
// Step 3: Send https request and receive response.
var response = ai.https.request(URL, method, body, headers);
ai.log.logInfo('Import Data: Sending Request');
var response = ai.https.request(URL, method, body, headers);
Import Data Step 4: Check that the response was successful.
// Step 4: check if response was successful
if (response.getHttpCode() == 200) {
ai.log.logInfo('Import Data: Successful response received');
Import Data Step 5: Parse the HTTPS response body into a XML document.
// Step 5: parse the https response body into an XML document
var data = parseData(response.getBody());
var tablesArray = data.getChildElements('Table');
//loop through the first 10 tables in data - theses are the only tables we imported in import structure
for (numTable = 0; numTable < 10; numTable++) {
// if Table time attribute = tableid, add column data
if (tablesArray[numTable].getAttribute('time').getValue() == tableId) {
//get 'Cube' data rows
var rowsArray = tablesArray[numTable].getChildElements('Cube');
Import Data Step 6: Process each row to extract cell values for each column.
//Step 6: Process each row to extract the cell values for each column
for (numRows = 0; numRows < rowsArray.length; numRows++) {
//restrict columns to only those selected for import
var cols = [];
for (numCols = 0; numCols < columns.length; numCols ++){
var colValue = rowsArray[numRows].getAttribute(columns[numCols].getId().toLowerCase()).getValue();
cols.push(colValue);
}
Import Data Step 7: Add each row array to the rowset.
//Step 7: add each row array to the rowset
rowset.addRow(cols);
}
break; //exit loop if data table was found
} //end if data is for current table
} //end loop through each table element of data
} else {
throw "Import Data Failed. Response code was not 200";
}
} catch (error) {
throw error; //recommended if you use try/catch, otherwise import data will return success
ai.log.logInfo('ERROR: Import Data failed','Error: "' + error + '"');
ai.log.logError('ERROR: Import Data failed','Error: "' + error + '"');
}
}
function parseData(responseXml) {
ai.log.logInfo('Parsing Data...');
var parser = ai.xml.createParser();
var xmlDoc = parser.parse(responseXml);
var root = xmlDoc.getRootElement();
var data = root.getChildElement('Data');
ai.log.logInfo('Data parsing completed');
return data;
}
function getColumnRemoteIds(columns) {
var columnIds = [];
for (var i=0; i<columns.length; i++) {
columnIds.push(columns[i].getId());
}
return columnIds;
}