CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-drive-api
Ivan Sinigaglia
Ivan Sinigaglia

Reputation: 1072

Copy one sheet to many others spreadsheets at the same folder

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:

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.

Upvotes: 1

Views: 1067

Answers (1)

Marios
Marios

Reputation: 27350

Explanation / Issue:

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

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].

Upvotes: 2

Related Questions