Sustituciones: Reemplazar texto con. 

🗪 Para reemplazar texto con otro texto. He creado una tabla en una hoja de Google Spreadsheet. Combinando fórmulas y Google Apps Script. Es una forma de intentar simular la opción nativa sustituciones de Google Docs ubicado en :

Ir a barra de menús »🗦🖰 » Herramientas »🗦🖰 » Preferencias » 🗔 Cuadro diálogo  »  🖰  » Sustituciones

Se utiliza lo menos posible código de guión. La Lista de funciones de Hojas de cálculo de Google tiene seguridad en el funcionamiento.

Se muestra un menú denominado «🛠️ Tools»  Dos submenús La primera vez solicitará permisos de Authorization Scopes. En caso de utilizar las sustituciones manuales.

• Submenú «👤Manual substitutions» Sustituye el texto de la celda actual..

• Submenú «🔐InvalidateAuthorization» Invalida permisos otorgados. Similar a Google Account: Security Review: Remove access.

🎩 Truco:

Utiliza el procesador de texto App Google Docs. Añade tus preferencias en sustituciones. Copia el texto y después pegar en App Google Spreadsheet. 

💭 Recuerda:

La opción nativa deshacer. Su funcionamiento no es lo esperado al utilizar la función onEdit(e) si está marca « ✅Automatic substitution ».  Hay que ejecutarla varias veces.

Ir a barra de iconos »🗦🖰 » ⮌ Deshacer

Atajo del teclado »  🖮 »  Ctrl + Z

⚠️ Aviso(s):

El procedimiento es lento al utilizar el disparador y hay varias llamadas para establecer valores en la hoja. Necesita su tiempo. Puede haber errores inesperados. Es complicado controlarlos todos.

🔬 Investiga:

Modifica las fórmulas y código Apps Script para personalizarlo.

💡 Propuesta fórmula(s):

Hoja: Substitutions!F1

=LET(getTestError;INDIRECT(E1);getTextOriginal;B2;getTextCopy;B3;

IFS(ISFORMULA(getTestError);"❌Formula";ISDATE(getTestError);"❌Date";ISNUMBER(getTestError);"❌Number";

ISBLANK(getTestError);"❌Blank";ISLOGICAL(getTestError);"❌Logical";ISERROR(getTestError);"❌Error";

ISEMAIL(getTestError);"❌Email";ISURL(getTestError);"❌Url";getTextOriginal=getTextCopy;"❌Original=Copy";ISTEXT(getTestError);GETVALUECELLTEXT();

TRUE();"❌Other"))

Hoja: Substitutions!B3

=LET(getTextOriginal;B2;getRangeremplace;B5:B;getCellLastRemplace;B5;getColumnCheckBox;"A";getColumnRemplace;"B"; getColumWith;"C";

REDUCE(getTextOriginal; SEQUENCE(COUNTA(getRangeremplace);1;ROW(getCellLastRemplace)); LAMBDA(getText; getRow;

IF(INDIRECT(getColumnCheckBox&getRow);SUBSTITUTE(getText; INDIRECT(getColumnRemplace&getRow);INDIRECT(getColumWith&getRow));getText))))

💡 Propuesta Apps Script:

/**

 * Limits the script to only accessing the current sheet.

 * @OnlyCurrentDoc

 * @link https://developers.google.com/apps-script/guides/services/authorization?hl=419

*/

/** Object global. Sheet Substitutions */

const substitutions = {

  sheetname: 'Substitutions',

  a1NotationCheckBoxAutomaticSubstitutions: 'A1',

  a1NotationCellTexOriginal: 'B2',

  a1NotationCellTexCopy: 'B3',

  a1NotationCellEdited: 'E1',

  a1NotationCellTest: 'F1',

  valueCellText: '✅Test'

};

/** Array  global. Sheet name excluded of remplace.  */

var getSheetNameExclude = [substitutions.sheetname, 'Information', 'Información'];

function onOpen(e) {

  const classUi = SpreadsheetApp.getUi();

  /* Create menu and submenús. */

  classUi.createMenu('⚒️Tools')

    .addItem('👤Manual substitutions', 'manualSubstitutionsCurrenCell')

    .addItem('🔐Invalidate authorization', 'invalidateAuthorization')

    .addToUi();

} /*  End onOPen(e) */

function manualSubstitutionsCurrenCell() {

  try {

    /** Get the class Spreadsheet. */

    const classSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();

    /** Get the class sheet name substitutions. */

    const classSheetNameSubstitutions = classSpreadsheet.getSheetByName(substitutions.sheetname);

    /* Is not exist sheet. Exception error. */

    if (!classSheetNameSubstitutions) { throw objectError = { message: `Sheet name ${substitutions.sheetname} not exists` }; };

    /* Modify array global. Sheet name exclude of remplace. */

    getSheetNameExclude = [substitutions.sheetname];

    /** Get the class range of current cell. */

    const classRangeActive = classSpreadsheet.getCurrentCell();

    /* Set class range. Reference cell active in substitutions sheet. */

    classSheetNameSubstitutions.getRange(substitutions.a1NotationCellEdited).setValue(`${classSpreadsheet.getSheetName()}!${classRangeActive.getA1Notation()}`);

    /* Set value text cell active in substitutiones sheet. */

    classSheetNameSubstitutions.getRange(substitutions.a1NotationCellTexOriginal).setValue(classRangeActive.getValue());

    /* Set properties. Object event edit. */

    const e = {

      source: classSpreadsheet,

      range: classSpreadsheet.getRange(classSheetNameSubstitutions.getRange(substitutions.a1NotationCellEdited).getValue()),

      value: classSheetNameSubstitutions.getRange(substitutions.a1NotationCellTexOriginal).getValue(),

      automaticSubstitutions: true,

      classSheetNameSubstitutions: classSheetNameSubstitutions

    };

    /* Function call. */

    automaticSubstitutionsCurrenCell(e);

  }/* End try */

  catch (err) {

    showAlert(err);

  } /* End catch(err) */

}/* End  manualSubstitutionsCurrenCell() */

/**

 * The event handler triggered when editing the spreadsheet.

 * @param {event} e The onEdit event.

 * @link https://developers.google.com/apps-script/guides/triggers#onedite

 */

function onEdit(e) {

  /* Function call. */

  automaticSubstitutionsCurrenCell(e);

}

/**

 * Remplace original text with copy text.

 * Arguments established in substitution sheet

 * @param {event} e transferred from function onEdit.

 */

