Spreadsheet NLP on GCP

spreadsheet.gs

/**
 * Copyright 2020 Google LLC
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

/*********************************************/
Add this code to a Google Sheet!
  
To use it, youll need to add script property called
NL_API_KEY with your Google Cloud Natural Language API Key.
/*********************************************/
// USE NATURAL LANGUAGE API

const grabPeople = (x) => grabEntities(x, "PERSON");
const grabPlaces = (x) => grabEntities(x, "LOCATION");
const grabOrgs = (x) => grabEntities(x, "ORGANIZATION");


function grabEntities (line, kind) {

  if(!line) return [];
  
  // Grab the API key (stored in Apps Sheet Properties)
  const env = PropertiesService.getScriptProperties();
  const key = env.getProperty('NL_API_KEY');

  
  const apiEndpoint = `https://language.googleapis.com/v1beta2/documents:analyzeEntities?key=${key}`;
  
 // Create our json request, w/ text, language, type & encoding
  const nlData = {
   document: {
     language: 'en',
     type: 'PLAIN_TEXT',
     content: line
   },
   encodingType: 'UTF8'  };
  
 //  Package all of the options and the data together for the call
 const nlOptions = {
   method : 'post',
   contentType: 'application/json',
   payload : JSON.stringify(nlData)
 };
  
 //  And make the call
 const response = JSON.parse(UrlFetchApp.fetch(apiEndpoint, nlOptions));
  
 const result = [];
 console.log('',response);
 response.entities.forEach(function(entity) {
   console.log(`Entity ${entity.type} ${entity.name}`);
   if(entity.type != kind.toUpperCase()) return;
   for (let i = 0; i < entity.mentions.length; i++) {
     // Only include proper nouns
     if(entity.mentions[i].type != "PROPER") continue;
     if(!result.includes(entity.name.toLowerCase())) {
       result.push(entity.name.toLowerCase());
     }
     break;
   }
  });
  
  // Join all of the entities of the same type into one string
  return result.join(", ");
};