README.md

Search Console API GAS

Este script de Google Apps Script está diseñado para trabajar con Google Sheets y Google Search Console. Agrega un menú personalizado en una hoja de cálculo y permite al usuario recuperar datos de clics mensuales para URLs específicas desde Google Search Console.

Basado en este script: https://medium.com/@george.halikiopoulos/build-your-own-google-sheet-extension-to-get-keyword-data-from-google-search-console-using-google-1ed42efe4be9

Explicación

Obtiene la hoja activa y las URLs:

1. Obtiene la hoja activa de la hoja de cálculo.
2. Recupera todas las URLs de la columna B y las almacena en un array.

Solicita información al usuario:

1. Solicita al usuario la URL del sitio y la fecha de inicio mediante cuadros de diálogo.

Configuración inicial:

1. Obtiene el token OAuth para autenticar las solicitudes a la API de Google Search Console.
2. Configura la fecha actual y la columna inicial para los datos.

Bucle para cada mes:

1. Mientras la fecha actual sea menor o igual a la fecha de hoy, realiza lo siguiente:
   • Calcula el inicio y fin del mes actual.
   • Formatea las fechas.
   • Inserta el mes correspondiente en la primera fila de la columna actual.

Solicitudes a la API:

1. Para cada URL, realiza una solicitud a la API de Google Search Console para obtener los datos de clics mensuales.
2. Procesa la respuesta de la API y suma los clics para cada URL.
3. Inserta los datos de clics en la celda correspondiente.

Avanza al siguiente mes:

1. Incrementa el mes actual y la columna.

Notifica al usuario:

1. Muestra un mensaje indicando que los datos de clics mensuales han sido añadidos a la hoja de cálculo.

ClicksDat Importer.js

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Search Console GC Extension')
    .addItem('Get clics Mes', 'getClicksByMonth')
    .addToUi();
}

function getClicksByMonth() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var urls = sheet.getRange("B2:B" + sheet.getLastRow()).getValues().flat(); // Obtener las URLs de la columna B
  
  // Solicitar al usuario la fecha inicial
  var ui = SpreadsheetApp.getUi();
  var siteUrl = ui.prompt('Enter site URL (e.g. www.example.com):').getResponseText();
  var startDate = ui.prompt('Enter start date (yyyy-mm-dd):').getResponseText();
  
  Logger.log('Site URL: ' + siteUrl);
  Logger.log('Start Date: ' + startDate);
  
  var oauthToken = ScriptApp.getOAuthToken();
  var currentDate = new Date(startDate);
  var today = new Date();
  var column = 3; // Comienza en la columna C
  
  while (currentDate <= today) { // Bucle hasta el mes actual
    var monthStart = new Date(currentDate.getFullYear(), currentDate.getMonth(), 1);
    var monthEnd = new Date(currentDate.getFullYear(), currentDate.getMonth() + 1, 0);
    
    var formattedStartDate = Utilities.formatDate(monthStart, Session.getScriptTimeZone(), 'yyyy-MM-dd');
    var formattedEndDate = Utilities.formatDate(monthEnd, Session.getScriptTimeZone(), 'yyyy-MM-dd');
    
    Logger.log('Fetching data for: ' + formattedStartDate + ' to ' + formattedEndDate);
    
    // Poner el mes en la fila 1
    var monthLabel = Utilities.formatDate(monthStart, Session.getScriptTimeZone(), 'yyyy-MM');
    sheet.getRange(1, column).setValue(monthLabel);
    
    urls.forEach(function(url, rowIndex) {
      if (url) {
        var apiUrl = 'https://www.googleapis.com/webmasters/v3/sites/https%3A%2F%2F' + siteUrl + '%2F/searchAnalytics/query';
        
        var payload = {
          startDate: formattedStartDate,
          endDate: formattedEndDate,
          dimensions: ["date", "page"],
          dimensionFilterGroups: [{
            filters: [{
              dimension: "page",
              expression: url
            }]
          }],
          rowLimit: 1000
        };
        var headers = {
          'Authorization': 'Bearer ' + oauthToken,
          'Content-Type': 'application/json'
        };
        var options = {
          headers: headers,
          method: 'POST',
          payload: JSON.stringify(payload),
          muteHttpExceptions: true
        };

        var response = UrlFetchApp.fetch(apiUrl, options).getContentText();
        Logger.log('API Response: ' + response);
        
        var json = JSON.parse(response);
        var clicks = 0;
        if (json.rows) {
          json.rows.forEach(row => {
            clicks += row.clicks;
          });
        }

        // Escribir los datos de clics en la celda correspondiente
        sheet.getRange(rowIndex + 2, column).setValue(clicks);
      }
    });
    
    // Avanzar al siguiente mes
    currentDate.setMonth(currentDate.getMonth() + 1);
    column++;
  }
  
  ui.alert('Clicks by month and URL data has been added to the sheet.');
}

