Duplicate worksheet with macro

Plugins
1

Hello,

I’m using the spreadsheet from OnlyOffice (via the kDrive infomaniak), and I would like to know how to duplicate a whole worksheet using a macro.

The use case is very basic : I have a template worksheet, which contains a lot of elements, shapes, formulas, and so on, and once a month, I would like to create a new worksheet from this template sheet.

I’ve only managed to create a blank sheet and copy/paste its content in the new sheet, but it lack of the column sizing, the objects, etc.

I didn’t find any solution in the docs.

Did I miss something, or is this impossible to do for now ?

2

hi @NainzguL

Unfortunately, there is no separate method to implement your scenario similar to Worksheet.Copy in Excel.
We need some time to discuss it internally. I will notify you when we have something to share.

4

Ok, thank you for the quick answer, I’ll do it manually for now and wait for news on that topic.

5

:ok_hand:

We would be delighted if you could share your efforts in creating the macro with us.
I believe many others who have the same question would be grateful for your code.

6

I didn’t go very far in this, but I can share with you the beginning :

(function()
{
    var oTechnicalSheet = Api.GetSheet("Technical");
    // Get Sheet name from a technical sheet that contains month names (in my locale)
    var sNewSheetName = oTechnicalSheet.GetRange("B5").GetValue2() + " " + oTechnicalSheet.GetRange("B6").GetValue2();
    var oCurrentMonth = Api.GetSheet(sNewSheetName);
    // If sheet don't exists, then create it
    if(oCurrentMonth === null){
        //Create sheet don't return the new sheet, it's a bit weird to me, but that's how it works
        Api.AddSheet(sNewSheetName);
        oCurrentMonth = Api.GetSheet(sNewSheetName);

        // "Duplicate" templates
        var oTemplateSheet = Api.GetSheet("Template");
        oTemplateSheet.GetRange("A1:L72").Copy(oCurrentMonth.GetRange("A1:L72"));
        oCurrentMonth.GetRange("A1").SetValue(oTechnicalSheet.GetRange("B3").GetValue2());
    }
})();

I was about to try to recover the objects from the templates using GetAllShapes(), but then I realized that I needed to create a new of each kind using AddShape(), and that’s where I left my work : translate the shapes were to much of work regarding the “right clic → Copy” on the sheet once a month :slight_smile:

I also planned to set the column width manually using SetColumnWidth() (no link for this one, I’m not allowed :frowning: ), but as for above, it was a lot of work, for something that would break every time I update the template sheet.

I hope that might helps

1 Like