Import Data

最後の更新
PDFとして保存

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