如何将新生成的PDF链接写入Google Sheet列？脚本问题求助

问题出在你创建PDF文件时没有保存文件对象，因此无法获取其URL。只需修改生成PDF和写入链接的部分，以下是直接可用的完整修改脚本：

function onOpen() {
  const ui = SpreadsheetApp.getUi();
  const menu = ui.createMenu('Create Form');
  menu.addItem('Create New Docs', 'createNewGoogleDocs')
  menu.addToUi();
}

function createNewGoogleDocs() {
  const googleDocTemplate = DriveApp.getFileById('1wROa5kWXGvsOSaeb_34ncF_vcbWA4SFXGuXkwCqjAW0');
  const destinationFolder = DriveApp.getFolderById('1qld3qQDQNtaGdoOxQCsSre1VjWQ6NKGn');
  const sheet = SpreadsheetApp
    .getActiveSpreadsheet()
    .getSheetByName('Sheet1');
  
  const rows = sheet.getDataRange().getValues();
  
  rows.forEach(function(row, index){
    if (index === 0) return;
    if (row[23]) return;

    const copy = googleDocTemplate.makeCopy(`${row[1]} - ${row[2]} Order Form`, destinationFolder);
    const doc = DocumentApp.openById(copy.getId());
    const body = doc.getBody();
    const friendlyDate = new Date(row[18]).toLocaleDateString();

    body.replaceText('{{Submission Date}}', row[0]);
    body.replaceText('{{Case ID}}', row[1]);
    body.replaceText('{{Name}}', row[2]);
    body.replaceText('{{Contact Number}}', row[3]);
    body.replaceText('{{Main Service}}', row[4]);
    body.replaceText('{{Type}}', row[5]);
    body.replaceText('{{Brand}}', row[6]);
    body.replaceText('{{Model}}', row[7]);
    body.replaceText('{{IMEI No. Or Serial No.}}', row[8]);
    body.replaceText('{{Warranty}}', row[9]);
    body.replaceText('{{Password/Pattern}}', row[10]);
    body.replaceText('{{Format}}', row[11]);
    body.replaceText('{{Include Parts}}', row[12]);
    body.replaceText('{{Issues}}', row[13]);
    body.replaceText('{{Full Address}}', row[14]);
    body.replaceText('{{Choose One}}', row[15]);
    body.replaceText('{{Details}}', row[16]);
    body.replaceText('{{Status}}', row[17]);
    body.replaceText('{{Collection Date}}', friendlyDate);
    body.replaceText('{{Special Case Reject Reason}}', row[19]);
    body.replaceText('{{Quotation}}', row[20]);
    body.replaceText('{{Collection Date}}', row[21]);
    body.replaceText('{{Installation Date}}', row[22]);

    doc.saveAndClose();

    // 生成PDF并保存到指定文件夹
    const pdfContentBlob = doc.getAs(MimeType.PDF);
    const pdfFile = destinationFolder.createFile(pdfContentBlob)
      .setName(`${row[1]} - ${row[2]} Order Form`);
    
    // 获取PDF的URL并写入表格
    const pdfUrl = pdfFile.getUrl();
    sheet.getRange(index + 1, 24).setValue(pdfUrl);
  });
}

修改说明： #

• 将DriveApp.createFile(pdfContentBlob)改为destinationFolder.createFile(pdfContentBlob)，确保PDF和文档存到同一个指定文件夹
• 把创建PDF的结果赋值给pdfFile变量，通过pdfFile.getUrl()获取PDF链接
• 将原来写入文档URL的代码替换为写入PDF的URL

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