How to get values from Google Sheet Column

Question

I have the following Apps Script, which is almost what I'm looking for. What it's doing is getting a list of items from a Google Sheet to populate a Google Form Multiple Choice Question with a list of items. However, it's including all 100 rows, rather than just the ones that have values in them.

What am I missing to make sure it only includes cells that have a len > 1?

function getWorkbookNames(){
  var form = FormApp.getActiveForm();
  var items = form.getItems();

  var ss = SpreadsheetApp.openByUrl(
     'spreadsheetnamestringhere');
    var sheet = ss.getSheetByName('Unique Databases');
  var sheetValues = sheet.getRange("A2:A").getValues();
    var values = sheetValues.slice(1);

 var names = [];

  for(var p = 0; p < values.length; p++){
    names.push(values[p][0])
  }

  var pValues = items[8].asListItem();
  pValues.setChoiceValues(names).setRequired(true)

  var areas = [];
  for (var i = 0; i < values.length; i++) {
      areas.push(values[i][1])
    }
}

Any help/advice you all could provide would be greatly appreciated.

Possible duplicate of [Google App Script: How to ignore empty cell values for getRange().getValues()](https://stackoverflow.com/questions/44579300/google-app-script-how-to-ignore-empty-cell-values-for-getrange-getvalues) — Kos, Jan 08 '18 at 15:42

score 1 · Answer 1 · answered Jan 08 '18 at 17:30

Solved by adding another filter layer:

function getWorkbookNames() {
    var form = FormApp.getActiveForm();
    var items = form.getItems();

    var ss = SpreadsheetApp.openByUrl(
        'spreadsheetnamestringhere');
    var sheet = ss.getSheetByName('Unique Databases');
    var sheetValues = sheet.getRange("A2:A").getValues();
    var filterValues = sheetValues.filter(String);
    var values = filterValues.slice(1);

    var names = [];

    for (var p = 0; p < values.length; p++) {
        names.push(values[p][0])
    }

    var pValues = items[8].asListItem();
    pValues.setChoiceValues(names).setRequired(true)

    var areas = [];
    for (var i = 0; i < values.length; i++) {
        areas.push(values[i][1])
    }
}

How to get values from Google Sheet Column

1 Answers1