Google Script运行generatePDF函数提示文档无法访问报错求解

问题根因 #

• 报错Exception: The document is inaccessible. Please try again later.核心原因：你的原始模板是Word格式（.docx），调用makeCopy生成的副本仍然是Word文件，而DocumentApp.openById()仅支持原生Google Docs格式文件，无法直接打开Word格式文件
• 现有代码还存在几处隐性错误：
  1. 替换占位符时仅调用了getRange()，未补充.getValue()，实际传入的是Range对象而非单元格内的文本内容
  2. PDF命名错误使用了列号常量FIRST_NAME、SECOND_NAME，而非对应行的用户姓名值
  3. generatePDF函数内强制重写participant_row = 2，会导致循环调用时所有行都使用第2行的用户信息

解决方案 #

前置操作（必做）

先将模板转换为原生Google Docs格式：

1. 打开Google Drive找到你当前使用的模板文件
2. 右键点击文件 → 选择「用Google Docs打开」
3. 在打开的Docs页面点击顶部菜单栏「文件」→「另存为Google Docs」
4. 复制新生成的Google Docs文件的ID，替换代码中TEMPLATE_DOCS_FILE_ID的变量值

代码修改

修正后的完整代码如下：

// Global variables
var PDF_FOLDER_ID = "1OiCzqoHChrBoPKBo2n8rf-xhpc71S86G";
var TMP_FOLDER_ID = "11mtvVbt4qNUmfWK-zlZHE0W60O14aBB5";
// 替换为转成Google Docs后的模板ID
var TEMPLATE_DOCS_FILE_ID = "1fVOB5sdkSGvEhjGYPlJsYiOGgPMcyqGo";
var sheet_name = "Foaie1";
var FIRST_NAME = 2;
var SECOND_NAME = 3;
var email_position = 7;
var PARTICIPANT_ADDRESS = 8;
var PARTICIPANT_CITY = 9;
var PARTICIPANT_POSTAL_CODE = 10;
var PARTICIPANT_COUNTRY = 11;
var PARTICIPANT_ID = 29;
var yesno_position = 30;

// Open spreadsheet
SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheet_name).activate();
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

// Get person's email
function get_email(spreadsheet, participant_row)
{
  //                               ROW        , COLUMN
  return spreadsheet.getRange(participant_row, email_position).getValue();
}


// Dont send the same email to the same person
function check_already_sent(spreadsheet, participant_row)
{
  var bool_alreadySentEmail = spreadsheet.getRange(participant_row, yesno_position).getValue();
  if(bool_alreadySentEmail.toString().toUpperCase() == "YES") {
    Logger.log("Skipped " + spreadsheet.getRange(participant_row, email_position).getValue());
    return 1;
  }
  else {
    Logger.log("Sending to " + spreadsheet.getRange(participant_row, email_position).getValue());
    return 0;
  }
}


// Return person's name
function get_name(spreadsheet, participant_row) {
  return spreadsheet.getRange(participant_row, FIRST_NAME).getValue() + " " + spreadsheet.getRange(participant_row, SECOND_NAME).getValue();
}


// Generate custom PDF for every person
function generatePDF(spreadsheet, participant_row)
{
  // 测试单人生成时可保留这行，正式批量生成时删掉即可
  // participant_row = 2;
  // Get files
  var pdfFolder = DriveApp.getFolderById(PDF_FOLDER_ID);
  var tmpFolder = DriveApp.getFolderById(TMP_FOLDER_ID);
  var templateDocs = DriveApp.getFileById(TEMPLATE_DOCS_FILE_ID);
  var userName = get_name(spreadsheet, participant_row);

  // Make temporary files in the tmpFolder
  var newTMP_File = templateDocs.makeCopy(userName + "_临时模板", tmpFolder);

  // Change values
  var opened_Docs = DocumentApp.openById(newTMP_File.getId());
  var get_body = opened_Docs.getBody();

  // Execute replacements 补充getValue()获取实际内容
  get_body.replaceText("{{First Name}}",spreadsheet.getRange(participant_row, FIRST_NAME).getValue());
  get_body.replaceText("{{Second Name}}",spreadsheet.getRange(participant_row, SECOND_NAME).getValue());
  get_body.replaceText("{{ID}}",spreadsheet.getRange(participant_row, PARTICIPANT_ID).getValue());
  get_body.replaceText("{{Address}}",spreadsheet.getRange(participant_row, PARTICIPANT_ADDRESS).getValue());
  get_body.replaceText("{{City}}",spreadsheet.getRange(participant_row, PARTICIPANT_CITY).getValue());
  get_body.replaceText("{{Postal Code}}",spreadsheet.getRange(participant_row, PARTICIPANT_POSTAL_CODE).getValue());
  get_body.replaceText("{{Country}}",spreadsheet.getRange(participant_row, PARTICIPANT_COUNTRY).getValue());

  // 保存文档修改
  opened_Docs.saveAndClose();

  // Create PDF with the modified temporary file 修正命名逻辑
  var newPDF = newTMP_File.getAs(MimeType.PDF);
  var savedPDF_File = pdfFolder.createFile(newPDF).setName("JEE_Summer_Conference_Invoice " + userName);
  // 可选：删除临时文件避免占用空间
  newTMP_File.setTrashed(true);
}


// Check for duplicate entry in the excel sheet
function count_duplicate(spreadsheet, participant_row)
{
  var duplicates = 0;
  var counter = 1;
  // 修正循环边界，避免下标越界
  for(var i = participant_row; i > 1 ; i--)
    if(get_email(spreadsheet, participant_row).localeCompare(get_email(spreadsheet, participant_row - counter)) == 0) {
      duplicates++;
      counter++;
    }
    else
      break;
  
  return duplicates;
}

function onOpen() {
  SpreadsheetApp.getUi().createMenu('Send PDFs').addItem('SEND PDFs','main').addToUi();
}

// 测试单人生成时可以直接运行这个test函数
function testSingleGenerate() {
  generatePDF(spreadsheet, 2);
}

// Main function
function main()
{
  // GET THE INDEX OF THE LAST ROW
  var last_row = spreadsheet.getLastRow();

  // Generate PDF for every participant
  for(var participant_row = 2; participant_row <= last_row; ++participant_row) {
  
    // Check if we didn't already send an email to that person
    if(check_already_sent(spreadsheet, participant_row) == 1) {
        continue;
    }

    // Check duplicate
    if(participant_row < last_row && get_email(spreadsheet, participant_row).localeCompare(get_email(spreadsheet,participant_row+1)) == 0)
      continue;

    // Generate PDF
    generatePDF(spreadsheet, participant_row);

    // Mark the person as someone who has received the email to not receive another one
    spreadsheet.getRange(participant_row, yesno_position).setValue("YES");

    // Mark duplicates as sent as well
    var nr_of_duplicates = count_duplicate(spreadsheet, participant_row);
    for(var i=1; i<=nr_of_duplicates;i++) {
        var get_value = spreadsheet.getRange(participant_row,yesno_position).getValue();
        spreadsheet.getRange(participant_row - i, yesno_position).setValue(get_value);
    }
  }
}

测试说明

如果仅需要先测试单人生成功能，直接运行代码中的testSingleGenerate函数即可，不需要修改main函数的循环逻辑。

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