Clever Geek Handbook

Copy meaning and format from worksheet to new Google Sheets document?

I need to copy a sheet in Google SpreadSheet to another SpreadSheet.

I did 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 the format, but it keeps the cell references, so I get a pivot error in the new document (#ref). I need a function that copies format and values ​​(not links).

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

   sheet.copyTo(destination);
}

This second method copies unreferenced values, however it only copies unformatted values.

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 preserves the format and copies the values?

+3


source to share


3 answers


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

The script editor autocomplete is a big help in this case to try not to forget it!

I hope the list is complete, it hurts a little to write !!

below, if one of them is not useful to you, just uninstall it (in both directions (install 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:

Brian's answer and your comment made me think of another solution, much simpler, and that merged cells also handle. 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 scripts, make sure the destination sheet name does not exist . I didn't get it right, although it's fairly easy to do it with a try / catch structure.

+5


source


Another way to try it, I think Serge was hinting at 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
}
+3


source


This is Sergey's answer.

This script will copy all visible sheets and export them to a new table 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" */
  }
}
0


source






More articles:


All Articles