嵌套for循环迭代图表异常：单幻灯片重复插入所有图表的修复需求

问题背景 #

我编写了一个脚本，用于为Google Sheets的每一行创建对应的Google Slide，期望每张幻灯片能添加一个单独的图表。

当前问题 #

现有嵌套for循环会将所有图表插入到每一张幻灯片中，不符合需求。

预期目标 #

实现每张幻灯片仅对应一个图表，即chart[0]置于slide[0]、chart[1]置于slide[1]，以此类推。

原代码 #

function videoviewsforloop(){
   var ss = SpreadsheetApp.getActiveSpreadsheet();
   var awarenesstemplateId = "15q13aEt3MuHHnOnYyX6RE04bvnXk_B38qDWkowd5tzs";
   var awarenesstemplate = DriveApp.getFileById(awarenesstemplateId);
   var fileName = ss.getName();
   var copy = awarenesstemplate.makeCopy();
   copy.setName(fileName);
   var copyID = copy.getId();
   var slides = SlidesApp.openById(copyID);
   var defaultSlides = slides.getSlides();

   var genderposition = {right: 30, bottom: 105};
   var gendersize = {height: 300, width: 380};
   var snapchatvideoviewsassheet = ss.getSheetByName('Snapchat Video Views Ad Set');
   var snapcharts = snapchatvideoviewsassheet.getCharts();
   var snapchatvideoviewspostvalues = snapchatvideoviewsassheet.getRange ('A3:K32').getValues();

   snapchatvideoviewspostvalues.forEach(function(row){
      if(row[0]){    
         var title = row[0]  
         defaultSlides[71].duplicate();
         defaultSlides = slides.getSlides();           
         var shapes = (defaultSlides[72].getShapes());
         shapes.forEach(function(shape){
            shape.getText().replaceAllText('{{title}}',title);
         });    
         for (var i = 0 ; i< snapcharts.length; i++){
            defaultSlides[72].insertSheetsChart
            (snapcharts[i],
            genderposition.right,
            genderposition.bottom,
            gendersize.width,
            gendersize.height); 
         }
         defaultSlides = slides.getSlides();
         var presLength = defaultSlides.length; 
         defaultSlides[72].move(presLength);     
         defaultSlides = slides.getSlides();
      }
   });
}

解决方案 #

核心问题是遍历行时循环插入了所有图表，需改为用行索引匹配对应图表。修改后的代码如下：

function videoviewsforloop(){
   var ss = SpreadsheetApp.getActiveSpreadsheet();
   var awarenesstemplateId = "15q13aEt3MuHHnOnYyX6RE04bvnXk_B38qDWkowd5tzs";
   var awarenesstemplate = DriveApp.getFileById(awarenesstemplateId);
   var fileName = ss.getName();
   var copy = awarenesstemplate.makeCopy();
   copy.setName(fileName);
   var copyID = copy.getId();
   var slides = SlidesApp.openById(copyID);
   var defaultSlides = slides.getSlides();

   var genderposition = {right: 30, bottom: 105};
   var gendersize = {height: 300, width: 380};
   var snapchatvideoviewsassheet = ss.getSheetByName('Snapchat Video Views Ad Set');
   var snapcharts = snapchatvideoviewsassheet.getCharts();
   var snapchatvideoviewspostvalues = snapchatvideoviewsassheet.getRange ('A3:K32').getValues();

   snapchatvideoviewspostvalues.forEach(function(row, index){
      if(row[0] && snapcharts[index]){
         var title = row[0]  
         defaultSlides[71].duplicate();
         defaultSlides = slides.getSlides();           
         var shapes = (defaultSlides[72].getShapes());
         shapes.forEach(function(shape){
            shape.getText().replaceAllText('{{title}}',title);
         });    
         defaultSlides[72].insertSheetsChart(
            snapcharts[index],
            genderposition.right,
            genderposition.bottom,
            gendersize.width,
            gendersize.height
         );
         defaultSlides = slides.getSlides();
         var presLength = defaultSlides.length; 
         defaultSlides[72].move(presLength);     
         defaultSlides = slides.getSlides();
      }
   });
}

修改说明 #

• 给forEach添加索引参数，用当前行的index匹配对应位置的图表
• 移除遍历所有图表的循环，仅插入当前索引对应的图表
• 增加snapcharts[index]的判断，避免行数量多于图表数量时出现报错

内容的提问来源于stack exchange，提问作者Karim