How to ignore empty cell values for getRange().getValues()

Question

I am able to get the range values using getValues() and put it into a string by declaring the following variables in Google App Script

var ss = SpreadsheetApp.getActiveSpreadsheet(); 
var sheet = ss.getSheetByName("Test"); 
var range_input = ss.getRange("A1:A").getValues();

However, I realize I am getting a lot of commas in my string probably from all the empty calls.

For example, if values are following

================
Spreadsheet("Test") Values

A1=abc
A2=def
A3=    
A4=
A5=
A6=uvw
A7=xyz
================

If I do msgBox, it gets something like below.

Browser.msgBox(range_input) // results = abc,def,,,,uvw,xyz,,,,,,,,,,,

Is there a way to remove the trailing commas so I get something like below? (i.e. ignore the empty cells)

Browser.msgBox(range_input) // results = abc,def,uvw,xyz

Alper

Answer 1

• You want to achieve the following result.

  • Input

    A1=abc
    A2=def
    A3=
    A4=
    A5=
    A6=uvw
    A7=xyz
    

  • Output

    Browser.msgBox(range_input) // results = abc,def,uvw,xyz
    

In the current stage, I thought that although the comprehensions of var result = [i for each (i in range_input)if (isNaN(i))] can be still used, it is not suitable for this situation as tehhowch's comment. Alto I think that filter() is suitable for this situation. In this update, I would like to update this by proposing other solution. If this was useful, I'm glad.

Pattern 1:

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("Test"); 
var range_input = ss.getRange("A1:A").getValues();
var result = range_input.reduce(function(ar, e) {
  if (e[0]) ar.push(e[0])
  return ar;
}, []);
Logger.log(result) // ["abc","def","uvw","xyz"]
Browser.msgBox(result)

• In this pattern, the empty rows are removed by reduce().

Pattern 2:

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("Test"); 
var range_input = ss.getRange("A1:A").getValues();
var result = [].concat.apply([], range_input).filter(String); // or range_input.filter(String).map(String)
Logger.log(result) // ["abc","def","uvw","xyz"]
Browser.msgBox(result)

• In this pattern, the empty rows are removed by filter() and when filter() is used, the 2 dimensional array is returned. In order to return 1 dimensional array, the array is flatten.

Pattern 3:

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("Test"); 
var range_input = ss.getRange("A1:A").getValues();
var criteria = SpreadsheetApp.newFilterCriteria().whenCellNotEmpty().build();
var f = ss.getRange("A1:A").createFilter().setColumnFilterCriteria(1, criteria);
var url = "https://docs.google.com/spreadsheets/d/" + ss.getId() + "/gviz/tq?tqx=out:csv&gid=" + sheet.getSheetId() + "&access_token=" + ScriptApp.getOAuthToken();
var res = UrlFetchApp.fetch(url);
f.remove();
var result = Utilities.parseCsv(res.getContentText()).map(function(e) {return e[0]});
Logger.log(result) // ["abc","def","uvw","xyz"]
Browser.msgBox(result)

• In this pattern, the empty rows are removed by the filter, then the filtered values are retrieved.

Result:

References:

• reduce()
• filter()
• map()
• Class Filter

Redding

Dear Tanaike, Thank you for your comments and wow your fast :-) This is exactly what I was looking for! I will immediately start using your suggest. – Alper

Thanks again. Sorry I didn't know about the 'accept' button until today :) It's now accepted. – Alper

I had no idea list comprehensions worked in Google Apps Script! – Carnatic

Array comprehensions should not be utilized - they are a now-deprecated language feature – Mulvihill

@Mulvihill Thank you for your comment. I had not been able to notice that situation. Thank you for letting me know. So I updated my answer. Could you please confirm it? I think that TripleDeal's answer should be as the solved answer. So I proposed other patterns. – Redding

Answer 2

You can also use filter().

var ss = SpreadsheetApp.getActiveSpreadsheet(); 
var sheet = ss.getSheetByName("Test"); 
var range_input = ss.getRange("A1:A").getValues();
var filtered_input = range_input.filter(String);

Litotes

Hi Triple, thanks also for the suggest. I tried you suggestion and that also indeed worked! I like your code because its very clean and easy to understand. – Alper

You're welcome! Oh and thanks for the up-vote, I now have more than 50 reputation, so I'm now finally able to comment on any question. – Litotes

My pleasure. My up-vote is petty compared to your help :) – Alper

Well, you can choose my answer as the correct answer to your situation if you want, that should be enough to make me even more happier than I already was. – Litotes

Best answer so far. Thank you. – Auntie

Answer 3

Not tested for efficiency, but for a simple removal of multiple ,s, you can use regex:

const a=[['abc'],['def'],[''],[''],[''],['xyz'],['']];//simulate getValues()
const out =  a.join(',').replace(/,+(?=,)|,*$/g,'') //'abc,def,xyz'
const out2 = a.join('«').replace(/«+$/,'').split(/«+/) //flattened

console.log({out, out2})

Buckman

Answer 4

In case that you want to get multiple columns with the getValues(), the filter(String) won't work, instead you have to create a custom filter function like:

dataSheet.getRange("F3:H").getValues().filter(function(row) {
 return !row.some(cell => cell === '' || cell === null) 
})

Apprise