Introduction
Créer et structurer automatiquement des tables dans Google Sheets via Apps Script et l’API avancée Google Sheets peut grandement simplifier la gestion de vos données. Toutefois, la partie la plus délicate n’est pas l’appel batchUpdate lui-même, mais la découverte de tous les types de colonnes disponibles et de leur syntaxe exacte. Dans ce billet, nous revenons sur le chemin parcouru pour débusquer chaque type valide et vous proposons un script clé en main couvrant tous les cas.
Contexte & objectif
Nous souhaitions automatiser la création d’un onglet dédié dans un classeur Sheets, avec une ligne d’en-tête et plusieurs colonnes aux formats variés :
- nombres, pourcentages, devises
- textes, dates, heures, dates+heures
- menus déroulants (dropdown)
- cases à cocher (checkbox)
- puces intelligentes (smart chips) : contacts, fichiers, finance, lieux, notes
Pour ce faire, on utilise une unique requête addTable dans Sheets.Spreadsheets.batchUpdate(), en spécifiant à chaque colonne son columnType et, si nécessaire, une règle de validation.
Difficultés rencontrées
En cherchant dans la documentation officielle de l’API Sheets, section « Tables », nous trouvons une mention vague des « column types such as numeric, date, dropdown, smart chip, checkbox » .
De même, le centre d’aide de Sheets liste les types disponibles dans l’UI (Number, Percentage, Currency, Text, Date, Time, Date and time, Dropdown, Checkbox, Smart chips avec sous-types) .
Mais aucune de ces pages n’indique clairement la valeur de chaîne (columnType) à passer dans le JSON. Les erreurs telles que « Invalid value … “STAR” » ou « “NUMBER” » sont fréquentes tant que l’on n’a pas déniché exactement les identifiants acceptés par l’API.
Découverte des types de colonnes via l’API
Pour lever le doute, nous avons créé dans Google Sheets manuellement toutes les types de colonne et ensuite utilisé un petit script :
const response = Sheets.Spreadsheets.get(SPREADSHEET_ID, {
fields: 'sheets(properties(title),tables(tableId,range,columnProperties)'
});
// Puis Logger.log(response.sheets[…].tables[…].columnProperties);
Ce log a renvoyé la liste exacte des « columnType » utilisés par Google pour chaque colonne existante :
DOUBLE, PERCENT, CURRENCY, TEXT, DATE, DATE_TIME, TIME, DROPDOWN, BOOLEAN, PEOPLE_CHIP, FILES_CHIP, FINANCE_CHIP, PLACE_CHIP, RATINGS_CHIP
Script complet : création d’une nouvelle feuille + table
Voici un code Apps Script qui :
- Supprime l’onglet Table exemple s’il existe déjà,
- Crée un nouvel onglet,
- Exécute `addTable` avec tous les `columnType` listés ci-dessus.
function creerUneTableExemple() {
const classeur = SpreadsheetApp.getActiveSpreadsheet();
const SPREADSHEET_ID = classeur.getId();
const nomFeuille = 'Table exemple';
// Supprime l’onglet s’il existe déjà
const ancienOnglet = classeur.getSheetByName(nomFeuille);
if (ancienOnglet) {
classeur.deleteSheet(ancienOnglet);
}
// Crée la nouvelle feuille
const feuille = classeur.insertSheet(nomFeuille);
const feuilleID = feuille.getSheetId();
// Prépare la table
const tableId = 'tableExemple_' + Date.now();
const requests = [{
addTable: {
table: {
name: nomFeuille,
tableId: tableId,
range: {
sheetId: feuilleID,
startRowIndex: 0,
endRowIndex: 7,
startColumnIndex: 0,
endColumnIndex: 14
},
columnProperties: [
{ columnIndex: 0, columnName: "Nombre", columnType: "DOUBLE" },
{ columnIndex: 1, columnName: "Pourcentage", columnType: "PERCENT" },
{ columnIndex: 2, columnName: "Devise", columnType: "CURRENCY" },
{ columnIndex: 3, columnName: "Texte", columnType: "TEXT" },
{ columnIndex: 4, columnName: "Date", columnType: "DATE" },
{ columnIndex: 5, columnName: "Date & heure", columnType: "DATE_TIME" },
{ columnIndex: 6, columnName: "Heure", columnType: "TIME" },
{
columnIndex: 7,
columnName: "Menu déroulant",
columnType: "DROPDOWN",
dataValidationRule: {
condition: {
type: "ONE_OF_LIST",
values: [
{ userEnteredValue: "Option 1" },
{ userEnteredValue: "Option 2" },
{ userEnteredValue: "Option 3" },
{ userEnteredValue: "Option 4" }
]
}
}
},
{ columnIndex: 8, columnName: "Case à cocher", columnType: "BOOLEAN" },
{ columnIndex: 9, columnName: "Contact", columnType: "PEOPLE_CHIP" },
{ columnIndex: 10, columnName: "Fichier", columnType: "FILES_CHIP" },
{ columnIndex: 11, columnName: "Finance", columnType: "FINANCE_CHIP" },
{ columnIndex: 12, columnName: "Carte", columnType: "PLACE_CHIP" },
{ columnIndex: 13, columnName: "Note", columnType: "RATINGS_CHIP" }
]
}
}
}];
// Envoi à l'API Sheets
try {
Sheets.Spreadsheets.batchUpdate({ requests: requests }, SPREADSHEET_ID);
classeur.toast(`Table "${nomFeuille}" (ID : ${tableId}) créée avec succès ! 🎉`);
} catch (e) {
Logger.log('Erreur de création de la table : %s', e.toString());
classeur.toast(`Erreur : ${e.message}`, 'Erreur', 5);
}
}
Détail des types de colonnes
| Type API | Comportement dans Sheets |
|---|---|
| DOUBLE | Nombre décimal |
| PERCENT | Pourcentage |
| CURRENCY | Monétaire |
| TEXT | Texte libre |
| DATE | Date seule |
| DATE_TIME | Date + heure |
| TIME | Heure seule |
| DROPDOWN | Menu déroulant (ONE_OF_LIST) |
| BOOLEAN | Checkbox (TRUE/FALSE) |
| PEOPLE_CHIP | Puce Contact |
| FILES_CHIP | Puce Fichier |
| FINANCE_CHIP | Puce Finance |
| PLACE_CHIP | Puce Lieu |
| RATINGS_CHIP | Puce Note (étoiles) |
Chaque « columnType » doit être exactement nommé ainsi dans votre requête JSON, faute de quoi l’API renverra une erreur « Invalid value » — c’est pourquoi la découverte de cette liste a été essentielle pour réussir l’automatisation.
Conclusion
L’utilisation des tables via l’API avancée de Google Sheets offre un puissant levier pour structurer et valider vos données. La clé du succès réside dans la maîtrise des `columnType` officiels : une petite liste à mémoriser ou à garder sous la main, mais dont la confirmation passe par un