function testConnection(context) {
// Step 1: Create a https request to send to API
var URL = 'https://drive.google.com/uc?export=d...FJrak1oUVlpQzg';
var method = 'GET';
var body = '';
var headers = null;
// Step 2: Send request and receive response
var response = ai.https.request(URL, method, body, headers);
// Step 3: Interrogate response to see if it was successful. Return true or false depending on the result.
//In this case we are just checking the response code.
var responseCode = response.getHttpCode();
if (responseCode == 200) {
ai.log.logInfo('Test Connection Succeeded','Response code was 200')
return true;
}else{
ai.log.logInfo('Test Connection Failed','Response code was not 200')
return false;
}
}
function importStructure(context) {
try{
// Step 1: Get the structure builder object that is used to create tables/columns etc.
var builder = context.getStructureBuilder();
//optionally, create a progress manager to increment progress percentage in import structure status dialog and tasks
var progressManager = context.getProgressManager();
// Step 2: Construct and send an https request to get all tables.
var URL = 'https://drive.google.com/uc?export=d...FJrak1oUVlpQzg';
var method = 'GET';
var body = '';
var headers = null;
ai.log.logInfo('Import Structure: Sending Request');
var response = ai.https.request(URL, method, body, headers);
// Step 3: if response was successful, parse the https response body into an XML document and create tables and columns
if (response.getHttpCode() == 200) {
ai.log.logInfo('Import Structure: Successful response received');
var parser = ai.xml.createParser();
var xmlDoc = parser.parse(response.getBody());
//Parse the https response body into a XML document
var data = parseData(response.getBody()); //returns data XML
function previewData(context) {
//For this particular google doc, there is limited data so we can re-use the import data function with the context for preview data.
//Generally you would need a different function to limit the amount of data you get back on preview
importData(context);
}
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();
// 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;
// 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);
// Step 4: check if response was successful
if (response.getHttpCode() == 200) {
ai.log.logInfo('Import Data: Successful response received');
// 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');
//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);
}
//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;
}