Is there a getter for an entire worksheet or range including formulas, values, merges, formatting et-CodePudding

I'm trying to make a backup of an entire worksheet, including formulas, values, formatting, row and column size, cell merges, etc. so that when a user is finished editing I can reset the sheet. Currently I'm using a Range.getFormulas() to create a stringified object (that I can then paste into my code as a constant) to reset all of the content of the cells, but if the user changes the row size or deletes a cell, I'd like to be able to quickly rebuild the entire sheet without iterating through rows and columns (Apps Script is too slow for that). My previous method was to create a duplicate of the sheet and simply hide it, but someone can still unhide and edit that.

I've been digging through the documentation, but I haven't found anything useful. To sum up, I'd like to have something like this:

function resetHandler() {
    var destinationWorksheet = SpreadsheetApp.getActiveSpreadsheet().getRange("A1:H132"),
        backupWorksheet = [...object...];
    backupWorksheet.copyTo(destinationWorksheet);
}

where "[...object...]" is the output of a getter that contains the entire sheet as an object. I tried JSON.stringify(SpreadsheetApp.getActive().getSpreadSheetByName("Workorder").getRange("A1:H132")) but it just outputs "{}" since the Range class is all private.

If there isn't a way, I can always fall back on a hidden backup sheet.

CodePudding user response：

There is no Apps Script method to retrieve a spreadsheet by its name, you need to retrieve it by

After restore

Script

function testCopyTo() {
  try {
    let source = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
    let dest = SpreadsheetApp.openById("xxxx.......");
    source.copyTo(dest);
  }
  catch(err) {
    console.log(err);
  }
}

function testCopyFrom() {
  try {
    let dest = SpreadsheetApp.getActiveSpreadsheet();
    let source = SpreadsheetApp.openById("xxxx.......").getSheetByName("Copy of Sheet1");
    source.copyTo(dest);
    source = dest.getSheetByName("Copy of Copy of Sheet1");
    dest = dest.getSheetByName("Sheet1");
    source.getDataRange().copyTo(dest.getRange(dest.getLastRow() 1,1));
    SpreadsheetApp.getActiveSpreadsheet().deleteSheet(source);
  }
  catch(err) {
    console.log(err);
  }
}