英文:
How do I force Google Slides to update using GAS?
问题
我有一段代码,设置在表单提交时触发。表单携带数据,这些数据将输入到幻灯片上我放置的文本框中,然后一个单独的函数将幻灯片导出为PDF,并通过电子邮件发送到指定的地址。
这两个函数我都可以独立运行,但是当我尝试将它们合并成一个“链”时,遇到了问题。PDF文件到达我的收件箱,但未更新为来自表单的数据。
我推断当GAS检测到脚本结束时,然后应用更改。所以,如果我手动和分开运行每个脚本,一切都正常,PDF文件会完全更新。
我尝试了Slide.refreshSlide(),但没有任何变化。
我还尝试了Utilities.sleep(),假设GAS在甚至在更新之前就导出PDF文件了(尽管代码是在更新之后调用的),但没有经过的时间看到幻灯片上的更改。
我看到有人提到“批量更新”,但我很难理解它到底是做什么以及如何测试/实现它到我的代码中。
注意:当我说将这两个函数合并成一个“链”时,我只是指:
function CHAIN() {
EditPDF();
EmailPDF();
}
我需要弄清楚在“EmailPDF()”运行之前如何刷新/更新幻灯片。
我认为我需要理解GAS完成脚本时究竟发生了什么,并尝试在脚本内部重新创建这个过程。或者,作为另一种选择,我需要弄清楚如何使用“表单提交”触发器来分别调用两个函数并依次执行它们。
编辑:这是EditPDF()和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"}]});
}
(我知道EditPDF函数可能需要一些循环,但在清理我的代码之前,我仍在努力让一切正常工作)
英文:
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)
答案1
得分: 1
根据您的问题,我理解您在单个运行中(通过CHAIN()
)使用表单提交触发器时,遇到了关于在特定顺序中运行EditPDF
和EmailPDF
函数的问题。您观察到的行为是,发送到您收件箱的PDF文件不反映表单中的最新数据。
我在我的端上复制了这个问题,似乎问题可能与在将幻灯片演示转换为PDF之前未完全保存幻灯片演示有关。为了解决这个问题,您可以在EditPDF
函数的末尾使用SlidesApp
库中的saveAndClose()
方法。
调整后的脚本
您只需要在EditPDF
函数的末尾添加SlidesApp
方法saveAndClose()
:
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(); //这将保存当前演示文稿,导致所有待处理的更新被刷新并应用。
}
演示
-
虚拟的
Form Submissions
工作表: -
虚拟的幻灯片演示:
-
在
CHAIN()
中一次性运行EditPDF
和EmailPDF
函数后,这是我的测试邮件中的PDF文件:
参考
英文:
Suggestion
> 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.
Tweaked Script
> You will only need to add the SlidesApp method saveAndClose()
at the end of the EditPDF
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.
}
Demo
- Dummy
Form Submissions
Sheet:
- Dummy Slide Presentation:
- After running the functions
EditPDF
andEmailPDF
in order all at once inCHAIN()
, here's the PDF in my test email:
Reference
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论