0

I'm using this script but it takes all sheets of current spreadsheet. A bit modified script from original, but it doesn't achieve what I want:

function importallMain() {
  var id = 'some id';  
  var ss = SpreadsheetApp.openById(id); //Managed to exclude first 7 from array because adds (;)
  var imports = ss.getSheets().splice(7).map(function(s) {
 if(['MasterBook', 'MasterClients', 'Rates', 'Listing', 'CreateDOC', 'Accounting' , 'Project'].indexOf(s.getName()) == -1) // Excluding by name
    return 'importrange("' + id + '", "' + s.getName() + '!A11:Y500")'; 
  });
  var formula = '=query({' + imports.join('; ') + '},"Select * where Col3 <> \'\' ")';
  SpreadsheetApp.getActiveSheet().getRange("A2").setFormula(formula);
}

What I would like is that it would take sheets whose names contain Ready.

1

You can use string.match() of JavaScript to find the string "ready" in the name and only take those sheets. Like so:

function importallMain() {
  var id = 'some id';  
  var ss = SpreadsheetApp.openById(id); //Managed to exclude first 7 from array because adds (;)
  var imports = ss.getSheets().splice(7).map(function(s) {
   var sheetName = s.getName()
 if(sheetName.match("Ready") != null) // Excluding sheet that dont have ready in their name! 
    return 'importrange("' + id + '", "' + s.getName() + '!A11:Y500")'; 
  });
  var formula = '=query({' + imports.join('; ') + '},"Select * where Col3 <> \'\' ")';
  SpreadsheetApp.getActiveSheet().getRange("A2").setFormula(formula);
}
| improve this answer | |
  • Hi, thanks! It now excludes sheets, but it adds trailing semicolons for excluded sheets like so: =query({importrange("id", "ReadyDublicate!A11:Y500"); importrange("id", "ReadyDublicate1!A11:Y500"); ; ; },"Select * where Col3 <> '' ") Maybe you have an idea how to get rid of them? Thanks! – Petras K Apr 14 '17 at 7:32
  • Found [this] (stackoverflow.com/questions/281264/…) and helped solving the problem This line takes care of empty array imports = imports.filter(function(n){ return n != undefined }); // excludes empty array elements – Petras K Apr 14 '17 at 8:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.