在Google Apps Script中从表格数据追加新的幻灯片

Question

我已经设置了一个Google Apps Script来从电子表格中包含的数据生成新的模板幻灯片。这些数据将定期更新。我计划设置一个项目触发器，以便定期生成新的幻灯片。

现在，我通过简单地删除以前生成的幻灯片(参见下面的代码示例)来避免在触发更新期间重复幻灯片的问题，但我更愿意简单地添加新幻灯片，而不是删除和重新生成(以保留对以前生成的幻灯片的任何编辑)。

对于这个任性的R编码人员如何解决这个问题，有什么更有经验的Javascript开发人员的提示和技巧吗?

function myFunction() {
var dataSpreadsheetUrl = "https://docs.google.com/spreadsheets/d/1RjA4BJ4IKyjlh_l6vNaC7Q8wgUet1eNBo4_KJiF1pbQ/edit";
var ss = SpreadsheetApp.openByUrl(dataSpreadsheetUrl);
var deck = SlidesApp.getActivePresentation();
var sheet = ss.getSheetByName('test');
var values = sheet.getRange(2, 1, sheet.getLastRow(), sheet.getLastColumn()).getValues();
var slides = deck.getSlides();
var templateSlide = slides[1];
var presLength = slides.length;

// remove previously generated slides
// TODO: figure out how to append rather than delete and regenerate
for (var i = 2; i < slides.length; i++){
slides[i].remove()
}

values.forEach(function(page){
if(page[0]){ 

templateSlide.duplicate();
slides = deck.getSlides();
newSlide = slides[2];

var shapes = (newSlide.getShapes());
shapes.forEach(function(shape){
shape.getText().replaceAllText('{{name}}',page[0]);
shape.getText().replaceAllText('{{value1}}',page[1]);
shape.getText().replaceAllText('{{value2}}',page[2]);
shape.getText().replaceAllText('{{value3}}',page[3]);
shape.getText().replaceAllText('{{value4}}',page[4]);
shape.getText().replaceAllText('{{value5}}',page[5]);
shape.getText().replaceAllText('{{value6}}',page[6]);
shape.getText().replaceAllText('{{value7}}',page[7]);
shape.getText().replaceAllText('{{value8}}',page[8]);
shape.getText().replaceAllText('{{value9}}',page[9]);
shape.getText().replaceAllText('{{value10}}',page[10]);
}); 
presLength = slides.length; 
newSlide.move(presLength); 
}
}); 
}

Answer 1

通过检查演示长度来更新起始行来计算!

function myFunction() {
var dataSpreadsheetUrl = "https://docs.google.com/spreadsheets/d/1RjA4BJ4IKyjlh_l6vNaC7Q8wgUet1eNBo4_KJiF1pbQ/edit";
var ss = SpreadsheetApp.openByUrl(dataSpreadsheetUrl);
var deck = SlidesApp.getActivePresentation();
var sheet = ss.getSheetByName('test');
var slides = deck.getSlides();
var templateSlide = slides[1];
var presLength = slides.length;
// new data update //
var rowLength = sheet.getLastRow();
var startRow = 2;
// update startRow when not initial run
if(presLength != 2){
startRow = presLength;
}
// save newly generated data range
var values = sheet.getRange(startRow, 1, sheet.getLastRow(), sheet.getLastColumn()).getValues();

values.forEach(function(page){
if(page[0]){ 

templateSlide.duplicate();
slides = deck.getSlides();
newSlide = slides[2];

var shapes = (newSlide.getShapes());
shapes.forEach(function(shape){
shape.getText().replaceAllText('{{name}}',page[0]);
shape.getText().replaceAllText('{{value1}}',page[1]);
shape.getText().replaceAllText('{{value2}}',page[2]);
shape.getText().replaceAllText('{{value3}}',page[3]);
shape.getText().replaceAllText('{{value4}}',page[4]);
shape.getText().replaceAllText('{{value5}}',page[5]);
shape.getText().replaceAllText('{{value6}}',page[6]);
shape.getText().replaceAllText('{{value7}}',page[7]);
shape.getText().replaceAllText('{{value8}}',page[8]);
shape.getText().replaceAllText('{{value9}}',page[9]);
shape.getText().replaceAllText('{{value10}}',page[10]);
}); 
presLength = slides.length; 
newSlide.move(presLength); 
}
}); 
}