Automatice los informes multisitio con Google Sheets y la API de GSC

Trabajar en SEO conlleva desafíos interesantes que estoy seguro que todos habéis enfrentado en algún momento.

Eres un maestro de la flexibilidad y de la gestión de tareas tediosas. Recientemente me encontré tratando con más de 100 sitios de primer nivel.

Al trabajar con empresas globales, resulta todo un enigma:

• Recopila datos para más de 100 sitios.
• Controle el rendimiento de cada sitio.

Y, dado que algunos de estos sitios compiten entre sí en la primera página de Google, es muy posible que el tráfico del Sitio 1 disminuya pero el Sitio 2 capture la pérdida.

Verificar la Consola de búsqueda de Google (GSC) de un sitio es fácil, pero es intenso con cientos de sitios a escala global.

¿Qué puedes hacer?

Ideé un script de aplicaciones de Google Sheets que se conecta a la API de GSC para transformar los informes globales de una tarea ardua que puede llevar días (o semanas) a una que lleva unos minutos.

Después de crear el script, puedo poner fácilmente un rango de fechas y extraer de cada sitio:

• Clics e impresiones.
• Palabras clave.
• Clasificaciones promedio.
• Etc.

Dado que administramos cientos de sitios, no es raro que los usuarios terminen en uno de nuestros sitios para realizar su compra, como se mencionó anteriormente.

En el gran esquema de las cosas, el panorama general es más importante que el rendimiento de un sitio individual.

Lo que te voy a mostrar es mi Proceso de 10 pasos para crear un script que extraiga clics e impresiones y luego lo compara año tras año (YoY).

Proceso de 10 pasos para crear un script de aplicaciones de Google Sheets para generar informes en cientos de sitios

Paso 1: crear tus hojas de cálculo de Google

Captura de pantalla del autor, abril de 2024

El primer paso es crear su archivo original de Google Sheets. Puedes hacerlo siguiendo estos pasos:

• Vaya a GoogleDrive.
• Navegue hasta la carpeta donde desea colocar los archivos.
• Botón derecho del ratón en el fondo
• Seleccione > Google Sheets > Hoja de cálculo en blanco.

Querrás cambiar el nombre del archivo. Llamé al mío “Informes de Global Search Console”.

Captura de pantalla del autor, abril de 2024

Su archivo ya está configurado y está listo para el siguiente paso.

Paso 2: configurar su hoja de Google

Una hoja en blanco no es útil y no tendrá sentido para los usuarios hasta que agregue algunos encabezados en la Fila 1. Los encabezados que recomiendo agregar, en este orden y en negrita, son:

• Sitio web.
• Nicho.
• Clics.
• Impresiones.
• Clics interanuales.
• Impresiones interanuales.
• % de diferencia de clics.
• Impresiones % Diferencia.

Su archivo ahora debería verse así:

Captura de pantalla del autor, abril de 2024

El siguiente paso es crear un proyecto de Google Cloud, que también es bastante sencillo y directo.

Paso 3: cree un proyecto de datos de Google Cloud Console

Crear tu proyecto debería ser gratis porque Google ofrece un crédito de 300 dólares para probar su plataforma. Si no ha utilizado Google Cloud, puede encontrarlo en https://console.cloud.google.com/.

Ahora puedes seguir estos pasos:

• Grifo Seleccione Proyecto > Nuevo proyecto.
• Ingrese el nombre del proyecto (ejemplo: “Mi proyecto de datos GSC”).
• Grifo Crear.

Captura de pantalla del autor, abril de 2024

• Hacer clic Seleccionar Proyecto.
• Selecciona tu Proyecto.

• Haga clic en la parte superior Buscar bar.
• Escriba “API de Google Search Console”.“
• Seleccionar “API de la consola de búsqueda de Google”.
• Hacer clic Permitir.

Paso 4: cree secuencias de comandos de aplicaciones en Google Sheets

En este paso, trabajaremos para integrar Apps Script en la hoja de Google que creó anteriormente. Deberá abrir la Hoja y seguir estos pasos:

• Grifo Extensiones > Guión de aplicaciones.

No voy a entrar en detalles sobre cómo funciona el script, pero puedes 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;
  }
}

Y luego regrese a su proyecto de Apps Script y haga lo siguiente:

• Prensa CTRL+A para seleccionar todo.
• Prensa CTRL+V para pegar el código que copiaste.
• Grifo DE ACUERDO.
• Hacer clic Guardar proyecto.
• Grifo Correr.

*Nota: Si recibe un error de Solicitud incorrecta de Google con demasiadas redirecciones, esto se debe a que tiene varias cuentas iniciadas. Pruebe en un navegador con solo una cuenta de Google iniciada.

Captura de pantalla del autor, abril de 2024

Se le pedirá que Revisar permisos y deberá seleccionar la cuenta de Google asociada con su Google Search Console.

Google te dará una advertencia porque la aplicación no está verificada, así que simplemente toca el “Avanzado” configuración y luego “Ir al proyecto sin título (inseguro)”.

Captura de pantalla del autor, abril de 2024

Finalmente, puede completar este paso tocando o haciendo clic en el Permitir botón.

Paso 5: configurar las credenciales de acceso

Sé que hay muchos intercambios entre Sheets y Google Cloud Console, pero es una necesidad desafortunada en este momento. Ahora configuraremos las Credenciales de acceso, lo que requerirá que regrese a Google Cloud Console.

Nota: Debes tener activado la API de Google Search Console del paso anterior.

Su pantalla debería verse así:

