OK, so my main question here is if I can reduce the time it takes for this script below to run--it takes on average 8 minutes, 5-6 of which is just from copying sheets into another spreadsheet and pasting their original display values over them.
The purpose of this is to have vendor sheets from four different spreadsheets totaled in alphabetical order for sending as-is or for printing. I already researched trying do use PDFs instead, but have not been able to find a way to be able to print in a specific order with the resources I have--which do not include Adobe premium or access to printers for something like powershell scripts, which I do not know anyway. (Printing a folder of files does not actually print in sequential order, unfortunately, so generating PDFs of each sheet does not give me an option to print in alphabetical order.)
So, my workaround is to get an array of the four source spreadsheets, loop through those with another loop that goes through each sheet, and for each sheet make a copy to the merge spreadsheet, and then get and paste over each sheet with the display values as there would otherwise be issues with formulas not referencing. Then it calls other functions to rename the sheets without "Copy of " on them and then to place them in alphabetical order. For ongoing use, it starts by deleting all the old sheets except for an Index sheet from the merged spreadsheet.
Is there any way to reduce the number of calls? I looked into while loops, but it's different in that a lot of the "data" I'm moving is actual entire sheets, and I need their formatting as well. I considered just inserting the same number of sheets as I would be copying, and then repeating copying the same format which they all have, but that hits into an issue with the names--there can sometimes be duplicate named sheets between the source spreadsheets which have to be allowed in, and copying in just makes additional ones have a number at the end. That is fine, but I cannot set the names of the sheets before they are in the merged spreadsheet due to this, unless I can somehow check for duplicate sheet names and add "1", "2", etc, to them to allow them in.
Is the time running this just inevitable for this process, or can I speed it up?
//https://stackoverflow.com/questions/58834873/copy-google-sheet-values-to-another-google-spreadsheet-and-create-new-sheet-with?rq=1
//https://stackoverflow.com/questions/36758479/google-apps-script-to-iterate-through-a-list-of-spreadsheet-ids-and-copy-range
//Global variables. Runs from the merging spreadsheet, and the IDs of the four source Vendor spreadsheets
var mergeSpreadsheet = SpreadsheetApp.getActive();
var indexSheet = "Index"; //global for alphabetzing script
var monthly732 = '1TLH8HencpyH-4pDkQ1LetTErRxqYjh4gYC0XDdQxSVM'
var monthly827 = '19UfhUAvpFi0UJBF-rQoc4LWcL20-79nRltbSL-Wpj0A'
var quarterly732 = '1BRhoO_GcEoBmV_SoaV2xkw9BjgasAX3CorKxgWuEd2I'
var quarterly827 = '1JaAQtRIiCaQjO_A5S0p-VjHk8LUXExmqOYo75LxWf58'
//main function
function mergeTest2(){
deleteAllSheets(); //calls function at end of script to delete old sheets from merged spreadsheet
//Array of source spreadsheets for looping through
var idList = [monthly732, monthly827, quarterly732, quarterly827];
for (var i = 0; i < idList.length; i++){
//get sheets from each source spreadsheet
var allsheets = SpreadsheetApp.openById(idList[i]).getSheets();
//For each spreadsheet, iterate through sheets except for Index and Template
// Stop iteration execution if the condition is meet.
for(var s in allsheets){
var sheet = allsheets[s];
if(
(sheet.getName() == "Index") ||
(sheet.getName() == "Template") ||
(sheet.getName() == "Totals")
) continue;
//For each sheet, copyTo merged spreadsheet, get original's display values and paste them on the copy
//Using copyTo to get format of sheet, and allow for duplicate sheet names added
var mergeSheet = sheet.copyTo(mergeSpreadsheet);
//Getting display values to override formulas to avoid REF errors
var sValues = sheet.getDataRange().getDisplayValues();
mergeSheet.getRange(1,1,sValues.length,sValues[0].length).setValues(sValues);
} //allsheets, end of for-loop for sheets within a spreadsheet
} //end for-loop of array of spreadsheets
//Get numbers of sheets from each spreadsheet for comparing to make sure all applicable ones were copied
//formulas set in Index page for comparing applicable sheets from sources (Vendors only) to make sure they equal merged Vendor sheets at the end
var index_sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(indexSheet);
index_sheet.getRange("C2").setValue(SpreadsheetApp.openById(monthly732).getNumSheets());
index_sheet.getRange("D2").setValue(SpreadsheetApp.openById(monthly827).getNumSheets());
index_sheet.getRange("E2").setValue(SpreadsheetApp.openById(quarterly732).getNumSheets());
index_sheet.getRange("F2").setValue(SpreadsheetApp.openById(quarterly827).getNumSheets());
index_sheet.getRange("B2").setValue(mergeSpreadsheet.getNumSheets());
//Loggers used for testing, replace with getNumSheets above in final
Logger.log(SpreadsheetApp.openById(monthly732).getNumSheets());
Logger.log(SpreadsheetApp.openById(monthly827).getNumSheets());
Logger.log(SpreadsheetApp.openById(quarterly732).getNumSheets());
Logger.log(SpreadsheetApp.openById(quarterly827).getNumSheets());
Logger.log(mergeSpreadsheet.getNumSheets());
slicerOfNames(); //call slicer function below for removing "Copy of " from sheet names.
sortSheetsByName(); //call function to put sheets in alphabetical order
} //end mergeTest2 main function
//Function to get sheet names except for Index, or that all have "Copy of " in name, and slice(8)
function slicerOfNames(){
mergeSpreadsheet.getSheets().forEach(function(sheet) {
var sheetName = sheet.getSheetName();
if (sheetName.indexOf("Copy of ") == -1) {
Logger.log(sheetName);
} else {
sheet.setName(sheetName.slice(8));
} //end if/else
}) //end function(sheet)
} //end slicer function
//Function for alphabetical ordering of sheets within merged spreadsheet
function sortSheetsByName() {
var aSheets = new Array();
var ss = SpreadsheetApp.getActiveSpreadsheet();
var allsheets = ss.getSheets();
for (var s in allsheets)
{
var ss = SpreadsheetApp.getActive();
var sheet = allsheets[s];
if(
(sheet.getName() == indexSheet)
) continue;
aSheets.push(ss.getSheets()[s].getName());
}
if(aSheets.length)
{
ss.getSheetByName(indexSheet).activate()
ss.moveActiveSheet(1)
aSheets.sort();
for (var i = 0; i < aSheets.length; i++)
{
var theSheet = ss.getSheetByName(aSheets[i]);
if(theSheet.getIndex() != i + 2){
ss.setActiveSheet(theSheet);
ss.moveActiveSheet(i + 2);
} //end if statement
} //end for-loop
} // end if(aSheets.length)
}//end alphabetization function
//Function to delete old sheets from merged spreadsheet at the beginning of the script
function deleteAllSheets() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var result = SpreadsheetApp.getUi().alert("This will delete all sheets except the Index, are you sure?", SpreadsheetApp.getUi().ButtonSet.OK_CANCEL);
if(result === SpreadsheetApp.getUi().Button.OK) {
var sheets = ss.getSheets();
for (i = 0; i < sheets.length; i++) {
switch(sheets[i].getSheetName()) {
case "Index":
break;
default:
ss.deleteSheet(sheets[i]);}}
} else {
SpreadsheetApp.getActive().toast("Sheets not deleted");
}
} //end delete sheets function
