Analisando texto em um  Google Sheet usando a API de linguagem Natural de Cloud e Apps Script

Existem muitas fórmulas no Google Sheets para analisar dados quantitativos, mas as planilhas geralmente capturam valiosos dados de texto também. Os dados de texto no Google Sheets podem vir de muitas fontes: respostas do Google Form, notas colunas, descrições e muito mais. Como seres humanos, podemos entender esses dados lendo isso, mas isso se torna difícil à medida que seus dados crescem em centenas ou milhares de linhas.

O Cloud Natural Language (Cloud NL) utiliza a tecnologia de aprendizado de máquinas usada pelo Google Search e o Google Assistant e permite que qualquer pessoa realize análise de sintaxe, análise de sentimentos e análise de entidade com seus próprios dados. O Cloud NL é acessível a partir do Google Sheets com algumas linhas do Script de Aplicativos e pode ajudar a extrair significado de dados de texto de forma escalável.

Nesta publicação, vamos importar um conjunto de dados do Kaggle para o Google Sheets, escrever alguns scripts de aplicativos para enviar nossos dados de texto para o Cloud NL e visualizar os resultados no Data Studio.


Importando dados para o Sheets

O conjunto de dados Boston Airbnb Open Data em Kaggle inclui capturas de tela de reviews de convidados para milhares de listagens da Airbnb em Boston. Uma listagem pode ter centenas de reviews, o que torna esses dados um candidato ideal para o Cloud NL.

Devido ao grande tamanho dos arquivos de conjunto de dados, selecionaremos apenas uma listagem para esta análise, list_id = 66288. No reviews.csv, carregaremos as 400+ avaliações para a listagem selecionada em uma planilha do Google Sheets. Em seguida, vamos importar e inserir os dados do listing.csv como um sheet adicional nesta mesma planilha.

Pré-processamento de dados com a função de tradução incorporada do Google Sheets

Para a nossa análise, usaremos o Cloud NL para identificar as entidades mencionadas nos reviews (pense em "estacionamento", "localização", "wifi", etc.) e determine o sentimento (positivo ou negativo) expresso em relação a essas entidades .

O método Cloud NL que usaremos, análise de sentimento da entidade, atualmente suporta inglês. Como o texto do comentário em nossos dados está em vários idiomas, traduziremos o texto para o inglês antes de enviá-lo para o Cloud NL. O Google Sheets tem duas funções internas que facilmente fazem isso acontecer: DETECTLANGUAGE e GOOGLETRANSLATE.



Enviando dados de texto para Cloud Natural Language

Agora, estamos prontos para escrever alguns Apps Script para gerenciar as chamadas da API e adicionar os dados do Cloud NL à nosso sheet.

Em primeiro lugar, escreveremos uma função chamada retrieveEntitySentiment que leva uma linha de texto como parâmetro e consulta o método analyzeEntitySentiment do Cloud NL. Chamar este método requer uma conta e projeto do Google Cloud Platform, permitindo a API do Google Natural Language e criando uma chave de API. Na função, forneceremos esta chave da API, o URL da solicitação e nosso corpo de solicitação para analyzeEntitySentiment. O corpo do pedido inclui o nosso texto junto com seu idioma e tipo, e um campo chamado codificação do tipo. Uma vez que o nosso pedido JSON esteja pronto, a função chamará o ponto final e analisará a resposta JSON com as entidades e o sentimento relacionado.

function retrieveEntitySentiment (line) {
 var apiKey = "your key here";
 var apiEndpoint = https://language.googleapis.com/v1/documents:analyzeEntitySentiment?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);
 return JSON.parse(response);
};

Ao usar as chaves da API, tenha cuidado para mantê-las seguras. Você também pode considerar métodos alternativos para autenticar, inclusive usando uma conta de serviço.

Agora podemos incorporar esta função em nossa função primária, vamos chamá-lo de MarkEntitySentiment. Esta função principal identificará quais comentários ainda não foram enviados para o Cloud NL, ligue para a função retrieveEntitySentiment e registre os dados de resposta em um formato pronto para análise posterior. Também definiremos variáveis ​​que identificam o sheet de review e indexam as colunas que precisaremos fazer referência.

