Create a copy of a Google Sheet with Google Apps Script

Question

I am new in google apps script. I need help for creating new spreadsheet by using existing spreadsheet and copy and sort the data into newly created spreadsheet using google apps script. Can anyone help me?

Answer 1

this does the trick:

function copySpreadSheet(newSpreadSheetName) {
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getActiveSheet();
  var data = sheet.getSheetValues(1, 1, 1000, 26);  
  var newSheet = SpreadsheetApp.create(newSpreadSheetName)
  newSheet.getSheetByName("Sheet1")
          .getRange(1,1,1000,26).setValues(data);

}

function main() {
  copySpreadSheet("some_Spreadsheet");
}

After executing main, you can find the new spreadsheet in your drive file list. If you want to change the range of the data that should be copied, adjust the getSheetValues() method like this: getSheetValues(startRow, startColumn, numRows, numColumns). Don't forget to do the same for the getRange method a few lines below, otherwise you will get an error.

Answer 2

You can use this simpler code:

function copySpreadSheet() {

  var s = SpreadsheetApp.openById("id"); 
  var destination = DriveApp.getFolderById("idOfFolder");
  DriveApp.getFileById("id_ofFile").makeCopy("New name", destination);
}

This will create a copy of your file in a new destination.

Answer 3

The SpreadsheetApp#spreadsheet class has a method copy:

var wb = SpreadsheetApp.getActiveSpreadsheet();  
var new = wb.copy("Copy of " + wb.getName());

This copy method copies the current spreadsheet and returns a reference to the new one, so additional tasks (such as removing unneeded sheets, formatting ranges, adding additional values, etc.) can be performed.