Captura de pantalla del autor, abril de 2024

Necesitarás:

• Grifo Credenciales > Crear credenciales.
• Grifo ID de cliente de OAuth > Configurar pantalla de consentimiento.

• Hacer clic Externo.
• Grifo Crear.
• Ingresar “Mis datos GSC” como nombre de la aplicación.
• Agrega tu Correo electrónico de soporte (su correo electrónico utilizado para GSC).
• Agrega tu Dinformación de contacto del desarrollador (el correo electrónico que utilizó para GSC).
• Grifo Guardar y continuar.
• Grifo AÑADIR O QUITAR VISORES.
• Marque 2 de los API de la consola de búsqueda de Google alcances (puede estar en la página 2).

• Hacer clic Actualizar.
• Hacer clic Guardar y continuar.
• Ahora haga clic Agregar usuarios.

• Puede agregar varios usuarios, preferiblemente aquellos que tengan acceso a GSC.
• Guardar y continuar.

Paso 6: configurar el proyecto Google Cloud para datos de GSC

Mientras todavía estamos en Google Cloud Project, querrás hacer clic en el icono de hamburguesa E ir a Descripción general de la nube > Panel de control:

Captura de pantalla del autor, abril de 2024

Notarás que dice “Número de proyecto”, que debes seleccionar y Copiar presionando CTRL+C.

Vuelva a la pestaña Apps Script y toque Configuración del proyecto:

Captura de pantalla del autor, abril de 2024

Vaya a la sección titulada Proyecto Google Cloud Platform (GCP)pegue el número del proyecto (CTRL + V) en el cuadro de texto y haga clic en Establecer proyecto.

Paso 7: cambie el nombre de su secuencia de comandos de Google Apps

Ahora querrás cambiar el nombre de tu Apps Script yendo a Historia del proyecto como esto:

Entonces podrás:

• Hacer clic Proyecto sin título en la parte superior de la pantalla.
• Ingresar “Mi guión de proyecto de datos de GSC”.
• Haga clic en Rebautizar.

Paso 8: Edite el archivo de manifiesto de Google Apps para el script Code.gs

Todavía te quedas dentro de tu guión y vamos a volver a Configuración del proyecto tal como lo hicimos antes.

Esta vez querrás hacer clic Mostrar el archivo de manifiesto “appsscript.json” en el editor para asegurarte de que haya una marca de verificación al lado.

A continuación, haga clic en Editor y navegar hasta el aplicacionesscript.jsonque podéis ver a continuación:

Captura de pantalla del autor, abril de 2024

Querrá eliminar todo el contenido del archivo appsscript.json y pegar el siguiente 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"
  ]
}

Una vez que haya agregado el código, puede hacer clic en su Código.gs archivo y toque Ahorrar, y luego Correr. Se le pedirá que revise los permisos y deberá seleccionar su cuenta adecuada para continuar usándolos.

Después de algunas indicaciones, se le pedirá que permita su aplicación “Mis datos GSC” y comenzará la ejecución.

Paso 9: ajuste las fechas para el análisis de datos del sitio web

En el archivo de Google Sheets, querrás agregar lo siguiente en:

• L1: Fecha de inicio.
• L2: Fecha final.

Nota: Las fechas de inicio y finalización deben especificarse en M1 y M2. Por ejemplo, puede ingresar:

Nota: El formato de fecha puede diferir según la configuración y la ubicación de su sistema.

Paso 10: establezca el formato condicional para celdas no vacías menores que cero

Todo está configurado, pero debes agregar algún formato condicional para que se vea mejor. Nos centraremos en las columnas “% de diferencia de clics” y “% de diferencia de impresiones”:

Captura de pantalla del autor, abril de 2024

Seleccione las filas debajo de los encabezados “Clics % de diferencia” y “Impresiones % de diferencia” y haga clic en Formato > Formato condicional. Bajo Reglas de formato, querrás seleccionar Menos que.

En el área de texto “Valor o fórmula”, puede agregar 0.

Lo que esto hace es que si es menor que 0, cambiaremos el color a rojo ya que está en negativo y se ha perdido tráfico. Puedes hacer esto haciendo clic en la lata de pintura y cambiándola a rojo antes de hacer clic en Listo.

Si desea cambiar un aumento positivo en el tráfico a verde, agregará otra regla para Mas grande que y agrega el 0 valor.

Aquí están las fórmulas para usar en G2 y H2 (puede replicarlas para cada fila; simplemente haga clic y arrastre hacia abajo para las otras filas):

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

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

Ahora tiene una forma sencilla de ejecutar informes en varios sitios a la vez.

Eso es todo, ya tienes tu informe global

En la columna A, ingrese sus propiedades de Google Search Console; si es una propiedad de dominio, agréguela como dominio-sc:ejemplo.com o una propiedad de URL como https://ejemplo.com

Para ejecutar o actualizar el informe, utilice el menú especial Consola de búsqueda > Obtener datos:

*Nota: Este script admite alrededor de 150 dominios, pero si necesita más, puede ajustar la fila n.° 14 en su archivo AppScripts:

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

Con este tutorial, le resultará fácil convertir días de recopilación de datos y ejecución de informes en unos pocos minutos. Incluso puedes expandir los scripts para realizar otros cálculos o recopilar más datos para tu informe.

Consulte mi otro tutorial sobre la integración de ChatGPT con Google Sheets.

Automatizar sus informes es una excelente manera de agilizar tareas tediosas y espero que facilite un poco su trabajo.

Más recursos:

Imagen de portada: 200dgr /Shutterstock