CODEWITHSUNDEEP
google-sheetssyntaxgoogle-sheets-formulagoogle-sheets-query
Soraru5656
Soraru5656

Reputation: 55

What are the characters which mark the beginning of a Google Sheet Formula?

I am trying to find out characters that marks the beginning of a formula in Google Sheets (or Excel) in order to filter out characters in order to prevent Command Injections (e.g. =IMPORTDATA()).

The only ones that I know are '=' (equal sign) and '+' (plus sign) characters.

I tried searching it on the official websites but to no luck. Does anyone know other characters that might work besides those?

Upvotes: 1

Views: 255

Answers (2)

Rafa Guillermo
Rafa Guillermo

Reputation: 15377

Answer:

Google Sheets Formulae can start with either a = or +. These are the only two.

More Information:

There's no official documentation for this (and actually the support page only mentions the use of =), but you can test it yourself. Run through each ASCII character and use it to prefix a formula using setFormula():

function myFunction() {  
  for(var i = 32; i < 127; i++) {
    var c = String.fromCharCode(i);
    SpreadsheetApp.getActiveSpreadsheet().getSheets()[0]
      .getRange(i - 31, 1)
      .setFormula(c + "SUM(2+3)");
  }
}

After running, you get a column full of #ERROR!, #NAME?, and #N/A errors, and four cells which have been evaluated.

The cells which get evaluated to formulae correspond to the ASCII symbols =, +, - and (space).

From here we can then test directly the four characters by entering them manually:

  • +SUM(2+3) evaluates to 5, so + is a valid prefix.
  • =SUM(2+3) evaluates to 5, so = is also valid prefix.
  • -SUM(2+3) stays as a string so + is not a valid prefix.
  • SUM(2+3) does not get evaluated, and so can not be used either.

Side note: According to Microsoft's documentation on Excel:

Note: Formulas in Excel always begin with the equal sign.

References:

Upvotes: 1

player0
player0

Reputation: 1

= / + ...well those are the only ones, although only the equal sign is used as a marker of formula function.

both signs can be escaped by ' character

there is no official Google documentation on this topic - as always

Upvotes: 1

Related Questions