Copying a spreadsheet values to a new sheet monthly

Question

I am trying to create a backup function for my spreadsheet using Google script editor, am using the following code that I found in Github:

function makeCopy(){

 var formattedDate= Utilities.formatDate(new Date(),"GMT","yy:mm:dd''HH:mm:ss");

 var saveAs =  "Copy " + formattedDate;

 var destinationFolder = DriveApp.getFolderById("ID");

 DriveApp.getFileById(SpreadsheetApp.getActiveSpreadsheet().getId())
 .makeCopy(saveAs,destinationFolder);

 }

but I have two problems:

• I can't use it with a sheet that uses IMPORT-AGE.
• I don't want to get an active spreadsheet, instead I want to use the sheet's ID and am not sure how to do that.

Answer 1

Explanation:

Unfortunately:

• importranges can not be allowed programmatically. So you need to set get the values from the source spreadsheet and paste them to the newly created (target) spreadsheet.

• copyTo can not be used between two different spreadsheets, so you can use getValues and setValues instead.

• The logic is to iterate over the source sheets and for every sheet get values and copy them to the corresponding target sheets.

Additional Modification points:

• In the date object, you specified "yy:mm:dd''HH:mm:ss" but mm gives the minutes. In the first set of strings I think you want to get the month, therefore use MM instead. Check also the official documentation regarding the dates.

• Instead of passing a hardcopy value GMT get the timezone of your spreadsheet file by using getSpreadsheetTimeZone() instead.

With the above modifications, you have:

const formattedDate = Utilities.formatDate(new Date(),source_ss.getSpreadsheetTimeZone(),"yy:MM:dd''HH:mm:ss");

Solution:

function makeCopy() {
  const source_id = "SpreadsheetID"; // add the id of the spreadsheet to copy
  const source_ss = SpreadsheetApp.openById(source_id);
  const formattedDate = Utilities.formatDate(new Date(),source_ss.getSpreadsheetTimeZone(),"yy:MM:dd''HH:mm:ss");
  const saveAs =  "Copy " + formattedDate;
  const destFolder = DriveApp.getFolderById("FolderID"); // add the destination folder ID
  const file = DriveApp.getFileById(source_id).makeCopy(saveAs, destFolder);
  const target_ss = SpreadsheetApp.openById(file.getId());
  const source_sheets = source_ss.getSheets();
  const target_sheets = target_ss.getSheets();
  source_sheets.forEach((sh,i)=>{
     let values = sh.getDataRange().getValues();
     target_sheets[i].getRange(1,1,values.length,values[0].length).setValues(values);
     SpreadsheetApp.flush();
  })
}