Automatize relatórios de vários sites com planilhas do Google e API GSC

Trabalhar com SEO leva a desafios interessantes que tenho certeza que todos vocês já enfrentaram.

Você é um mestre em flexibilidade e no gerenciamento de tarefas tediosas. Recentemente, me vi lidando com mais de 100 sites de primeira linha.

Trabalhando com empresas globais, é um grande enigma:

• Dispute dados para mais de 100 sites.
• Acompanhe o desempenho de cada site.

E, como alguns desses sites competem entre si na primeira página do Google, é bem possível que o tráfego do Site 1 caia, mas o Site 2 capture a perda.

Verificar o Google Search Console (GSC) de um site é fácil, mas é intenso com centenas de sites em escala global.

O que você pode fazer?

Eu desenvolvi um script de aplicativos do Planilhas Google que se conecta à API do GSC para transformar os relatórios globais de uma tarefa árdua que pode levar dias – ou semanas – em uma que leva alguns minutos.

Depois de criar o script, posso facilmente definir um intervalo de datas e extrair cada site:

• Cliques e impressões.
• Palavras-chave.
• Classificações médias.
• Etc.

Como gerenciamos centenas de sites, não é incomum que os usuários acessem um de nossos sites para fazer compras, conforme mencionado acima.

No grande esquema das coisas, o panorama geral é mais importante do que o desempenho de um site individual.

O que vou te mostrar é meu Processo de 10 etapas para criar um script que extraia cliques e impressões e depois compara ano após ano (YoY).

Processo de 10 etapas para criar um script de aplicativos do Planilhas Google para gerar relatórios sobre centenas de sites

Etapa 1: criando suas planilhas Google

Captura de tela do autor, abril de 2024

A primeira etapa é criar seu arquivo original do Planilhas Google. Você pode fazer isso seguindo estas etapas:

• Vá para o Google Drive.
• Navegue até a pasta onde deseja colocar os arquivos.
• Clique com o botão direito no fundo
• Selecione > Planilhas Google > Planilha em branco.

Você vai querer renomear o arquivo. Chamei o meu de “Relatórios globais do Search Console”.

Captura de tela do autor, abril de 2024

Seu arquivo agora está configurado e você está pronto para a próxima etapa.

Etapa 2: configurando sua planilha Google

Uma folha em branco não é útil e não fará sentido para os usuários até que você adicione alguns cabeçalhos na linha 1. Os cabeçalhos que recomendo adicionar, nesta ordem e em negrito, são:

• Local na rede Internet.
• Nicho.
• Cliques.
• Impressões.
• Cliques YoY.
• Impressões anuais.
• Cliques % Diferença.
• Diferença de % de impressões.

Seu arquivo agora deve ser parecido com isto:

Captura de tela do autor, abril de 2024

A próxima etapa é criar um projeto do Google Cloud, que também é bastante simples e direto.

Etapa 3: crie um projeto de dados do console do Google Cloud

A criação do seu projeto deveria ser gratuita porque o Google oferece um crédito de US$ 300 para testar sua plataforma. Se você ainda não usou o Google Cloud, poderá encontrá-lo em https://console.cloud.google.com/.

Agora você pode seguir estas etapas:

• Tocar Selecione Projeto > Novo Projeto.
• Insira o nome do projeto (exemplo: “Meu projeto de dados GSC”).
• Tocar Criar.

Captura de tela do autor, abril de 2024

• Clique Selecione o projeto.
• Selecione seu projeto.

• Clique no topo Procurar bar.
• Digite “API do Google Search Console.”
• Selecione “API do Google Search Console.”
• Clique Habilitar.

Etapa 4: criar scripts de aplicativos no Planilhas Google

Nesta etapa, trabalharemos na integração do Apps Script na Planilha Google que você criou anteriormente. Você precisará abrir a planilha e seguir estas etapas:

• Tocar Extensões > Script de aplicativos.

