Google-sheets – Cloning a sheet without the formula(s)

google sheetsgoogle-apps-script

I have a generated number based by row (Google Form response). However, doing this limits my ability to filter.

I want to create a secondary sheet that updates regularly with the values only (no formulas) then I will gain the function to filter again.

Current sheet called "Ticket Creation" and the New Sheet created/updated called "Active Tickets+".

function cloneGoogleSheet() {
  /* Copy current sheet and replaced it >.< */
  var name = "labnol";
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Ticket Creation');
  sheet.getDataRange().copyTo(sheet.getDataRange(), {contentsOnly:true});

  /* Before cloning the sheet, delete any previous copy */
  var old = ss.getSheetByName(name);
  if (old) ss.deleteSheet(old); // or old.setName(new Name);

  SpreadsheetApp.flush(); // Utilities.sleep(2000);
  sheet.setName('Active Tickets+');

  /* Make the new sheet active */
  ss.setActiveSheet(sheet);

}

Currently this will clone to the same sheet (Ticket Creation) and remove the formulas. I'm just starting to play with scripting.

Best Answer

On the following line you are using the same object as source and destination:

sheet.getDataRange().copyTo(sheet.getDataRange(), {contentsOnly:true});

You should use a different range as destination. By the other hand, the order of the statements is wrong because your code clear/create the destination spredsheet after the copyTo statement.

Suggestion, inverse the order and correct the destination of copyTo properly.

From https://developers.google.com/apps-script/reference/spreadsheet/range#copytodestination-options

 // The code below will copy only the values of the first 5 columns over to the 6th column.
 var sheet = SpreadsheetApp.getActiveSheet();
 sheet.getRange("A:E").copyTo(sheet.getRange("F1"), {contentsOnly:true});
 }
Related Topic