Reputation: 1
I am trying to create a self-populating slideshow that pulls from a google spreadsheet - and I've got that part down, but somehow I can't get the text boxes with the data to fit inside the slide.
I tried to align them "LEFT" as they are currently "MIDDLE" but since it's a vertical alignment, it doesn't recognize the horizontal. I don't see where the horizontal alignment goes.
I am very confused as to how alignment works in general; I'm new to all of this and have been fiddling around trying to learn by changing various numbers in the script, but then things happen that don't make sense.
Here is the script I'm working with, which I partially found online and partially created with Chat GPT. I see where the color values I want show up, and the font sizes are correct, but the text box locations feel random.
The script also somehow creates a blank title page that I don't understand, either.
Here is the script. I apologize in advance for my complete noobness.
function onSpreadsheetChange() {
var spreadsheetId = '1CqOtaUDLor3tnXdiWGg6BNgpQtFJaeNZ8FRISRaea6I';
var sheetName = 'Form Responses 1';
var presentationName = 'Supporter List';
var spreadsheet = SpreadsheetApp.openById(spreadsheetId);
var sheet = spreadsheet.getSheetByName(sheetName);
if (!sheet) {
Logger.log("Sheet not found!");
return;
}
var data = sheet.getDataRange().getValues();
// Delete existing presentation to avoid conflicts
var files = DriveApp.getFilesByName(presentationName);
while (files.hasNext()) {
var file = files.next();
file.setTrashed(true);
}
// Create a new Google Slides presentation
var presentation = SlidesApp.create(presentationName);
var bgColor = "#006055";
// Define slide dimensions
var slideWidth = 960; // Standard Google Slides width
var slideHeight = 540; // Standard Google Slides height
// Loop through each row of data (starting from the second row to skip headers)
for (var i = 1; i < data.length; i++) {
var row = data[i];
// Extract only columns B (index 1) and E (index 4)
var colB = row[1] ? row[1].toString().trim() : ""; // Column B
var colE = row[4] ? '"' + row[4].toString().trim() + '"' : ""; // Column E, wrapped in quotes
if (!colB && !colE) continue; // Skip empty rows
var slide = presentation.appendSlide(SlidesApp.PredefinedLayout.BLANK);
slide.getBackground().setSolidFill(bgColor); // Set slide background color
// Add Column B text (bold, centered, #C55200, 42pt)
if (colB) {
var textBoxB = slide.insertTextBox(colB, 50, 50, slideWidth - 100, 150);
var textRangeB = textBoxB.getText();
textRangeB.getTextStyle()
.setBold(true)
.setFontSize(42)
.setForegroundColor("#C55200");
textBoxB.setContentAlignment(SlidesApp.ContentAlignment.MIDDLE);
}
// Add Column E text (italicized, 12pt, white)
if (colE) {
var textBoxE = slide.insertTextBox(colE, 50, 220, slideWidth - 100, 100);
var textRangeE = textBoxE.getText();
textRangeE.getTextStyle()
.setItalic(true)
.setFontSize(12)
.setForegroundColor("#FFFFFF");
textBoxE.setContentAlignment(SlidesApp.ContentAlignment.MIDDLE);
}
}
Logger.log("Presentation updated: " + presentation.getUrl());
}
Upvotes: 0
Views: 13