Não vou entrar em detalhes de como o script funciona, mas você pode copiar este código:

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  // Or DocumentApp or FormApp.
  ui.createMenu('Search Console')
    .addItem('Fetch Data', 'menuItem1')
    .addToUi();
}

function menuItem1() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lastRow = sheet.getLastRow(); // Find the last row with data in column A

  // Clear cells C2:F151 before processing data
  sheet.getRange("C2:F151").clearContent();

  for (var i = 2; i <= lastRow; i++) { var siteProperty = sheet.getRange(i, 1).getValue(); var startDateValue = sheet.getRange('M1').getValue(); var endDateValue = sheet.getRange('M2').getValue(); var timeZone = SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone(); var format = "yyyy-MM-dd"; // Calculate dates for last year var lastYearStartDate = new Date(startDateValue); lastYearStartDate.setFullYear(lastYearStartDate.getFullYear() - 1); var lastYearEndDate = new Date(endDateValue); lastYearEndDate.setFullYear(lastYearEndDate.getFullYear() - 1); var startDate = Utilities.formatDate(lastYearStartDate, timeZone, format); var endDate = Utilities.formatDate(lastYearEndDate, timeZone, format); // Fetch data for the previous year var previousYearResponse = requestSearchConsoleAPI(siteProperty, startDate, endDate); // Fetch data for the current year (unchanged) startDate = Utilities.formatDate(new Date(startDateValue), timeZone, format); endDate = Utilities.formatDate(new Date(endDateValue), timeZone, format); var currentYearResponse = requestSearchConsoleAPI(siteProperty, startDate, endDate); // Process and write data for both years processAndWriteData(sheet, i, previousYearResponse, currentYearResponse); } } function processAndWriteData(sheet, row, previousYearResponse, currentYearResponse) { // Check if response is not defined or null and has at least one row if (previousYearResponse && previousYearResponse.length > 0) {
    var previousYearClicks = 0;
    var previousYearImpressions = 0;

    previousYearResponse.forEach(function(row) {
      previousYearClicks += row.clicks;
      previousYearImpressions += row.impressions;
    });

    sheet.getRange(row, 5).setValue(previousYearClicks); // Write to column D (index 5)
    sheet.getRange(row, 6).setValue(previousYearImpressions); // Write to column E (index 6)
  } else {
    Logger.log('No data found for previous year in row: ' + row);
  }

// Process and write data for the current year
  if (currentYearResponse && currentYearResponse.length > 0) {
    var currentYearClicks = 0;
    var currentYearImpressions = 0;

    currentYearResponse.forEach(function(row) {
      currentYearClicks += row.clicks;
      currentYearImpressions += row.impressions;
    });

    sheet.getRange(row, 3).setValue(currentYearClicks); // Write to column C (index 3)
    sheet.getRange(row, 4).setValue(currentYearImpressions); // Write to column D (index 4)
  } else {
    Logger.log('No data found for current year in row: ' + row);
  }
}



function requestSearchConsoleAPI(siteProperty, startDate, endDate) {

  try {
    const oauthToken = ScriptApp.getOAuthToken(); // Correctly call the method
    const siteUrl = siteProperty;
    const url="https://www.googleapis.com/webmasters/v3/sites/" + encodeURIComponent(siteUrl) + '/searchAnalytics/query';
    const payload = {
      startDate: startDate,
      endDate: endDate,
      type: 'web'
    };

    const headers = {
      'Authorization': 'Bearer ' + oauthToken,
      'Content-Type': 'application/json'
    };
    const options = {
      'method': 'post',
      'contentType': 'application/json', // Consistent content type
      'headers': headers,
      'payload': JSON.stringify(payload),
      'muteHttpExceptions': true
    };

    const response = UrlFetchApp.fetch(url, options);
    const responseCode = response.getResponseCode();
    const contentText = response.getContentText(); // Get response text for logging

  Logger.log('Response Code: ${responseCode}'); // Use backticks
  Logger.log('Response Content: ${contentText}'); // Use backticks


  if (responseCode === 200) {
    const json = JSON.parse(contentText);
    Logger.log(json); // This will log the actual JSON response
    return json.rows; // Adjust this line based on the actual structure of your API response
  } else {
    // Correctly use backticks here for template literals
    const errorMessage="Error fetching data: ${responseCode} - ${contentText}";
    Logger.log(errorMessage);
    throw new Error(errorMessage);
  }

  } catch (e) {
    Logger.log('Error: ${e.toString()}');
    return null;
  }
}

