Generating Slides from Sheets: select placeholders by columns instead of rows

[Hadrien Picq]

Hi,

I'm attempting to automate a slide deck from google sheets, in which data inputted into a Google Sheet automatically generate a corresponding slide from a template.

I tested the app script code from this tutorial, which worked at intended, generating content onto a slide from a g-sheet: https://spreadsheet.dev/generate-google-slides-from-google-sheets

I want to tweak the existing script so that, instead of listing the template variable and template value (i.e. {{value_on_slide}}, value_on_sheet) vertically over two columns, I want to adapt the script to my data entry schema, where the template variable is interspersed over a single row with my feature attributes.

So instead of this:

I have the variables laid out like this:

Accounting for the headers as well.

For clarity, this is what my Slide template looks like:

In python I'd have used the iloc function to select odd or even columns (something akin to what's described in this stackoverflow's post: https://stackoverflow.com/questions/60510316/how-can-i-read-only-odd-columns-from-dataframe). 

This is the Javascript function I'm referring to:

Any ideas? 

[Tanaike]

From your sample Spreadsheet, your template of Slides, and your showing script, for example, you want to replace all values like `{{title}}` in Google Slides with the value of cell "B2". Is my understanding correct? Because, if your Spreadsheet has more data rows, in your script, only the 1st data row is used. So, before I propose the modified script, I thought that I would like to correctly understand your actual goal. Can I ask you about the detail of your goal?

[Hadrien Picq]

Hi Tanaike,

that's almost right: in the snapshot I posted of my table, I want my script to start on row 3 (skipping the headers) and identify where the columns are odd (i.e. column A, C, E etc.) for the variables and even for the values (column B, D, F etc.). The original script only uses the first two columns indeed; the schema is a lot simpler.

My objective is to have a sheet that can be updated row-by-row, with each row's inputs generating a new slide with the content as listed in the sheet.

[Michael O'Shaughnessy]

Just off the top of my head....

IF your "filed templates" will always be the same (ie {{title}}  {{subtitle}}) then I suggest you make these the column headers.  You could then read the spreadsheet data in and then you could either turn it into an object or use the header row for your field elements.  For example, if you had something like this:

You could read the data like you do normally and then you would loop through the row in your for each function.  First "strip" the header row off the data and save to a "header" variable then do something like this:

let hdr = values.shift()

values.forEach(row =>{

  for ( var i = 0: i < 2; i++){

       let templateField = hdr[i]

       let templateValue = row[i]

      presentation.replaceAllText(templateField, templateValue)

  }

}

Hope this makes sense!

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/bbec9731-6ad1-4e68-aa0f-bd1b2c2bca8an%40googlegroups.com.

[Hadrien Picq]

Thanks! That was such an obvious schema to implement, eliminates the whole complexity of iterating between even and odd columns.

I tested your snippets with some light modifications (i.e. expanding the range of the columns I want to input into my slides). The remaining questions I have are:

1. How do I iterate so that each row generates its own slides? Right now the {{variables}} will duplicate the first set of values for every slides:

Although every row will have different sets for variables I want to visualize in Slides:

My ultimate objective is to automate an informative Slide deck from information iteratively added to a google Sheet. 

2. How do I pass an image as a {{variable}} and have it visible on Slides? As you can see on my screenshot, the image is recognized as a cell image, whether I embed my image directly from a g-drive directory or a url.

[Michael O'Shaughnessy]

OK, the first question I think I can help you with that right away.  In a nutshell, what you want to do in the "forEach" loop is to duplicate your template slide.  So if your presentation always has the template slide as the FIRST slide what you can do is something like this:

let presentation = SlidesApp.openById(PresentationID)

let templateSlide = presentation.getSlides()[0]

//then in your forEach loop replace the 'presentation.replace....' with this

templateSlide.duplicate().replaceAllText(templateField, templateValue)

That last line will create a new slide for each row.

Now, as for image goes.... you are using "replacetext" so your script is doing just that.  I believe what you are going to have to do is when you "read" the data in you will have to somehow let the script know that it is an "image" that it is getting.  I would do a logger.log and see what value the script actually has for the logo.  If it is an image file (it ends in .jpg or .png) you can convert that to a "blob" then use insertimage.  

I suggest taking a look at this code from GAS documentation:

(https://developers.google.com/apps-script/reference/slides/slide#insertimageblobsource)

var slide = SlidesApp.getActivePresentation().getSlides()[0];
// Get the Drive image file with the given ID.
var image = DriveApp.getFileById(fileId);
slide.insertImage(image);

Hope this helps!

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/984bf98f-9988-4270-ac1a-f61b74230c32n%40googlegroups.com.

[Hadrien Picq]

Thanks, that was very helpful! I tweaked your code snippet a bit and was able to generate unique slides per rows with the variables substituted.

I still am trying to figure out a couple of things:

1. I don't want my function to be additive every time I'm running the script. I.e. I want to overwrite over existing slides and only add new slides only when new rows are fed new data. The simplest way to do so, I'd imagine, would be to delete the entire presentation (save for the template slide) every time I'm running the script. Is that the best way to go about it?
2. I made some progress with embedding images. In the screenshot below, I am able to display an image from a URL, which would works for my purposes. However I hardcoded the url into my script, and am struggling to find a method to call the specific column where the image's URL is stored on my Google Sheet (since each slides will generate a different image). 

Script and output below for reference:

I used part of the solution to embed the image into Slides from this post: https://stackoverflow.com/questions/71690163/how-to-replace-a-placeholder-in-google-slide-with-an-image-from-a-image-url-in-s

By comparison what I'm trying to achieve is much simpler.

[Michael O'Shaughnessy]

OK for question 1 you have some options. You could delete all the slides and then "recreate them" but this is very inefficient.  Another option would be to add a column to your spreadsheet, maybe call it "New" and enter a "Y" if it is new data.  Then as you do your forEach you check for the "Y" and create the slide if you have a "Y".  When done go back to your sheet and delete all the "Y's".  Finally you could create a "helper" tab that filters the data on some value that makes it new (maybe a date?) then use that sheet to create all your slides.

As for question 2, I have never had luck with images and URLs.  I have had success with inserting images that are stored in a drive folder.  Here is a code snippet that you might find useful.  You could put all the images you need into a folder then add the ID to the spreadsheet.... just a thought.

const imageFun = ()=>{

let folder = DriveApp.getFolderById("FOLDER ID OF IMAGES")

let files = folder.getFiles()

let rslt = []

while (files.hasNext()){

let file = files.next()

rslt.push([file.getName(),file.getId()])

addImage(file.getId())

}

console.log(rslt)

}

const addImage = (theID)=>{

let presentation = SlidesApp.openById("PRESENTATION ID")

let image = DriveApp.getFileById(theID)

presentation.appendSlide().insertImage(image)

}

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/cfc62c0d-2375-47c9-8c1e-e5c6c9c66d66n%40googlegroups.com.

[Hadrien Picq]

Solving this has taken me longer than I care to admit. This functions iteratively adds one slide from a row in a Google Sheet, without adding redundant slides every time the code executes. Here's the code snippet for posterity:

function fillTemplate() {

// Id of the slides template

// Remember to replace this with the Id of your presentation

var PRESENTATION_ID = "1izqUEFrnBb5ghRkV5Q84eaRuPSgi78s4e_O8lBy3gho";

// Open the presentation

var presentation = SlidesApp.openById(PRESENTATION_ID);

// Read data from the spreadsheet

var values = SpreadsheetApp.getActive().getDataRange().getValues();

// Replace template variables in the presentation with values

let hdr = values.shift()

var slides = presentation.getSlides();

//change i to any other index if desired

for (var i = 1; i < slides.length; i++){

slides[i].remove()

}

values.forEach(row =>{

let templateSlide = presentation.getSlides()[0].duplicate()

for ( var i = 1 ; i < 6; i++){

let templateField = hdr[i]

let templateValue = row[i]

let logo = row[6]

console.log(logo)

templateSlide.replaceAllText(templateField, templateValue)

templateSlide.getShapes().forEach(s => {

if (s.getText().asString().trim() == "{{logo_url}}") s.replaceWithImage(logo);

});

}

}

);

}

[Michael O'Shaughnessy]

Awesome!  Thanks for posting your working code!  I like how you took care of the image issue with a shape and replace image!

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/9c8572e2-7471-4f5e-907a-86defad461aen%40googlegroups.com.