Replace entire sheet with another in Google Apps Scripts

Question

What I'd like to do is warehouse information from a particular sheet within a spreadsheet and copy it to a second spreadsheet at the end of every day. The second spreadsheet will run complex pivots and reports against the copied information that don't need to run throughout the day.

I can set up a time-driven trigger which will run the job every day within an hour block.

I'm working on the following script which uses SpreadsheetApp.getActiveSpreadsheet to get the current Spreadsheet. Then gets the individual sheet to backup with spreadsheet.getSheetByName. And then uses the sheet.copyTo method to add the current sheet to a new spreadsheet. I'm getting the new spreadsheet by looking up the id with SpreadsheetApp.openById all like this:

function startBackupJob() {

  var currentSpreadSheet = SpreadsheetApp.getActiveSpreadsheet()
  var masterSheet = currentSpreadSheet.getSheetByName("Sheet1")

  var backupSpreadSheetId = "#######################################";
  var backupSpreadSheet = SpreadsheetApp.openById(backupSpreadSheetId);

  // var backupSheet = backupSpreadSheet.getSheetByName("Sheet1");
  // backupSpreadSheet.deleteSheet(backupSheet);

  masterSheet.copyTo(backupSpreadSheet).setName("Sheet1");

}

The issue I'm having is that copyTo will create a new worksheet rather than overwrite the existing spreadsheet. The point of moving to the new workbook is to run pivot tables off the data and not re-wire them to point to a new sheet.

I can delete the previous sheet to make room for the new one, but this kills the references on the PivotTable as well, so it doesn't help much.

Is there an easy way to transfer the entire contents of one worksheet to another?

---

This is similar to (but different from) the following questions:

• How do I script making a backup copy of a spreadsheet to an archive folder? - However, I don't want to move the whole file, but a specific sheet within the spreadsheet.
• How can copy specifics sheet to another spreadsheet using google script & copy one spreadsheet to another spreadsheet with formatting - However copying produces a new sheet, whereas I need to replace the contents of an existing sheet
• Scripts, copy cell from one sheet to another sheet EVERYDAY at a specific time - However, I do want to replace the entire sheet, rather than just specific cells within the sheet.

---

Update

I might be able to do this by calling getRange on each sheet and then using getValues and setValues like this:

var currentValues = masterSheet.getRange(1, 1, 50, 50).getValues()
backupSheet.getRange(1, 1, 50, 50).setValues(currentValues)

But I'm worried about edge cases where the master sheet has a different available range than the backup sheet. I also don't want to hardcode in the range, but for it to encompass the entire sheet. If I call .getRange("A:E") then the two worksheets have to have the exact same number of rows which is not likely.

Answer 1

Your update has you about 90% of the way there. The trick is to explicitly check the size of the destination sheet before you copy data into it. For example, if I did something like this:

var cromulentDocument = SpreadsheetApp.getActiveSpreadsheet();
var masterSheet = cromulentDocument.getSheetByName('master');
var logSheet = cromulentDocument.getSheetByName('log');
var hugeData = masterSheet.getDataRange().getValues();
var rowsInHugeData = hugeData.length;
var colsInHugeData = hugeData[0].length;

/* cross fingers */
logSheet.getRange(1, 1, rowsInHugeData, colsInHugeData).setValues(hugeData);

...then my success would totally depend upon whether logSheet was at least as big as masterSheet. That's obvious, but what's less so is that if logSheet is bigger then there will be some old junk left over around the edges. Ungood.

Let's try something else. As before, we'll grab the master data, but we'll also resize logSheet. If we don't care about logSheet being too big we could probably just clear() the data in it, but let's be tidy.

var cromulentDocument = SpreadsheetApp.getActiveSpreadsheet();
var masterSheet = cromulentDocument.getSheetByName('master');
var logSheet = cromulentDocument.getSheetByName('log');
var hugeData = masterSheet.getDataRange().getValues();
var rowsInHugeData = hugeData.length;
var colsInHugeData = hugeData[0].length;

/* no finger crossing necessary */
var rowsInLogSheet = logSheet.getMaxRows();
var colsInLogSheet = logSheet.getMaxColumns();

/* adjust logSheet length, but only if we need to... */
if (rowsInLogSheet < rowsInHugeData) {
  logSheet.insertRowsAfter(rowsInLogSheet, rowsInHugeData - rowsInLogSheet);
} else if (rowsInLogSheet > rowsInHugeData) {
  logSheet.deleteRows(rowsInHugeData, rowsInLogSheet - rowsInHugeData);
}

/* likewise, adjust width */
if (colsInLogSheet < colsInHugeData) {
  logSheet.insertColumnsAfter(colsInLogSheet, colsInHugeData - colsInLogSheet);
} else if (colsInLogSheet > colsInHugeData) {
  logSheet.deleteColumns(colsInHugeData, colsInLogSheet - colsInHugeData);
}

/* barring typos, insert data with confidence */
logSheet.getRange(1, 1, rowsInHugeData, colsInHugeData).setValues(hugeData);

What's going on here is pretty straightforward. We figure out how big the log needs to be, and then adjust the destination sheet's size to match that data.