Em seguida, volte ao seu projeto do Apps Script e faça o seguinte:

• Imprensa CTRL+A para selecionar tudo.
• Imprensa CTRL+V para colar o código que você copiou.
• Tocar OK.
• Clique Salvar projeto.
• Tocar Correr.

*Observação: Se você estiver recebendo um erro de solicitação incorreta do Google com muitos redirecionamentos, é porque você tem várias contas conectadas. Tente em um navegador com apenas uma conta do Google conectada.

Captura de tela do autor, abril de 2024

Você será solicitado a Revise as permissões e precisará selecionar a Conta do Google associada ao seu Google Search Console.

O Google avisará você porque o aplicativo não foi verificado, então basta tocar no “Avançado” configuração e depois “Vá para o projeto sem título (inseguro).”

Captura de tela do autor, abril de 2024

Finalmente, você pode concluir esta etapa tocando ou clicando no Permitir botão.

Etapa 5: configurar as credenciais de acesso

Eu sei que há muitas idas e vindas entre o Planilhas e o Console do Google Cloud, mas é uma necessidade infeliz neste momento. Agora, configuraremos credenciais de acesso, o que exigirá que você volte ao Console do Google Cloud.

Observação: Você deve ter habilitado a API do Google Search Console da etapa anterior.

Sua tela deverá ficar parecida com esta:

Captura de tela do autor, abril de 2024

Você precisará:

• Tocar Credenciais > Criar credenciais.
• Tocar ID do cliente OAuth > Configurar tela de consentimento.

• Clique Externo.
• Tocar Criar.
• Digitar “Meus dados GSC” como o nome do aplicativo.
• Adicione o seu E-mail de suporte (seu e-mail usado para GSC).
• Adicione o seu Dinformações de contato do desenvolvedor (o e-mail que você usou para o GSC).
• Tocar Salve e continue.
• Tocar ADICIONE OU REMOVA ESCOPOS.
• Verifique 2 dos API do Google Search Console escopos (pode estar na página 2).

• Clique Atualizar.
• Clique Salve e continue.
• Agora clique Adicionar usuários.

• Você pode adicionar vários usuários, de preferência aqueles que tenham acesso ao GSC.
• Salve e continue.

Etapa 6: configurar o projeto Google Cloud para dados GSC

Enquanto ainda estamos no projeto Google Cloud, clique no botão ícone de hambúrguer e vai para Visão geral da nuvem > Painel:

Captura de tela do autor, abril de 2024

Você notará que diz “Número do projeto”, que você deve selecionar e cópia de pressionando CTRL+C.

Volte para a guia do Apps Script e toque em Configurações do projeto:

Captura de tela do autor, abril de 2024

Vá para a seção intitulada Projeto Google Cloud Platform (GCP)cole o número do projeto (CTRL + V) na caixa de texto e clique em Definir projeto.

Etapa 7: renomeie seu script do Google Apps

Agora você deseja renomear seu Apps Script acessando Histórico do Projeto assim:

Você então:

• Clique Projeto sem título na parte superior da tela.
• Digitar “Meu script de projeto de dados GSC.”
• Clique em Renomear.

Etapa 8: edite o arquivo de manifesto do Google Apps para o script Code.gs

Você ainda está dentro do seu roteiro, e vamos voltar ao Configurações do projeto assim como fizemos antes.

