求助：基于Google Apps Script实现幻灯片模板复制与表格数据更新

我想做一个脚本，点击自定义菜单后自动复制指定的Google Slides模板，再用Google Sheets里的数据替换新幻灯片里的占位符。相关资源包括对应的表格、脚本项目和幻灯片模板。

我写了下面这段代码，但运行起来有问题：

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Auto Data Output')
    .addItem('generate PCRPPT','generatePCRPPT')
    .addToUi();
}

function generatePCRPPT() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var update = ss.getSheetByName("update");
  var new_deck_id = make_slide_copy();

  var success = update_new_deck(new_deck_id);

  if (success==1)
  {
    var value = "=HYPERLINK(\"https://docs.google.com/presentation/d/"+new_deck_id+"/edit\";\"Click to open\")";
    update.getRange("B3").setFormula(value);
    }
}

function make_slide_copy() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var prototype_deck_id = ss.getSheetByName("update").getRange(2,2).getValue();
  var presentation_prototype= SlidesApp.openById(prototype_deck_id);
  var templateSlides = presentation_prototype.getSlides();
  var currentDate = new Date();
  var newDeck = SlidesApp.create("New PCR PPT"+ currentDate);
  var defaultSlides = newDeck.getSlides();

  defaultSlides.forEach(function(slide) {
    slide.remove();
  });

  var index = 0;
  templateSlides.forEach(function(slide) {
    var newSlide = newDeck.insertSlide(index);
    var elements = slide.getPageElements();
    elements.forEach(function(element) {
      newSlide.insertPageElement(element);
    });
    index++;
  });

  var new_deck_id = newDeck.getId();
  return new_deck_id;
}

function update_new_deck(new_deck_id) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var slide_new_deck = SlidesApp.openById(new_deck_id);

    var data_sheet = ss.getSheetByName("update");
    
    var colWidth = data_sheet.getLastColumn();
    var selectedHeigth = data_sheet.getLastRow()
    var selectedFullRange = data_sheet.getRange(1,1,selectedHeigth,colWidth);
    var source_data = selectedFullRange.getValues();

    for(n=1;n<source_data.length;++n){
       slide_new_deck.replaceAllText('{{metric_}}',data_sheet.getRange(n+5,3).getValue());
       slide_new_deck.replaceAllText('{{campaign_name}}',data_sheet.getRange(2,1).getValue());
      }
}

关键问题修复及优化点 #

1. 返回值缺失：原update_new_deck函数没有返回值，导致generatePCRPPT里的success永远是undefined，无法触发HYPERLINK链接生成，需要添加return 1标记成功
2. 幻灯片复制效率：原代码逐个复制页面元素，改用insertSlide(index, slide)直接复制整页幻灯片，减少API调用次数
3. 日期格式化：直接拼接Date对象会生成混乱的字符串，改用Utilities.formatDate生成标准化日期
4. 减少重复API调用：循环里多次调用getRange会拖慢性能，改成一次性获取所有数据到数组后再读取
5. 变量命名规范：调整为更易读的驼峰式命名，提升代码维护性

修复后的代码：

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Auto Data Output')
    .addItem('生成PCR演示文稿','generatePCRPPT')
    .addToUi();
}

function generatePCRPPT() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var updateSheet = ss.getSheetByName("update");
  var newDeckId = makeSlideCopy();

  var success = updateNewDeck(newDeckId);

  if (success === 1) {
    var hyperlinkFormula = `=HYPERLINK("https://docs.google.com/presentation/d/${newDeckId}/edit";"点击打开")`;
    updateSheet.getRange("B3").setFormula(hyperlinkFormula);
  }
}

function makeSlideCopy() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var prototypeDeckId = ss.getSheetByName("update").getRange(2,2).getValue();
  var prototypePresentation = SlidesApp.openById(prototypeDeckId);
  var templateSlides = prototypePresentation.getSlides();
  var currentDate = new Date();
  var formattedDate = Utilities.formatDate(currentDate, Session.getScriptTimeZone(), "yyyy-MM-dd HH:mm:ss");
  var newDeck = SlidesApp.create(`新PCR演示文稿 ${formattedDate}`);
  
  // 删除默认幻灯片
  newDeck.getSlides().forEach(slide => slide.remove());
  // 批量复制模板幻灯片
  templateSlides.forEach((slide, index) => {
    newDeck.insertSlide(index, slide);
  });

  return newDeck.getId();
}

function updateNewDeck(newDeckId) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var newDeck = SlidesApp.openById(newDeckId);
  var dataSheet = ss.getSheetByName("update");
  var sourceData = dataSheet.getDataRange().getValues();
  
  // 提前获取固定值
  var campaignName = sourceData[1][0];

  for(let n = 1; n < sourceData.length; n++) {
    // 从数组读取数据，避免重复调用getRange
    var metricValue = sourceData[n+4] ? sourceData[n+4][2] : "";
    newDeck.replaceAllText('{{metric_}}', metricValue);
    newDeck.replaceAllText('{{campaign_name}}', campaignName);
  }

  return 1;
}

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