Cómo editar los títulos SEO de toda tu web desde Google Sheets sin abrir WordPress

<?php
/**
 * Plugin Name: Mi REST Meta
 * Description: Expone los campos de Yoast SEO en la API REST de WordPress.
 * Version: 1.0.0
 */

if (!defined('ABSPATH')) exit;

add_action('init', function () {
  // Añade aquí los tipos de contenido que quieres sincronizar.
  // 'product' es para WooCommerce. Elimínalo si no tienes tienda.
  $post_types = ['post', 'page', 'product'];

  $yoast_keys = [
    '_yoast_wpseo_title',
    '_yoast_wpseo_metadesc',
  ];

  foreach ($post_types as $pt) {
    foreach ($yoast_keys as $key) {
      register_post_meta($pt, $key, [
        'type'              => 'string',
        'single'            => true,
        'sanitize_callback' => 'sanitize_text_field',
        'auth_callback'     => function () {
          return current_user_can('edit_posts');
        },
        'show_in_rest'      => true,
      ]);
    }
  }
});

/* ═══════════════════════════
   CONFIG — Solo cambia estas 4 líneas
   ═══════════════════════════ */
const CONFIG = {
  wpBaseUrl: 'https://tuweb.com',          // ← Tu URL sin slash final
  wpUser: 'TU_USUARIO_WP',              // ← Tu nombre de usuario de WordPress
  wpAppPassword: 'xxxx xxxx xxxx xxxx xxxx xxxx', // ← La Application Password del paso 2

  sheets: [
    { name: 'Posts',     postType: 'post',    endpoint: '/wp-json/wp/v2/posts' },
    { name: 'Pages',     postType: 'page',    endpoint: '/wp-json/wp/v2/pages' },
    { name: 'Portfolio', postType: 'mi_cpt',  endpoint: '/wp-json/wp/v2/mi_cpt' }, // ← Cambia mi_cpt por el slug de tu CPT
    { name: 'WooCommerce', postType: 'product', endpoint: '/wp-json/wp/v2/product' },
  ],

  requiredHeaders: ['Actualizar', 'ID', 'Post Type', 'Title', '_yoast_wpseo_title', '_yoast_wpseo_metadesc'],
  batchSize: 15,
  retryCount: 3,
  retryBaseSleepMs: 800,
  hashFields: ['Title', 'Slug', 'Status', '_yoast_wpseo_title', '_yoast_wpseo_metadesc'],
};

/* ═══════════════════════════
   MENÚ
   ═══════════════════════════ */
function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('WordPress Sync')
    .addItem('Push selected (Actualizar marcado)', 'syncPushSelected')
    .addItem('Push changed (solo cambios reales)', 'syncPushChanged')
    .addToUi();
}

/* ═══════════════════════════
   HELPERS
   ═══════════════════════════ */
function basicAuthHeader_() {
  const token = Utilities.base64Encode(`${CONFIG.wpUser}:${CONFIG.wpAppPassword}`);
  return { Authorization: `Basic ${token}` };
}

function getSheetByName_(name) {
  const sh = SpreadsheetApp.getActive().getSheetByName(name);
  if (!sh) throw new Error(`No existe la pestaña: ${name}`);
  return sh;
}

function getHeaderMap_(sh) {
  const headers = sh.getRange(1, 1, 1, sh.getLastColumn()).getValues()[0].map(String);
  const map = {};
  headers.forEach((h, i) => map[h.trim()] = i);
  return { headers, map };
}

function ensureHeaders_(headers) {
  CONFIG.requiredHeaders.forEach(h => {
    if (!headers.includes(h)) throw new Error(`Falta la columna: ${h}`);
  });
}

function getCell_(row, map, key) {
  const idx = map[key];
  return idx !== undefined ? row[idx] : '';
}

function setCell_(row, map, key, value) {
  const idx = map[key];
  if (idx !== undefined) row[idx] = value;
}

function normalize_(v) {
  if (v === null || v === undefined) return '';
  if (typeof v === 'boolean') return v ? 'true' : 'false';
  return String(v).trim();
}

