Copy Google Sheet Values to another Google Spreadsheet and create new sheet with a button

Question

I'm hoping you can help, I use Google Sheets for predominately all my work now and trying to make it a bit autonomous.

So I currently have a template sheet that is copied multiple times to be used on different projects. I want to write a script that when I click a button on Sheet1 ('Home') on any copies of the template it copies all values from Sheet14 ('Workshop Prep') which is formulated from the other sheets onto another existing spreadsheet and create a new sheet.

I have tried various different scripts and used "copyto" which does work but does not copy values only the formulas. I have tried CopyDatatoNewFile but this has to overwrite an existing sheet on the other spreadsheet. I can't use getActiveSheet in the script because I want the button to be on a different sheet.

Any help would be much appreciated. Apologies this is my first time posting.

Thanks

Edit 1 - Some of the scripts I have tried:

  var source = SpreadsheetApp.getActiveSheet();
  var sheet = source.getSheets()[14];
  var sValues = source.getDataRange().getValues();
  var destination = SpreadsheetApp.openById('1-oro3Vf_YXH6QvQ_oQlTL723TTdACo3YxMI7XVnYsqI');
  source.copyTo(destination)
  var destinationSheet = destination.getSheetByName('Copy of '+sourceName)
  destinationSheet.getRange(1,1,sValues.length,sValues[0].length).setValues(sValues);

EDIT 2 - Error - TypeError: Cannot find function getSheet in object Sheet. (line 3, file "CopyTo")

Welcome to SO, please show what you've tried so far to make it easier for others to provide you with an answer. — ross

Andres Duarte Andres Duarte · Accepted Answer · 2019-11-13T15:50:10

To copy the values and not the formulas you need to use getDisplayValues() function [1] instead of getValues(). This is the code I came up with:

function myFunction() {
  var source = SpreadsheetApp.getActiveSpreadsheet();
  var sourceSheet = source.getSheetByName("Workshop Prep");

  var destination = SpreadsheetApp.openById('[SPRADSHEET-ID]');
  var destinationSheet = sourceSheet.copyTo(destination);

  var sValues = sourceSheet.getDataRange().getDisplayValues();
  destinationSheet.getRange(1,1,sValues.length,sValues[0].length).setValues(sValues);
}

[1] https://developers.google.com/apps-script/reference/spreadsheet/range#getDisplayValues()

Copy Google Sheet Values to another Google Spreadsheet and create new sheet with a button

1 Answers