function automaticSubstitutionsCurrenCell(e) {

  try {

    /** Get the class Spreadsheet Active. */

    const classSpreadsheet = e.source;

    /** Get the class range active */

    const classRange = e.range

    /** Get the sheet name active. */

    const getSheetNameActive = classRange.getSheet().getName();

    /* if sheet name active Substitutions. Exit */

    if (getSheetNameActive === substitutions.sheetname) { return; };

    /** Get the class. Sheet name Substitutions. */

    const classSheetNameSubstitutions = e.classSheetNameSubstitutions || classSpreadsheet.getSheetByName(substitutions.sheetname);

    /* Is not exist sheet. Exception error */

    if (!classSheetNameSubstitutions) { throw objectError = { message: `Sheet name ${substitutions.sheetname} not exists` }; };

    /** Get the class range of the cell edited. */

    const classRangeActive = classRange.getCell(1, 1);

    /** Get the string A1 notation. Cell activated. */

    const getA1NotationActive = `${getSheetNameActive}!${classRangeActive.getA1Notation()}`

    /* set value Substitutions!E1 notation of current cell.*/

    classSheetNameSubstitutions.getRange(substitutions.a1NotationCellEdited).setValue(getA1NotationActive);

    /* Set the text original the sheet name Substitutions!B2 */

    classSheetNameSubstitutions.getRange(substitutions.a1NotationCellTexOriginal).setValue(e.value);

    /** Get the boolean. Checkbox automatic substitutions. */

    const isAutomaticSustitutions = e.automaticSubstitutions || classSheetNameSubstitutions.getRange(substitutions.a1NotationCheckBoxAutomaticSubstitutions).getValue();

    /* Automatic substitutions disabled go exit */

    if (!isAutomaticSustitutions) { return; };

    /* if not excluded sheet name  */

    if (!getSheetNameExclude.includes(getSheetNameActive)) {

      /* if text ❌Error. Go exit */

      if (classSheetNameSubstitutions.getRange(substitutions.a1NotationCellTest).getValue() !== substitutions.valueCellText) { return; };

      /* if is formula go exit */

      if (classRangeActive.getFormula()) { return; };

      /** Get value text copy. Is null string void. */

      const getTexCopy = classSheetNameSubstitutions.getRange(substitutions.a1NotationCellTexCopy).getValue() || 'void';

      /* if value no void */

      if (getTexCopy !== 'void') {

        /* set value text copy */

        classRangeActive.setValue(getTexCopy);

        /* send result to console */

        Logger.log(' Text original:  %s\n Text copy:      %s\n Text old:       %s\n Reference cell: %s', e.value, getTexCopy, e.oldValue, getA1NotationActive)

      } /* End if(getTexCopy) */

    } /* End if(getSheetNameExclude) */

  }/* End try */

  catch (err) {

    showAlert(err);

  } /* End catch(err) */

} /* End onEdit(e) */

/**

 * Invalidates the authorization.

 * @link https://developers.google.com/apps-script/reference/script/script-app#invalidateauth

 */

function invalidateAuthorization() {

  ScriptApp.invalidateAuth();

} /* End invalidateAuthorization() */

/**

 * show dialog box type alert.

 * @param {eventError} objectError The variable declare en try...catch...finally for message to display in the modal dialog box.

 */

function showAlert(objectError = { stack: 'undefined', message: 'undefined' }) {

  /** Get the string object error of stack or message. */

  let msg = objectError.stack ? objectError.stack.replace(/_/gi, ' ') : objectError.message;

  /* Show modal dialog box. */

  SpreadsheetApp.getUi().alert(`❌ ${msg}`);

  /* Statement type error for send console */

  throw msg;

} /* function showAlert() */

/** Respose ok. test error.

 * Formula and Apps Script.

 * @link https://developers.google.com/apps-script/guides/sheets/functions

 * @return {substitutions.valueCellText} string value test ok.

 * @customfunction

 */

function GETVALUECELLTEXT() {

  return substitutions.valueCellText;

}

📝 Propuesta detalle fórmula(s):

Hoja: Substitutions!F1

• Función LET() asigna nombre a celda E1. la cual su valor es automatizado para  insertar la referencia de celda editada mediante apps script. Con función INDIRECT() obtenemos el valor de la referencia de celda

• Función IFS() incorpora expresiones lógicas para testear posibles errores ISNUMBER(), ISDATE(), etc. Si todas las expresiones lógicas son falsas la expresión penúltima ISTEXT() si su valor es TRUE() muestra el mensaje « ✅Test » mediante una Custom functions. La última expresión es siempre TRUE() y muestra un mensaje ❌Other. Su tarea es actuar como condición predeterminada en caso de que todas los demás su valor lógico sea FALSE().

Hoja: Substitutions!B3

• Función LET() asigna nombres descriptivos para una mejor aclaración de las referencias de celdas y no repetir los intervalos.

• Función REDUCE() crea una matriz y retorna el resultado final si está activada la casilla de verificación en A1 que puede desactivar la sustituciones y si la fórmula anterior el test es correcto con AND(). El primer parámetro es el  inicial en hoja « Substitutions!B2 » Segundo parámetro creamos una secuencia numérica SEQUENCE() del número total de filas donde se encuentra las sustituciones y a partir de qué número fila empieza. La función auxiliar LAMBDA() recorre la matriz con un condicional. Si está activada cada reemplazar con. En la columna A. Entonces la función SUBSTITUTE() reemplaza el texto que coincida el la columna B por el nuevo texto en la columna C por mediación de INDIRECT() utilizando la secuencia de número creadas. Está a su vez acumula el nuevo texto sustituido con el nombre asignado « getText » Si no hay para reemplazar retorna el texto sin modificar.

