How to copy data from a cell in Google Sheets to another cell using scripts if there is a formula in the original cell?

Question

Google Apps Script

It is necessary to save the table sheet in PDF format, for this I use this script:

function generatePdf() {
  
  var email = Session.getActiveUser().getEmail();
  
  // Get active spreadsheet.
  var sourceSpreadsheet = SpreadsheetApp.getActive();
  
  // Get active sheet.
  var sheets = sourceSpreadsheet.getSheets();
  var sheetName = sourceSpreadsheet.getActiveSheet().getName();
  var sourceSheet = sourceSpreadsheet.getSheetByName(sheetName);
  
  // Set the output filename as SheetName.
  var pdfName = sheetName;

  // Get folder containing spreadsheet to save pdf in.
  var parents = DriveApp.getFileById(sourceSpreadsheet.getId()).getParents();
  if (parents.hasNext()) {
    var folder = parents.next();
  }
  else {
    folder = DriveApp.getRootFolder();
  }
  
  // Copy whole spreadsheet.
  var destSpreadsheet = SpreadsheetApp.open(DriveApp.getFileById(sourceSpreadsheet.getId()).makeCopy("tmp_convert_to_pdf", folder))

  // Delete redundant sheets.
  var sheets = destSpreadsheet.getSheets();
  for (i = 0; i < sheets.length; i++) {
    if (sheets[i].getSheetName() != sheetName){
      destSpreadsheet.deleteSheet(sheets[i]);
    }
  }
  
  var destSheet = destSpreadsheet.getSheets()[0];

  // Repace cell values with text (to avoid broken references).
  var sourceRange = sourceSheet.getRange(1,1,sourceSheet.getMaxRows(),sourceSheet.getMaxColumns());
  var sourcevalues = sourceRange.getValues();
  var destRange = destSheet.getRange(1, 1, destSheet.getMaxRows(), destSheet.getMaxColumns());
  destRange.setValues(sourcevalues);

  // Save to pdf.
  var theBlob = destSpreadsheet.getBlob().getAs('application/pdf').setName(pdfName);
  var newFile = folder.createFile(theBlob);

  // Delete the temporary sheet.
  DriveApp.getFileById(destSpreadsheet.getId()).setTrashed(true);
    
}

If the data on the sheet is static, then everything works without problems, but since the sheet contains formulas, and the ranges contained in these formulas are deleted, it produces a PDF file with type errors: #REF!
Therefore, a script is needed that would translate data from cells with formulas into static data. I did not find a similar method for the Range object.
Can anyone suggest something?

Answer 1

The order of execution of procedures is violated. First you need to replace all the results with values, and then delete the sheets.
Here is a generic snippet. Exports sheet/sheets to different formats

• spec- array of sheet names to be exported
  
• spreadsheet- copy of the Table for export
  

/**
 *
 */
function runSheet() {
  const spec = ['COUNTIF']; // List of sheets for export

  const spreadsheet = SpreadsheetApp.openById(
    '1TpHUfTvA7xBi4TLnWaplGasDumauA3YyMgXjXeQ2cyo'
  ).copy('tmp');

  spec.forEach(sheetName => {
    const dr = spreadsheet.getSheetByName(sheetName).getDataRange();
    dr.setValues(dr.getValues());
  });

  spreadsheet.getSheets().forEach(sheet => {
    if (spec.indexOf(sheet.getName()) < 0) spreadsheet.deleteSheet(sheet);
  });

  const spreadsheetId = spreadsheet.getId();

  const file = exportSpreadsheetToFile_(spreadsheetId, 'xlsx');

  DriveApp.getFileById(spreadsheetId).setTrashed(true);

  return file;
}

exportSpreadsheetToFile_can be found here google-apps-script-snippets/standalone/export_spre...