Created
January 18, 2023 13:19
-
-
Save ag7-alexis/5a44722f6399d69cc05ae5d053dc8615 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
function main() { | |
const sheet = SpreadsheetApp.getActiveSheet(); | |
const [columns, ...data] = sheet.getDataRange().getValues(); | |
data.forEach(function (row, i) { | |
let y = 0; | |
const values = columns.reduce((prev, column, i) => { | |
y = i; | |
return { ...prev, [column]: row[i] } | |
}, {}); | |
const urlPresentation = createCopyUsingDriveApp(values); | |
sheet.getRange(i + 2, y + 2).setValue(urlPresentation); | |
}); | |
} | |
function createCopyUsingDriveApp(variables) { | |
// L'id de la présentation | |
const templateId = "1fNhVw0i8ZYIvBf_K5DWi12tNRiIqHTZ9Nn8ICs2Cf4s"; | |
const template = DriveApp.getFileById(templateId); | |
const copy = template.makeCopy(); | |
copy.setName("Présentation for " + variables["email"]); | |
if (variables['zipcode']) { | |
const url = "https://geo.api.gouv.fr/communes?codePostal=" + variables['zipcode']; | |
const response = UrlFetchApp.fetch(url); | |
const cities = JSON.parse(response.getContentText()); | |
const biggestCity = cities.sort((a,b) => b['population'] - a['population'])[0] | |
variables = variables = {...variables, populationCity: biggestCity['population']} | |
} | |
const presentation = SlidesApp.openById(copy.getId()) | |
const slides = presentation.getSlides(); | |
// On parcours toutes les slides de la présentation | |
slides.forEach(function (slide) { | |
const shapes = slide.getShapes(); | |
// On parcours toutes les formes de chaque slides | |
shapes.forEach(function (shape) { | |
const text = shape.getText(); | |
let textString = text.asString(); | |
textString = templateComputing(textString, variables); | |
// Pour finir on remplace le texte de la forme avec le nouveau texte avec les variables remplacés | |
text.setText(textString); | |
}) | |
}); | |
const file = DriveApp.getFileById(copy.getId()); | |
// On convertit le Google Slide au format PDF | |
const pdf = file.getAs("application/pdf"); | |
const subject = "Google Slides PDF"; | |
const body = "Voici votre présentation."; | |
// On envoi le PDF par email en pièce jointe | |
MailApp.sendEmail(variables["email"], subject, body, { attachments: [pdf] }); | |
// Puis on renvoi l'url de la présentation | |
return presentation.getUrl(); | |
} | |
function templateComputing(textString, variables) { | |
// Gestion FOR | |
const regExFor = /{%for (.*) in (.*)%}(.*){%endfor%}/mgs; | |
const matchFor = regExFor.exec(textString); | |
if (matchFor != null) { | |
const variableName = matchFor[1]; | |
const arrayName = matchFor[2]; | |
const textToReplace = matchFor[3]; | |
if (variables[arrayName]) { | |
let textReplace = "" | |
if (Array.isArray(variables[arrayName])) { | |
for (const obj of variables[arrayName]) { | |
// On prefix toutes les keys de notre objet avec le nom de variables données afin de la rendre | |
// accessible via nomDeLaVariableVoulu.NomDeLaCléInitiale | |
// for city in cities donnera un objet avec pour clé city.name ou city.zipcode par exemple | |
const object = Object.keys(obj).reduce((a, c) => (a[`${variableName}.${c}`] = obj[c], a), {}); | |
textReplace += templateComputing(textToReplace, { ...variables, ...object }) | |
} | |
} | |
textString = textString.replace(matchFor[0], textReplace) | |
} else { | |
// Si il ne n'agit pas d'une variables on va regarder si ce n'est pas du json | |
try { | |
const arrayCandidate = JSON.parse(arrayName); | |
let textReplace = "" | |
if (Array.isArray(arrayCandidate)) { | |
for (const x of arrayCandidate) { | |
textReplace += templateComputing(textToReplace, { ...variables, [variableName]: x }) | |
} | |
} | |
textString = textString.replace(matchFor[0], textReplace) | |
} catch { | |
textString = textString.replace(matchFor[0], "") | |
} | |
} | |
} | |
// Gestion du IF | |
const regExIf = /{%if (.*)%}(.*){%endif%}/mgs; | |
const matchIf = regExIf.exec(textString); | |
if (matchIf != null) { | |
const variableName = matchIf[1]; | |
const textToDisplay = matchIf[2] | |
if (variables[variableName]) { | |
textString = textString.replace(matchIf[0], textToDisplay) | |
} else { | |
textString = textString.replace(matchIf[0], "") | |
} | |
} | |
// Dans le texte de chaque forme on va remplacer les champs qu'on a mis en mode variables | |
// par la valeur qu'on récupère dans le Google Sheet via le nom de colonne | |
textString = textString.replace(/{{[^{}]+}}/g, function (key) { | |
const variableName = key.replaceAll('{', '').replaceAll('}', ''); | |
return variables[variableName]; | |
}); | |
return textString; | |
} | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment