1

I have a sheet that I want to share with various people. The formulas I use are input through Apps Script as they need to be changed. I separate the various parts by commas. For example,

function example(){sheet.getRange(1, 1).setFormula("=SUM(10, 20)")}

Some of the people I share with have settings to use semi-colons. For example,

function example2(){sheet.getRange(1, 1).setFormula("=SUM(10; 20)")}

They get an #ERROR for all my formulas. I did some research but don't see a general solution for when they are input by apps script. I would have thought Google would translate the formulas but it doesn't seem so when they are input with range.setformula.

I found this, but it just seems to help figure out how to change it. I want it to change automatically or somehow function for anyone who opens it.

Luke
  • 183
  • 9

1 Answers1

2

Using United States as Locale, Google Sheets supports SUM(10, 20) and SUM(10; 20) but using a Locale that uses comma as decimal separator, i.e. Argentina, doesn't, so what about this "fix"

instead of using SUM(10, 20) use SUM(10; 20)

in other words, use semicolon always as the formula parameter separator.

Related

Rubén
  • 34,714
  • 9
  • 70
  • 166
  • With a UK Locale, the ; is converted to a , after pressing Enter..... – Metric Rat Nov 10 '20 at 23:12
  • my point is that even by typing in the formula with semi-colons, google sheets then converts them to commas. – Metric Rat Nov 11 '20 at 21:22
  • @MetricRat This question is about using Apps Script, so when I said use semicolon always as the formula parameter separator it's implicit that this refers to scripts, not to writing formulas on the Google Sheets UI. – Rubén Nov 11 '20 at 21:25