Is there a possibility to import a diagram from google sheets into a new file with a macro?

Question

I want to import a graphical evaluation (diagram) into a new file using a macro. So the evaluation should not be inserted on a new sheet in the associated file, but a completely new file should be created, in which the graphical evaluation (in the form of a combination diagram) is then copied. The file should be either a Google Sheets or an Excel file. The combination diagram already exists and is located in a Google sheet, but it should be copied into a new file automatically with the help of a macro and the corresponding button.

I have already tried to start a macro and opened a new file and copied the diagram, but it only showed this code which is pretty useless:

function AuswertungTest() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('B25').activate();
};

It would be very kind of you to help me with that problem! :)

Answer 1

Depending on how you want to insert the diagram/chart in the newly created Spreadsheet, I have two solutions using Sheet [1] and EmbeddedChart [2] methods:

1) If you want to convert the chart to an image before inserting it:

function AuswertungTest() {
  //Sheet with diagram
  var sheet = SpreadsheetApp.getActiveSheet();

  //Create new Spreadsheet in Drive and get its first Sheet
  var newSpreadsheet = SpreadsheetApp.create("new spreadsheet");
  var newSheet = newSpreadsheet.getSheets()[0];

  //Get the first chart and convert it to an image blob
  var chart = sheet.getCharts()[0];
  chart = chart.getAs('image/jpeg');

  //Insert image in new spreadsheet
  newSheet.insertImage(chart, 2, 2)
};

2) If you want to insert the chart as it is (The chart will depend on the data in the new Spreadsheet):

function AuswertungTest() {
  //Sheet with diagram
  var sheet = SpreadsheetApp.getActiveSheet();

  //Create new Spreadsheet in Drive and get its first Sheet
  var newSpreadsheet = SpreadsheetApp.create("new spreadsheet");
  var newSheet = newSpreadsheet.getSheets()[0];

  //Get the first chart and modify it to set the wanted position
  var chart = sheet.getCharts()[0];
  chart = chart.modify().addRange(newSheet.getRange("A1:B8")).setPosition(2, 2, 0, 0).build();

  //Insert chart in new spreadsheet
  newSheet.insertChart(chart); 
};

[1] https://developers.google.com/apps-script/reference/spreadsheet/sheet

[2] https://developers.google.com/apps-script/reference/spreadsheet/embedded-chart