0
174
VistasFilter 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!
1 Respuestas
Responde la pregunta0
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.
