1

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.

Community
  • 1
  • 1
Mr. J
  • 1,524
  • 2
  • 19
  • 42

2 Answers2

5

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.

Tanaike
  • 181,128
  • 11
  • 97
  • 165
  • Thanks for the detailed explanation Tanaike. I should be the one apologizing... I didn't realize that .filter() needs to literally be .filter(String). I thought string was just a placeholder. I will try the removal of the last row as well. Could be useful for similar functions where I do not want to remove the empty cells that are in the middle of the column. – Mr. J Aug 23 '19 at 13:34
  • @James H Thank you for replying. I'm glad your issue was resolved. Thank you, too. – Tanaike Aug 23 '19 at 14:19
  • @Tanaike so this `.filter(String)` is supposed to take a delegate. Is this basing resolving the String like a truthy statement? Basically `TypeOf(String) == true` then filter it out? – Jay Jordan Aug 23 '19 at 15:44
  • 1
    @Jay Jordan Although I'm not sure whether I could correctly understand about your question, are these threads useful for your situation? https://stackoverflow.com/q/49689907 and https://stackoverflow.com/q/36232576 If these were not useful, I apologize. – Tanaike Aug 23 '19 at 23:21
  • 1
    @Tanaike, I meant to use the work "predicate" not "delegate". Yes, those links answered my question. Thanks. – Jay Jordan Aug 27 '19 at 18:28
0

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)
}
Jay Jordan
  • 643
  • 4
  • 16