-1

I am trying to build a code that will pull any text options from column B without any of the blank cells. I want to be able to add additional text items to the spreadsheet and have them appear in the dropdown without having to change the code. I also do not want any blank options when there the selection I have made in the code includes blank cells.

Link to Spreadsheet

function doGet(e) {
  var ss = SpreadsheetApp.openByUrl(url);
  var ws = ss.getSheetByName("Options");
  var list = ws.getRange(1,2,ws.getRange("A1").getDataRegion().getLastRow(),1).getValues();
  Logger.log(list);
Aaron D.
  • 1
  • 3

2 Answers2

1

Like this?

var list = ws.getRange('B1:B6').getValues().flat()
// pop off the empty ones at the end
while(list[list.length - 1] === '') list.pop()
// add more to list
list = [...list, 'more', 'stuff', 'added']
// save them in B
ws.getRange(`B1:B${list.length}`).setValues(list.map(v => [v]))
pguardiario
  • 53,827
  • 19
  • 119
  • 159
  • I'm sorry I miss-typed my original question. I am trying to build a code that will pull any text options from column B without any of the blank cells. I want to be able to add additional text items to the spreadsheet and have them appear in the dropdown without having to change the code. I also do not want any blank options when there the selection I have made in the code includes blank cells. – Aaron D. Aug 29 '20 at 06:28
  • Use B:B for the full column, I'm not sure I understand what you're asking... – pguardiario Aug 29 '20 at 06:41
  • If I use B:B, I get everything in column B including all the empty cells in column B and if I do B:B6 then I am restricted to only 6 cells even if only one of them is empty and have to change the code if I remove 1 option from the spreadsheet. So how can I grab everything in B1 to the last row in B that has text? – Aaron D. Aug 29 '20 at 06:51
  • I showed you how to pop off the trailing empties. – pguardiario Aug 29 '20 at 08:35
0

To "grab anything in column B up to B6 " is the same as B1:B6, so, instead of

ws.getRange(1,2,ws.getRange("A1").getDataRegion().getLastRow(),1)

use

ws.getRange('B1:B6')
Rubén
  • 34,714
  • 9
  • 70
  • 166