Exportar datos de hoja de cálculo a una presentación de Google

viktormorales

Jun 21st, 2025

707

Never

Add comment

Not a member of Pastebin yet? Sign Up, it unlocks many cool features!

JavaScript 4.36 KB | Source Code | 0 0

raw download clone embed print report

/**
* VER ARTÍCULO EN MI SITIO WEB
* https://viktormorales.com/diseno-y-desarrollo-web/exportar-datos-de-hoja-de-calculo-a-una-presentacion-de-google/
*/
/**
* createVoucher()
* This function is used to create the voucher
*/
function createVoucher() {
// Get data from Google Sheets
const sheet = SpreadsheetApp.getActiveSpreadsheet();
const sheetName = "DIA SPA";
const tab = sheet.getSheetByName(sheetName);
// Access an slide presentation
const presentationId = "SLIDE_ID";
const presentation = SlidesApp.openById(presentationId);
const presentationName = presentation.getName();
// Get the last non-empty row values
const lastRow = getLastRowInColumn(tab);
const getColumn = {
"issue_date": formatDateToSpanish(tab.getRange(lastRow,1).getValue()),
"due_date": formatDateToSpanish(tab.getRange(lastRow,2).getValue()),
"name": tab.getRange(lastRow,3).getValue()
}
// If there is data on the sheet, continue with the process
if (lastRow > 1) {
// Mensaje
SpreadsheetApp.getActive().toast("Aguarde mientras se crea el archivo PDF");
// Create a COPY of the original slide presentation
const copy = DriveApp.getFileById(presentationId).makeCopy(`Copy ${presentationName}`);
const copyId = copy.getId();
const presentationCopy = SlidesApp.openById(copyId);
// Replace text holder in texts box
const slide = presentationCopy.getSlides()[0];
const elements = slide.getPageElements();
for (const element of elements) {
if (element.getPageElementType() === SlidesApp.PageElementType.SHAPE) {
var shape = element.asShape();
var textRange = shape.getText();
var originalText = textRange.asString().trim();
if (originalText == "{{NAME}}") {
var newText = originalText.replace("{{NAME}}", getColumn["name"]);
textRange.setText(newText);
}
if (originalText == "{{DUE_DATE}}") {
var newText = originalText.replace("{{DUE_DATE}}", getColumn["due_date"]);
textRange.setText(newText);
}
}
}
// Save changes and close
presentationCopy.saveAndClose();
// Export as PDF
exportAsPDF(copyId, tab, lastRow);
} else {
SpreadsheetApp.getActive().toast("No hay información para crear un voucher");
}
}
/**
* exportPDF()
*/
function exportAsPDF(presentationId, tab, lastRow) {
// Folder ID to save VOUCHERS as PDF when exported
const folder = DriveApp.getFolderById("FOLDER_ID");
// Export as PDF
const blob = DriveApp.getFileById(presentationId).getAs("application/pdf");
const FILENAME = {
"issue_date": formatDate(tab.getRange(lastRow,1).getValue()),
"due_date": formatDate(tab.getRange(lastRow,2).getValue()),
"name": tab.getRange(lastRow,3).getValue(),
}
const fileName = `${FILENAME["due_date"]} - ${FILENAME["name"]}.pdf`;
const pdfFile = folder.createFile(blob.setName(fileName));
// Delete copy
DriveApp.getFileById(presentationId).setTrashed(true);
SpreadsheetApp.getActive().toast("Se creó el PDF");
}
/**
* getLastRowInColumn()
* Get the last populated row from "tab"
*/
function getLastRowInColumn(tab) {
// Get the first column
const column = 1; // Column A = 1, B = 2, etc.
// Get all values in the column
const values = tab.getRange(1, column, tab.getLastRow()).getValues();
// Loop through all the rows
let lastRow = 0;
for (let i = values.length - 1; i >= 0; i--) {
if (values[i][0] !== "") {
lastRow = i + 1; // Adjust for 1-based index
break;
}
}
return lastRow;
}
/**
* formatDate()
* Format dates to "yyyy-mm-dd" format
*/
function formatDate(date) {
if (!(date instanceof Date)) return date; // Return original if not a date
const year = date.getFullYear();
const month = String(date.getMonth() + 1).padStart(2, '0'); // Months are 0-indexed
const day = date.getDate(); // No leading zero for single-digit day
return `${year}-${month}-${day}`;
}
/**
* formatDateToSpanish()
* Format dates to "spanish" version
*/
function formatDateToSpanish(date) {
if (!(date instanceof Date)) return date;
const formatter = new Intl.DateTimeFormat('es-ES', {
year: 'numeric',
month: 'long',
day: 'numeric'
});
return formatter.format(date); // e.g., "20 de junio de 2025"
}

Tags: google spreadsheets slides

Add Comment

Please, Sign In to add comment