Tuenti, where I work, is a Spanish company, based in Madrid, with an English-language working environment.  One particular problem this causes is how to format dates, numbers, currencies, and spreadsheet functions.   We use Google Apps for everything – and within Google Apps, formatting conventions pose the biggest challenge in spreadsheet.  If some people are creating spreadsheets with Locale=Spain, and others with Locale=United States you have some serious problems: dates are entered erroneously, you must constantly switch how you type numbers/functions, and copy-and-paste creates multiple layers of issues.

My solution to reach a sane standard:

1. set Google Spreadsheet locale to UK (see Google Spreadsheets Help on Locales). This gives the EU date format convention (dd/mm/yyyy), the decimal point (period) as the decimal mark, and the comma to separate function arguments. Unfortunately, it creates the problem of having the default currency as GBP; buries the option to format in EUR several levels down in the UX, and completely removes option to format in EUR rounded. Therefore, you must

2. use a Google App Script menu to format values in EUR (€). Google App Script is immature and a bit buggy, but it allows you to easily add a custom menu to a spreadsheet, from which you can invoke a function that will apply a format to the “active” (selected by the cursor) in the spreadsheet.  This is exactly analogous to the UI of the native formatting functions Google has in the toolbar by default.

To accomplish this, I wrote a simple App Script to format cells as rounded euro values, following English conventions (eg “€1,000” rather than the Spain default of “1.000€”). It is available in the App Script Gallery (or will be, once Google approves it). To add it to your spreadsheet, follow Google’s instructions on Installing a Script from the Gallery.

While you can look at the code yourself from the gallery, I’ve also included it below in appendix 1.

Appendix I: The Code

Here’s the code of the script; it is pretty simple, but it’s about as much coding as I get to do these days. I’ve structured it to be extensible to add a lot of custom formats, using the syntax/semantics defined in appendix II.  Feel free to adapt it however you wish.

/*
 * format active range as EURO round, comma separator on thousands
 *
 * why? bc if you're using English in the spreadsheet, but
 * want to use euros as units Goog lacks a direct way to format it
 *
 * placement of currency sign is language dependent, not currency
 * dependent; a spreadsheet in english should have EURO-sign before
 * the number, not after.
 * http://en.wikipedia.org/wiki/Linguistic_issues_concerning_the_euro
 */
function formatEuroRound() {
  applyNumberFormat("€,###");
}

/**
 * applies a numberFormat to the active range
 */
function applyNumberFormat(formatString) {
  var range = SpreadsheetApp.getActiveRange();

  var format = [];
  for (var i=0; i < range.getHeight(); i++) {
    format[i] = [];
    for (var j=0; j < range.getWidth(); j++) {
      format[i][j] = formatString;
    }
  }

  range.setNumberFormats(format);
}

Appendix II: Number Formats

The Google App Script documentation of the Spreadsheets Service describes the setNumberFormat  method, but doesn’t provide any guidance on what the syntax/semantics for the formatString parameter is.  It appears that number formats in Google Spreadsheets follow some historical conventions they’ve inherited from Excel – and I presume other places.  A decent forum post directed me to a spreadsheet giving numerous examples of formatting strings.  You can look at that, in combination with the applyNumberFormat() function I gave above to create arbitrary formatting functions.