I'm trying to figure out how I can use this script.Idea is to merge data from spreadsheet to doc file. Works fine without any date formatting but soon I 'm using Utilities.formatDate, the output is updated on cell where there's a date value but keeps tags like %n10% on my doc file for empty cells. Without Utilities.formatDate, output on doc file is perfect. Thanks for help, Newby on programming Zef
My files
Spreadsheet http://goo.gl/kQCpmo Doc template http://goo.gl/uKc8nB
My code:
function onOpen() {
var menuEntries = [ {name: "Créer liste des repas", functionName:"exportToPdf"}];
var ss = SpreadsheetApp.getActiveSpreadsheet();
ss.addMenu("Liste des repas", menuEntries);
}
function exportToPdf() {
var data = SpreadsheetApp.getActiveSheet().getRange("A5:D100").getValues(); //Get data range
var row;
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet = spreadsheet.getActiveSheet();
var dateRepas = sheet.getRange("B1").getValue();
var fileName = "MergeTest" + dateRepas;
var template_file = DriveApp.getFilesByName("template_test")
while (template_file.hasNext()) {
template = template_file.next(); //Find template
}
var templateCopyId = template.makeCopy().setName(fileName).getId(); //Make a copy of the template and get the ID of that copy.
var doc = DocumentApp.openById(templateCopyId); //Open the template copy doc with DocumentApp.
var docBody = doc.getBody();
for (i = 0; i < data.length; i += 1) {
row = data[i];
docBody.replaceText("%i" + i + "%", Utilities.formatDate(row[0], "GMT", "dd/MM/yyyy")); //Replace the marks "%something%" with data.
//docBody.replaceText("%i"+i+"%", row[0]);
docBody.replaceText("%n" + i + "%", row[1]);
docBody.replaceText("%e" + i + "%", row[2]);
docBody.replaceText("%q" + i + "%", row[3]);
}
doc.saveAndClose();
var file = doc.getAs('application/pdf').setName(doc.getName() + ".pdf"); //Create the pdf file from the template copy.
if (file != null) {
DocsList.createFile(file)
}
DocsList.getFileById(doc.getId()).setTrashed(true); //Delete the template copy.
}
Aucun commentaire:
Enregistrer un commentaire