Copy one sheet to many others spreadsheets at the same folder

Question

I have many spreadsheets inside a folder and today I have developed a new sheet (tab) inside one of these spreadsheets. I have a code to copy and paste one sheet to another spreadsheet, but for this there is another way without code.

My goal is:

a way to copy one sheet (order number 13) to all other spreadsheets at the same folder.

This is my code for copying one-to-one:

function sendspreadsheet(){
var source = SpreadsheetApp.getActiveSpreadsheet();
var aba = source.getSheets()[13];
var destination = SpreadsheetApp.openByUrl('https:sheetID');
aba.copyTo(destination);}

I have a great help from @Tanaike about similar issue at post: "Array for Google Sheet celarcontet" and I have studied this code and Google Class Sheet, but something is not working.

Code I'm trying to copy one-to-multiple spreadsheets at the same folder:

     Function sendtomultiple(){
        var source = SpreadsheetApp.getActiveSpreadsheet();
        var aba = source.getSheets()[13];
        var destination = DriveApp.getFolderById('1o6p-53Q1ntJAVIJt9w4k0UK___XXXXXX');
          var sheetDestino = destination.getFilesByType(MimeType.GOOGLE_SHEETS);
        while (sheetDestino.hasNext()) {
            SpreadsheetApp.open(sheetDestino.next()).getSheets().forEach(aba => {
              aba.copyTo(sheetDestino);          
            });
        };
       }

I guess this line into while instruction is the problem, I have tried other options, but all them return error message, kind this:

Exception: The parameters (DriveApp.FileIterator) don't match the method signature for SpreadsheetApp.Sheet.copyTo.

Hope someone can help to fix this.

Oh, sorry. I will fix this right now. Yes @Mario, one sheet to multiple spreadsheets at the same folder. — Ivan Sinigaglia

soMario soMario · Accepted Answer · 2021-03-04T22:44:50

Explanation / Issue:

Your goal is to copy a particular sheet (tab) into multiple spreadsheets within the same folder.

The issue has do to with the fact that sheetDestino.next() is a file object but copyTo(spreadsheet) accepts a spreadsheet object.
You can get the id of the file and then use openById(id) to get the spreadsheet object which you can use to copy the sheet to.

Bonus code:

I added an if condition to make sure the code does not try to copy the sheet to the origin spreadsheet file.
I added a code to rename the copied sheet to the destination sheet to the original name. The default would be copy of Sheet14...

Solution:

function sendtomultiple(){
    const source = SpreadsheetApp.getActiveSpreadsheet();
    const source_id = source.getId();
    const aba = source.getSheets()[13];
    const destination = DriveApp.getFolderById('1o6p-53Q1ntJAVIJt9w4k0UK___XXXXXX');
    const sheetDestino = destination.getFilesByType(MimeType.GOOGLE_SHEETS);
    while (sheetDestino.hasNext()) {
          let target_file = sheetDestino.next();
          let target_id = target_file.getId();
          let target_ss = SpreadsheetApp.openById(target_id);
          if(target_id!=source_id){
             aba.copyTo(target_ss).setName(aba.getName());          
          } 
    };
}

Keep in mind that sheet getSheets()[13] is the 14th sheet in the spreadsheet file. If you want to get the 13th sheet you need to use getSheets()[12].

Copy one sheet to many others spreadsheets at the same folder

1 Answers

Explanation / Issue:

Solution: