Adapt this google docs autofill script to make a Google Presentation rather than a Google Document

Question

I'm new to Google Scripts (but not programming generally).

I'm trying to use a google spreadsheet to make a bunch of google presentation docs, one per row in the source spreadsheet.

I'm following this, which has some nice, clean code for making google docs.
https://jeffreyeverhart.com/2020/09/29/auto-fill-a-google-doc-template-from-google-sheet-data/

I like the way it works generally, where it only makes the doc if the 'Document Link' column in the source data sheet isn't populated.

It works for me, and i can use it to make a bunch of google docs with my dynamic vars filled in from the sheet - so far so good. However, I can't work out how to adapt it to make slideshows instead.

My source data is here: https://docs.google.com/spreadsheets/d/1YXLNd6vdavC0VXVA4egGydRKN-7Toga2m9RGm2btz3M/edit#gid=0

And my Presentation template is here: https://docs.google.com/presentation/d/127JIyUlFsfz2ST7lQz8iPOzTPwYqh6yIQ1oj-FQPdOY/edit#slide=id.p

And my Document template is here: https://docs.google.com/document/d/1dxtrCAf7m7SLf7mG6ShCbVIJCQXmjaca4r1VnNWh1Kk/edit

So, this script works with the Document template: it makes a new Google Document for each row in the spreadsheet, filling in the variables.

function onOpen() {
  const ui = SpreadsheetApp.getUi();
  const menu = ui.createMenu('AutoFill Docs');
  menu.addItem('Create New Docs', 'createNewGoogleDocs')
  menu.addToUi();

}

function createNewGoogleDocs() {
  //This value should be the id of your document template that we created in the last step
  //CHANGED TO THE ID OF MY PRESENTATION TEMPLATE
  const googleDocTemplate = DriveApp.getFileById('1dxtrCAf7m7SLf7mG6ShCbVIJCQXmjaca4r1VnNWh1Kk');
  
  //This value should be the id of the folder where you want your completed documents stored
  const destinationFolder = DriveApp.getFolderById('1PJEPE38E9ZryW12ijASk3NgebxqMzRim')
  //Here we store the sheet as a variable
  const sheet = SpreadsheetApp
    .getActiveSpreadsheet()
    .getSheetByName('Data')
  
  //Now we get all of the values as a 2D array
  const rows = sheet.getDataRange().getValues();
  
  //Start processing each spreadsheet row
  rows.forEach(function(row, index){
    //Here we check if this row is the headers, if so we skip it
    if (index === 0) return;
    //Here we check if a document has already been generated by looking at 'Document Link', if so we skip it
    if (row[5]) return;
    //Using the row data in a template literal, we make a copy of our template document in our destinationFolder
    const copy = googleDocTemplate.makeCopy(`${row[1]}, ${row[0]} Employee Details` , destinationFolder)
    //Once we have the copy, we then open it using the DocumentApp
    const doc = DocumentApp.openById(copy.getId())
    //All of the content lives in the body, so we get that for editing
    const body = doc.getBody();
    //In this line we do some friendly date formatting, that may or may not work for you locale
    const friendlyDate = new Date(row[3]).toLocaleDateString();
    
    //In these lines, we replace our replacement tokens with values from our spreadsheet row
    body.replaceText('{{First Name}}', row[0]);
    body.replaceText('{{Last Name}}', row[1]);
    body.replaceText('{{Position}}', row[2]);
    body.replaceText('{{Hire Date}}', friendlyDate);
    body.replaceText('{{Hourly Wage}}', row[4]);
    
    //We make our changes permanent by saving and closing the document
    doc.saveAndClose();
    //Store the url of our new document in a variable
    const url = doc.getUrl();
    //Write that value back to the 'Document Link' column in the spreadsheet. 
    sheet.getRange(index + 1, 6).setValue(url)
    
  })
  
}

I tried just using the presentation ID in place of the sample document ID, in the line where we tell it the id of the template:

  const googleDocTemplate = DriveApp.getFileById('1dxtrCAf7m7SLf7mG6ShCbVIJCQXmjaca4r1VnNWh1Kk');

but i get this error:

Exception: Document is missing (perhaps it was deleted, or you don't have read access?)

I tried changing it to this:

  const googleDocTemplate = SlidesApp.openById('1dxtrCAf7m7SLf7mG6ShCbVIJCQXmjaca4r1VnNWh1Kk');

But then I get

TypeError: googleDocTemplate.makeCopy is not a function

so i guess the Presentation's ewquivalent function to makeCopy is called something else - or it doesn't have one?

Can someone help? I'm hoping it just needs a couple of tweaks... thanks

Answer 1

You can not copy a Presentation but you can copy the file. Then open the copy and replace the text. I let you figure out the destination.

function createNewGooglePresentation() {
  try {
    let spread = SpreadsheetApp.getActiveSpreadsheet();
    let sheet = spread.getSheetByName("Data");
    let rows = sheet.getDataRange().getValues();
    rows.shift(); // remove the headers
    let template = DriveApp.getFileById("xxxxxxxxxxxxxxxxxxxxxxxxxxx");
    let urls = [];
    rows.forEach( row => {
        if( row[5] === "" ) {
          let file = template.makeCopy( row[1]+", "+row[0]+" Employee Details" );
          let presentation = SlidesApp.openById(file.getId());
          urls.push([file.getUrl()])
          let slide = presentation.getSlides()[0];  // there is only one slide
          slide.replaceAllText("{{First Name}}",row[0]);
          slide.replaceAllText("{{Position}}",row[2]);
          presentation.saveAndClose();
        }
        else {
          urls.push([row[5]]);
        }
      }
    );
    sheet.getRange(2,6,urls.length,1).setValues(urls);
  }
  catch(err) {
    console.log("Error in createNewGooglePresentation: "+err)
  }
}

References

• File.makeCopy()
• Presentation.getSlides()
• Slide.replaceAllText()