SpreadsheetApp

La classe de Google Sheets est SpreadsheetApp. Dans votre fichier, pour manipuler des cellules et des valeurs, il sera donc obligatoire de déclarer cette classe.

Une fois cette étape réussie, vous pourrez alors utiliser le panel de fonctions que SpreadsheetApp propose. En voici les principales :

• getActive()

Cette fonction retourne une variable de classe Spreadsheet, c'est à dire un document Google Sheet

var sp=SpreadsheetApp.getActive()

// récupère le document courant dans lequel est contenu le script

• OpenByUrl()

Cette fonction retourne une variable de classe Spreadsheet

var sp=SpreadsheetApp.openByUrl("https://....")

// Récupère le document google sheet via son url

// ATTENTION : l'url doit être de la forme : https://docs.google.com/spreadsheets/d/id/edit

• getUi()

Cette fonction retourne un objet de la classe UI permettant d'afficher des menus et des boite de dialogue

// Fonction qui ajoute un menu personnalisé dans Google Sheet

function onOpen() {

SpreadsheetApp.getUi()

.createMenu('My Menu')

.addItem('My menu item', 'myFunction')

.addSeparator()

.addSubMenu(SpreadsheetApp.getUi().createMenu('My sub-menu')

.addItem('One sub-menu item', 'mySecondFunction')

.addItem('Another sub-menu item', 'myThirdFunction'))

.addToUi();

}

Spreadsheet

Le document Google Sheets récupéré, vous pourrez accéder aux feuilles du tableur via les fonctions suivantes :

• getSheetByName()

Cette fonction retourne une variable de classe sheet, c'est à dire votre onglet dans le Google Sheet

var sp=SpreadsheetApp.getActive()

var sheet=sp.getSheetByName("Feuille1")

// Récupère la feuille dans le document par son nom

Sheet

La classe Sheet permet d'accéder aux cellules qui se trouvent à l’intérieur d'une feuille. Ci dessous la liste des principales fonctions :

• getRange( plage texte)

Cette fonction retourne une variable de classe Range, c'est à dire une plage de cellules

var sheet=SpreadsheetApp.getActive().getSheetByName("Feuille1")

var range_select=sheet.getRange("B3:D7")

//Récupère la plage B3 à D7 de la feuille intitulée "Feuille1"

• getRange(ligne)

Cette fonction retourne une variable de classe Range

var sheet=SpreadsheetApp.getActive().getSheetByName("Feuille1")

var range_select=sheet.getRange("7:7")

// Récupère la ligne 7 de la feuille intitulée "Feuille1"

• getRange(debut_ligne, debut_col, nb_ligne, nb_col)

Cette fonction retourne une variable de classe Range

var sheet=SpreadsheetApp.getActive().getSheetByName("Feuille1")

var range_select=sheet.getRange(1,2,3,4)

// Récupère la plage commençant à:

// la ligne 1

// la colonne 2

// Sur

//3 lignes

//4 colonnes

// Soit la plage B1:E3

• InsertRowAfter(position)

Cette fonction permet d'insérer une ligne après la position indiquée dans l'onglet

// Récupération de la feuille

var sheet=SpreadsheetApp.getActive().getSheetByName("Feuille1")

//Récupération de la dernière ligne avec données de la feuille

var position=sheet.getLastRow()

//Insertion de la ligne après position

sheet.insertRowAfter(position)

• deleteRow(position)

Cette fonction supprime la ligne à la position donnée

// Récupération de la feuille

var sheet=SpreadsheetApp.getActive().getSheetByName("Feuille1")

//Suppression de la ligne N° 10

sheet.deleteRow(10)

• deleteRows(position, nb)

Cette fonction supprime un nombre de lignes donné à partir de la position donnée

//Suppression de la ligne N° 10 et N°11

sheet.deleteRow(10,2)

• InsertRowsAfter(position, nb)

Cette fonction permet d'insérer un nombre de lignes donné après la position indiquée dans l'onglet

// Récupération de la feuille

var sheet=SpreadsheetApp.getActive().getSheetByName("Feuille1")

//Récupération de la dernière ligne avec données de la feuille

var position=sheet.getLastRow()

//Nombre de ligne à ajouter

var nb=5

//Insertion de 5 lignes après position

sheet.insertRowsAfter(position ,nb)

• getLastRow() & getLastColum()

Ces deux fonctions renvoient la dernière position (ligne ou colonne) des cellules contentant des valeurs dans l'onglet sélectionné du Google Sheet

var sp = SpreadsheetApp.getActive();

var feuille= sp.getSheetByName("Feuille 1");

//Récupère la dernière ligne et colonne avec des valeurs

var der_ligne = feuille.getLastRow();

var der_col = feuille.getLastColumn();

Range

Une plage de cellules est donc définie sous la classe Range. Vous trouverez en dessous une liste de fonctions vous permettant de modifier ou accéder aux paramètres et valeurs des cellules d'une plage :

• getValue()

Cette fonction retourne une variable de type égale au type de la valeur de la cellule

var sheet=SpreadsheetApp.getActive().getSheetByName("Feuille1")