SEOReportGenerator.js

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Search Console GC Extension')
    .addItem('Get Data by Month', 'getDataByMonth')
    .addToUi();
}

function getDataByMonth() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('raw_kw');
  var urls = sheet.getRange("B2:B" + sheet.getLastRow()).getValues().flat().filter(String);
  
  var ui = SpreadsheetApp.getUi();
  var siteUrl = ui.prompt('Enter site URL (e.g. www.example.com):').getResponseText();
  var startDate = ui.prompt('Enter start date (yyyy-mm-dd):').getResponseText();
  
  Logger.log('Site URL: ' + siteUrl);
  Logger.log('Start Date: ' + startDate);
  
  var oauthToken = ScriptApp.getOAuthToken();
  var currentDate = new Date(startDate);
  var today = new Date();
  
  // Obtener o crear la hoja kw_by_url
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var activeSheet = spreadsheet.getSheetByName('kw_by_url') || spreadsheet.insertSheet('kw_by_url');
  activeSheet.clear(); // Clear any existing content
  activeSheet.getRange(1, 1).setValue("URL");
  activeSheet.getRange(1, 2).setValue("Periodo");
  activeSheet.getRange(1, 3).setValue("Queries");
  activeSheet.getRange(1, 4).setValue("Posiciones");
  
  var row = 2; // Start from the second row
  
  while (currentDate <= today) {
    var monthStart = new Date(currentDate.getFullYear(), currentDate.getMonth(), 1);
    var monthEnd = new Date(currentDate.getFullYear(), currentDate.getMonth() + 1, 0);
    
    var formattedStartDate = Utilities.formatDate(monthStart, Session.getScriptTimeZone(), 'yyyy-MM-dd');
    var formattedEndDate = Utilities.formatDate(monthEnd, Session.getScriptTimeZone(), 'yyyy-MM-dd');
    
    Logger.log('Fetching data for: ' + formattedStartDate + ' to ' + formattedEndDate);
    
    var periodLabel = Utilities.formatDate(monthStart, Session.getScriptTimeZone(), 'yyyy/MM');
    
    urls.forEach(function(url) {
      if (url) {
        var apiUrl = 'https://www.googleapis.com/webmasters/v3/sites/' + encodeURIComponent('https://' + siteUrl) + '/searchAnalytics/query';
        
        var payload = {
          startDate: formattedStartDate,
          endDate: formattedEndDate,
          dimensions: ["query", "page"],
          dimensionFilterGroups: [{
            filters: [{
              dimension: "page",
              expression: url
            }]
          }],
          rowLimit: 1000
        };
        var headers = {
          'Authorization': 'Bearer ' + oauthToken,
          'Content-Type': 'application/json'
        };
        var options = {
          headers: headers,
          method: 'POST',
          payload: JSON.stringify(payload),
          muteHttpExceptions: true
        };

        var response = UrlFetchApp.fetch(apiUrl, options).getContentText();
        Logger.log('API Response: ' + response);
        
        var json = JSON.parse(response);
        var queries = [];
        var positions = [];
        
        if (json.rows) {
          json.rows.forEach(row => {
            queries.push(row.keys[0]);
            positions.push(row.position);
          });
        }

        var queriesString = queries.join(', ');
        var positionsString = positions.join(', ');
        
        // Escribir los datos en la hoja kw_by_url
        activeSheet.getRange(row, 1).setValue(url); // URL
        activeSheet.getRange(row, 2).setValue(periodLabel); // Periodo
        activeSheet.getRange(row, 3).setValue(queriesString); // Queries
        activeSheet.getRange(row, 4).setValue(positionsString); // Posiciones
        
        row++; // Avanzar a la siguiente fila
      }
    });
    
    currentDate.setMonth(currentDate.getMonth() + 1);
  }
  
  ui.alert('Data by month and URL has been added to the kw_by_url sheet.');
}