Google apps script getvalue and setvalue to another sheet take 3 minutes and want to simplify

Question

I have the following functions and it takes way too much time to execute, is there any way to simplify it?

In short,

1. I want to copy 10 docs

2. Paste them to 10 different folders

3. Rename the 10 new docs

4. Copy 5 ranges from each of the 10 original docs, each of which in different sheet names

5. Paste the above mentioned 5 ranges to 10 new docs respectively

These are my functions

function newcopy(Sheet,Folder) {  

var sss = SpreadsheetApp.openById(Sheet)

//getting the name of the new doc

var d = new Date();

var curr_month = d.getMonth();

var curr_year = d.getFullYear();

var theDate = curr_month + curr_year;

var newFileName = "Billing " + theDate;

//I want it to be Billing 92021, instead it becomes Billing 2030

var destFolder = DriveApp.getFolderById(Folder);

var tsss = DriveApp.getFileById(sss.getId()).makeCopy(newFileName, destFolder);

var tssid = tsss.getId()

var tss = SpreadsheetApp.openById(tssid)

//copy 1st range

var ss = sss.getSheetByName('Budget');

var sr = ss.getRange("I7:J29");

var format = sr.getNumberFormats();

var values = sr.getValues();

var bGcolors = sr.getBackgrounds();

var colors = sr.getFontColors();

var fontSizes = sr.getFontSizes();

//copy 2nd range

//not neccessary AA1:AH10000,but I don't know how to get the last row

var ss2 = sss.getSheetByName('Billing(last month)');

var sr2 = ss2.getRange("AA1:AH10000");

var format = sr2.getNumberFormats();

var values = sr2.getValues();

var bGcolors = sr2.getBackgrounds();

var colors = sr2.getFontColors();

var fontSizes = sr2.getFontSizes();

//copy 3rd range

var ss3 = sss.getSheetByName('Billing(YE to last month)');

var sr3 = ss3.getRange("AA1:AH10000");

var format = sr3.getNumberFormats();

var values = sr3.getValues();

var bGcolors = sr3.getBackgrounds();

var colors = sr3.getFontColors();

var fontSizes = sr3.getFontSizes();

//copy 4th range

var ss4 = sss.getSheetByName('Outstanding(last month)');

var sr4 = ss4.getRange("AA1:AN10000");

var format = sr4.getNumberFormats();

var values = sr4.getValues();

var bGcolors = sr4.getBackgrounds();

var colors = sr4.getFontColors();

var fontSizes = sr4.getFontSizes();

//copy 5th range

var ss5 = sss.getSheetByName('Outstanding(last month)');

var sr5 = ss5.getRange("AA1:AN10000");

var format = sr5.getNumberFormats();

var values = sr5.getValues();

var bGcolors = sr5.getBackgrounds();

var colors = sr5.getFontColors();

var fontSizes = sr5.getFontSizes();

//paste 1st range

var ts = tss.getSheetByName('Budget');

var tr = ts.getRange("I7:J29");

tr.setNumberFormats(format);

tr.setValues(values);

tr.setBackgrounds(bGcolors);

tr.setFontColors(colors);

tr.setFontSizes(fontSizes);

//paste 2nd range

var ts2 = tss.getSheetByName('Billing(last month)');

var tr2 = ts2.getRange("AA1:AH10000");

tr2.setNumberFormats(format);

tr2.setValues(values);

tr2.setBackgrounds(bGcolors);

tr2.setFontColors(colors);

tr2.setFontSizes(fontSizes);

//paste 3rd range

var ts3 = tss.getSheetByName('Billing(YE to last month)');

var tr3 = ts3.getRange("AA1:AH10000");

tr3.setNumberFormats(format);

tr3.setValues(values);

tr3.setBackgrounds(bGcolors);

tr3.setFontColors(colors);

tr3.setFontSizes(fontSizes);

//paste 4th range

var ts4 = tss.getSheetByName('Outstanding(last month)');

var tr4 = ts4.getRange("AA1:AN10000");

tr4.setNumberFormats(format);

tr4.setValues(values);

tr4.setBackgrounds(bGcolors);

tr4.setFontColors(colors);

tr4.setFontSizes(fontSizes);

//paste 5th range

var ts5 = tss.getSheetByName('Outstanding(Today)');

var tr5 = ts5.getRange("AA1:AN10000");

tr5.setNumberFormats(format);

tr5.setValues(values);

tr5.setBackgrounds(bGcolors);

tr5.setFontColors(colors);

tr5.setFontSizes(fontSizes)}



