Top 10 astuces pour Google Sheets

Tout le monde a déjà essayé d’utiliser les applications de Google Drive pour des soins personnels, mais l’avènement des solutions d’entreprise Google nous a fait découvrir Google Docs et Google Sheets. Moins onéreux qu’un pack Office et surtout ouvert à la collaboration, ces applications Google sont de plus en plus utilisées au quotidien. Après une utilisation intensive de Google Sheets au travail, je vous propose un top 10 astuces pour Google Sheets qui va faciliter votre quotidien.

google sheets

Filtre vs Vue filtrée

Google a récemment lancé la fonctionnalité vue filtrée qui gomme les problèmes régulièrement rencontrés avec les filtres classiques. Pourquoi et dans quel cadre utiliser les vues filtrées :

  • Contrairement aux filtres, la vue filtrée peut être utilisée par les internautes en mode lecture seule.
  • Si plusieurs utilisateurs parcourent la feuille de calcul Google Sheets, le filtre utilisé sera appliqué pour tout le monde. La vue filtrée est personnelle et n’aura aucun impact sur les autres utilisateurs.
  • Il est possible de créer plusieurs vues filtrées et de les dupliquer.

Voici une animation montrant comment mettre en place une vue filtrée :

vue-filtree

 

Liste des fonctions

A l’instar de Microsoft Office, vous avez un système d’autocomplétion vous aidant à trouver la bonne formule dès lors que vous saisissez un simple “=” dans votre cellule. Mais l’autocomplétion a ses limites si vous ne connaissez pas les formulaires disponibles et leur fonctionnement. Vous trouverez ici la liste de l’ensemble des fonctions, avec un descriptif et une aide pour les mettre en place.

 

Importer les données d’un autre document Google Sheets

Lorsqu’on tient un référentiel à jour sur un document qui doit être utilisé ailleurs, ou tout simplement si l’on ne souhaite pas partager le document entier à certains collaborateurs, la fonction “importrange” va vous sauver la vie.

spreadsheet_key doit être remplacé par le lien de votre feuille de calcul où se situent les données à récupérer.
range_string doit être remplacé par l’onglet ou les cellules où se situent les données à récupérer.

Ce qui nous donne l’exemple ci-dessous (données factices)

Malheureusement, si vous tenez à jour les données sur votre document initial, les modifications ne seront pas reportées sur les autres feuilles de calcul. Pour cela, nous allons utiliser la fonction now() :

 

Historique des modifications

Une mauvaise manipulation a été effectuée, impossible de savoir qui en est le responsable et quand cela s’est produit ? Et si ! Il faut cliquer sur le lien “Dernières modifications” :
historique-google sheets

 

Figer les lignes et colonnes

Cette astuce n’est pas bien compliquée à trouver, mais ô combien utile. Impossible de passer à côté :

figer les lignes - google sheets

 

Gestion des dates

Contrairement à Microsoft Office, Google Sheets nous offre de nombreuses fonctionnalités relatives à la gestion des dates.

Décaler une date de plusieurs mois

Vous avez besoin de connaître la date d’une prochaine échéance se situant x mois dans le futur ou dans le passé ?
Dans l’exemple ci-dessous, nous ajoutons 3 mois à la date du 6 décembre 2015. Il est possible de mettre une valeur négative pour obtenir une date passée.

Calculer le nombre de jours / mois / années entre deux dates

Il est toujours compliqué d’aller chercher le nombre de jours séparant deux dates. Plus maintenant, voici trois exemples pour faciliter les calculs :

 

Validation des données

Pour éviter que les utilisateurs de votre document saisissent des informations identiques, mais avec plusieurs variations, nous allons utiliser la validation des données.
Pour cela, nous utiliserons généralement un onglet de calcul dans lequel nous allons lister l’ensemble des données possibles. Une fois fait, vous pouvez saisir les cellules qui doivent répondre à la validation des données >> clic droit >> “Validation des données”

Plusieurs options de configuration :

  1. Plages de cellules. Là où doit s’appliquer la validation des données.
  2. Critères. Sélectionner le critère de validation des données.
  3. Afficher un avertissement si les données ne répondent pas à la validation ou les refuser
  4. Afficher ou non une liste déroulante lors de la saisie des données

validation des données - google sheets

 

Compte le nombre d’éléments remplissant x conditions

Vous avez besoin de compter le nombre de lignes remplissant des conditions bien précises en fin de tableau ? Utilisez la fonction ci-dessous :

 

Cumuler des valeurs remplissant x conditions

Sur la même base que l’exemple ci-avant, il est possible de cumuler des valeurs si les champs de la ligne remplissent une ou plusieurs conditions :

 

Rechercher sur une ligne

Vous avez un tableau servant de référentiel, votre but est de créer un nouvel onglet dans lequel vous avez quelques valeurs sur une ligne et vous souhaitez récupérer les valeurs associées dans votre référentiel. Il va falloir passer par la fonction recherche :

Dans cet exemple, vous avez d’avoir une plage de recherche étendue sur plusieurs colonnes. L’index va permettre de cibler la colonne qui vous intéresse.