📝 Propuesta detalle Apps Script:

Copiar ( 🖮 Ctrl + V ) código.gs » 🗦🖰 Ir a Menú » Extensiones » 🖰  » Apps Script » Pegar ( 🖮 Ctrl + C ) código.gs » 🖫 Guardar » ⭮ Actualizar hoja de cálculo de Google.

Declaramos const y var globales al inicio del código para mayor comodidad en caso de modificar su valor.

Function onOpen(e) en un Simple Triggers. Se ejecuta al abrir la hoja de cálculo y mediante Class Ui Method createMenu() generará un menú en la barra de menús.

Function personaliza « manualSubstitutionsCurrenCell() » esta es llamada desde el menú  y a su vez llama a la función « automaticSubstitutionsCurrenCell(e) »  modificando Event Object  para su ejecución. sustituyendo el texto de la celda activa. sin restricciones del nombre de la hoja.

Función onEdit(e) se ejecuta en editar una celda por ser un activador simple. La variable  « e » es un Event Object. Addquiere propiedades del evento por el propio sistema. A su vez llama a la función « automaticSubstitutionsCurrenCell(e) »

Función « automaticSubstitutionsCurrenCell(e) » hereda las propiedades del Event Object « e » . « e.source » Obtenemos la hoja de cálculo activa y las propiedades de Class Spreadsheet. « e.range » obtenemos la Class Range del rango editado. Obtenemos el nombre de la hoja activa Class Range Method getSheet(). Si la hoja editada es la de « Substitutions » objeto global declarado « substitutions.sheetname » Ejecuta un retorno y finaliza la función. Si la hoja « Substitutions » al declarar la variable mediante Class Spreadsheet Method getSheetByName() no existe entonces enviamos una excepción de error throw incluyendo mensaje en forma de Template literals. Class Range Method getCell() logramos obtener la primera celda del rango editado. Declaramos variable para la referencia de celda Class Range Method getA1Notations() para después establecer el valor Class Range Method setValue() el la hoja « Substitutions » objeto « e.value » obtenemos el valor de la celda editada y a su vez lo establecemos en la hoja de « Substitutions » «isAutomaticSustitutions » comprueba que esté activada la ✅casilla de verificación de « Substitutions!A1 » Comprobamos con un condicional if()...else() el Array() creado con los nombres de las hojas excluidas y retorna un valor lógico con Array Method includes(). Se comprueba el mensaje « ✅Test » objeto « substitutions.valueCellText » proporcionado en la fórmula de « Substitutions!F1 »  Si la celda editada es un fórmula Class Range Method getFormula() no ejecutar. En caso contrario modificar el texto original por el nuevo « getTexCopy ». Finalmente  Class Logger enviamos a la cónsola los resultados. try{} catch(err){} atrapamos los errores y los enviamos a otra función para mostrar una alerta.

Función personalizada « invalidateAuthorization() » Utiliza Class ScriptApp Method invalidateAuth() para retirar los permiso autorizado. Siempre que lo hayas concedido al utilizar el menú. La opción marcada « ✅ Automatic substitution » no necesita permisos.

Función personalizada « showAlert(objectError) » Utilizando Class Ui Method alert() para mostrar un mensaje tipo error. Cuadro diálogo modal y un excepción  throw  para enviar Logging.

Custom functions « GETVALUECELLTEXT()» utiliza JSDoc para mostrar ayuda al insertar en la celda. Retorna el valor « ✅Test » mediante el objeto global « substitutions.valueCellText ». Logrando tener el mismo valor en el código y en la hoja de cálculo.

📷 Resultado(s):

📺 Demo: