I have code that is set to trigger on form submission. The form carries data that is to be entered into text boxes that I have positioned on a slide show, and then a separate functions exports the slide as a PDF, and emails it out to a specified address.
I have both of these functions fully operational individually, however when I try to combine them into one "chain" I encounter an issue. The PDF arrives in my inbox and is NOT updated with the data from the form.
I've deduced that when GAS detects the end of a script, the changes are then applied. So if I run each script manually and separately, everything runs fine and the PDF arrives fully updated.
I've tried Slide.refreshSlide() but nothing changed.
I also tried Utilities.sleep(), under the assumption that GAS was exporting the PDF before it even made the updates (even though the code was called on after the updates) but no amount of time passed saw the changes get applied to the slide.
I've seen people mention a "batch update" however I am having trouble understanding what exactly that does and how to go about testing/implementing it into my code.
Note: When I say I combine the two functions together into a "chain" I just mean:
function CHAIN() {
EditPDF();
EmailPDF();
}
I need to figure out how to refresh/update the slide before "EmailPDF()" runs.
I think I need to understand what exactly happens when GAS finishes a script, and attempt to recreate that INSIDE of the script. OR, alternatively, I need to figure out how to use the "On Form Submission" trigger to call on two functions separately and one after another.
EDIT: Here is my code for EditPDF() and EmailPDF()...
function EditPDF () {
Logger.log("Editing!");
var Sapp = SpreadsheetApp;
var Papp = SlidesApp;
var FS = Sapp.getActiveSpreadsheet().getSheetByName("Form Submissions");
var Template = Papp.openById("10Up_PcLxVopXont9Qcu-yk-PrFGWfWPQ3ETsgys4v0Y")
var TSlide = Template.getSlideById("g2580bcdba17_0_22");
var TBox = TSlide.getShapes();
var Prod1 = FS.getRange("B"+FS.getLastRow()).getValue();
var Farm1 = FS.getRange("C"+FS.getLastRow()).getValue();
var Loc1 = FS.getRange("D"+FS.getLastRow()).getValue();
var Price1 = FS.getRange("E"+FS.getLastRow()).getValue();
var Prod2 = FS.getRange("F"+FS.getLastRow()).getValue();
var Farm2 = FS.getRange("G"+FS.getLastRow()).getValue();
var Loc2 = FS.getRange("H"+FS.getLastRow()).getValue();
var Price2 = FS.getRange("I"+FS.getLastRow()).getValue();
var Prod3 = FS.getRange("J"+FS.getLastRow()).getValue();
var Farm3 = FS.getRange("K"+FS.getLastRow()).getValue();
var Loc3 = FS.getRange("L"+FS.getLastRow()).getValue();
var Price3 = FS.getRange("M"+FS.getLastRow()).getValue();
TBox[0].getText().setText(Prod1);
TBox[1].getText().setText(Farm1);
TBox[2].getText().setText(Loc1);
TBox[3].getText().setText(Price1);
TBox[4].getText().setText(Prod2);
TBox[5].getText().setText(Farm2);
TBox[6].getText().setText(Loc2);
TBox[7].getText().setText(Price2);
TBox[8].getText().setText(Prod3);
TBox[9].getText().setText(Farm3);
TBox[10].getText().setText(Loc3);
TBox[11].getText().setText(Price3);
}
function EmailPDF () {
Logger.log("Emailing!");
var Sapp = SpreadsheetApp;
var Papp = SlidesApp;
var FS = Sapp.getActiveSpreadsheet().getSheetByName("Form Submissions");
var Template = Papp.openById("10Up_PcLxVopXont9Qcu-yk-PrFGWfWPQ3ETsgys4v0Y")
var ssID = "10Up_PcLxVopXont9Qcu-yk-PrFGWfWPQ3ETsgys4v0Y"
var Template = Papp.openById("10Up_PcLxVopXont9Qcu-yk-PrFGWfWPQ3ETsgys4v0Y")
var TSlide = Template.getSlideById("g2580bcdba17_0_22");
var Addy1 = FS.getRange("N"+FS.getLastRow()).getValue();
var requestData = {"method": "GET", "headers":{"Authorization":"Bearer "+ScriptApp.getOAuthToken()}}; //This is an array I think
var url = "https://docs.google.com/presentation/d/"+ ssID + "/export?format=pdf&id="+ssID; //This creates the PDF export url
var result = UrlFetchApp.fetch(url , requestData);
var contents = result.getContent();
MailApp.sendEmail(Addy1,"Local Sign Template" ,"Here is your custom sign Template.", {attachments:[{fileName: "LST.pdf", content:contents, mimeType:"application/pdf"}]});
}
(I know the EditPDF function could use some loops but I'm still in the process of getting everything to work properly before cleaning up my code)
NOTE: If you think your question has been misinterpreted, kindly clarify it again and include examples of your data and desired results.
Based on your question, I understand that you are experiencing an issue with "chaining" or running the functions EditPDF
and EmailPDF
in a specific order within a single run (via CHAIN()
) using the on form submission trigger. The behavior you're observing is that the resulting PDF, which is sent to your inbox, does not reflect the latest data from the form.
I replicated the issue on my end, and it seems that the problem might be related to the slide presentation not being fully saved before converting it to PDF. In order to fix this, you can use the saveAndClose()
method from the SlidesApp
library.
You will only need to add the SlidesApp method
saveAndClose()
at the end of theEditPDF
function:
function EditPDF () {
Logger.log("Editing!");
var Sapp = SpreadsheetApp;
var Papp = SlidesApp;
var FS = Sapp.getActiveSpreadsheet().getSheetByName("Form Submissions");
var Template = Papp.openById("10Up_PcLxVopXont9Qcu-yk-PrFGWfWPQ3ETsgys4v0Y")
var TSlide = Template.getSlideById("g2580bcdba17_0_22");
var TBox = TSlide.getShapes();
var Prod1 = FS.getRange("B"+FS.getLastRow()).getValue();
var Farm1 = FS.getRange("C"+FS.getLastRow()).getValue();
var Loc1 = FS.getRange("D"+FS.getLastRow()).getValue();
var Price1 = FS.getRange("E"+FS.getLastRow()).getValue();
var Prod2 = FS.getRange("F"+FS.getLastRow()).getValue();
var Farm2 = FS.getRange("G"+FS.getLastRow()).getValue();
var Loc2 = FS.getRange("H"+FS.getLastRow()).getValue();
var Price2 = FS.getRange("I"+FS.getLastRow()).getValue();
var Prod3 = FS.getRange("J"+FS.getLastRow()).getValue();
var Farm3 = FS.getRange("K"+FS.getLastRow()).getValue();
var Loc3 = FS.getRange("L"+FS.getLastRow()).getValue();
var Price3 = FS.getRange("M"+FS.getLastRow()).getValue();
TBox[0].getText().setText(Prod1);
TBox[1].getText().setText(Farm1);
TBox[2].getText().setText(Loc1);
TBox[3].getText().setText(Price1);
TBox[4].getText().setText(Prod2);
TBox[5].getText().setText(Farm2);
TBox[6].getText().setText(Loc2);
TBox[7].getText().setText(Price2);
TBox[8].getText().setText(Prod3);
TBox[9].getText().setText(Farm3);
TBox[10].getText().setText(Loc3);
TBox[11].getText().setText(Price3);
Template.saveAndClose(); //This will save the current Presentation, causing all pending updates to be flushed and applied.
}
Form Submissions
Sheet:EditPDF
and EmailPDF
in order all at once in CHAIN()
, here's the PDF in my test email: