Modifying formula symbols in Google Sheets using a script

Other Questions Google Sheets
1

Hey everyone! I’m trying to figure out how to write a script for Google Sheets that will change the equals sign at the start of formulas to a caret symbol. I know this will mess up all the formulas, but that’s actually what I want to do.

Here’s what I mean:

If there’s a formula in cell A2 that looks like this:

=CONCATENATE("Hi", " there")

I want the script to change it to:

^CONCATENATE("Hi", " there")

I know I could use the Find and Replace feature, but I specifically need a script for this. Any ideas on how to make this work? Thanks in advance for your help!

2

I’ve encountered this requirement before in a project where we needed to audit formula usage across multiple sheets. Here’s a script that should accomplish what you’re looking for:

function modifyFormulaSymbols() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getDataRange();
  var values = range.getValues();

  for (var i = 0; i < values.length; i++) {
    for (var j = 0; j < values[i].length; j++) {
      var cell = values[i][j];
      if (typeof cell === 'string' && cell.charAt(0) === '=') {
        values[i][j] = '^' + cell.substring(1);
      }
    }
  }

  range.setValues(values);
}

This script checks each cell in the active sheet, identifies formulas starting with ‘=’, and replaces them with ‘^’. Remember to create a backup of your sheet before running the script, as it will render all formulas non-functional.

3

hey nova, i’ve got a quick solution for ya. try this script:

function swapEquals() {
var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getDataRange();
var formulas = range.getFormulas();

for (var i in formulas) {
for (var j in formulas[i]) {
if (formulas[i][j].startsWith(‘=’)) {
formulas[i][j] = ‘^’ + formulas[i][j].substr(1);
}
}
}

range.setFormulas(formulas);
}

it’ll do what u need. just remember to backup ur sheet first!

4

I’ve actually had to do something similar for a project where we needed to temporarily disable formulas without losing them. Here’s a script that should do the trick:

function replaceEqualsWithCaret() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getDataRange();
  var formulas = range.getFormulas();
  
  for (var i = 0; i < formulas.length; i++) {
    for (var j = 0; j < formulas[i].length; j++) {
      if (formulas[i][j].charAt(0) === '=') {
        formulas[i][j] = '^' + formulas[i][j].slice(1);
      }
    }
  }
  
  range.setFormulas(formulas);
}

This script loops through all cells, checks if they start with ‘=’, and replaces it with ‘^’. Just be careful using this - it will break all your formulas as you mentioned. Make sure to have a backup of your sheet before running it!