I created a spreadsheet that I used as checking in and checking out employees with QR Code and Google Forms. the script works and when I start it it also generates the google slides including the Qr code as an image.

My problem is that when I start the script it stops automatically after 6 minutes and I can not generate all rows of the table containing information. I want to create from row 2 to 601 (they are containing data) the slides with mailmerge. My idea is to tell the script that it should not automatically generate all rows but only if in column (O) as example the cells for each row contain an x. And I don't know how to do that.

I really dont now howHere is my script:

function mailMergeSlidesFromSheets() {
  // Load data from the spreadsheet
  var dataRange = SpreadsheetApp.getActive().getDataRange();
  var sheetContents = dataRange.getValues();

  // Save the header in a variable called header
  var 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 Google Slides.
  var updatedContents = [];

  // Add the header to the array that will be written back
  // to the sheet.
  updatedContents.push(header);

  // For each row, see if the 4th column is empty.
  // If it is empty, it means that a slide deck hasn't been
  // created yet.
  sheetContents.forEach(function(row) {
    if(row[2] === "") {
      // Create a Google Slides presentation using
      // information from the row.
      var slides = createSlidesFromRow(row);
      var slidesId = slides.getId();
   
      // Create the Google Slides' URL using its Id.
      var slidesUrl = `https://docs.google.com/presentation/d/${slidesId}/edit`;

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

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

}

function createSlidesFromRow(row) {
 // Create a copy of the Slides template
 var deck = createCopyOfSlidesTemplate();

 // Rename the deck using the firstname and lastname of the student
 deck.setName(row[0]   " "   row[5]   row[4]   row[10]);

 // Replace template variables using the student's information.
    deck.replaceAllText("{{id}}", row[0]);
    deck.replaceAllText("{{tag}}", row[4]);
    deck.replaceAllText("{{besetzung}}", row[5]);
    deck.replaceAllText("{{beginn}}", row[6]);
    deck.replaceAllText("{{ende}}", row[7]);
    deck.replaceAllText("{{halle}}", row[8]);
    deck.replaceAllText("{{stand}}", row[9]);
    deck.replaceAllText("{{firma}}", row[2]);
    deck.replaceAllText("{{veranstaltung}}", row[10]);
    deck.getSlides()[0].getShapes().find(s => s.getText().asString().trim().toUpperCase() == "{{IMAGE}}").replaceWithImage(`https://chart.googleapis.com/chart?chs=300x300&cht=qr&chl=${row[13]}`);

 return deck;
}

function createCopyOfSlidesTemplate() {
 //
 var TEMPLATE_ID = "1bcIS7K-CH9KH0IixCKehFniCgAReZEH3m84pd5UxhFg";

 // Create a copy of the file using DriveApp
 var copy = DriveApp.getFileById(TEMPLATE_ID).makeCopy();

 // Load the copy using the SlidesApp.
 var slides = SlidesApp.openById(copy.getId());

 return slides;
}

function onOpen() {
 // Create a custom menu to make it easy to run the Mail Merge
 // script from the sheet.
 SpreadsheetApp.getUi().createMenu("⚙️ Create BWN by Pavlos")
   .addItem("Create Slides","mailMergeSlidesFromSheets")
   .addToUi();
}

CodePudding user response：

From your following reply,

I want from the spreadsheet to create the slides with the DAta inside. The script looks at collumn C if a generated link is in it from the slide. If not then the script generates the slide if the contition on Collum L is JA.

And, your following new request,

I want to convert automatically the slides to pdf .

In this case, please modify mailMergeSlidesFromSheets as follows.

Modified script:

function mailMergeSlidesFromSheets() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var dataRange = sheet.getDataRange();
  var sheetContents = dataRange.getValues();
  sheetContents.shift();
  var updatedContents = [];
  var check = 0;
  sheetContents.forEach(function (row) {
    if (row[2] === "" && row[11] === "JA") {
      check  ;
      var slides = createSlidesFromRow(row);
      var slidesId = slides.getId();
      var slidesUrl = `https://docs.google.com/presentation/d/${slidesId}/edit`;
      updatedContents.push([slidesUrl]);

      slides.saveAndClose();
      var pdf = UrlFetchApp.fetch(`https://docs.google.com/feeds/download/presentations/Export?exportFormat=pdf&id=${slidesId}`, { headers: { authorization: "Bearer "   ScriptApp.getOAuthToken() } }).getBlob().setName(slides.getName()   ".pdf");
      DriveApp.createFile(pdf); // Or DriveApp.getFolderById("###folderId###").createFile(pdf);

    } else {
      updatedContents.push([row[3]]);
    }
  });
  if (check == 0) return;
  sheet.getRange(2, 4, updatedContents.length).setValues(updatedContents);
}