look at all instances of an array in a if statement?

Question

I have about 20 different sheets and I wrote some google script to combine all of the data into a master sheet. Now I'd like like to be able to exclude certain sheets. My idea to do this was to storage the names of those sheet in a variable. This is what I have so far, but I am getting an error? Any ideas?

label is the name of the Column that I am scanning each sheet for and masterSheetName is the sheet where I am storing the data.

 if (sheetName !== masterSheetName && sheetName !== skippedsheets)

lines are the ones I am having trouble with. It is not going though all of the instances of skipped sheets.

Is there a way to do this with a for each loop?

function getColVals(label, masterSheetName) {

  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  var colValues = []
  for ([i,sheet] in sheets) {
    var sheetName = sheet.getSheetName();
    var skippedsheets = ["HHS 1","HHS 2"];
    Logger.log(skippedsheets);
    Logger.log(skippedsheets[0]);
    if (sheetName !== masterSheetName && sheetName !== skippedsheets) {
    var colValues2 = getColValues(label,sheetName);
    colValues = colValues.concat(colValues2);
    } 

  }
  return colValues; 

}

thank you, Jerome

Please edit the question tags to include the language you are working with — Govind Parmar
– Govind Parmar, Commented Mar 7, 2019 at 2:52

Jerome Learman · Accepted Answer · 2019-03-07 02:51:20Z

1

I found this function called inArray that some one wrote and shared and it worked perfectly.

function getColVals(label, masterSheetName) {

  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  var colValues = []
  for ([i,sheet] in sheets) {
    var sheetName = sheet.getSheetName();
    var skippedsheets = ["template","intro","games"];
    // Logger.log(skippedsheets);
    // Logger.log(skippedsheets[0]);
    if (sheetName !== masterSheetName && !(skippedsheets.inArray(sheetName))) {
    var colValues2 = getColValues(label,sheetName);
    colValues = colValues.concat(colValues2);
    Logger.log(sheetName);
    } 

  }
  return colValues; 

}


/*
 * @function
 * @name Object.prototype.inArray
 * @description Extend Object prototype within inArray function
 *
 * @param {mix}    needle       - Search-able needle
 * @param {bool}   searchInKey  - Search needle in keys?
 *
 */
Object.defineProperty(Object.prototype, 'inArray',{
    value: function(needle, searchInKey){

        var object = this;

        if( Object.prototype.toString.call(needle) === '[object Object]' || 
            Object.prototype.toString.call(needle) === '[object Array]'){
            needle = JSON.stringify(needle);
        }

        return Object.keys(object).some(function(key){

            var value = object[key];

            if( Object.prototype.toString.call(value) === '[object Object]' || 
                Object.prototype.toString.call(value) === '[object Array]'){
                value = JSON.stringify(value);
            }

            if(searchInKey){
                if(value === needle || key === needle){
                return true;
                }
            }else{
                if(value === needle){
                    return true;
                }
            }
        });
    },
    writable: true,
    configurable: true,
    enumerable: false
});

answered Mar 7, 2019 at 2:51

Jerome Learman

871 silver badge10 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

tehhowch Over a year ago

Make sure you understand the implications of extending the object prototype.

Jerome Learman Over a year ago

@tehhowch I don't, at all... I was working in excel, needed something that more people could work in at the same time and found sheets. Trying to convert my VBA to script. I need a class in the worst way. Where should I be researching this?

Collectives™ on Stack Overflow

look at all instances of an array in a if statement?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related