function computeHash_(row, map) {
  const raw = CONFIG.hashFields.map(f => normalize_(getCell_(row, map, f))).join('||');
  const digest = Utilities.computeDigest(Utilities.DigestAlgorithm.SHA_256, raw, Utilities.Charset.UTF_8);
  return digest.map(b => ('0' + (b & 0xFF).toString(16)).slice(-2)).join('');
}

function buildPayload_(row, map) {
  const payload = {};
  const title  = normalize_(getCell_(row, map, 'Title'));
  const slug   = normalize_(getCell_(row, map, 'Slug'));
  const status = normalize_(getCell_(row, map, 'Status'));
  const yTitle = normalize_(getCell_(row, map, '_yoast_wpseo_title'));
  const yDesc  = normalize_(getCell_(row, map, '_yoast_wpseo_metadesc'));

  if (title)  payload.title  = title;
  if (slug)   payload.slug   = slug;
  if (status) payload.status = status;

  payload.meta = {};
  if (yTitle) payload.meta._yoast_wpseo_title    = yTitle;
  if (yDesc)  payload.meta._yoast_wpseo_metadesc  = yDesc;
  if (!Object.keys(payload.meta).length) delete payload.meta;

  return payload;
}

function wpRequest_(url, body) {
  const options = {
    method: 'post',
    headers: { ...basicAuthHeader_(), 'Content-Type': 'application/json' },
    payload: JSON.stringify(body),
    muteHttpExceptions: true,
  };
  let lastErr = null;
  for (let i = 0; i < CONFIG.retryCount; i++) {
    try {
      const r = UrlFetchApp.fetch(url, options);
      const code = r.getResponseCode();
      if (code >= 200 && code < 300) return { ok: true, code };
      if (code === 429 || code >= 500) {
        Utilities.sleep(CONFIG.retryBaseSleepMs * Math.pow(2, i));
        lastErr = `HTTP ${code}`;
        continue;
      }
      return { ok: false, code, error: `HTTP ${code}: ${r.getContentText().slice(0,300)}` };
    } catch(e) {
      lastErr = e.message;
      Utilities.sleep(CONFIG.retryBaseSleepMs * Math.pow(2, i));
    }
  }
  return { ok: false, code: 0, error: String(lastErr) };
}

/* ═══════════════════════════
   SYNC
   ═══════════════════════════ */
function syncPushSelected() { runSync_('selected'); }
function syncPushChanged()  { runSync_('changed');  }

function runSync_(mode) {
  const now = new Date().toISOString();

  CONFIG.sheets.forEach(cfg => {
    const sh = getSheetByName_(cfg.name);
    if (sh.getLastRow() < 2) return;

    const { headers, map } = getHeaderMap_(sh);
    ensureHeaders_(headers);

    const range  = sh.getRange(2, 1, sh.getLastRow() - 1, sh.getLastColumn());
    const values = range.getValues();

    const items = values
      .map((row, i) => ({ row, i, id: normalize_(getCell_(row, map, 'ID')), hash: computeHash_(row, map) }))
      .filter(({ row, id, hash }) => {
        if (!id) return false;
        if (normalize_(getCell_(row, map, 'Post Type')) !== cfg.postType) return false;
        if (mode === 'selected') return getCell_(row, map, 'Actualizar') === true;
        return hash !== normalize_(getCell_(row, map, 'last_synced_hash'));
      });

    items.forEach(({ row, i, id, hash }) => {
      const url = `${CONFIG.wpBaseUrl}${cfg.endpoint}/${id}`;
      const res = wpRequest_(url, buildPayload_(row, map));

      if (res.ok) {
        setCell_(row, map, 'last_synced_at',   now);
        setCell_(row, map, 'last_synced_hash', hash);
        setCell_(row, map, 'sync_result',      `OK ${res.code}`);
        setCell_(row, map, 'sync_error',       '');
        setCell_(row, map, 'Actualizar',       false);
      } else {
        setCell_(row, map, 'sync_result', `ERROR ${res.code}`);
        setCell_(row, map, 'sync_error',  res.error.slice(0, 500));
      }
    });

    range.setValues(values);
    SpreadsheetApp.getActive().toast(`${cfg.name}: ${items.length} filas procesadas`, 'WordPress Sync', 5);
  });
}