Desta vez, você vai querer clicar Mostrar arquivo de manifesto “appsscript.json” no editor para ter certeza de que há uma marca de seleção ao lado dele.

A seguir, clique em editor e navegue até o appsscript.jsonque você pode ver abaixo:

Captura de tela do autor, abril de 2024

Você desejará excluir tudo no arquivo appsscript.json e colar o seguinte script:

{
  "timeZone": "America/New_York",
  "dependencies": {
  },
  "exceptionLogging": "STACKDRIVER",
  "oauthScopes": (
    "https://www.googleapis.com/auth/webmasters",
    "https://www.googleapis.com/auth/script.external_request",
    "https://www.googleapis.com/auth/spreadsheets",
    "https://www.googleapis.com/auth/spreadsheets.currentonly"
  )
}

Depois de adicionar o código, você pode clicar no seu Código.gs arquivo e toque Salvar, e então Correr. Você será solicitado a revisar as permissões e precisará selecionar sua conta apropriada para continuar usando.

Após algumas solicitações, você será solicitado a permitir “Meus dados GSC” para seu aplicativo e a execução começará.

Etapa 9: ajuste as datas para análise de dados do site

No arquivo do Planilhas Google, você deseja adicionar o seguinte em:

• L1: Data de início.
• L2: Data final.

Observação: As datas de início e término devem ser especificadas em M1 e M2. Por exemplo, você pode inserir:

Observação: o formato da data pode variar dependendo das configurações do sistema e da localização.

Etapa 10: definir a formatação condicional para células não vazias menores que zero

Tudo está configurado, mas você deve adicionar alguma formatação condicional para melhorar a aparência. Vamos nos concentrar nas colunas “% de diferença de cliques” e “% de diferença de impressões”:

Captura de tela do autor, abril de 2024

Selecione as linhas sob os cabeçalhos “% de diferença de cliques” e “% de diferença de impressões” e clique em Formatar > Formatação condicional. Sob Regras de formato, você vai querer selecionar Menor que.

Na área de texto “Valor ou fórmula”, você pode adicionar 0.

O que isso faz é que, se for menor que 0, mudaremos a cor para vermelho, pois está negativo e o tráfego foi perdido. Você pode fazer isso clicando na lata de tinta e alterando-a para vermelho antes de clicar em concluído.

Se quiser alterar um aumento positivo no tráfego para verde, você adicionará outra regra para Maior que e adicione o 0 valor.

Aqui estão as fórmulas para usar em G2 e H2 (você pode replicá-las para cada linha; basta clicar e arrastar para baixo para as outras linhas):

=IFERROR(IF(AND(C2<>"",E2<>""), (C2-E2)/E2, ""),"")

=IFERROR(IF(AND(D2<>"",F2<>""), (D2-F2)/F2, ""),"")

Agora você tem uma maneira fácil de gerar relatórios em vários sites ao mesmo tempo.

É isso, você tem seu relatório global

Na coluna A, insira as propriedades do Google Search Console; se for uma propriedade de domínio, adicione-a como sc-domain:example.com ou uma propriedade de URL como https://example.com

Para executar ou atualizar o relatório, use o menu especial Search Console > Buscar dados:

*Observação: Este script suporta cerca de 150 domínios, mas se precisar de mais, você pode ajustar a linha 14 em seu arquivo AppScripts:

sheet.getRange("C2:F151").clearContent();

Usando este mesmo tutorial, você terá facilidade em transformar dias de coleta de dados e execução de relatórios em poucos minutos. Você pode até expandir os scripts para realizar outros cálculos ou coletar mais dados para o seu relatório.

Confira meu outro tutorial sobre Integração do ChatGPT com o Planilhas Google.

Automatizar seus relatórios é uma ótima maneira de agilizar tarefas tediosas e espero que facilite um pouco seu trabalho.

Mais recursos:

Imagem em destaque: 200dgr /Shutterstock