Copy value and format from a sheet to a new google Spreadsheet document?

Question

I need to copy a sheet on google SpreadSheet to another SpreadSheet document.

I´ve done my research, and I found two methods that do this, however both have problems that I don´t know how to fix.

The first method copies the sheet with format, but it keeps the referenced cells, so I get a reference error in the new document (#ref). I need a function that copies the format and the values (not references).

function copySheet() {
   var source = SpreadsheetApp.getActiveSpreadsheet();
   var sheet = source.getSheets()[0];
   var destination = SpreadsheetApp.openById("15ucPbZrIYXZAOCYVdpK6OA0oyQT1NcsmuiJmDRfdpHQ");

   sheet.copyTo(destination);
}

This second method copies the values without references, however it copies only values, without format.

function copySheetValues()
{
  var source = SpreadsheetApp.getActiveSheet();
  var sourcename = source.getSheetName();
  var sourceDataRange = source.getDataRange();
  var sourceSheetValues = sourceDataRange.getValues();
  var sourceRows = sourceDataRange.getNumRows();
  var sourceColumns = sourceDataRange.getNumColumns();

  var destination = SpreadsheetApp.openById('15ucPbZrIYXZAOCYVdpK6OA0oyQT1NcsmuiJmDRfdpHQ');
  destination.insertSheet(sourcename, 0);
  destination.getDataRange().offset(0, 0, sourceRows, sourceColumns).setValues(sourceSheetValues);

}

How do I write a function that keeps the format and copies the values?

Answer 1

Since you seem to know how to get and set values using the whole data range, just use the other methods to get and set all the other parameters.

The script editor autocomplete is a great help in this case to try not to forget one !

I hope the list is complete, it is a bit of a pain to write though !.

code below, if one of them is not useful to you just delete it (in both directions (set and get).

function copySheetValues(){
  var source = SpreadsheetApp.getActiveSheet();
  var sourcename = source.getSheetName();
  var sValues = source.getDataRange().getValues();
  var sBG = source.getDataRange().getBackgrounds();
  var sFC = source.getDataRange().getFontColors();
  var sFF = source.getDataRange().getFontFamilies();
  var sFL = source.getDataRange().getFontLines();
  var sFFa = source.getDataRange().getFontFamilies();
  var sFSz = source.getDataRange().getFontSizes();
  var sFSt = source.getDataRange().getFontStyles();
  var sFW = source.getDataRange().getFontWeights();
  var sHA = source.getDataRange().getHorizontalAlignments();
  var sVA = source.getDataRange().getVerticalAlignments();
  var sNF = source.getDataRange().getNumberFormats();
  var sWR = source.getDataRange().getWraps();

  var destination = SpreadsheetApp.openById('15ucPbZrIYXZAOCYVdpK6OA0oyQT1NcsmuiJmDRfdpHQ');
  var destinationSheet = destination.insertSheet(sourcename, 0);
  destinationSheet.getRange(1,1,sValues.length,sValues[0].length).setValues(sValues)
  .setBackgrounds(sBG)
  .setFontColors(sFC)
  .setFontFamilies(sFF)
  .setFontLines(sFL)
  .setFontFamilies(sFFa)
  .setFontSizes(sFSz)
  .setFontStyles(sFSt)
  .setFontWeights(sFW)
  .setHorizontalAlignments(sHA)
  .setVerticalAlignments(sVA)
  .setNumberFormats(sNF)
  .setWraps(sWR);
}

Edit :

Bryan's answer and your comment made me think of another solution, much more simple and that handles merged cells as well. Here is the code :

function copySheetValuesV2(){
  var source = SpreadsheetApp.getActiveSheet();
  var sourceName = source.getSheetName();
  var sValues = source.getDataRange().getValues();
  var destination = SpreadsheetApp.openById('15ucPbZrIYXZAOCYVdpK6OA0oyQT1NcsmuiJmDRfdpHQ');
  source.copyTo(destination)
  var destinationSheet = destination.getSheetByName('Copy of '+sourceName)
  destinationSheet.getRange(1,1,sValues.length,sValues[0].length).setValues(sValues);// overwrite all formulas that the copyTo preserved
}

In both script be sure that the destination sheet name does not already exist. I didn't handle that case although it is quite easy to do with a try/catch structure.

Answer 2

Another route to try that I think Serge was alluding to here.

function myFunction() {
  var source = SpreadsheetApp.openById('SOURCE_ID');
  var sourceSheet = source.getSheetByName('Sheet1');
  var sourceRange = sourceSheet.getDataRange();
  var sourceValues = sourceRange.getValues();

  var tempSheet = source.getSheetByName('temp');
  var tempRange = tempSheet.getRange('A1');

  var destination = SpreadsheetApp.openById('DEST_ID');

  sourceRange.copyTo(tempRange);  // paste all formats?, broken references
  tempRange.offset(0, 0, sourceValues.length, sourceValues[0].length)
  .setValues(sourceValues);  // paste all values (over broken refs)

  tempSheet.copyTo(destination);  // now copy temp sheet to another ss
}

Answer 3

This is a variation upon Sergei's answer.

This script will copy all visible sheets and export them in to a new spreadsheet with " Final" appended to the document title.

function copySheetValuesV4(){
  var sourceSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sourceSheets = sourceSpreadsheet.getSheets();
  var destination = SpreadsheetApp.create(sourceSpreadsheet.getName()+' Final');
    for (var i = 0; i < sourceSheets.length; i++){
      var sourceSheet = sourceSheets[i];
      if (!sourceSheet.isSheetHidden()) {
        var sourceSheetName = sourceSheet.getSheetName();
        var sValues = sourceSheet.getDataRange().getValues();
        sourceSheet.copyTo(destination)
        var destinationSheet = destination.getSheetByName('Copy of '+sourceSheetName).setName(sourceSheetName);
        destinationSheet.getRange(1,1,sValues.length,sValues[0].length).setValues(sValues);// overwrite all formulas that the copyTo preserved */

      }
    destination.getSheetByName("sheet1").hideSheet() // Remove the default "sheet1" */
  }
}