CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Asking Bob
Asking Bob

Reputation: 68

How to combine multiple spreadsheet into one sheet using ID with Google App Script

I just want to ask about how to combine data from multiple spreadsheets into one sheet by using their ID. So, I made 2 sheets in the Master spreadsheet (1 for Data Master and 1 for ID list). I want to take the ID from ID List Sheet to get the file and a sheet that I would like to combine. I have using the DriveApp.getFileById but it's still not working. My current Script is using the file name list to access the data on sheet2 only and still not working. Do you have any solution to change or repair my script? It'll be very helpful. Thank you!

Note: The Source spreadsheets data can always be updated

This is my current Script (Inspired by https://codewithcurt.com/combine-multiple-spreadsheets-into-one-google-sheet/):

function myFunction() {
  var sheetIDurl = 'xxxxxxxx';
  
  var source = SpreadsheetApp.openByUrl(sheetIDurl).getSheetByName('ID').getDataRange().getValues();
  
  var folder = DriveApp.getFolderById('xxxxxxxxxx');
  
  var ssa = SpreadsheetApp.getActiveSpreadsheet();
  
  var copySheet = ssa.getSheetByName('Master');
  copySheet.getRange('A2:Z').clear();
  var search = [];
  for (var i = 1; i < source.length; i++) {
    
    search = source[i][0].toString();

    var file = folder.getFilesByName(search);

    while (file.hasNext()) {
      var getFile = file.next();
      break;
    }
    Logger.log(getFile);
    var ss = SpreadsheetApp.open(getFile);
    SpreadsheetApp.setActiveSpreadsheet(ss);
    var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet2');

    for (var i = 0; i < sheets.length; i++) {
      var nameSheet = ss.getSheetByName(sheets[i].getName());
      var nameRange = nameSheet.getDataRange();
      var nameValues = nameRange.getValues();

      for (var y = 1; y < nameValues.length; y++) {
        copySheet.appendRow(nameValues[y]);
      }
    }
  }
}

Upvotes: 0

Views: 2452

Answers (2)

AlwaysBeta
AlwaysBeta

Reputation: 1

This is elegant and works fine. The only issue I was facing was "Exception: Invalid argument: url data_Merger"

The issue seems to be the ID.length. When I have 3 links and give it as below, it works fine.
const IDs = id_Sheet.getRange('A1:A3').getValues().flat() // Change to your column range which contains IDs

But if I give it as A1:A, it gives me an error. Logging ID.length gives its value as 997 when it should be 3 and so the counter doesn't stop.

Instead, I made the following changes and it worked well. Replaced ID.length with total_IDs

var total_IDs = id_Sheet.getLastRow(); for(var i = 0 ; i < total_IDs; i++)

Upvotes: 0

vector
vector

Reputation: 1022

Try Below Sample Script after changing sheet names :-

function data_Merger()
{
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const mergeSheet = ss.getSheetByName('Master Sheet Name, where data should be pasted'); //Change sheet name
  const id_Sheet = ss.getSheetByName('ID SheetName'); //Change sheet name
  const IDs = id_Sheet.getRange('A2:A').getValues().flat() // Change to your column range which contains IDs
  var mergedData = []
  for(var i = 0 ; i < IDs.length ; i++)
  { 
       var ns = SpreadsheetApp.openById(IDs[i]).getSheetByName('Sheet2')
       var MRange = ns.getRange(2, 1, ns.getLastRow()-1, ns.getLastColumn()).getValues()   // Excluding Header Row
       for(var j = 0 ; j < MRange.length ; j++)
       {
         mergedData.push(MRange[j])
       }     
  }
  
  mergeSheet.getRange(2, 1, mergedData.length, mergedData[0].length).setValues(mergedData) // Pasting data in Master Sheet  
}
  • Structure of all the sheets should be exactly same.

Upvotes: 0

Related Questions