How to convert Date and Time with mailmerge google slides from sheets as it is in cell

Question

I have created a table with which I can record our check in times of our employees with the help of a generated Qr code in each line.The data in the table is generated as slides and converted into pdf. For this I use a script that I got to work with your help and it works. Here I would like to thank you especially @tanaike.

My problem is that the date and time are not copied to the slides to be generated as indicated in the cell but completely with Central European time and I added in the script to look in column if its empty to generate the slide. If it's not empty don't do anything. As I said everything is working except this two things.

I must confess I did not try to correct it somehow because I had already shot the script and I made some here despair. It would be really great if you write me the solutions and I can take them over. I will share the spreadsheet with you and the screenshot with ae and time. Thanks for your time and effort to help people like us; we are really trying.

Answer 1

As another approach, when I saw your question, I thought that if your Spreadsheet has the correct date values you expect, and in your script, you are retrieving the values using getValues, getValues is replaced with getDisplayValues(), it might be your expected result.

When I saw your provided sample Spreadsheet, I found your current script, when your script is modified, how about the following modification?

From:

var sheetContents = dataRange.getValues();

To:

sheetContents = dataRange.getDisplayValues();

Note:

• When I saw your sample Spreadsheet, it seems that the column of the date has mixed values of both the string value and the date object. So, if you want to use the values as the date object using getValues, please be careful about this.

Reference:

• getDisplayValues()

Added:

About your 2nd question of I mean that when a slide has been generated, the script saves the link from the slide in column D if the word YES is in column L. How do I make the script create the slide if there is JA in the column L and there is no link in column D. is a link in column D, the script should not generate a slide again. Thus, the script should only generate a slide if column D is empty and at the same time the word JA is in column L., when I proposed to modify from if (row[2] === "" && row[11] === "JA") { to if (row[3] == "" && ["JA", "YES"].includes(row[11])) {, you say as follows.

If ichanged as you descripted if (row[3] == "" && ["JA", "YES"].includes(row[11])) { i got this error. Syntax error: Unexpected token 'else' Line: 21 File: Code.gs

In this case, I'm worried that you might correctly reflect my proposed script. Because when I tested it, no error occurs. So, just in case, I add the modified script from your provided Spreadsheet as follows. Please test this.

Modified script:

function mailMergeSlidesFromSheets() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var dataRange = sheet.getDataRange();
  sheetContents = dataRange.getDisplayValues(); // Modified
  sheetContents.shift();
  var updatedContents = [];
  var check = 0;
  sheetContents.forEach(function (row) {
    if (row[3] == "" && ["JA", "YES"].includes(row[11])) { // Modified
      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.getFolderById("1tRC505IWtTj8nnPB7XyydvTtCJmOb6Ek").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);
}