Create a copy of google spreadsheet, without copying script

Question

I want to know if I can make a copy of an existing Spreadsheet through script, but without copying its original script.

I have an yearly spreadsheet that manages all the co-worker's salary. At the end of the year, I want to make a copy of the spreadsheet just to save the data for that year. I will be reusing the original spreadsheet for next year.

So I made a script that creates a new copy of the original spreadsheet. I executed the script then opened script editor, but there was the same script which was also embedded in original spreadsheet.

SpreadsheetApp.getActiveSpreadsheet().copy(); This is what I have written on the script. What should I do to copy spreadsheet only?

`getActiveSpreadsheet().getSheets()` will give you an Array of `Sheet`s, create a new spreadsheet, and for each sheet in the old one, [`sheet.copyTo(newSS)`](https://developers.google.com/apps-script/reference/spreadsheet/sheet#copyTo(Spreadsheet)) — sinaraheneba, Jul 25 '19 at 10:00
I understand. Is there any options to paste sheet with values only? @sinaraheneba — NAMSOO LEE, Jul 25 '19 at 10:12
As other workaround, how about using Sheets API? https://stackoverflow.com/q/51790433/7108653 — Tanaike, Jul 26 '19 at 00:47

score 2 · Answer 1 · answered Jul 25 '19 at 13:50

If you want to save a copy of values only, without script or formulas:

After copying the spreadsheet including structure with SpreadsheetApp.getActiveSpreadsheet().copy(); copy paste the cell contents with getValues() and setValues() within a loop.

function copyPasteValues(){
  var ss=SpreadsheetApp.getActiveSpreadsheet();
  var copy=ss.copy("Copy of spreadsheet "+ss.getId());
  var copyId=copy.getId()  
  var sheetNumber=ss.getSheets().length;
  for(var i=0; i<sheetNumber;i++)  {
    var values=ss.getSheets()[i].getDataRange().getValues();
    SpreadsheetApp.openById(copyId).getSheets()[i].getDataRange().setValues(values);
  }

If it is only the script that you want to get rid of (cells do not contain formulas):

Use the method CopyTo() for copying sheets:

function copyPasteSheet(){
  var ss=SpreadsheetApp.getActiveSpreadsheet();
  var destination=SpreadsheetApp.openById('DestinationID')
  var sheetNumber=ss.getSheets().length;
  for(var i=0; i<sheetNumber;i++)  {
    var values=ss.getSheets()[i].copyTo(destination);
  }
}

Create a copy of google spreadsheet, without copying script

1 Answers1

If you want to save a copy of values only, without script or formulas:

If it is only the script that you want to get rid of (cells do not contain formulas):