Google Sheet Script to copy a a sheet to a new tab with values and not formulas

user_13868665 0
Viewed 290

I am currently trying to get a sheet to copy to a new tab within the same workbook which is working perfectly fine. However I cannot seem to copy over the the values and format only and the formulas keep coming with it. I have tried to use "{contentsOnly:true}" but it keeps giving me all sorts of errors.

Is anyone able to help with this please

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  // Or DocumentApp or FormApp.
  ui.createMenu('SUBMIT')
      .addItem('SUBMIT', 'menuItem1')
      .addToUi();
}

function menuItem1() {
  SpreadsheetApp.getUi() // Or DocumentApp or FormApp.
     moveValuesDown(); 

}
  
function moveValuesDown() {
  var ss = SpreadsheetApp.getActiveSpreadsheet()
    ss.getSheetByName('Abandoned Phone Calls')
        .copyTo(ss)
        .setName(Utilities.formatDate(new Date(ss.getSheetByName('Abandoned Phone Calls')
            .getRange(1, 1)
            .getValue()), Session.getScriptTimeZone(), "dd MMM yy" ))

}

I have also attached a copy of the sheet i am working on - thanks in advance

Link

google-apps-script google-sheets
1 Answers

Not sure exactly what you tried, but I tested this in your spreadsheet and it works as expected:

function moveValuesDown() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var source_sheet = ss.getSheetByName('Abandoned Phone Calls')
  var new_sheet =  source_sheet.copyTo(ss).setName(Utilities.formatDate(new Date(source_sheet.getRange(1, 1).getValue()),
                   Session.getScriptTimeZone(), "dd MMM yy" ))
  new_sheet.getDataRange().copyTo(new_sheet.getDataRange(),{contentsOnly:true})

}

It is a good idea to create variables when you are planning to use them again, instead of chaining everything together. That can cause confusion, but also you might repeat the same code multiple times.

user_112252910
Related