Create a copy of google spreadsheet, without copying script

2
votes

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?

1
google-apps-script
getActiveSpreadsheet().getSheets() will give you an Array of Sheets, create a new spreadsheet, and for each sheet in the old one, sheet.copyTo(newSS) – sinaraheneba
I understand. Is there any options to paste sheet with values only? @sinaraheneba – NAMSOO LEE
As other workaround, how about using Sheets API? stackoverflow.com/q/51790433/7108653 – Tanaike

1 Answers

2
votes

  • 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);
  }
}