CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Veress Attila
Veress Attila

Reputation: 33

Exclude rows with blank cells in getvalue() Google Sheets

I've made a sheet that has a submit function for the data there, but I want to make it ignore the rows, where there's an empty cell in the row. The submit sheet looks like this:

Date | Name | Number | Reason
-----------------------------
123  | AAA  | 321    | xyz
-----------------------------
123  | BBB  | 321    | xyz
-----------------------------
123  | CCC  |        | xyz
-----------------------------
123  | DDD  | 321    | xyz

This gets submitted verbatim into the other sheet, but I would like the script to ignore the 3rd row since it's empty in the numbers column.

This is how I set up the script.

function RaidDKPedit() {
  var sss = SpreadsheetApp.getActiveSpreadsheet();
  var ss = sss.getSheetByName('DKP-Raid');

  var data_range = ss.getRange('A2:D101'); 
  var data_data = data_range.getValues();
  var data_clean = ????;

  var dkp_range = ss.getRange('C2:C101');

  var tss = SpreadsheetApp.getActiveSpreadsheet();
  var ts = tss.getSheetByName('DKP-History');

  var tarrow = ts.getLastRow(); 

  ts.getRange(tarrow+1, 1,data_clean.length,4).setValues(data_clean);
  dkp_range.clear();

}

I tried different methods, but I couldn't get away to clean up the data but I think this is how it should work, with some filtering in the data clean part. This could be stupidly easy, but I just couldn't find the solution so far.

This is how it should get added to the other sheet at the bottom of the table

Date | Name | Number | Reason
-----------------------------
123  | AAA  | 321    | xyz
-----------------------------
123  | BBB  | 321    | xyz
-----------------------------
123  | DDD  | 321    | xyz

Upvotes: 3

Views: 1731

Answers (2)

jbra95
jbra95

Reputation: 909

This is a different approach:

function copyValues (){

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('DKP-Raid');
  var range = sheet.getDataRange().getValues();

  var newRange = []
  rows = 0;
  for (var i=0; i<range.length; i++){
    var row = range[i];
    //If the column 2(C) is empty the line is skipped
    if (range[i][2]){
      newRange.push(range[i]);
      rows+=1;
    }
  }

  var dest_sheet = ss.getSheetByName('DKP-History');
  dest_sheet.getRange(1, 1, rows, 4).setValues(newRange)
}

You could use getDataRange to extract all the cells with data in the spreadsheet. This is functionally equivalent to creating a Range bounded by A1 and (Range.getLastColumn(), Range.getLastRow()).

Upvotes: 1

JPV
JPV

Reputation: 27302

If you want to filter out the rows with a empty cell in column C, try:

var data_clean  = data_data.filter(function (r) {return r[2]})

Upvotes: 3

Related Questions