CODEWITHSUNDEEP
javascriptarraysloopsgoogle-apps-scriptgoogle-sheets
thetran209
thetran209

Reputation: 25

How to loop through all sheets to get cell values to create a dropdown box?

I have a Google Sheets Spreadsheet with multiple sheets that contain the same Column Headers. I am trying to pull data from range "A2:A" from all of the sheets and create a dropdown box with all of the cell values, but at the moment it's only giving me a dropdown box with cell values from the last sheet.

function getCategoryList() {
   var spreadsheet = SpreadsheetApp.getActive();
   var lastrow = spreadsheet.getActiveSheet().getLastRow()
  
  
   var dynamicList = spreadsheet.getRange('A2:A' + lastrow); 
   var arrayValues = dynamicList.getValues();

   var rangeRule = SpreadsheetApp.newDataValidation().requireValueInList(arrayValues);

   console.log(arrayValues)

   spreadsheet.getRange('Main!C4').setDataValidation(rangeRule);

  
}


function doForAllTabs() {
  var spreadsheet = SpreadsheetApp.getActive();
     var lastrow = spreadsheet.getActiveSheet().getLastRow()
  var allSheets = spreadsheet.getSheets();
 
  
  allSheets.forEach(function(sheet) {
    if(sheet.getSheetName() !== "Main") {
      sheet.activate();
      
      getCategoryList()
      
    }    
  })
}

Upvotes: 1

Views: 338

Answers (1)

JPV
JPV

Reputation: 27292

With the current code datavalidation is written to the cell Main!C4 after a single sheet is processed inside the sheets-loop. In other words, the datavalidation is overwritten time and again. What is needed is the consolidation of values in an array that then is written to Main!C4. See if this helps.

function setDataValidation() {

var ss = SpreadsheetApp.getActive();
var values = allColsA(ss, 'Main', 'A2:A');
ss.getRange('Main!C4').setDataValidation(SpreadsheetApp.newDataValidation()
.requireValueInList(values));
}

EDITED to include an array with excluded sheets...

function allColsA(spreadsheet, targetSheetName, range) {
var arr, excluded; 
arr = [[]];
excluded = [targetSheetName, 'Sheet2', 'Sheet3'];
spreadsheet.getSheets().filter(sh => excluded.indexOf(sh.getName()) === -1)
    .forEach((s, i) => {
        arr = arr.concat(s.getRange(range)
                .getValues())
    })
return  arr.filter(String).reduce((a, b) => a.concat(b), []);
}

Upvotes: 2

Related Questions