In Google Sheets, how to copy tab to another tab in SAME workbook using Script, if the dimensions of the starting sheet are dynamic?
Background & goal:
I'm using multiple Google Sheets that are each an interactive interface for multiple users, with various triggers and functions built in. The problem is that, even with instructions, highlighting, and protected ranges, people still find ways to break functions on the sheet. The sheet's added data needs to be copied, collated, then deleted so that the sheet is back in its original configuration for the next day's operations. My goal is to find a more efficient way to do this.
Current solution (clunky):
Currently, I solve this problem with an Apps Script clearRange function. Every night, it takes all 4 tabs in the workbook, clears the formatting and then deletes all rows and columns until each tab is a single cell. Then, the script rebuilds all four tabs, resets the conditional formatting, rewrites the cell functions, and the workbook is ready for the next day.
The problem:
The reset requires a considerable amount of code, currently 217 lines to fully reset the four tabs. The execution time is between 13 and 28 seconds and script failures with errors like Too many simultaneous invocations are not uncommon, especially since the same function is running at roughly the same time on multiple workbooks for different users.
Desired solution:
My present thinking is that the best option is to use a hidden sheet in the workbook, or rather 4 such sheets, each being a hidden template for the four visible pages, complete with conditional formatting and whatnot. The daily reset needs to accommodate ignorant and careless errors, not malicious ones, so I'm not concerned about users hacking into and damaging the hidden template sheets.
So, the ideal new script would start by cutting each sheet down to a single cell (this is still necessary to prevent errors caused by users adding or deleting rows / columns and thus breaking the reset parameters. Then, it would simply copy the cell values and conditional formatting from the template onto the visible sheet (tab). After that, only a couple lines would be needed to reinitialize some IMPORTRANGE functions and the like.
Needed criteria:
- The copy-and-pasting should preferably not rely on fixed row and column figures, but rather be a select-all operation, so that if any changes are made to the template, they will be incorporated automatically the next time the script runs, even if the number of rows and columns in the template changes.
- The process should erase any possible errors or damage done to the visible sheet by users who can’t follow instructions.
- The process should incorporate the conditional formatting of the hidden template, so that it doesn’t have to be re-encoded nightly.
So, how do I go about this? I tried using a macro, but although the copy-all-and-pasting worked when I did it the first time, the script failed to run subsequently. I’ve done searches, but all of the questions seem to be about copying from one spreadsheet to another, not doing so within a workbook, and not to an existing sheet, rather than just duplicating. I’d prefer to avoid sheet duplication, because I like having one of the visible pages being a gid=0 stable URL. Any help would be immensely appreciated. If I can get this far, I’d be a happy, happy clam.
Ultra long-term (bonus) goal:
Beyond this, I’d ideally like to get to the point where I could have a single master template that I could change, and then with a single command push all of those changes out to the several dozen workbooks that currently (with a couple functions variable between users) exist using the same layout. As it stands right now, any changes I make to one workbook have to be encoded in the script, then that new code has to be copied into each of the dozens of copies. I can’t tell if there’s a way to make a master template that could propagate out in that way. It doesn’t seem like there, as least not that would work properly for what I need, because:
- It can’t rely on an continually-updating, dynamic function like IMPORTRANGE; the whole thing already runs about as slowly as people can tolerate, and anything that increase the processing load would be unacceptable,
- I don’t think that copying between multiple Google Sheets workbooks currently copies conditional formatting, which would really be helpful, and
- It seems like the copy-to-another-workbook functions all create a new sheet, which would then run into the problem of the existing template tab still being the source, not the new tab, so that manual changes to every workbook would still be required. If anyone has any ideas how to move in that direction, I’m all ears, but only solving the first problem is essential for the time being. Thank you to anyone who can offer input!
Update
I used Cooper's fix, and it's working -- almost perfectly. It had an issue with adding extra blank rows and failing to copy the cell formatting for any blank rows, as below:
The coding looks like this:
spreadsheet.getSheetByName('Lupin Academy page').showSheet().activate();
spreadsheet.getRangeList(['H6','F6','A1','A6']).clearFormat().clear({contentsOnly: true, skipFilteredRows: true});
spreadsheet.getActiveSheet().setFrozenRows(0);
var maxColumns = spreadsheet.getActiveSheet().getMaxColumns();
if(maxColumns>1){spreadsheet.deleteColumns(2, maxColumns-1);};
var maxRows = spreadsheet.getActiveSheet().getMaxRows();
if(maxRows>1) {spreadsheet.deleteRows(2, maxRows-1);};
var ssh = ss.getSheetByName('TempAP');
var dsh = ss.getSheetByName('Lupin Academy page');
var srg = ssh.getRange(1,1,ssh.getLastRow(),ssh.getLastColumn());
srg.copyTo(dsh.getRange(dsh.getLastRow() + 1, 1));What fixed it (on three of the four tabs) was to swap one part of the code, as follows:
var srg = ssh.getRange(1,1,ssh.getMaxRows(),ssh.getLastColumn());
srg.copyTo(dsh.getRange(dsh.getLastRow() + 1, 1));Using GetMaxRows only from the source captured the correct number of blank rows, and that preserved the formatting on the destination sheet.
However, the search tab is still doing one wonky thing. Compared to the template, it's adding five extra rows. The weird part is that they aren't blank. It's adding a checkbox to each one. If I change the number of rows on the template, it always adds five more to that total. It's not a deal-breaker, but I'd love to know why. Code is:
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Student search page'), true);
spreadsheet.getRangeList(['A1','B6']).clearFormat().clear({contentsOnly: true, skipFilteredRows: true});
spreadsheet.getActiveSheet().setFrozenRows(0);
var maxColumns = spreadsheet.getActiveSheet().getMaxColumns();
if(maxColumns>1){spreadsheet.deleteColumns(2, maxColumns-1);};
var maxRows = spreadsheet.getActiveSheet().getMaxRows();
if(maxRows>1) {spreadsheet.deleteRows(2, maxRows-1);};
var ssh = ss.getSheetByName('TempSP');
var dsh = ss.getSheetByName('Student search page');
var srg = ssh.getRange(1,1,ssh.getMaxRows(),ssh.getLastColumn());
srg.copyTo(dsh.getRange(dsh.getLastRow() + 1, 1));
spreadsheet.getRange('B6').setFormula('=iferror(query(\'Imported student list\'!A6:F,"Select A,B,C,D where LOWER(A) contains LOWER(\'"&B3&"\') LIMIT 30",0),"None")');
spreadsheet.getActiveSheet().setColumnWidth(1, 80)
.setColumnWidth(2, 240)
.setColumnWidth(3, 80)
.setColumnWidth(4, 220)
.setColumnWidth(5, 370)
.setFrozenRows(5);
spreadsheet.getActiveSheet().protect().setUnprotectedRanges([spreadsheet.getRange('A6:A35'), spreadsheet.getRange('B3')])
.setWarningOnly(true);If, however, I add a final row to the template that has text, then the CopyTo function adds five more rows that are totally blank. Any ideas? This is totally bizarre:
I've tried changing the formula, changing where the formula falls in the page-building, which caused a an error message about out-of-bounds columns.
function copyFromOneShtToAnother() {
const ss = SpreadsheetApp.getActive();
const sh = ss.getSheetByName('Sheet0');
const dsh = ss.getSheetByName('Sheet1');
const srg = sh.getRange(1,1,sh.getLastRow(),sh.getLastColumn());
srg.copyTo(dsh.getRange(dsh.getLastRow() + 1, 1));
}