function newpaste () {

//run the above function 10times

copy("Sheet1","Folder1")

copy("Sheet2","Folder2")

copy("Sheet3","Folder3")

copy("Sheet4","Folder4")

copy("Sheet5","Folder5")

copy("Sheet6","Folder6")

copy("Sheet7","Folder7")

copy("Sheet8","Folder8")

copy("Sheet9","Folder9")

copy("Sheet10","Folder10")

}

Answer 1

I don't know how could it be speed up significantly, but I see how you can short it:

function newcopy(Sheet, Folder) {

    var sss = SpreadsheetApp.openById(Sheet);

    var d           = new Date();
    var curr_month  = d.getMonth();
    var curr_year   = d.getFullYear();
    var theDate     = '' + curr_month + curr_year;
    var newFileName = 'Billing ' + theDate;

    var destFolder  = DriveApp.getFolderById(Folder);
    var tsss        = DriveApp.getFileById(sss.getId()).makeCopy(newFileName, destFolder);
    var tssid       = tsss.getId();
    var tss         = SpreadsheetApp.openById(tssid);

    // all names of the sheets and its ranges can be gathered 
    // in one array of objects [{name,range},{name,range}...]

    var sheets = [
        {name: 'Budget',                    range: 'I7:J29'},
        {name: 'Billing(last month)',       range: 'AA1:AH10000'},
        {name: 'Billing(YE to last month)', range: 'AA1:AH10000'},
        {name: 'Outstanding(last month)',   range: 'AA1:AH10000'},
        {name: 'Outstanding(last month)',   range: 'AA1:AH10000'}
    ]

    // then they can be iterated in loop
    // you can call name of a sheet as 'sheet.name'
    // and get a 'a1' notation of its range as 'sheet.range'

    for (var sheet of sheets) {
        var ss = sss.getSheetByName(sheet.name);
        var sr = ss.getRange(sheet.range);

        var ts = tss.getSheetByName(sheet.name);
        var tr = ts.getRange(sheet.range);

        // these getValues and setValues commands are
        // most time consuming, but there is no way to avoid them
        // in this particular algorithm

        // sr.copyTo(tr); <-- it would work much faster
        // but it copies ranges within the same spreadsheet only

        var format    = sr.getNumberFormats();
        var values    = sr.getValues();
        var bGcolors  = sr.getBackgrounds();
        var colors    = sr.getFontColors();
        var fontSizes = sr.getFontSizes();

        tr.setNumberFormats(format);
        tr.setValues(values);
        tr.setBackgrounds(bGcolors);
        tr.setFontColors(colors);
        tr.setFontSizes(fontSizes);
    }
}

function newpaste() {
    newcopy('Sheet1', 'Folder1');
    newcopy('Sheet2', 'Folder2');
    newcopy('Sheet3', 'Folder3');
    newcopy('Sheet4', 'Folder4');
    newcopy('Sheet5', 'Folder5');
    newcopy('Sheet6', 'Folder6');
    newcopy('Sheet7', 'Folder7');
    newcopy('Sheet8', 'Folder8');
    newcopy('Sheet9', 'Folder9');
    newcopy('Sheet10','Folder10');
}

But I didn't test it. So it's up to you to debug it.

If you want to get ranges dynamically based on last row on the sheet you I'd change the object this way:

var sheets = [                   // define the ranges without last row numbers
    {name: 'Budget',                    range: 'I7:J'},   
    {name: 'Billing(last month)',       range: 'AA1:AH'},
    {name: 'Billing(YE to last month)', range: 'AA1:AH'},
    {name: 'Outstanding(last month)',   range: 'AA1:AH'},
    {name: 'Outstanding(last month)',   range: 'AA1:AH'}
]

And change define the ranges this way:

var ss = sss.getSheetByName(sheet.name);
var last_row = ss.getLastRow();                // get last row number
var sr = ss.getRange(sheet.range + last_row);  // add the number to the range
var ts = tss.getSheetByName(sheet.name);
var tr = ts.getRange(sheet.range + last_row);  // add the number to the range

But it unlikely makes the script faster. You reduced amount of the copied data, but you added one more call to server. I suspect the result will be about the same.