CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Mr. J
Mr. J

Reputation: 1839

Removing or Ignoring Empty Cells Returned by .getRange()

So I have a column of data in a sheet, that I am iterating over with an apps script function.

The problem is that my sheet has a number of empty cells at the end, which are rendered as [] when I use .getRange().

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("AUD Data");
var sheetRange = sheet.getRange("F2:F").getValues();

function logRange() {
Logger.log(sheetRange)
}

Returns enter image description here

I want to get all these blank cells at the end, out of my cell array, as it is messing with results when I loop over this array.

Have tried filter() and isNaN but those solutions didn't work.

Ideally I would like to remove the empty arrays, before looping, but a way of ignoring them in a condition would be fine too.

I find it interesting that even though these empty cells are displaying as empty arrays, Apps Script seems to treat them as having a value. My conditions should ignore imo... eg.

else if (i > 0 && sheetRange[i] <= 0 && sheetRange[i-1] <= 0)

How can an empty array be treated as a number?

NOTE: I can fix this by deleting the empty rows in the sheet, but would rather do this with code.

Upvotes: 1

Views: 3802

Answers (2)

Jay Jordan
Jay Jordan

Reputation: 743

All of the cases in the filter should be eliminated with a simple truthy statement, but this will explicitly check to make sure none of those conditions happen.

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("AUD Data");
var sheetRange = sheet.getRange("F2:F").getValues();

sheetRange = sheetRange.filter(sheet => sheet != null && sheet != 'undefined' && sheet.trim() != '');

function logRange() {
     Logger.log(sheetRange)
}

Upvotes: 0

Tanaike
Tanaike

Reputation: 201428

How about this answer?

Issue 1:

In your spreadsheet, if the last row of all columns is the same or the last row of the column "F" is most bottom, how about this modification?

From:

var sheetRange = sheet.getRange("F2:F").getValues();

To:

var sheetRange = sheet.getRange(2, 6, sheet.getLastRow() - 1, 1).getValues();

Or if the last row of the column "F" is different from the last row of other columns, how about the following modified script?

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("AUD Data");
var sheetRange = sheet.getRange("F2:F").getValues();
for (var i = sheetRange.length - 1; i >= 0; i--) {
  if (sheetRange[i][0]) {
    sheetRange.splice(i + 1, sheetRange.length - (i + 1));
    break;
  }
}
Logger.log(sheetRange)

Or if you want to also remove the empty elements of the middle of array, how about the following script?

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("AUD Data");
var sheetRange = sheet.getRange("F2:F").getValues().filter(String);
Logger.log(sheetRange)

Issue 2:

About else if (i > 0 && sheetRange[i] <= 0 && sheetRange[i-1] <= 0), from your script, I thought that you might use 2 dimensional array is used for this situation. If sheetRange is the value retrieved by getValues(), it is 2 dimensional array. And getRange("F2:F") is used for this. So how about this modification?

From:

else if (i > 0 && sheetRange[i] <= 0 && sheetRange[i-1] <= 0)

To:

else if (i > 0 && sheetRange[i][0] <= 0 && sheetRange[i-1][0] <= 0)

References:

If I misunderstood your question and this was not the direction you want, I apologize.

Upvotes: 5

Related Questions