Astuces Google Sheets

Google Sheets propose des fonctionnalités très utiles quand il s'agit d'éditer des documents en ligne et de collaborer. Quelques astuces permettent d'optimiser encore davantage ses formules et de disposer de documents vraiment complets.

Contenu

Ne pas interpréter le contenu d'une cellule comme une formule

Par défaut, toute cellule commençant par un symbole mathématique (=, +, -, etc.) est interprété comme une formule par Google Sheets. Pour éviter ce comportement, il suffit de faire commencer le contenu de la cellule par le caractère ' :

'== Texte ==

Formules

Identifiant automatique par ligne

=IF(NOT(ISBLANK(B2)); ROW()-1; "")

En français :

=SI(NON(ESTVIDE(B2)); LIGNE()-1; "")

Opération si une autre cellule n'est pas vide

=IF(NOT(ISBLANK(A2)); A2*1.2; '-')

En français :

=SI(NON(ESTVIDE(A2)); A2*1.2; '-')

Somme conditionnelle

=SUMIF(plage_a_tester; condition; plage_a_additionner)

En français :

=SOMME.SI(plage_a_tester; condition; plage_a_additionner)

Si plage à tester non vide :

=SUMIF(plage_a_tester; "<>"; plage_a_additionner)

En français :

=SOMME.SI(plage_a_tester; "<>"; plage_a_additionner)

Afficher le résultat d'une requête issue de plusieurs feuilles

=QUERY({Divers!A2:M; 'Commande Amazon'!A2:M}; "select * where (Col8 = 'Commandé' or Col8 = 'Expédié') order by Col8 desc, Col2")

Barre de progression dans une cellule

=IMAGE("http://url.image.de/1px.png", 4, <hauteur_cellule>, <pourcentage> * <largeur_cellule>)

Date du jour dans un format donné par une formule

=TEXT(TODAY(); "YYYY-MM-DD")

En français :

=TEXTE(MAINTENANT(); "YYYY-MM-DD")

Mise en forme conditionnelle

Échapper le caractère "?"

~?

Expression régulière

En utilisant Format cells if... Custom formula is :

=regexmatch(A1, "1904|1905")

ou, en utilisant le contenu d'une cellule d'une autre feuille :

=regexmatch(A1, INDIRECT("Sheet2!B3"))

Scripts

Ajouter un menu

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  
  var menuEntries = [
    {name: "Trier les projets", functionName: "sortProjects"}
  ];
  ss.addMenu("— Scripts —", menuEntries);
}

ou :

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('— Scripts —')
    .addItem('Trier les projet', 'sortProjects')
    .addSubMenu(SpreadsheetApp.getUi().createMenu('Marquer comme …')
      .addItem('à jour', 'markUpdated'))
    .addToUi();
}

Trier une zone

var SORTING = [
  3 /* C ascending */,
  2 /* B ASC */,
  {column: 1 , ascending: false} /* A DESC */
];


// https://developers.google.com/apps-script/reference/spreadsheet/range?csw=1#sortsortspecobj
function sortProjects() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var range = sheet.getRange("A2:G" + sheet.getMaxRows());
  range.sort(SORTING);
}

// Plus complexe :

var SORTINGS = {
  "Devices" : {range: "A2:J%MAX_ROWS%", sort: [ "B", "G:DESC" ]}
};

/**
* Convert ["A", "B:DESC"] array to [1, {column: 2, ascending: false}] array.
*/
function sortingToGAppsSorting(sorting) {
  var gAppsSorting = [];
  if (!Array.isArray(sorting)) {
    sorting = [ sorting ];
  }
  
  for (var i = 0, n = sorting.length; i < n; i++) {
    var sort = sorting[i];
    var splitSort = sort.split(":");
    var col = splitSort[0].charCodeAt(0) - 64;
    var asc = splitSort[1] && splitSort[1] === "DESC" ? false : true;
    Logger.log(sort + " => " + col + ", asc: " + asc);
    gAppsSorting.push({column: col, ascending: asc});
  }
  
  return gAppsSorting;
}


// https://developers.google.com/apps-script/reference/spreadsheet/range?csw=1#sortsortspecobj
function sortSheet() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  
  var sorting = SORTINGS[sheet.getName()];
  
  // If no sorting options found returns.
  if (!sorting) return;
  
  var rangeA2 = sorting.range.replace('%MAX_ROWS%', sheet.getMaxRows()).replace('%MAX_COLS%', sheet.getMaxColumns());
  
  var range = sheet.getRange(rangeA2);
  range.sort(sortingToGAppsSorting(sorting.sort));
}

Lettre correspondant à un numéro de colonne

// Conversion de 27 en "AA", etc.
// Only works for "A" to "ZZ".
function colIndexToANotation(colIndex) {
  var A1 = String.fromCharCode(colIndex + 64);
  
  if (colIndex > 26) {
    var letter1 = Math.floor((colIndex - 1) / 26) + 1;
    var letter2 = ((colIndex - 1) % 26) + 1;
    
    A1 = String.fromCharCode(letter1 + 64) + String.fromCharCode(letter2 + 64);
  }
  
  return A1;
}

Index correspondant à une colonne

// Conversion de "AA" en 27, etc.
function colANotationToIndex(colLetter) {
  var index = 0;
  
  for (var i = 0, n = colLetter.length, j = n-1; i < n; i++, j--) {
    var char = colLetter.charCodeAt(j) - 64;
    index += Math.pow(26, i) * char;
  }
  
  return index;
}