CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
swolfe2
swolfe2

Reputation: 451

How do I Filter array from two columns to non-null pairs?

With the code below, I have a Google Sheet, and I'm trying to create arrays from different sections. 

function onFormSubmitTest(e) {
    //Open spreadsheet based on URL
    var ss = SpreadsheetApp.openByUrl('sheetnamehere');
    //Set as Active
    SpreadsheetApp.setActiveSpreadsheet(ss);
    //Set Tabs as Variables
    var Rsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Emailer");
    var Lsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Unique Databases");

    //----Set last Rows as Variables----
    // Gets Values from different sections
    var formResponses = Rsheet.getRange("N3:N").getValues();
    var databaseNames = Lsheet.getRange("A2:B").getValues();
    var developerNames = Lsheet.getRange("F2:F").getValues();

    // Filters to ranges that have values, ignores null
    var lastRvalues = formResponses.filter(String);
    var lastLvalues = databaseNames.filter(String);
    var lastDvalues = developerNames.filter(String);

    // Sorts arrays for faster indexing
    var lastR = lastRvalues.sort();
    var lastL = lastLvalues.sort();
    var lastD = lastDvalues.sort();

    // Unique Databases
    var currentDatabases = lastL;

    // Current Developers
    var currentDevelopers = lastD

On the Unique Databases tab, I have 100 rows on the sheet, with only 28 of them having data. If I have the code below, I can see it only has 28 values as expected:

var databaseNames = Lsheet.getRange("A2:A").getValues();

However, when I add the other column, I get 100 values, with the vast majority being ["", ""]. Should note that the 28 values are key>value pair, so if there's a value in A:A, there will be one in B:B. If null in A:A, then B:B is null.

What am I doing wrong?

Upvotes: 0

Views: 746

Answers (1)

user6655984
user6655984

Reputation:

Indeed, [["", ""], ["", ""]].filter(String) returns the entire array. To see why, replace filter by map: 

[["", ""], ["", ""]].map(String)  //  [",", ","]

Those are truthy strings, with a comma.

A correct way to filter text by nonemptiness of the first column of the range would be 

range.getValues().filter(function(row) {return row[0]})

(The usual caveat about 0 value being falsy applies, but it doesn't look like you would encounter numeric 0 there, the column being text. And the string "0" is truthy.)

(Also, "null" is not the right term here, Apps Script returns empty strings "" for empty cells, which is different from null)

Upvotes: 1

Related Questions