0

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")
}
Rubén
  • 34,714
  • 9
  • 70
  • 166
Eric K.
  • 121
  • 3
  • 17
  • Try to put before the line "var theBlob ..." SpreadsheetApp.flush() this forces calculations before moving further on the coffee – Stefan van Aalst Feb 20 '18 at 21:46
  • Thank you, Stefan! Where, specifically, would you recommend plugging this in? Sorry, somewhat new to apps script. Thanks! – Eric K. Feb 20 '18 at 21:52
  • Still getting "REF" for cell values with formulas after taking a stab at including your suggestion. I know I'm not plugging it into the code in the right place. Thanks, in advance, for your added help! – Eric K. Feb 20 '18 at 22:03
  • Before you create the blob. Flush forces to update the spreadsheet. Changes to the spreadsheet become usually only visible - like ref - after the script finishes or when it is forced by flush – Stefan van Aalst Feb 22 '18 at 15:53
  • Thanks, Stefan. I edited the above code to show where I've plugged in the flush request. Still getting 'REF's' on the PDF. Wondering if you can take a closer look at this. – Eric K. Feb 22 '18 at 16:12
  • It might help if you can share the spreadsheets. Does the sheet POTemplate contain references to one or more cells on the sheets you delete? If so, you need to copy/paste the content of POTemplate as value only. – Stefan van Aalst Feb 23 '18 at 10:02
  • Haven't tried it myself, but: var poNo = sourceSheet.getRange("N3").getValue(); var source = sourceSheet.getDataRange(); source.copyTo (sourceSheet.getDataRange), {contentsOnly: true}); var pdfName = "Sample Howard" + poNo; – Stefan van Aalst Feb 23 '18 at 10:06

2 Answers2

0

I had the same problem as you. Solved it by doing a copy & paste in the same location. (Essentially converting formulas to values.)

I did the copy/paste on the cells with formulas, thereby changing the formulas to text, and everything came through! (Keep in mind that if you wish to reuse the sheet that you're converting (to a pdf), then you'll have to eventually reset the formulas using the setFormulas statement.)

I'm at work right now, and typing on my cell. When I get back home I'll post the code.

UPDATE: I'm at home now, and found the post that gave me the idea. See https://webapps.stackexchange.com/questions/70270/how-do-you-replace-a-formula-with-its-result

user3279926
  • 59
  • 2
  • 12
0

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.

Add two lines of code:

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();
Stefan van Aalst
  • 788
  • 1
  • 7
  • 19
  • Thanks, Stefan! Getting an error message stating that the line beginning with source.copyTo is missing a ; I've tried plugging a semi-colon in to several spots within that line to no avail. Still getting the same error message. Thanks. – Eric K. Feb 23 '18 at 14:54
  • Can't try out now, but quick count of brackets suggest: source.copyTo (( – Stefan van Aalst Feb 25 '18 at 10:50
  • Thanks, so far, for you help. Your suggestion cleared up the previous error message, but now getting the following: Cannot convert Range getDataRange() to Range. (line 47, file "Code") – Eric K. Feb 26 '18 at 16:15
  • Try to replace it with source, out add () at the end – Stefan van Aalst Feb 27 '18 at 06:24
  • Stefan, possible to edit your code above to show your updated request? Your last post was a little unclear to me, unfortunately. I appreciate it! – Eric K. Feb 27 '18 at 14:52
  • Stefan, one last thing. When this function is executed, though, the VLOOKUP cells in my original file are replaced by the cell values. I need the formulas to not be copied over for future use within the file. Does that make sense? Sorry if I was being confusing. – Eric K. Mar 01 '18 at 19:26
  • I changed the code. Please look at the place where to insert the two new code lines. The main difference, the value copying now takes place on the copied spreadsheet. – Stefan van Aalst Mar 03 '18 at 08:54