CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Cirinu B.
Cirinu B.

Reputation: 33

How to get the range of a non blank cell in google script

I´m trying to get the values of the rows that don't have a blank cell in column K.

  var spreadsheet = SpreadsheetApp.getActive();
  var bigrange = spreadsheet.getSheetByName('sheet').getRange("K2:K").getValues();
//  var smallrange= bigrange .filter(String);

  for (var i=0; i < smallrange.length; i++) {

var calId = spreadsheet.getSheetByName('sheet').getRange("M" + [i+ 2]).getValue();    

}

The code above works if I don't use the .filter(String). But since my data is big and most of range K2:K is blank, I think I should use the .filter(String).

What is the best way of getting the range of the few cells in K2:K that are not blank?

Upvotes: 3

Views: 5602

Answers (1)

Tanaike
Tanaike

Reputation: 201378

How about this answer?

Pattern 1:

  • The sheet name is sheet. In this sheet, you want to retrieve the ranges when the cell of column "K" is empty.

If my understanding is correct, how about this modification?

Modified script:

In this modified script, at first, the values are retrieved from "K2:K". Then, the ranges are retrieved from the values.

var sheetName = "sheet"; // Please set the sheet name here.

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName(sheetName);
var values = sheet.getRange("K2:K").getValues();
var ranges = values.map(function(e, i) {return e[0] ? sheetName + "!K" + (i + 2) : ""}).filter(String);
Logger.log(ranges)

Pattern 2:

  • The sheet name is sheet. In this sheet, you want to retrieve the values of the column "M" when the cell of column "K" is empty.

From your question and script, I also understood like above. If my understanding is correct, how about this modification?

Modified script:

In this modified script, at first, the values are retrieved from "A2:M". Then, the values of the column "M" when the column "K" is empty are retrieved.

var sheetName = "sheet"; // Please set the sheet name here.

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName(sheetName);
var values = sheet.getRange("A2:M").getValues();
var res = values.map(function(e) {return e[10] ? e[12] : ""}).filter(String);
Logger.log(res)

Note:

  • In both modified scripts, from your script, it supposes that you want to retrieve the values from one sheet which is sheet.

References:

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

Upvotes: 3

Related Questions