Google Sheets – Create Copy of Sheet by Copying Values Only

google sheets

I use the below script to basically duplicate an existing worksheet. The worksheet I am copying contains the ImportRange. I don't want the copy spreadsheet to have the formula just the value from the field on the original worksheet. Is there a way of specifying that the inserted sheet only inserts values?

function newSheet() {
  var sheetName = formatDate();
  var ss = SpreadsheetApp.getActiveSpreadsheet(); 
  var templateSheet = ss.getSheetByName("MeetingTemplate");
  ss.insertSheet(sheetName, 4, {template: templateSheet}); 
}

function formatDate() {
  var month, day, d = new Date();
  month = ('0'+(d.getUTCMonth()+1)).slice(-2);
  day = ('0'+d.getUTCDate()).slice(-2);
  return d.getUTCFullYear()+'-'+month+'-'+day;
}

Best Answer

Replace the last line of the first function, 

ss.insertSheet(sheetName, 4, {template: templateSheet});

with the following:

var sheet = ss.insertSheet(sheetName, 4, {template: templateSheet}); 
sheet.getDataRange().copyTo(sheet.getDataRange(), {contentsOnly:true});

Here, the first line gets the reference to the inserted sheet. The second line uses copyTo to copy the inserted sheet into itself, but pasting only the values.

Related Topic