Sheets to Slides merge duplication: why is it still duplicating?

[Martin Mullan]

Hi all,

I am struggling to stop a duplication of an inserted slide when merging sheet data to a slide template and putting that into a slice deck. I have read up on it and tried to use the lockservice but it is still occurring. Is my code incorrect or is this still the bug in GAS: //script executing twice https://gsuite-developers.googleblog.com/2011/10/concurrency-and-google-apps-script.html?

function formToGSlide() {

  

  var lock = LockService.getPublicLock(); //script executing twice https://gsuite-developers.googleblog.com/2011/10/concurrency-and-google-apps-script.html

  if (lock.tryLock(30000))  {  

  

Utilities.sleep(5000); //stall the function for 5 seconds giving time for the GSheet functions and CopyDown to work

  var sheet = SpreadsheetApp.openById('sheetIDxxxxx12345').getSheetByName('Form responses 1');

  var lastRow = sheet.getDataRange().getValues();

  var last_row = sheet.getLastRow(); //last populated row in sheet

  var last_col = sheet.getLastColumn(); // last populated column in sheet

  var values = sheet.getRange(last_row, 1, 1, last_col).getValues(); //gets the range corresponding with the last populated row in the sheet

  var submitTime = Utilities.formatDate(values[0][0], "GMT+8", "dd/MM/yyyy HH:mm:ss");//values[0][0];   // time in column 1 Utilities.formatDate(tempDate1, "GMT+8", "dd/MM/yyyy")

  var studentEmail = values[0][1]; // email in column 2

  var eBook = values[0][2];        // ebook URL in column 3

  var studentName = values[0][3];

  var lesson = values[0][4]; 

  var notesUpload = values[0][5].replace("open", "uc"); // replaces open in the url from drive to uc allowing the image to be inserted. files/folder needs to be public without the authentication token Anyone with link can view

  var notesUploadLink = values[0][5];

  var whiteImage = "https://drive.google.com/uc?id=xxxxxx1234567890";

  var hyperlink = values[0][6];

  var questions = values[0][7].toString();

  var peerQs = values[0][8];

  var answers = values[0][9];

  var score = values[0][10].toString();

  var extension = values[0][11];

  var teacher = values[0][63];

  

  // Duplicate the template presentation using the Drive API.

  var copyTitle = studentName + ': ' + lesson + ' Prep';

  var copyFile = {

    title: copyTitle,

    parents: [{id: 'xxxxxxx0987654321'}] //folder where the slides dump is

  };

  var templatePresentationId = 'zzzzzzxxxxxx2837498273981273'; //id of template presentation for EBOOKS

  copyFile = Drive.Files.copy(copyFile, templatePresentationId);

  var presentationCopyId = copyFile.id;

  // Create the text merge (replaceAllText) requests for this presentation.    

  requests = [{

    replaceAllText: {

      containsText: {

        text: '{{Lesson}}',

        matchCase: true

      },

      replaceText: lesson

    }

  }, {

    replaceAllText: {

      containsText: {

        text: '{{Score}}',

        matchCase: true

      },

      replaceText: score

    }

  }, {

    replaceAllText: {

      containsText: {

        text: '{{Student}}',

        matchCase: true

      },

      replaceText: studentName

    }

  }, {

    replaceAllText: {

      containsText: {

        text: '{{Date}}',

        matchCase: true

      },

      replaceText: submitTime 

    }

  }, {

    replaceAllText: {

      containsText: {

        text: '{{Add hyperlink to digital notes}}',

        matchCase: true

      },

      replaceText: hyperlink

    }

  }, {

    replaceAllText: {

      containsText: {

        text: '{{Notes upload link}}',

        matchCase: true

      },

      replaceText: notesUploadLink

    }

  }, {

    replaceAllText: {

      containsText: {

        text: '{{Questions about the content}}',

        matchCase: true

      },

      replaceText: questions

    }

  }, {

    replaceAllText: {

      containsText: {

        text: '{{Questions Challenge your peers}}',

        matchCase: true

      },

      replaceText: peerQs

    }

  }, {

    replaceAllText: {

      containsText: {

        text: '{{Answers}}',

        matchCase: true

      },

      replaceText: answers

    }

  }, {

    replaceAllText: {

      containsText: {

        text: '{{Extension}}',

        matchCase: true

      },

      replaceText: extension

    }}];

  if (notesUpload == "" || notesUpload == undefined ) {

  

    requests.push( {

    replaceAllShapesWithImage: { //  https://drive.google.com/uc?id=

    imageUrl: whiteImage,

    replaceMethod: 'CENTER_INSIDE',

    containsText: {

      text: '{{Notes upload}}',

      matchCase: true

    }

  }

} );

  } else {

    requests.push( {

    replaceAllShapesWithImage: { //  https://drive.google.com/uc?id=

    imageUrl: notesUpload,

    replaceMethod: 'CENTER_INSIDE',

    containsText: {

      text: '{{Notes upload}}',

      matchCase: true

    }

  }

} );

    

  }

  

  // Execute the requests for this presentation.

  try{                             

  var result = Slides.Presentations.batchUpdate({

    requests: requests

  }, presentationCopyId);

  // Count the total number of replacements made.

  

  var srcSlideId = presentationCopyId;

  var copysrcSlideIndex = 0; // 0 means page 1.

  var copydstSlideIndex = 0; // 0 means page 1.

  var src = SlidesApp.openById(srcSlideId).getSlides()[copysrcSlideIndex];

  SlidesApp.openById(eBook).insertSlide(copydstSlideIndex, src);

  var subject = copyTitle;

  var messageUpdates = "<table>";

  messageUpdates += "<tr><td valign=\"top\">Submitted: <br><strong>" + submitTime + "</strong></td></tr>";

  messageUpdates += "<tr><td valign=\"top\">Student: <br><strong>" + studentName + "</strong></td></tr>";

  messageUpdates += "<tr><td valign=\"top\">Lesson: <br><strong>" + lesson + "</strong></td></tr>";

  messageUpdates += "<tr><td valign=\"top\">Score: <br><strong>" + score + "</strong></td></tr>";

  messageUpdates += "<tr><td valign=\"top\">Hyperlink: <br><strong>" + hyperlink + "</strong></td></tr>";

  messageUpdates += "<tr><td valign=\"top\">Notes Upload Link: <br><strong>" + notesUploadLink + "</strong></td></tr>";

  messageUpdates += "<tr><td valign=\"top\">Questions: <br><strong>" + questions + "</strong></td></tr>";

  messageUpdates += "<tr><td valign=\"top\">PeerQs: <br>" + peerQs + "</td></tr><br><br>";

  messageUpdates += "<tr><td valign=\"top\">Answers: <br><strong>" + answers + "</strong></td></tr>";

  messageUpdates += "<tr><td valign=\"top\">Extension: <br><strong>" + extension + "</strong></td></tr>";

  messageUpdates += "<tr><td valign=\"top\">Notes Upload: <br><img src=\"" + notesUpload + "\" width=\"500px\" \"></td></tr>";

  messageUpdates += "<tr><td valign=\"top\"><a href=\"https://docs.google.com/presentation/d/" + eBook + "\">EBOOK</a></td></tr>";

  messageUpdates += "</table>";

          var recipient = teacher;

          var body = messageUpdates;

  GmailApp.sendEmail(recipient,subject, "", {htmlBody: body });

  lock.releaseLock();

  }catch(Err){

     var subject = copyTitle;

  var messageUpdates = "<table>";

  messageUpdates += "<tr><td valign=\"top\">Submitted: <br><strong>" + submitTime + "</strong></td></tr>";

  messageUpdates += "<tr><td valign=\"top\">Student: <br><strong>" + studentName + "</strong></td></tr>";

  messageUpdates += "<tr><td valign=\"top\">Lesson: <br><strong>" + lesson + "</strong></td></tr>";

  messageUpdates += "<tr><td valign=\"top\">Score: <br><strong>" + score + "</strong></td></tr>";

  messageUpdates += "<tr><td valign=\"top\">Hyperlink: <br><strong>" + hyperlink + "</strong></td></tr>";

  messageUpdates += "<tr><td valign=\"top\">Notes Upload Link: <br><strong>" + notesUploadLink + "</strong></td></tr>";

  messageUpdates += "<tr><td valign=\"top\">Questions: <br><strong>" + questions + "</strong></td></tr>";

  messageUpdates += "<tr><td valign=\"top\">PeerQs: <br>" + peerQs + "</td></tr><br><br>";

  messageUpdates += "<tr><td valign=\"top\">Answers: <br><strong>" + answers + "</strong></td></tr>";

  messageUpdates += "<tr><td valign=\"top\">Extension: <br><strong>" + extension + "</strong></td></tr>";

  messageUpdates += "<tr><td valign=\"top\">Notes Upload: <br><img src=\"" + notesUpload + "\" width=\"500px\" \"></td></tr>";

  messageUpdates += "<tr><td valign=\"top\"><a href=\"https://docs.google.com/presentation/d/" + eBook + "\">EBOOK</a></td></tr>";

  messageUpdates += "</table>";

          var recipient = "mmullan@emailaddress";

          var body = messageUpdates;

    GmailApp.sendEmail(recipient,"Problem with Y10 Form to Slides", "", {htmlBody: body})

    lock.releaseLock();

  }   //if an error occurs, such as no email in sheet because a teacher has deleted it, an email is sent to mmu

      } else {

    GmailApp.sendEmail("mmullan@emailaddress","Form to Slides Y10 lock failed", "", {htmlBody: body})

  }

}

[Martin Mullan]

Any Google Slides masters in the group can help with this?

[Steven Bazyl]

Two questions:

* What's triggering that code (e.g form trigger, timer, user clicking on a button, etc..)?

* Are you also getting multiple emails in the case where a slide is inserted twice?

Reason I'm asking is that locks only protect code from running concurrently, but do nothing if the function is called twice sequentially. And given that it's just reading the last row, if called twice for some reason you'd get the duplicate slide if that function happens to be called twice without the sheet updating. There are some known bugs with form triggers executing multiple times and that could be the source of the problem. If that is the issue, you'd need some other guard other than the lock to determine if you've processed a row, like adding a column to mark processed rows or checking the destination presentation to see if a slide with the data in question has already been added.

Important Information

______________________

This correspondence, and any files transmitted with it, are for the named person(s) use only. They may contain confidential or legally privileged information or both. No confidentiality or privilege is waived or lost by any mis-transmission. If you receive this correspondence in error, please immediately delete it from your system and notify the sender. You must not disclose, copy or relay on any part of this correspondence if you are not the intended recipient.

Any opinions expressed in this message are those of the individual sender, except where the sender expressly, and with authority, states them to be the opinions of The Kuala Lumpur Alice Smith School ("KLASS"), a school wholly-owned by The Alice Smith Schools Association ("ASSA").

