Home / Questions / 115264
Accepted
Goerman
Asked: 2020-11-07 13:48:19 +0800 CST 2020-11-07 13:48:19 +0800 CST

How to extract formula to text in google spreadsheet?

  • 772

In the cell A1I have the formula =25/26, to see it as text I copy and paste it B1adding a quote at the beginning '=25/26.

With which function can I do this automatically?

enter image description here

google-hoja-de-cálculo

2 Answers

  1. Best Answer

    Currently Google Spreadsheets don't have a built-in function to get the formula from another cell but you can use Google Apps Script to create a custom function that will do it.

    Example:

    /**
 * Devuelve la fórmula de la referencia especificada
 *
 * @param {string} reference Referencia en formato "A1"
 * @customfunction
 */
function getFormula(reference) {
  return SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
                       .getRange(reference).getFormula();
}

    Reference

    • 3

  2. Responsive adaptation so that instead of working with it =getFormula("A1")works with =getFormula('Hoja 1'!A1)in such a way that it can be applied by dragging and dropping to multiple cells.

    /**
 * Devuelve la fórmula de la referencia especificada
 *
 * @param {'Hoja 1'!A1} reference Referencia en 'Nombre Hoja'!Celda
 * @customfunction
 */
function getFormula(reference) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var formulaCelda1 = sheet.getActiveRange().getFormula();
  var celda2 = formulaCelda1.match(/=\w+\((.*)!(.*)\)/i)[2];
  return SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(celda2).getFormula();
}

    See reference example.

    • 0