CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Aditya Malewar
Aditya Malewar

Reputation: 1

Fetch data from multiple sheets using Google app script

Trying to fetch data from multiple spreadsheet's. All Sheet's are stored in same folder. Have to fetch data in one master sheet from only specific files by file name. I have written below script. It's working fine if we enter only one file name in specified range (in master sheet tab) (getSheetByName) but showing error while trying to fetch data for multiple files. Showing error - "TypeError: Cannot read property 'length' of undefined" Below is Script - 

function get_compiled_data() {    
  var filelist = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("FileName");  
  var filelists = filelist.getRange("A2:A").getValues();  
  var folder = DriveApp.getFolderById("1li9hBP_W5gPkb_ASKqin4j1ZGEn1Gvji");   
  var fileindex = folder.getFilesByName(filelists);  
  var file;
  var filetype;
  var sheetID;
  var collect_data = [];
  var data;    
  while (fileindex.hasNext()) {    
    file = fileindex.next();    
    filetype = file.getMimeType();    
    if (filetype === "application/vnd.google-apps.spreadsheet"){      
      sheetID = file.getId();      
      data = getData(sheetID);      
      data = data.map(function(r){return r.concat([file.getName()]);});      
      collect_data = collect_data.concat(data);      
    }      
  }  
  var target = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Compiled_Data");
  target.getRange("A2:AX").clearContent();
  target.getRange(2, 1, collect_data.length, collect_data[0].length).setValues(collect_data);  
}

function getData (sheetID) {  
  var sheet = SpreadsheetApp.openById(sheetID);
  var tab = sheet.getSheets()[0];
  var data = tab.getRange("A3:AX" + tab.getLastRow()).getValues();
  return data;  
}

Upvotes: 0

Views: 2792

Answers (1)

Iamblichus
Iamblichus

Reputation: 19309

Issue:

You are providing a 2D array to getFilesByName(name), when you should be providing a string. Because of this, the code never enters the while block and collect_data remains an empty array, causing collect_data[0] to be undefined, and producing the observed error when trying to access its length.

When you were looking for a single file name you were probably using getValue(), which retrieves a single value, which can be a string, which can be used in getFilesByName. getValues() returns a 2D array instead, so you should adapt your code so that it iterates through each value returned by getValues().

Solution:

Edit the getValues() line and wrap all the actions made from getFilesByName inside a loop the following way:

function get_compiled_data() {
  var filelist = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("FileName");      
  var filelists = filelist.getRange(2, 1, filelist.getLastRow() - 1).getValues(); 
  var folder = DriveApp.getFolderById("FOLDER-ID");
  filelists.forEach(fileName => {
    var fileindex = folder.getFilesByName(fileName[0]);
    // Rest of code
  });
});

Reference:

Upvotes: 1

Related Questions