Google Sheets Full Example

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) {
       ai.log.logInfo('ERROR:  Import Data failed','Error: "' + error + '"');
       throw error;    //recommended if you use try/catch, otherwise import data will return success
   }
}


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