如何用Google Apps Script将生成的PDF链接写入Google Sheet

需要新增的核心逻辑 #

你只需要对现有代码做3处调整即可实现需求：

1. 在makePDF函数末尾返回生成的PDF文件链接
2. 新增逻辑检测月度归档工作表是否存在pdf link列，不存在则自动在最后一列创建表头
3. 匹配当前员工在月度归档表中的对应行，将PDF链接写入该行的pdf link列

完整修改后的代码 #

function finishedPayslip(){
  // 生成PDF并获取链接
  const pdfUrl = makePDF()
  // 归档数据到月度工作表
  mnthly()
  // 写入PDF链接到归档表
  writePdfUrlToArchive(pdfUrl)
  clearPayslipFields()
}

function makePDF() {
  // Get the currently active spreadsheet URL (link)
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var token = ScriptApp.getOAuthToken();
  var sheet = ss.getSheetByName("Paycheck");

  //Creating an exportable URL
  var url = "https://docs.google.com/spreadsheets/d/SS_ID/export?".replace("SS_ID", ss.getId());
  var folderID = "1A691O2hh96wlKWDuZ-P9S9CK_MDTAWcm"; // Folder id to save in a folder.
  var folder = DriveApp.getFolderById(folderID);
  var employeeName = ss.getRange("'Paycheck'!C4").getValue()
  var pdfName = employeeName;

  /* Specify PDF export parameters
  From: https://code.google.com/p/google-apps-script-issues/issues/detail?id=3579
  */
  var url_ext = 'exportFormat=pdf&format=pdf'        // export as pdf / csv / xls / xlsx
  + '&size=letter'                       // paper size legal / letter / A4
  + '&portrait=true'                    // orientation, false for landscape
  + '&fitw=true&source=labnol'           // fit to page width, false for actual size
  + '&sheetnames=false&printtitle=false' // hide optional headers and footers
  + '&pagenumbers=false&gridlines=false' // hide page numbers and gridlines
  + '&fzr=false'                         // do not repeat row headers (frozen rows) on each page
  + '&gid=';                             // the sheet's Id
    
  // Convert individual worksheet to PDF
  var response = UrlFetchApp.fetch(url + url_ext + sheet.getSheetId(), {
    headers: {
      'Authorization': 'Bearer ' +  token
    }
  });

  var blobs = response.getBlob().setName(pdfName + '.pdf');
  var folders = folder.getFoldersByName(pdfName);
  folder = folders.hasNext() ? folders.next() : folder.createFolder(pdfName);
  var newFile = folder.createFile(blobs);
  // 新增：给PDF设置和表格相同的访问权限，可根据需要删除
  newFile.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.VIEW);
  // 新增：返回PDF链接
  return newFile.getUrl();
}

// 新增函数：将PDF链接写入月度归档表
function writePdfUrlToArchive(pdfUrl) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  // ---------------请根据你的实际情况修改以下两个配置---------------
  // 1. 你的工资单模板中存储月份的单元格位置，示例为Paycheck表的C5，替换成你自己的
  const currentMonth = ss.getRange("'Paycheck'!C5").getValue().toString();
  // 2. 你的月度归档表中存储员工姓名的列号，A列为1，B列为2，以此类推
  const EMPLOYEE_NAME_COLUMN = 1;
  // -------------------------------------------------------------
  const employeeName = ss.getRange("'Paycheck'!C4").getValue();
  // 获取月度归档工作表
  const archiveSheet = ss.getSheetByName(currentMonth);
  if (!archiveSheet) return;
  // 获取表头行（默认第一行为表头，可自行修改行号）
  const headerRow = archiveSheet.getRange(1, 1, 1, archiveSheet.getLastColumn()).getValues()[0];
  let pdfLinkColIndex = headerRow.indexOf("pdf link");
  // 如果不存在pdf link列，在最后一列新增
  if (pdfLinkColIndex === -1) {
    pdfLinkColIndex = headerRow.length;
    archiveSheet.getRange(1, pdfLinkColIndex + 1).setValue("pdf link");
  }
  // 匹配当前员工的行
  const nameValues = archiveSheet.getRange(1, EMPLOYEE_NAME_COLUMN, archiveSheet.getLastRow(), 1).getValues().flat();
  const targetRow = nameValues.lastIndexOf(employeeName) + 1;
  if (targetRow < 2) return; // 没找到匹配的员工行
  // 写入链接，使用HYPERLINK公式可直接点击跳转
  archiveSheet.getRange(targetRow, pdfLinkColIndex + 1).setFormula(`=HYPERLINK("${pdfUrl}", "查看工资单")`);
  // 如果不需要公式直接放链接，替换上面一行为：
  // archiveSheet.getRange(targetRow, pdfLinkColIndex + 1).setValue(pdfUrl);
}

注意事项 #

• 请根据你表格的实际配置，修改writePdfUrlToArchive函数中标注的两处配置项
• 如果你不需要给PDF设置公开访问权限，删除newFile.setSharing(...)这行即可
• 代码默认第一行为表头，员工名列是第一列，如有不符可自行修改对应参数

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