是否可以使此应用程序脚本更快?如果是，如何

Question

我已经阅读了应用程序脚本的最佳实践。但我有一个特殊的情况，每一行我都需要创建一定数量的幻灯片，并且在这些幻灯片中我需要插入嵌入式图表。

目前，运行此脚本大约需要2分钟(仅1行(。我必须每排都这样做。行数可以是从1到100。

有可能缩短这个脚本的时间吗？

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!"
);
}

我必须为每一行创建一个campaignvalues.forEach循环，否则脚本将无法工作，因为每一行的嵌入图表都不同。此外，正如您在campaignvalues.forEach循环中看到的，我有getValue而不是getValues。我尝试使用getValues并调用一个数组，但奇怪的是，这比调用每个getValue花费了更多的时间。

感谢您帮助加快此脚本的运行时间。

Answer 1

您可以更改：

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()  

对此：

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