Neither the sender nor KLASS warrants that any communication via the internet is free of errors, viruses, interception or  interference.

______________________

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
Visit this group at https://groups.google.com/group/google-apps-script-community.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/7dce435a-38f1-4bf8-9b62-3c438781d969%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Reno Blair]

I believe this may be happening because the script waits a minimum of 5 seconds to determine what row to process, which is determined by getting the last row from the spreadsheet. Thus, if two submissions come in in fairly rapid succession, they will both ultimately use the same last row in the spreadsheet. There are two ways to resolve this case: get the row immediately at the start of execution, or utilize the onFormSubmit event object. In the past there have been reported issues of the event object not maintaining the range properly, but I can't say I've run into that myself. And it theoretically should ensure that you always get the correct row, while getting the last row from the spreadsheet still has the (very small, but non-zero) potential to get redirected to a second submission.

I believe that change alone should resolve this issue, but I took the time to refactor so I could more fully understand your code, so here's what I came up with (assuming this is tied to a Spreadsheet onFormSubmit trigger):

var __assign = (this && this.__assign) || function () {

__assign = Object.assign || function (t) {

for (var s, i = 1, n = arguments.length; i < n; i++) {

s = arguments[i];

for (var p in s) if (Object.prototype.hasOwnProperty.call(s, p))

t[p] = s[p];

}

return t;

};

return __assign.apply(this, arguments);

};

