1

I am trying to copy a static version of my google sheet and I found a code here...

Copy Spreadsheet to a new file, specify tabs

It is all well and good but when copied the graphs are broken, it doesn't understand to copy the references. Is there any way to get the graph references to be copied over too?

This is the code I found that works great for the numbers but not with the graphs...

function myFunction() {
  
  var spreadsheetId = "###"; // Please set the source Spreadsheet ID.
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
 var destFolderId = "###";  // Please set the destination folder ID.

  // Copy each sheet in the source Spreadsheet by removing the formulas as the temporal sheets.
  var ss = SpreadsheetApp.openById(spreadsheetId);
  var tempSheets = ss.getSheets().map(function(sheet) {
    var dstSheet = sheet.copyTo(ss).setName(sheet.getSheetName() + "_temp");
    var src = dstSheet.getDataRange();
    src.copyTo(src, {contentsOnly: true});
    return dstSheet;
  });
  
  // Copy the source Spreadsheet.
  var destination = ss.copy(ss.getName() + " - " + new Date().toLocaleString());
  
  // Delete the temporal sheets in the source Spreadsheet.
  tempSheets.forEach(function(sheet) {ss.deleteSheet(sheet)});
  
  // Delete the original sheets from the copied Spreadsheet and rename the copied sheets.
  destination.getSheets().forEach(function(sheet) {
    var sheetName = sheet.getSheetName();
    if (sheetName.indexOf("_temp") == -1) {
      destination.deleteSheet(sheet);
    } else {
      sheet.setName(sheetName.slice(0, -5));
    }
  });

  // Move file to the destination folder.
  var file = DriveApp.getFileById(destination.getId());
  DriveApp.getFolderById(destFolderId).addFile(file);
  file.getParents().next().removeFile(file);
}
Mee
  • 133
  • 1
  • 8
  • If all your references are on the same sheet, you can make a copy of the spreadsheet a whole (with all your graphs inside) and remove redundant sheets from this copy. – Yuri Khristich Jul 09 '21 at 15:46

1 Answers1

0

Rather than this complex copy method, can you just copy the entire spreadsheet into a new file? I would suggest the following code:

const spreadsheetId = "###"; // Please set the source Spreadsheet ID.
const spreadsheetFile = DriveApp.getFileById(spreadsheetId);
/* Previously, you were opening the active spreadsheet rather than the one specified
by the line above, and we want to use the DriveApp to be able to copy it */

const destFolderId = "###";  // Please set the destination folder ID.
const destFolder = DriveApp.getFolderById(destFolderId);

const destFile = spreadsheetFile.makeCopy("{NEW FILE NAME}", destFolder);
// Makes copy with name of your choice and places the new spreadsheet in the destination folder

console.log(destFile.getUrl()); // Logs out the destination file URL for easy access

Does that give you what you need?

Lle.4
  • 516
  • 4
  • 17
  • Thanks however it is giving me an error..., I think it is because it says 'destFolder' instead of destFolderId but when I change it it gives me this error... "Exception: The parameters (String,String) don't match the method signature for DriveApp.File.makeCopy. (line 9, file" – Mee Jul 10 '21 at 10:27
  • You're totally right, so sorry about that. I added one line below the declaration of destFolderId--please let me know if implementing that fixes the issue! (I had never declared `destFolder`, that was the original problem.) – Lle.4 Jul 10 '21 at 16:24
  • Thanks for that, it is now working great however I need it to be a static version of the sheet, not with references as I am taking a snapshot of a file and the source file will change. – Mee Jul 12 '21 at 05:57
  • Changed my structure of my sheet with the graphs in the same tab and now it is working perfect, thanks anyway much appreciated for your help – Mee Jul 12 '21 at 07:17
  • Ok got it--glad your issue was resolved either way! – Lle.4 Jul 12 '21 at 14:19