CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Elad Benda
Elad Benda

Reputation: 36654

How to copy sheet content to another sheet?

I want to copy a sheet#1 content to override the content of another sheet#2, but without deleting sheet#2 as my code currently does.

Is there any API to do so?

  newGSheet.getSheets()[0].copyTo(oldGSheet);
  
  var oldSheet = oldGSheet.getSheetByName('Sheet1');
  if (oldSheet != null) {
 oldGSheet.deleteSheet(oldSheet);
    oldGSheet.getSheetByName('Copy of Sheet1').setName('Sheet1');
 }

Upvotes: 1

Views: 140

Answers (1)

Marios
Marios

Reputation: 27350

Explanation:

The logic would be quite simple.

  • You get the data newData of the new sheet newSheet which is a 2D array.

  • You check if Sheet1 exists in the old spreadsheet oldGSheet. If it does not exist, copy newSheet to oldGSheet and name it Sheet1. If Sheet1 exists already, then clear its contents and paste (set) newData to it.

Solution:

  const newSheet = newGSheet.getSheets()[0];
  // get the data of newSheet
  const newData = newSheet.getRange(1,1,newSheet.getLastRow(),newSheet.getLastColumn()).getValues();
  const oldSheet = oldGSheet.getSheetByName('Sheet1');
  
  // if Sheet1 does not exists, then create it.
  // if Sheet1 exists, clear its contents and paste newData
  
  if(oldSheet == null){
  newSheet.copyTo(oldGSheet).setName('Sheet1');
  }
  else {
  oldSheet.clearContents();
  oldSheet.getRange(1,1,newData.length,newData[0].length).setValues(newData);
  }

Upvotes: 2

Related Questions