CODEWITHSUNDEEP
javascriptgoogle-sheets
jose Gajardo
jose Gajardo

Reputation: 45

Filter a table in AppScript ignoring the first row (column names)

Because of the issues Google Sheets has with the ImportRange function, I wanted to create an AppScript to replace the ImportRange. I am new into JS but currently, I have the following:

function My_ImportRange() {

  var clearContent = SpreadsheetApp.getActive().getSheetByName("Sheet 1").getDataRange().clearContent()

  var values = SpreadsheetApp.openById('sheet-id').
  getSheetByName('name').getRange('A:AI').getValues();

  // filtering data

  var filtered_values = values.filter(filterlogic)

  SpreadsheetApp.getActive().getSheetByName('Sheet 1').
  getRange(2,1,filtered_values.length,filtered_values[0].length).setValues(filtered_values);

}

var filterlogic = function(item){
  return item[0] === "filter 1" &&
  item[6] === "filter 2";};

The issue I have is that when I filter the table (with the filterlogic function), the first row or column names get dropped off because of the filter .

How can I tell the filter to only run the logic from the 2nd row, ignoring the column names?

Thank you in advance!

Upvotes: 1

Views: 764

Answers (1)

NightEye
NightEye

Reputation: 11184

Use the script below.

Remove the header using shift and add it back after filtering using unshift

// assuming data is in Sheet1 A:H
var values = SpreadsheetApp.getActive().getSheetByName("Sheet1").getRange('A:H').getValues();
// remove headers before filtering
var headers = values.shift();

// filter values
var filtered_values = values.filter(filterlogic)
// append back the headers in the first row after filtering
filtered_values.unshift(headers);

Logger.log(filtered_values)

OR tell the filter to exclude index 0 which should be the first row.

If we filter "A" and 10 for column A and G:

var filterlogic = function(item, index){
  // return true IF condition is met OR index is 0 or the first row.
  return (item[0] === "A" && item[6] === 10) || index == 0;
};

Both of modification will exclude the 1st element in values which is the 1st row. Choose what you think is best. Both will return the same data.

enter image description here

Upvotes: 2

Related Questions