function markEntitySentiment() {
 // set variables for reviewData sheet
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var dataSheet = ss.getSheetByName('reviewData');
 var rows = dataSheet.getDataRange();
 var numRows = rows.getNumRows();
 var values = rows.getValues();
 var headerRow = values[0];
 
 // checks to see if entitySentiment sheet is present; if not, creates new sheet and sets header row
 var entitySheet = ss.getSheetByName('entitySentiment');
 if (entitySheet == null) {
  ss.insertSheet('entitySentiment');
  var entitySheet = ss.getSheetByName('entitySentiment');
  var esHeaderRange = entitySheet.getRange(1,1,1,6);
  var esHeader = [['Review ID','Entity','Salience','Sentiment Score','Sentiment Magnitude','Number of mentions']];
  esHeaderRange.setValues(esHeader);
 };
 
 // find the column index for comments, language_detected, comments_english
 var commentsColumnIdx = headerRow.indexOf(COLUMN_NAME.COMMENTS);
 var languageColumnIdx = headerRow.indexOf(COLUMN_NAME.LANGUAGE);
 var translationColumnIdx = headerRow.indexOf(COLUMN_NAME.TRANSLATION);
 var entityColumnIdx = headerRow.indexOf(COLUMN_NAME.ENTITY);
 var idColumnIdx = headerRow.indexOf(COLUMN_NAME.ID);

Na próxima parte da função, processaremos cada linha, chamando Cloud NL para cada comentário. Chamaremos a API apenas para linhas que contenham um comentário de texto e também não tenham sido processadas em uma corrida anterior. Ao criar esta verificação em nosso código, podemos definir a função para executar em vários disparadores, como no formulário enviar, na edição ou em um intervalo de tempo regularmente pré-definido.

ss.toast("Analyzing entities and sentiment...");
 // Process each row
 for (var i = 0; i < numRows; ++i) {
   var value = values[i];
   var commentEnCellVal = value[translationColumnIdx];
   var entityCellVal = value[entityColumnIdx];
   var reviewId = value[idColumnIdx];
   
   // Call retrieveEntitySentiment function for each row that has comments and also an empty entity_sentiment cell
   if(commentEnCellVal && !entityCellVal) {
       var nlData = retrieveEntitySentiment(commentEnCellVal);
       // Paste each entity and sentiment score into entitySentiment sheet
       for (var j = 0; j < nlData.entities.length; ++j) {
         var entityInResponse = nlData.entities[j];
         var lastRowIdx = entitySheet.getLastRow() + 1;
         var newValues = [[reviewId, entityInResponse.name, entityInResponse.salience, entityInResponse.sentiment.score, entityInResponse.sentiment.magnitude, entityInResponse.mentions.length]];
         var pastingRange = entitySheet.getRange(lastRowIdx,1,1,6);
         pastingRange.setValues(newValues);
       }
       // Paste "complete" into entity_sentiment column to denote completion of NL API call
       dataSheet.getRange(i+1, entityColumnIdx+1).setValue("complete");
    }
  }
};

Agora que nosso Script de Aplicativos está completo, estamos prontos para executar o MarkEntitySentiment e assistir o fluxo de dados de resposta na guia entitySentiment.



Visualizando insights NL com o Data Studio

Uma vez que o script seja executado, encontraremos mais de 5.000 linhas de dados na planilha entitySentiment! Para conseguir visualizar os dados, use o Google Data Studio (beta), que é uma ferramenta de geração de relatórios e visualização com conexões integradas a várias fontes de dados, incluindo Planilhas do Google. O Data Studio permite compartilhar painéis interativos enquanto ajuda a proteger a segurança dos dados brutos.

Para começar, navegaremos para datastudio.google.com, iniciar um novo relatório e criar uma nova fonte de dados. Depois de escolher o conector do Google Sheets, poderemos adicionar nossos sheets de informações de entidade e de listagem como fontes de dados para o nosso relatório.


O Data Studio possui várias opções de gráfico para escolher. Em nosso relatório, vamos começar com duas tabelas simples mostrando as entidades pelo sentimento médio e número de menções tanto para as entidades mais positivas e negativas.


Adicionando dados de classificação da guia de listagens, usaremos gráficos de scorecard para mostrar as classificações de nossa listagem em relação às classificações médias em todas as propriedades de Boston. O Data Studio também oferece um mapa geográfico que mostra a localização da lista usando coordenadas de latitude e longitude a partir dos dados.

Dê uma olhada no produto final, como uma integração web interativa! Com apenas alguns cliques, o Data Studio lhe dará o trecho de código que você precisa para incorporar seu relatório em uma página da Web, tornando-o disponível para o público ou para um determinado conjunto de usuários.


Tente enviar um exemplo de seu próprio texto na página do produto Cloud NL para ver instantaneamente a resposta da API. Depois de determinar quais informações são importantes para você, copie e cole o código deste tutorial em seu próprio sheet e ajuste-o para suas necessidades usando a documentação do produto para o Cloud NL e Apps Script.


Por Alicia Williams, Cloud Advocate