Apps script to mailmerge data from sheet to slide including image for each row

[Pavlos]

Hi everyone, before I ask for your help I would like to apologize for my bad English. I have created a google spreadsheet and use it as a check in and check out system with a created Qr code and google form. So far everything ok. I have created a template in slides so that I want to generate certain data such as first name city etc and the Qr code in google slides template automatically.

My problem here is that the script shows me an error and I can't find a solution. that would be the error: Exception: The number of rows in the data does not match the number of rows in the range. In the data it is 1, but in the range 601.

This would be my app script which I have of course created with help by reading and informing which I am unfortunately not a professional but still a beginner.

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[14] === "") {

      // 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[14] = 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[4] + " " + row[9] + row[3]);

 // Replace template variables using the student's information.

    deck.replaceAllText("{{id}}", row[0]);

    deck.replaceAllText("{{tag}}", row[3]);

    deck.replaceAllText("{{besetzung}}", row[4]);

    deck.replaceAllText("{{beginn}}", row[5]);

    deck.replaceAllText("{{ende}}", row[6]);

    deck.replaceAllText("{{halle}}", row[7]);

    deck.replaceAllText("{{stand}}", row[8]);

    deck.replaceAllText("{{firma}}", row[2]);

    deck.replaceAllText("{{veranstaltung}}", row[9]);

    deck.getSlides()[0].getShapes().find(s => s.getText().asString().trim().toUpperCase() == "{{IMAGE}}").replaceWithImage(`https://chart.googleapis.com/chart?chs=300x300&cht=qr&chl=${row[12]}`);

 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();

}

I'm despairing and really don't know what to do. I have also tried the following:

change dataRange.setValues(updatedContents); to dataRange.offset(sheetContents.length, 0, updatedContents.length, updatedContents[0].length).setValues(updatedContents);

The script is working but it copys after the last row new rows starting from the header and generates only the last 3 rows from sheet,. If somebody wants the sheet to see what exactly i mean there is no problem. I must say that the sheet contains over 601 rows with data . And yes i know there is really a problem of the 6 minutes time out for App script. MAybe we find together a solution? My goal is to generate for each row a slide with the data in it and the link to the generated slide to write in Column which is empty. for example: collumn P Thanx all for your time.

[Halicate]

Hi Pavlos,

I'm not entirely sure how you want it to work, but the problem is along this line:

dataRange.setValues(updatedContents);

dataRange has 601 rows and updatedContents has the only row with row[14] ==== "" (column#15 empty)

For confirming, try wiih two rows with column #15 empty and the exception error must change to Exception: The number of rows in the data does not match the number of rows in the range. In the data it is 2, but in the range 601

Regards