Is it possible to make this Apps Script faster ? If so How?-CodePudding

I have read the Apps Script's Best Practices. But I have a special case where for every row I need to create a certain number of Slides and inside those slides I need to insert Embedded charts.

Currently it is taking around 2 minutes to run this script (for only 1 row). And I have to do this for every row. The number of rows could be from 1 to 100.

Is it possible to reduce the timing of this script ?

  function shortreport(){
    
    
      var ss = SpreadsheetApp.getActiveSpreadsheet();  
      var sheet = ss.getSheetByName('Copy of Overall Performance 1');
      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();
    
    defaultSlides[13].duplicate();
    defaultSlides = slides.getSlides();
    defaultSlides[15].duplicate();
    defaultSlides = slides.getSlides();
    var presLength = defaultSlides.length; 
    defaultSlides[14].move(presLength); 
    defaultSlides[16].move(presLength); 
    defaultSlides = slides.getSlides();
    var genderposition = {right: 30, bottom: 105};
    var gendersize = {height: 300, width: 380};
    var regionsposition = {right: 410, bottom: 140};
    var regionssize = {height: 240, width: 300};
    var devicesposition = {right: 410, bottom: 90};
    var devicessize = {height: 240, width: 300};
    var dailyposition = {right: 60, bottom: -30};
    var dailysize = {height: 480, width: 600}; 
    
    var campaignvalues = sheet.getRange('A4:L4').getValues(); 
    campaignvalues.forEach(function(row){
      if(row[0]){
        
       var landingPage = row[0];
       var reach = row[1];
       var impressions = row[2];
       var clicks = row[3];
       var ctr = row[4];
       var engagements = row[5];
       var engagementrate = row[6];
       var cpm = row[7];
       var cpr = row[8];
       var spent = row[9];
        var texts = [
          ["{{landing row}}", landingPage],
          ["{{reach}}", reach],
          ["{{impressions}}", impressions],
          ["{{clicks}}", clicks],
          ["{{ctr}}", ctr],
          ["{{engagements}}", engagements],
          ["{{engagementrate}}",engagementrate],
          ["{{cpm}}", cpm],
          ["{{cpr}}",cpr],
          ["{{spent}}", spent],
        ];
        
    
    defaultSlides[16].duplicate();
    
    defaultSlides = slides.getSlides();
        texts.forEach(t => defaultSlides[17].replaceAllText(...t));
    
    defaultSlides[18].duplicate();
    defaultSlides = slides.getSlides();
    
    defaultSlides[20].duplicate();
    defaultSlides = slides.getSlides();            
    
        var campaign1FBchartGender = sheet.getCharts()[7];
          defaultSlides[17].insertSheetsChart
         (campaign1FBchartGender,
         genderposition.right,
         genderposition.bottom,
         gendersize.width,
         gendersize.height);
    
        var campaign1FBchartRegions = [
          sheet.getCharts()[8],
          sheet.getCharts()[9],
          sheet.getCharts()[10],
          sheet.getCharts()[11],
          sheet.getCharts()[12],
          sheet.getCharts()[13]];
    
        var campaign1chartKSAValue = sheet.getRange("D1153").getValue()
        var campaign1chartUAEValue = sheet.getRange("H1153").getValue()
        var campaign1chartKuwaitValue = sheet.getRange("L1153").getValue()
        var campaign1chartBahrainValue = sheet.getRange("P1153").getValue()
        var campaign1chartQatarValue = sheet.getRange("T1153").getValue() 
        var campaign1chartOmanValue = sheet.getRange("X1153").getValue()                  
    
        if( campaign1chartKSAValue !== ""){
    
          defaultSlides[17].insertSheetsChart
         (campaign1FBchartRegions[0],  
         regionsposition.right,
         regionsposition.bottom,
         regionssize.width,
         regionssize.height)
        }
        if( campaign1chartUAEValue !== ""){
    
          defaultSlides[17].insertSheetsChart
         (campaign1FBchartRegions[1],  
         regionsposition.right,
         regionsposition.bottom,
         regionssize.width,
         regionssize.height)
        }
        if( campaign1chartKuwaitValue !== ""){
    
          defaultSlides[17].insertSheetsChart
         (campaign1FBchartRegions[2],  
         regionsposition.right,
         regionsposition.bottom,
         regionssize.width,
         regionssize.height)
        }        
        if( campaign1chartBahrainValue !== ""){
    
          defaultSlides[17].insertSheetsChart
         (campaign1FBchartRegions[3],  
         regionsposition.right,
         regionsposition.bottom,
         regionssize.width,
         regionssize.height)
        }
        if( campaign1chartQatarValue !== ""){
    
          defaultSlides[17].insertSheetsChart
         (campaign1FBchartRegions[4],  
         regionsposition.right,
         regionsposition.bottom,
         regionssize.width,
         regionssize.height)
        }
        if( campaign1chartOmanValue !== ""){
    
          defaultSlides[17].insertSheetsChart
         (campaign1FBchartRegions[5],  
         regionsposition.right,
         regionsposition.bottom,
         regionssize.width,
         regionssize.height)
        }        
    
       var campaign1FBchartDevices = sheet.getCharts()[14];
        defaultSlides[19].insertSheetsChart(
         campaign1FBchartDevices,
         devicesposition.right,
         devicesposition.bottom,
         devicessize.width,
         devicessize.height);
    
      var campaign1FBchartDaily = sheet.getCharts()[15];
        defaultSlides[21].insertSheetsChart(
         campaign1FBchartDaily,
         dailyposition.right,
         dailyposition.bottom,
         dailysize.width,
         dailysize.height);
    
          var presLength = defaultSlides.length; 
          defaultSlides[17].move(presLength);
          defaultSlides[19].move(presLength);
          defaultSlides[21].move(presLength);;     
          // defaultSlides[0].remove();
          // defaultSlides[6].remove(); 
    
          defaultSlides = slides.getSlides();
    
      }   // end our conditional statement
    
      }); //close our loop of values
        
    for (var i = 13 ; i < 265 ; i  ){
      defaultSlides[i].remove();
    }
      
    
      // Create && display a dialog telling the user where to
      // find the new presentation.
      var slidesUrl = copy.getUrl();
      var html = "<p>Find it in your home Drive folder:</p>"
            "<p><a href=\""   slidesUrl   "\" target=\"_blank\">"
            fileName   "</a></p>";
      
      SpreadsheetApp.getUi().showModalDialog(
        HtmlService.createHtmlOutput(html)
          .setHeight(120)
          .setWidth(350),
          "Report Generated!"
      );
    }

I have to create a campaignvalues.forEach loop for every row otherwise the script won't work because every embedded chart is different for every row. Also, as you can see inside the campaignvalues.forEach loop I have getValue instead of getValues. I tried having getValues and call an array but strangely that took more time than calling each getValue.

Thank you for your help in speeding up the runtime of this script.

CodePudding user response：

You can change this:

var campaign1chartKSAValue = sheet.getRange("D1153").getValue()
var campaign1chartUAEValue = sheet.getRange("H1153").getValue()
var campaign1chartKuwaitValue = sheet.getRange("L1153").getValue()
var campaign1chartBahrainValue = sheet.getRange("P1153").getValue()
var campaign1chartQatarValue = sheet.getRange("T1153").getValue() 
var campaign1chartOmanValue = sheet.getRange("X1153").getValue()

To this:

const [, , , campaign1chartKSAValue, , , , campaign1chartUAEValue, , , , campaign1chartKuwaitValue, , , , campaign1chartBahrainValuep, , , , campaign1chartQatarValue, , , , campaign1chartOmanValue, ,] = sheet.getRange("A1153:Z1153").getValues()[0];