为什么我将Google表格另存为PDF的功能为什么会拉"REF"?有公式的单元格值? [英] Why is my function to save Google Sheet as PDF pulling "REF" for cell values with formulas?

问题描述

有人可以帮助我辨别为什么以下脚本生成的我正在使用的Google表格的PDF版本仅对要在某些单元格中提取公式结果的单元格值显示"REF"吗?

Can someone help me discern why the PDF version of the Google Sheet I am working with that is produced by the below script only shows 'REF' for Cell Values that should pull results of formulas in certain cells?

function checkSheet() {
  var sheetName = "POTemplate";
  var folderID = "Eric";
  var sourceSpreadsheet = SpreadsheetApp.getActive();
  var sourceSheet = sourceSpreadsheet.getSheetByName(sheetName);
  var poNo = sourceSheet.getRange("N3").getValue();
  var pdfName = "Sample Howard" + poNo;
  var folder = DriveApp.getFoldersByName(folderID);
  var destSpreadsheet = SpreadsheetApp.open(DriveApp.getFileById(sourceSpreadsheet.getId()).makeCopy(folder));
  var sheets = destSpreadsheet.getSheets();
   for (i = 0; i < sheets.length; i++) {
   if (sheets[i].getSheetName() != sheetName){destSpreadsheet.deleteSheet(sheets[i])}}; 
   SpreadSheetApp.flush();
   var theBlob = 
destSpreadsheet.getBlob().getAs('application/pdf').setName(pdfName);
  var newFile = folder.next().createFile(theBlob);
  DriveApp.getFileById(destSpreadsheet.getId()).setTrashed(true);
  var strUrl = newFile.getUrl()
  var html = HtmlService.createTemplateFromFile("NewTab")
  html.strUrl = strUrl
  SpreadsheetApp.getUi().showModelessDialog(html.evaluate(), "Pdf Link")
}

推荐答案

假设您要保留的工作表中的公式中使用了删除的工作表，则需要确保工作表中仅包含值.

Assuming that the sheets you delete are used in a formula on the sheet you want to keep, you need to ensure that your sheet only contains values.

添加两行代码:

var destSpreadsheet = SpreadsheetApp.open(DriveApp.getFileById(sourceSpreadsheet.getId()).makeCopy(folder));
var source = destSpreadsheet.getSheetByName(sheetName).getDataRange; //new code
source.copyTo (source, {contentsOnly: true}); //new code
var sheets = destSpreadsheet.getSheets();

这篇关于为什么我将Google表格另存为PDF的功能为什么会拉"REF"?有公式的单元格值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！