Checking sheet names and selects the valid sheet names

Question

I have couple of spreadsheets in a google drive, each have couple of sheets, I want to iterate through them and copy specific ranges inside the sheets I specified before in a named range. I can iterate through the files, but I can check the sheet names against my list of sheets. I stored the List of desired sheets in valid variable, which reads from a named range of my spreadsheet. I checked the Lower case/ Upper case, the sheet names some are string and some numbers. but nothing is getting catched by my if condition. I tried both indexOf and includes, Any idea what is wrong?

var App = SpreadsheetApp;
var ss = App.openById("****"); //Destination SS
var sh = ss.getSheetByName("Data");
var files = DriveApp.getFolderById("****").getFiles(); 
var valid = ss.getRangeByName('ValidNames').getValues();

function Loop() {
  while (files.hasNext()){
    var f = files.next();
    if (f == ss.getName())  continue;

    var file = App.openById(f.getId());
    var sheets = file.getSheets();
    for (var i=0 ; i<sheets.length ; i++) {
        var s= sheets[i].getName().toUpperCase();
      if (valid.includes(s)) sh.appendRow([Date.now(),f, s ]);     // Not working? Shows Error 

      if (valid.indexOf(s)> -1) sh.appendRow([Date.now(),f, s ]); // Not Working
   };
 };

Thanks, M

I edited my question. I stored them in Valid variable, which is reading from my ss — Mahhdy
Valid is a two dimensional array. Even if it's only one column you still need to flatten it to use indexOf(). Try map method. — Cooper

Cooper Cooper · Accepted Answer · 2019-05-16T20:30:16

If it's only one column, You can use something like this:

var valid = ss.getRangeByName('ValidNames').getValues().map(function(r){return r[0]});

Here's what I have as a function:

 function  myFunction() {
  var ss = SpreadsheetApp.openById('ssid'); //Destination SS
  var sh = ss.getSheetByName("Data");
  var files = DriveApp.getFolderById("****").getFilesByType(MimeType.GOOGLE_SHEETS);//included mimetype
  var valid = ss.getRangeByName('ValidNames').getValues().map(function(r){return r[0]});
  while (files.hasNext()){
    var f=files.next();
    if (f==ss.getName())continue;
    var file=SpreadsheetApp.openById(f.getId());
    var sheets = file.getSheets();
    for (var i=0;i<sheets.length;i++) {
      var s= sheets[i].getName().toUpperCase();
      if (valid.indexOf(s)>-1)sh.appendRow([Date.now(),f, s ]); // Not Working
    }
  }
}

I also removed that function loop thing as well as it make no sense to me.

Checking sheet names and selects the valid sheet names

1 Answers