I am a teacher trying to simply making a daily agenda. I have a Google Sheet with the information that I need to add to daily slides and a Google Slide with the template that I want. So far with my code, I can do that, however, it updates everything every time.

I want to be able to add a new row and it only create slides for the ones that have not yet been created. I would also love if the newest slides were at the beginning of the presentation rather that at the end but I think I know how to do that. ANY HELP IS MUCH APPRECIATED!

My spreadsheet includes the information (Date, Topic, EQ, I can Statements and Objectives) and it is adding this information to Google Slides into a template that I created. Then with the code, I have it sending a url link back to the spreadsheet for me to view that specific slide.

You can view a copy of the spreadsheet including more sample data here: https://docs.google.com/spreadsheets/d/1dlS6rQqxz8t3YvHwMj8CxmVYPkCtJqrBpaZEAViQvx8/edit?usp=sharing

The problem is that when I run the script, it creates new slides for every single row in the spreadsheet and I would prefer for it to only add a slide for a new row. I thought there might be a way to use the date and url columns and just run for when they are not null however I do not know how to do that and can't seem to find the answer online. I am very very new to scripts and I am trying to learn.

This is what I have so far that is working just running everything.

// The onOpen function is executed automatically every time a Spreadsheet is loaded
function onOpen() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var menuEntries = [];
// When the user clicks on "addMenuExample" then "Menu Entry 1", the function function1 is
// executed.
menuEntries.push({name: "Run Function", functionName: "createOneSlidePerRow"});


ss.addMenu("Create Slides", menuEntries);
}

function createOneSlidePerRow() {

// Replace <INSERT_SLIDE_DECK_ID> wih the ID of your 
// Google Slides presentation.
let masterDeckID = "199316s_a4J3FrILLIG3UxYCAme8xR7h3s7F45rlLR14";

// Open the presentation and get the slides in it.
let deck = SlidesApp.openById(masterDeckID);
let slides = deck.getSlides();

// The 2nd slide is the template that will be duplicated
// once per row in the spreadsheet.
let masterSlide = slides[1];

// Load data from the spreadsheet.
let dataRange = SpreadsheetApp.getActive().getDataRange();
let sheetContents = dataRange.getValues();

// Save the header in a variable called header
let header = sheetContents.shift();

// Create an array to save the data to be written back to the sheet.
// We'll use this array to save links to the slides that are created.
let updatedContents = [];

// Reverse the order of rows because new slides will
// be inserted at the top. Without this, the order of slides
// will be the inverse of the ordering of rows in the sheet. 
sheetContents.reverse();

// For every row, create a new slide by duplicating the master slide
// and replace the template variables with data from that row.
sheetContents.forEach(function (row) {

// Insert a new slide by duplicating the master slide.
let slide = masterSlide.duplicate();

// Populate data in the slide that was created
slide.replaceAllText("{{Date}}", row[1]);
slide.replaceAllText("{{Topic}}", row[2]);
slide.replaceAllText("{{EQ}}", row[3]);
 slide.replaceAllText("{{I can}}", row[4]);
  slide.replaceAllText("{{Objectives}}", row[5]);

  // Create the URL for the slide using the deck's ID and the ID
  // of the slide.
  let slideUrl = `https://docs.google.com/presentation/d/${deck.getId()}/present#slide=id.${slide.getObjectId()}`;

// Add this URL to the 4th column of the row and add this row
// to the data to be written back to the sheet.
row[6] = slideUrl;
updatedContents.push(row);
});

// Add the header back (remember it was removed using 
// sheetContents.shift())
updatedContents.push(header);

// Reverse the array to preserve the original ordering of 
// rows in the sheet.
updatedContents.reverse();

// Write the updated data back to the Google Sheets spreadsheet.
dataRange.setValues(updatedContents);



}

CodePudding user response：

As someone "very very new to scripts", please spend some time learning the basics of JavaScript. As you are using Google Sheets as the container of your script you might start by reading https://developers.google.com/apps-script/guides/sheets.

In this specific case, the easier solution to create slides for rows that have not been created yet, I think, is to add an if statement to check if the row hasn't an URL (equal to empty string), in such case create the slide, otherwise skip creating the slide. Since the script is overwriting the original data with updatedContent, updatedContents.push(row) should be put out the if statement.

// For every row, create a new slide by duplicating the master slide
// and replace the template variables with data from that row.
sheetContents.forEach(function(row) {

  if (row[6] === '') {

    // Insert a new slide by duplicating the master slide.
    let slide = masterSlide.duplicate();

    // Populate data in the slide that was created
    slide.replaceAllText("{{Date}}", row[1]);
    slide.replaceAllText("{{Topic}}", row[2]);
    slide.replaceAllText("{{EQ}}", row[3]);
    slide.replaceAllText("{{I can}}", row[4]);
    slide.replaceAllText("{{Objectives}}", row[5]);

    // Create the URL for the slide using the deck's ID and the ID
    // of the slide.
    let slideUrl = `https://docs.google.com/presentation/d/${deck.getId()}/present#slide=id.${slide.getObjectId()}`;

    // Add this URL to the 4th column of the row and add this row
    // to the data to be written back to the sheet.
    row[6] = slideUrl;        
  }
  updatedContents.push(row);
});