CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-drive-api
Justine Chacko
Justine Chacko

Reputation: 23

Merge n Google spreadsheets files to n sheets

Say, I have 100 google spreadsheets file and I need to merge into one file with 100 tabs(sheets). Is it possible with app script. Currently I have a code below,

function popForm() {
  var ss = SpreadsheetApp.getActive();
  for(var b = 0; b<100; b++){
  var sheet = ss.getSheetByName('Sheet '+b);
  var numberRows = sheet.getDataRange().getNumRows();
....
....

The above code will work with spreadsheets with 100 tabs(Sheets). But I have 100 sreadsheets with one tab(Sheet).

Upvotes: 0

Views: 117

Answers (1)

Marios
Marios

Reputation: 27348

Explanation:

  • You have a folder with FolderId that contains all the spreadsheet files. You find this folder using the getFolderById() method.
  • You get the files of this folder using the getFiles() method and you use a while loop to iterate over these files.
  • For every file, you get the first sheet and you copy it using the copyTo() method to the source (active) spreadsheet file.
  • The newly create sheet will be named after the name of the spreadsheet file that it came from. Feel free to change this part or let me know so I can modify it.

Solution:

You must be looking for this:

function myFunction() {

const source_sh = SpreadsheetApp.getActive();  
const folder = DriveApp.getFolderById('FolderId');
const files = folder.getFiles();
  
while (files.hasNext()) { 
    var file = files.next();    
    var target_ss = SpreadsheetApp.openById(file.getId()); 
    var target_sh = target_ss.getSheets()[0];
    target_sh.copyTo(source_sh).setName(file.getName());
    }
}

Upvotes: 2

Related Questions