How to ignore empty cell values for getRange (). GetValues ()
I can get the range values using getValues () and put it in a string by declaring the following variables in a google script app
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("Test");
var range_input = ss.getRange("A1:A").getValues();
However, I realize that I am getting a lot of commas in my string, probably from all empty calls.
For example, if the values are as follows
================
Spreadsheet("Test") Values
A1=abc
A2=def
A3=
A4=
A5=
A6=uvw
A7=xyz
================
If I execute msgBox it gets something like below.
Browser.msgBox(range_input) // results = abc,def,,,,uvw,xyz,,,,,,,,,,,
Is there a way to remove the trailing commas to get something like below? (i.e. ignore empty cells)
Browser.msgBox(range_input) // results = abc,def,uvw,xyz
source to share
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
At the current stage, I thought that while understanding var result = [i for each (i in range_input)if (isNaN(i))]
is still usable, it is not appropriate for this situation as a comment. Alto I think is filter()
appropriate for this situation. In this update, I would like to update this by suggesting a different solution. If that was helpful, I'm glad.
Scheme 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, blank lines are removed
reduce()
.
Scheme 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, empty strings are removed with
filter()
, and when usedfilter()
, a two-dimensional array is returned. To return a one-dimensional array, the array is aligned.
Scheme 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, empty strings are removed by the filter and then the filtered values are retrieved.
Result:
Links:
source to share
Not tested for efficiency, but a ,
regular expression can be used to easily remove multiple :
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})
source to share