How can I insert the importRange function and prevent the values from changing after implementation?

I have the following code below and want to prevent the values from changing after implementation of the importRange function! I already tried setValue (), but it still changes. The code works fine apart from that problem. Do you have any ideas?

function Evaluation() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var newSpreadsheet = SpreadsheetApp.create("SheetEvaluation");
  var newSheet = newSpreadsheet.getSheets()[0];
  var range = sheet.getRange("A1:AX80");
  var chart = sheet.getCharts()[0];
    var newSpreadsheet = SpreadsheetApp.getActive();
    newSheet.getRange('A1').activate();
  newSheet.getCurrentCell().setValue('=ImportRange("LINK"; "Evaluation!A1:AX80")')
  .setValue('=ImportRange("LINK"; "Evaluation!A1:AX80")');
  chart = chart.modify()
  .asComboChart()
  .addRange(newSpreadsheet.getRange('B48:AX77'))
  .addRange(newSpreadsheet.getRange('B78:AX78'))
  .addRange(newSpreadsheet.getRange('B40:AX40'))
  .setMergeStrategy(Charts.ChartMergeStrategy.MERGE_ROWS)
  .setTransposeRowsAndColumns(true)
  .setNumHeaders(1)
  .setHiddenDimensionStrategy(Charts.ChartHiddenDimensionStrategy.IGNORE_BOTH)
  .setOption('bubble.stroke', '#000000')
  .setOption('useFirstColumnAsDomain', true)
  .setOption('isStacked', 'absolute')
  .setOption('series.1.type', 'LineChart')
  .setOption('series.2.type', 'LineChart')
  .setOption('series.3.type', 'LineChart')
  .setOption('series.4.type', 'ColumnChart')
  .setOption('series.5.type', 'ColumnChart')
  .setOption('series.6.type', 'ColumnChart')
  .setOption('series.7.type', 'ColumnChart')
  .setOption('series.8.type', 'ColumnChart')
  .setOption('series.9.type', 'ColumnChart')
  .setOption('series.10.type', 'ColumnChart')
  .setOption('series.11.type', 'ColumnChart')
  .setOption('series.12.type', 'ColumnChart')
  .setOption('series.13.type', 'ColumnChart')
  .setOption('series.14.type', 'ColumnChart')
  .setOption('series.15.type', 'ColumnChart')
  .setOption('series.16.type', 'ColumnChart')
  .setOption('series.17.type', 'ColumnChart')
  .setOption('series.18.type', 'ColumnChart')
  .setOption('series.19.type', 'ColumnChart')
  .setOption('series.20.type', 'ColumnChart')
  .setOption('series.21.type', 'ColumnChart')
  .setOption('series.22.type', 'ColumnChart')
  .setOption('series.23.type', 'ColumnChart')
  .setOption('series.24.type', 'ColumnChart')
  .setOption('series.25.type', 'ColumnChart')
  .setOption('series.26.type', 'ColumnChart')
  .setOption('series.27.type', 'ColumnChart')
  .setOption('series.28.type', 'ColumnChart')
  .setOption('height', 505)
  .setOption('width', 817)
  .setPosition(1, 3, 116, 4)
  .build(); 
  newSheet.insertChart(chart);
};

Solution

In Google Sheets you can have dynamic and static values.

On the Apps Script and API side you can fetch these values by:

formula - Dynamic value
value - Static Value
display value - The "Static" version of a value.

In your case you want to preserve the value of a formula on a certain point in time. To do this, you will want the "static" version of those values to be saved.

You need to use getDisplayValues to get the value that will be shown (not the formula) and then set your values to those with setValues.

Here is some sample code:

function freezeImportRange() {
  var importRangeUrl = "https://docs.google.com/spreadsheets/d/11lx7OPRXTV9xwqhCVcFPcpL43glyy_9y-PPIC1ADzhk/edit#gid=0";
  var importRangeA1 = "Sheet1!A:C";


  var importedValues = SpreadsheetApp.openByUrl(importRangeUrl).getRange(importRangeA1).getDisplayValues();

  var newSheet = SpreadsheetApp.create("New Sheet");
  var ss = newSheet.getSheetByName(newSheet.getSheets()[0].getName());

  ss.getRange(1, 1, importedValues.length, importedValues[0].length).setValues(importedValues);

}