var range=sheet.getRange("B3")

var valeur=range.getValue()

// Récupère la valeur de la cellule B3

• getValues()

Cette fonction retourne une variable de type Array (tableau), c'est à dire un tableau en 2 dimensions

var sheet=SpreadsheetApp.getActive().getSheetByName("Feuille1")

var range=sheet.getRange("B3:D4")

var valeur=range.getValues()

// Récupère sous forme de tableau les valeurs des cellules B3 à D4

• setValue(v)

Cette fonction permet l'insertion de la valeur du paramètre v dans une cellule

var sheet=SpreadsheetApp.getActive().getSheetByName("Feuille1")

var range=sheet.getRange("B3")

var ma_valeur=16

var valeur=range.setValue(ma_valeur)

// Insère la valeur de la variable ma_valeur, c'est à dire 16, dans la cellule B3

• setValues(valeur_unique)

Cette fonction permet l'insertion de la valeur du paramètre valeur_unique dans toutes les cellules d'une plage

var sheet=SpreadsheetApp.getActive().getSheetByName("Feuille1")

var range=sheet.getRange("B3:D4")

var valeur=range.setValues(ma_valeur)

// Insère la valeur de la variable ma_valeur dans les cellules B3 à D4

• setValues(tableau)

Cette fonction permet l'insertion des valeurs du paramètre tableau dans une plage. Elle peremt donc d'inserer un tableau de données dans une plage de cellule avec les même dimensions

var sheet=SpreadsheetApp.getActive().getSheetByName("Feuille1")

var range=sheet.getRange("B3:C4")

var mon_tableau=[[1,2],[3,4]]

var valeur=range.setValues(mon_tableau)

// Insère les valeurs du tableau de la variable mon_tableau dans les cellules B3 à C4 comme suit:

// B3=1 | C3=1

// B4=3 | C4=4

// Si le tableau n'a pas les même dimensions que la plage, un message d'erreur apparaîtra

UI

Cette classe permet de modifier l'affichage de Google Sheets en y ajoutant des menus ou des sidebar :

• alert(prompt)

Fonction qui permet d'afficher une popup avec un message personnalisé

// Affiche "Hello, world" dans une boite de dialogue avec un bouton "OK"

// L'utilisateur peut aussi fermer la popup en cliquant sur le bouton fermer dans le titre SpreadsheetApp.getUi().alert('Hello, world');

• rompt(title, prompt, buttons)

Cette fonction affiche une popup avec un titre, un message et un jeu de boutons

// Demande à l'utilisateur son prénom (en titre "Qui êtes vous?")

// L'utilisateur a le choix entre oui & non au niveau des boutons.

//La réponse de l'utilisateur est récupérée dans la variable response

var ui = SpreadsheetApp.getUi();

var response = ui.prompt('Qui êtes vous ?', 'Puis je connaître votre prénom ?', ui.ButtonSet.YES_NO);

// Actions suivant la réponse

if (response.getSelectedButton() == ui.Button.YES) {

Logger.log('Le prénom est', response.getResponseText());

}

else if (response.getSelectedButton() == ui.Button.NO) {

Logger.log('Pas de prénom');

} else {

Logger.log('L'utilisateur a fermé la popup');

}

• showSidebar(ui)

Cette fonction permet d'ouvrir un sidebar sur le côté droit du Google Sheets et d'y placer des données. Ces données doivent être contenues dans un fichier de type HTML. Nous verrons dan le chapitre Web App comment utiliser ces fichiers HTML

// Affiche un sidebar avec un fichier HTML intitulé html-sidebar

var htmlOutput = HtmlService

.createHtmlOutputFromFile("html-sidebar")

.setTitle('Module complémentaire');

SpreadsheetApp.getUi().showSidebar(htmlOutput);

• ButtonSet

Cet attribut représente le type de jeu de bouton existant. La liste complète via ce lien : Google Script ButtonSet

• Button

Cette attribut représente le type de réponse suivant le bouton sur lequel l'utilisateur a cliqué . La liste complète via ce lien : Google Script Button

• CreateMenu

Cette fonction permet de créer un menu parmi les menus déjà existants dans Google Sheet

function CreerMenu() {

SpreadsheetApp.getUi()

.createMenu('SANGO')

.addItem('Gestion facture', 'Ma_Fonction1')

.addSeparator()

// Ajout d'un menu à l’intérieur d'un menu

.addSubMenu(SpreadsheetApp.getUi().createMenu('Gestion de l'énergie')

.addItem('Gestion frais', 'Ma_Fonction2')

.addItem('Gestion devis', 'Ma_Fonction3'))

.addToUi();

}

Documentation

Les fonctions exposées précédemment ne représentent qu'une infime partie de l’ensemble des méthodes de chaque classe. Vous trouverez donc via les liens ci dessous toutes les informations concernant les scripts liés à Google Sheet :

• SpreadsheetApp

• Sheet

• Range

• UI

Voici un récapitulatif des classes liées à Google Sheets et de leur imbrications grâce à leurs fonctions :