function onFormSubmitCreateSlide(e) {

var whiteImageUrl = "https://drive.google.com/uc?id=xxxxxx1234567890";

var slidesTemplateId = "zzzzzzxxxxxx2837498273981273";

var slidesFolderId = "xxxxxxx0987654321";

var defaultEmail = "mmullan@emailaddress";

Utilities.sleep(5000);

var sheet = e.range.getSheet();

var values = sheet.getRange(e.range.getRow(), 1, 1, sheet.getLastColumn()).getValues()[0];

var header = [

"time",

"studentEmail",

"ebook",

"studentName",

"lesson",

"notesUploadLink",

"hyperlink",

"questions",

"peerQuestions",

"answers",

"score",

"extension",

"notesUpload",

"teacher",

];

var response = header.reduce(function (o, e, i) {

var _a;

return (__assign({}, o, (_a = {}, _a[e] = values[i], _a)));

}, {});

response.time = Utilities.formatDate(response.time, "GMT+8", "dd/MM/yyyy HH:mm:ss");

response.questions = response.questions.toString();

response.score = response.score.toString();

response.notesUpload = response.notesUploadLink.replace("open", "uc");

response.teacher = values[63];

var filename = response.studentName + ": " + response.lesson + " Prep";

var file = Drive.Files.copy({ title: filename, parents: [{ id: slidesFolderId }] }, slidesTemplateId);

var requests = [

["{{Lesson}}", response.lesson],

["{{Score}}", response.score],

["{{Student}}", response.studentName],

["{{Date}}", response.submitTime],

["{{Add hyperlink to digital notes}}", response.hyperlink],

["{{Notes upload link}}", response.notesUploadLink],

["{{Questions about the content}}", response.questions],

["{{Questions Challenge your peers}}", response.peerQuestions],

["{{Answers}}", response.answers],

["{{Extension}}", response.extension],

].map(function (_a) {

var text = _a[0], replacement = _a[1];

return ({

replaceAllText: {

containsText: {

text: text,

matchCase: true

},

replaceText: replacement

}

});

});

requests.push({

replaceAllShapesWithImage: {

imageUrl: response.notesUpload == "" || response.notesUpload == void 0 ? whiteImageUrl : response.notesUpload,

replaceMethod: "CENTER_INSIDE",

containsText: {

text: "{{Notes upload}}",

matchCase: true

}

}

});

Slides.Presentations.batchUpdate({ requests: requests }, file.id);

var slides = SlidesApp.openById(file.id).getSlides();

SlidesApp.openById(response.ebook).insertSlide(0, slides[0]); // Add first slide from template copy to beginning of ebook slides.

var body = "<table>";

body += '<tr><td valign="top">Submitted: <br><strong>' + response.time+ "</strong></td></tr>";

body += '<tr><td valign="top">Student: <br><strong>' + response.studentName + "</strong></td></tr>";

body += '<tr><td valign="top">Lesson: <br><strong>' + response.lesson + "</strong></td></tr>";

body += '<tr><td valign="top">Score: <br><strong>' + response.score + "</strong></td></tr>";

body += '<tr><td valign="top">Hyperlink: <br><strong>' + response.hyperlink + "</strong></td></tr>";

body += '<tr><td valign="top">Notes Upload Link: <br><strong>' + response.notesUploadLink + "</strong></td></tr>";

body += '<tr><td valign="top">Questions: <br><strong>' + response.questions + "</strong></td></tr>";

body += '<tr><td valign="top">PeerQs: <br>' + response.peerQuestions + "</td></tr><br><br>";

body += '<tr><td valign="top">Answers: <br><strong>' + response.answers + "</strong></td></tr>";

body += '<tr><td valign="top">Extension: <br><strong>' + response.extension + "</strong></td></tr>";

body += '<tr><td valign="top">Notes Upload: <br><img src="' + response.notesUpload + '" width="500px" "></td></tr>';

body +=

'<tr><td valign="top"><a href="https://docs.google.com/presentation/d/' + response.eBook + '">EBOOK</a></td></tr>';

body += "</table>";

try {

GmailApp.sendEmail(response.teacher, filename, "", { htmlBody: body });

}

catch (e) {

GmailApp.sendEmail(defaultEmail, "Problem with Y10 Form to Slides", "", { htmlBody: body });

}

}

Please note I've only written and pushed with clasp (hence the __assign function), so there could certainly be typos or logical errors. Due to the monolithic nature of this function - it's doing a lot of different things - I would recommend further refactoring to smaller pieces if possible.

Important Information

______________________

This correspondence, and any files transmitted with it, are for the named person(s) use only. They may contain confidential or legally privileged information or both. No confidentiality or privilege is waived or lost by any mis-transmission. If you receive this correspondence in error, please immediately delete it from your system and notify the sender. You must not disclose, copy or relay on any part of this correspondence if you are not the intended recipient.

Any opinions expressed in this message are those of the individual sender, except where the sender expressly, and with authority, states them to be the opinions of The Kuala Lumpur Alice Smith School ("KLASS"), a school wholly-owned by The Alice Smith Schools Association ("ASSA").

Neither the sender nor KLASS warrants that any communication via the internet is free of errors, viruses, interception or  interference.

______________________

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
Visit this group at https://groups.google.com/group/google-apps-script-community.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/7dce435a-38f1-4bf8-9b62-3c438781d969%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--

Reno Blair

Educational Technology Services

Grossmont Union High School District

619 644 8250

[Martin Mullan]

Thanks Reno,

I'll give it a go.

Be aware though that the duplication happens EVERY time the form triggers the script, even when I was testing it by myself.

Martin Mullan
Teacher of Science and Biology,
Achievement and Progression Leader.
Educational Technologist.

The Alice Smith School - an outstanding British education for a successful international future. For students…not for profit.

Primary Campus       |     Secondary Campus

Tel : +603 2148 3674   |    +603 9543 3688

www.alice-smith.edu.my

    

P Before you print think about the environment

You received this message because you are subscribed to a topic in the Google Groups "Google Apps Script Community" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-apps-script-community/OzY0okPdgxk/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-apps-script-c...@googlegroups.com.

Visit this group at https://groups.google.com/group/google-apps-script-community.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAOg4DjAdjPA%2B3VBxWJV67Z2xWodTENrPp_yg02_1NS9wPjAwvw%40mail.gmail.com.

For more options, visit https://groups.google.com/d/optout.