Rémi on twitterRémi on linkedin
Rémi
Chargé de projets web de profession, je partage à travers ce blog mes expériences, recherches et découvertes dans la création de projet web.
  • http://agb83-photo.com agb83

    Bonjour Rémi
    J’utilise un script sur une feuille google Sheets.
    function sendEmails() {
    var sheet = SpreadsheetApp.getActiveSheet();
    var startRow = 1; // First row of data to process
    var numRows = 24; // Number of rows to process
    // Fetch the range of cells A2:B3
    var dataRange = sheet.getRange(startRow, 1, numRows, 24)
    // Fetch values for each row in the Range.
    var data = dataRange.getValues();
    for (i in data) {
    var row = data[i];
    var prenom = row[8]; //colonne 8 pour le prénom
    var replyTo = ” 83@free.fr “;
    var Expediteur = ” 83@free.fr “;
    var Email = row[13]; //colonne 13 pour l’adresse mail
    var message = “Merci, ” + prenom + ” nous avons bien reçu votre formulaire.Vous avez 10 jours pour nous adresser votre dossier que nous attendons maintenant avec impatience. Nous accuserons de sa réception. Nous vous souhaitons bonne chance ! Le Président Alain GB objectif.photo83@free.fr“;
    var subject = “Confirmation de votre inscription”;
    MailApp.sendEmail(Email, replyTo, subject, message);
    }
    }

    Problèmes.
    1-L’envoi des mails se fait correctement mais à chaque fois qu’il y a une nouvelle entrée, le mail part pour toutes les lignes.
    Je modifie var startRow = 1, manuellement avec la prochaine ligne vide, pour que l’envoi ne se fasse que sur la dernière inscription.
    2-Le prénom ne s’intègre pas dans la mail mais c’est un problème secondaire.

    Si vous voyez comment résoudre ces petits soucis, ça me dépannerait bien.
    Il y a peu de documentation en français sur les scripts Google.
    Merci de votre éventuelle aide

  • Eric Clair

    bonjour
    comment figer le nombre en bas ajouter des lignes a 50 par exemple au lieu de 1000 car je me retrouve avec des tableaux trop gros ‘les users ne font pas attention’
    merci de votre aide

    • rbastien

      Bonjour,
      Il semble impossible de modifier le paramètre du nombre de lignes dans la création de nouveaux tableaux. A priori, la seule possibilité offerte, est de créer un tableau qui vous servira de template. Il faudra ensuite demander à chacun de créer une copie de votre tableau.

  • Roger Savry

    Bonjour, Je n’arrive pas à changer l’onglet qui s’ouvre par défaut dans google sheets. Avez-vous une astuce :-) ?

    • rbastien

      Bonjour, la solution se trouve dans l’URL.
      Voici les liens de base : https://docs.google.com/spreadsheets/d/clé_unique_du_document/edit?gid=clé_unique_de_la_feuille

      A chaque fois que vous changez d’onglet, la valeur après “gid=” se modifie automatiquement. C’est grâce à cela que vous pourrez forcer les utilisateurs à ouvrir un onglet plutôt qu’un autre 😉

    • Service COMPTA

      C’est une question dont je cherche la réponse depuis un moment. Merci d’avance

    • Laurent Duperrin – Amitiés Soc

      Bonjour, on peut simplement mettre l’onglet en première position, tout à gauche. Google Sheets ouvre par défaut sur le premier onglet

    • Guillaume BARJOU (Lomic)

      Ben justement pas chez moi, j’ai un tableau avec un onglet par an, j’aimerais bien qu’il arrête de m’ouvrir toujours 2015 au lieu de 2017, 2017 étant le 1er onglet, et 2015 au milieu avant 2014 et 2013.

  • Dams

    Bonjour , je souhaiterai utiliser la fonctionnalité IMPORTRANGE proposé dans vos illustrations de façon à pouvoir récupérer une plage de donnée d’un fichier googlesheet sur mon fichier excel non partagé.

    Sauriez-vous comment procéder ? la formule IMPORTRANGE n’existe pas sur excel il me semble…

    • rbastien

      Il n’est pas possible, à ma connaissance, de faire des liaisons entre Excel et Google Spreadsheet

  • Ian M.

    J’aimerais utiliser la fonction importrange puis pouvoir mettre la liste importée en ordre alphabétique…est ce possible?

    • rbastien

      Il est possible de combiner les fonctions SORT() et IMPORTRANGE() de la manière suivante :
      =SORT(IMPORTRANGE(spreadsheet_key; range_string);1;TRUE)

    • Ian M.

      Un grand MERCI!!!

  • A.bnv

    Bonjour,
    Je souhaite utiliser la fonction importrange en trouvant une solution pour “verrouiller les valeurs”.
    Par exemple, si dans le document dans lequel je prélève les valeurs, je rajoute une ligne, j’aimerais qu’il n’y ai pas d’impact sur mon second document, qu’il garde la valeur et non la cellule. Y a t il un moyen pour arriver à cette fin ?

    D’avance,merci

  • Gilles

    Bonjour,
    Est-il possible de nommer une cellule du nom du fichier ?
    Merci

  • Michel Staïner

    Bonjour,
    J’ai nommé une plage de données dans une feuille et je voudrais aider des visiteurs inexpérimentés à la sélectionner avant de créer une vue filtrée.
    Y a-t-il moyen d’imposer l’affichage du “panneau” des plages nommées par défaut à l’ouverture du fichier ? Voire de sélectionner la plage par défaut ?
    Peut-être via une option dans le lien de partage, un peu comme pour l’accès direct à une page donnée d’un pdf…
    Merci d’avance pour vos idées !

  • Ando Rabefaniraka

    Bonjour,
    Je souhaite comparer deux colonnes de noms pour identifier les noms qui sont présents sur les deux listes. J’aimerais pouvoir le faire sur un google sheets. Un coup de pouce me sauverait la vie!!! Par avance merci!