google-apps-script - 如何使用Apps Script自动将Google Sheets文档中每行的图片和图表插入到Slides模板中(邮件合并)?
我想让Apps Script使用Sheets文档中的数据自动生成一组新的幻灯片,其中有几行不同的信息,我想插入到幻灯片模板中,替换占位符标签。我想让它对表里面的每一行都用一个动作即时完成,所以如果有10行,就会生成10套Slides文档。
文字替换可以用,但是我不知道如何替换,比如说,里面写有"{{图片}}"的形状,用图片替换,使用URL下的 形象 列。同样的情况也发生在 图表.
function generateNewSlides() {
var wsID = "worksheet ID here";
var ws = SpreadsheetApp.openById(wsID).getSheetByName("Data");
var data = ws.getRange(2, 1, ws.getLastRow()-1, 6).getValues();
// the above should get the relevant table from the sheet
data.forEach(function(info){
if(info[0]){
var firstname = info[0];
var surname = info[1];
var email = info[2];
var phone = info[3];
var image = info[4];
var presName = info[5];
// the above are columns where the different pieces of data would be taken from for the placeholders in the Slides template
var slidesTemplateID = "slides template ID here";
var template = DriveApp.getFileById(slidesTemplateID);
var folderID = "folder where itll be saved ID here";
var copiedTemplate = template.makeCopy(presName, DriveApp.getFolderById(folderID));
var Presentation = SlidesApp.openById(copiedTemplate.getId());
// the above should create a copy and then open it
Presentation.getSlides().forEach(function(slide) {
slide.getShapes().forEach(function(shape) {
shape.getText().replaceAllText("{{firstname}}",firstname);
shape.getText().replaceAllText("{{surname}}",surname);
shape.getText().replaceAllText("{{email}}",email);
shape.getText().replaceAllText("{{phone}}",phone);
shape.getText().replaceAllText("{{presname}}",presName);
})
// replaces the placeholder tags with the desired text
// I am not sure how to insert something similar for images and charts in the code here
// I've tried variations of the below, none of which have worked
// slide.getShapes().forEach(function(picture) {
// picture.find("{{image}}").replace(image);
// picture.findText("{{image}}").replace(image);
// picture.getText("{{image}}").replaceWithImage(image);
// picture.getText().findText("{{image}}").replace(image);
});
};
});
}
【回答】:
之间的区别 {{image}}
另一个占位符是你想通过一个实际的图像来替换文本
不幸的是,你不能在文本框内粘贴图像。
相反,你可以指定如果一个
{{image}}
占位符是存在的,你想把图像粘贴到包含文本框的幻灯片中。您可以使用以下方法检查占位符是否存在
var imageText = shape.getText().replaceAllText("{{image}}",""); if(imageText == true){...}
如果条件满足,你就可以插入图片,并指定其大小和位置,例如。
slide.insertImage(image).scaleHeight(0.5).scaleWidth(0.5).setLeft(10);
重要:要在Slides中插入图片,你需要使用webContentLink而不是Url,而且图片必须是可公开访问的(请看 此处).
如果你不知道你的图片的webContentLink,我建议你替换为
URL
在您的电子表格中使用file Id
并修改你的代码为var id = info[4]; var image = Drive.Files.get(id).webContentLink
工作样本。
function generateNewSlides() {
var wsID = "worksheet ID here";
var ws = SpreadsheetApp.openById("xxx").getSheetByName("Data");
var data = ws.getRange(2, 1, ws.getLastRow()-1, 6).getValues();
// the above should get the relevant table from the sheet
data.forEach(function(info){
if(info[0]){
var firstname = info[0];
var surname = info[1];
var email = info[2];
var phone = info[3];
var id = info[4];
var image = Drive.Files.get(id).webContentLink
var presName = info[5];
// the above are columns where the different pieces of data would be taken from for the placeholders in the Slides template
var slidesTemplateID = "xxx";
var template = DriveApp.getFileById(slidesTemplateID);
var folderID = "folder where itll be saved ID here";
var copiedTemplate = template.makeCopy(presName, DriveApp.getFolderById(folderID));
var Presentation = SlidesApp.openById(copiedTemplate.getId());
// the above should create a copy and then open it
Presentation.getSlides().forEach(function(slide) {
slide.getShapes().forEach(function(shape) {
shape.getText().replaceAllText("{{firstname}}",firstname);
shape.getText().replaceAllText("{{surname}}",surname);
shape.getText().replaceAllText("{{email}}",email);
shape.getText().replaceAllText("{{phone}}",phone);
shape.getText().replaceAllText("{{presname}}",presName);
var imageText = shape.getText().replaceAllText("{{image}}","");
if(imageText == true){
slide.insertImage(image).scaleHeight(0.5).scaleWidth(0.5).setLeft(10);
}
})
});
};
});
}