automl-googlesheet.js

var COLUMN_NAME = {
  //Col For Extraction
  TEXTCOL: 'title',
  //last Col of the Sheet
  LASTCOL: 'source',
  //Sheet Name
  SHEETNAME: 'exp2'
};

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('ML Tools')
    .addItem('Mark Entities', 'markEntity')
    .addToUi();
};
function retrieveEntity (line) {
    //var line = "this is Google and that is Apple"
    var apiKey = "API KEY";
    var apiEndpoint = 'https://language.googleapis.com/v1/documents:analyzeEntities?key=' + apiKey;
    // Create our json request, w/ text, language, type & encoding
      var nlData = {
      document: {
        language: 'en-us',
        type: 'PLAIN_TEXT',
        content: line
      },
      encodingType: 'UTF8'  };
    //  Package all of the options and the data together for the call
    var nlOptions = {
      method : 'post',
      contentType: 'application/json',
      payload : JSON.stringify(nlData)
    };
    //  And make the call
    var response = UrlFetchApp.fetch(apiEndpoint, nlOptions);
    console.log(JSON.parse(response))
    return JSON.parse(response);
};

function markEntity() {
      // set variables for reviewData sheet
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var dataSheet = ss.getSheetByName(COLUMN_NAME.SHEETNAME);
      var rows = dataSheet.getDataRange();
      var numRows = rows.getNumRows();
      var values = rows.getValues();
      var headerRow = values[0];
      
      // checks to see if entity sheet is present; if not, creates new sheet and sets header row
      var entitySheet = ss.getSheetByName('EntityExtracted');
      if (entitySheet == null) {
        ss.insertSheet('EntityExtracted');
      };
      var entitySheet = ss.getSheetByName('EntityExtracted');
      var esHeaderRange = entitySheet.getRange(1,1,1,3);
      var esHeader = [['Text','Entities','Types']];
      esHeaderRange.setValues(esHeader);
      
      
      // find the column index for titles
      var titleColumnIdx = headerRow.indexOf(COLUMN_NAME.TEXTCOL);
      var statusColumnIdx = headerRow.indexOf(COLUMN_NAME.LASTCOL)+1;
      ss.toast("Analyzing entities...");
      // Process each row 
      for (var i = 1; i < numRows; ++i) {
        var value = values[i];
        var titleEnCellVal = value[titleColumnIdx];

        
        // Call retrieveEntity function for each row that has comments and also an empty entity cell
        if(titleEnCellVal) {
            var nlData = retrieveEntity(titleEnCellVal);
            // Paste each entity score into sheet
            var newValues = []
            nlData.entities.forEach(function(entity) {
            var row = [titleEnCellVal, entity.name, entity.type
            ];
              newValues.push(row);
            });

            if(newValues.length) {
              entitySheet.getRange(entitySheet.getLastRow() + 1, 1, newValues.length, newValues[0].length).setValues(newValues);
            }
            // Paste "complete" into status column to denote completion of NL API call
            dataSheet.getRange(i+1, statusColumnIdx+1).setValue("complete");
        }
      }

};