Is there a getter for an entire worksheet or range including formulas, values, merges, formatting etc. in Apps Script?

user_579701 0
Viewed 61

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.

google-apps-script spreadsheet google-sheets
2 Answers

Description

I've created an example where a sheet has formulas, conditional format and data validation. Using testCopyTo I copy Sheet1 to a Backup spreadsheet. All attributes are copied. Using testCopyFrom first copy from the Backup spreadsheet then copy the range within the spreadsheet and finally delete the copy of the backup sheet.

Before restore

enter image description here

After restore

enter image description here

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);
  }
}
user_36567390

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

  • Keep in ind that in Google Drive, multiple spreadsheets with the same name can exist within a folder, this is why it would not make any sense to try and retrieve a spreadsheet by its name
  • Instead, you need to use the method openById(id) or openByUrl(url) (less recommended)
  • To get a sheet from an external spreadsheet and copy it to the current one, you can do the following:
function resetHandler() {
    var destinationWorksheet = SpreadsheetApp.getActiveSpreadsheet();
    backupWorksheet = SpreadsheetApp.openById("123456789").getSheetByName("The Name of the Sheet");
    backupWorksheet.copyTo(destinationWorksheet);
}

where 123456789 is the id of the backup spreadsheet which you can find among others in the url in the address bar, which looks something like https://docs.google.com/spreadsheets/d/1234456789/edit#gid=1392862199

  • To copying a range from a sheet from an external spreadsheet into a sheet in the curent spreadsheet with copyTo is not possible, you can only use getValues() and setValues respectively, however this will not copy the formatting. Also for the formulas, you need to perform additoinally getFormulas() and setFormulas(formulas).

UPDATE

If you would like to copy a range from one sheet into another sheet of the same spreadsheet, you can modify your code as following:

function resetHandler() {
  var activeSpreadsheet = SpreadsheetApp.getActive();
  var destinationSheet = activeSpreadsheet.getSpreadSheetByName("Workorder");
  var destinationRange = destinationSheet.getRange("A1:H132");
  var backupWorksheet = activeSpreadsheet.getSpreadSheetByName("Backup").getRange("A1:H132");
  var backupRange = backupWorksheet.getRange("A1:H132");
  backupRange.copyTo(destinationRange);
}
  • The main problem with your code was that you mixed up the methods copyTo(destination) for ranges with copyTo(spreadsheet).
  • Both methods have the same name, but the first copies a range into another range (into the same sheet or another sheet of the same spreadsheet; overwriting the previous contents), while the other one inserts a whole (additoinal) sheet from a spreadsheet into the same or different spreadsheet
  • Have a careful look at the sample in the documentation for appplying the correct syntax in each case
  • Also: The method getSpreadSheetByName() does not exist, it is getSheetByName()
  • Careful with the wording: Google talks about sheets and spreadsheets, which are the equivalents of Excel worksheets and workbooks respectively.
user_115997890
Related