remove common elements from two arrays in appscript?

Question

I have a google sheet. I am try to get all tab names in to array. I used this code.

function allTabNames() {
  try{
  var out = new Array();
  
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  for (var i =0; i<sheets.length; i++) out.push([sheets[i].getName()]);
  Logger.log(out);
  Logger.log(out.length);
  return out

  }
  catch (er){
    Logger.log(er);
  }
}

It's working correctly. Now I want to remove some tab names. I created a new array using the tab names I want to remove.

var duparray = ["Sheet1", "newRoad147", "Sheet2", "Sheet3", "Sheet4","Sheet5", "Sheet6"];

When I run the first mentioned code I will get this. The array "out".

[[Sheet1], [newRoad147], [Sheet2], [Sheet3], [Sheet4], [Sheet5], [Sheet6], [Sheet7], [AddData], [WCO069], [WCO065], [WCO149], [WCO141], [WCO158], [BarChart], [Sheet11]]

I am trying to remove the common element in these two arrays. For that I used the following code. But it did not go well.

function allTabNames() {
  try{
  var out = new Array();
  
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  for (var i =0; i<sheets.length; i++) out.push([sheets[i].getName()]);
  Logger.log(out);
  Logger.log(out.length);
  //return out

    var duparray = ["Sheet1", "newRoad147", "Sheet2", "Sheet3", "Sheet4","Sheet5", "Sheet6"];
  var outx = out.filter( function( el ) {
  return duparry.indexOf( el ) < 0;
  } );
  }
  catch (er){
    Logger.log(er);
  }
Logger.log(outx);
}

The order of these tabs can be changed. That is why I try to remove the element using the specific names of the elements in the array. Then we hope to create a dropdown list of elements in this new array using data validation.

TipVisor · Accepted Answer · 2022-03-02 18:30:42Z

1

Get Sheet names and omit not included

function getTabName() {
  const ss = SpreadsheetApp.getActive();
  const nincl = ['Sheet1','Sheet2'];//not included
  const shts = ss.getSheets().filter(sh => !~nincl.indexOf(sh.getName())).map(sh => sh.getName()); 
  
  ss.getSheetByName('Sheet5').getRange("C5").setDataValidation(SpreadsheetApp.newDataValidation().requireValueInList(shts,true).build());

return shts; }

edited Mar 2, 2022 at 18:30

TipVisor

1,10211 silver badges24 bronze badges

answered Mar 2, 2022 at 1:12

Cooper

64.2k7 gold badges29 silver badges65 bronze badges

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

2 Comments

TipVisor Over a year ago

I also want to put the elements of this "shts" array into the "C5" cell of "sheet 5" as a dropdown list. How can that be done?

Cooper Over a year ago

The last line does that

Collectives™ on Stack Overflow

remove common elements from two arrays in appscript?

1 Answer 1

Get Sheet names and omit not included

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Get Sheet names and omit not included

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related