0
votes

Okay here is what I am trying to do:

  1. Create a new spreadsheet via my Function
  2. Copy a sheet from current spreadsheet VALUES ONLY into new spreadsheet that was created

The idea is I have a dynamic Google spreadsheet pulling in Data from various Google APIs and scripts but at the end of every month I want to be able to archive the daily values elsewhere.

I can do steps 1 and 2 above except for the important part I need this little archive function to only copy the values from the original sheet and not all the formulas to the other dynamic sheets.

Here is the code I have:

    function archivedata(){
      //creates my archive spreadsheet
       var ss1 = SpreadsheetApp.getActiveSpreadsheet();
       ss1.setActiveSheet(ss1.getSheetByName("Sheet1"));
       var ssNew = SpreadsheetApp.create("New spreadsheet").getId();
       var ss2 = SpreadsheetApp.openById(ssNew).setActiveSpreadsheet;
       ss1.copyTo(ssNew)
       ssNew.renameActiveSheet("newsheet");;
    }
1

1 Answers

0
votes
function archivedata(){
  //creates my archive spreadsheet
   var ss1 = SpreadsheetApp.getActiveSpreadsheet();
   var data = ss1.getSheetByName("Sheet1").getDataRange().getValues();
   var ss2 = SpreadsheetApp.create("New spreadsheet");
   ss2.getActiveSheet().getRange(1,1,data.length,data[0].length).setValues(data);
   ss2.renameActiveSheet("newsheet");
}