I have this script
function filter2() { var sss = SpreadsheetApp.getActiveSpreadsheet(); //replace with source ID var ss = sss.getSheetByName('Users'); //replace with source Sheet tab name var range = ss.getRange('A:G'); //assign the range you want to copy var rawData = range.getValues() // get value from spreadsheet 1 var data = [] // Filtered Data will be stored in this array for (var i = 0; i< rawData.length ; i++){ if(rawData[i][6] == "User missing") // Check to see if Column E says No Billing Accountif not skip it { data.push(rawData[i]) } } var tss = SpreadsheetApp.openById('hsgsklllssdllsls'); //replace with destination ID var ts = tss.getSheetByName('Summary'); //replace with destination Sheet tab name ts.getRange(ts.getLastRow()+1, 1, data.length, data[0].length).setValues(data); }
This is a filter script. In this filter is getting value from source in the range A:G, but i need to get only Column B, Column D, Column F, Column G
How to avoid getting filtered Column A,C,E?
Advertisement
Answer
Explanation:
One way to select particular columns is to use the map function.
The following will get columns
b,d,f,g
from the rangeA:G
.const rawData = ss.getRange("A:G").getValues().map(([,b,,d,,f,g]) => [b,d,f,g]);
Don’t use a for
loop to filter on a particular column.
It is way more efficient to use filter to filter on a particular column. You haven’t specified in your question for which column you want to filter on. Assuming the new data will consist of columns b,d,f,g
the column indexes for the raw data will be 0,1,2,3
.
For example:
const frawData=rawData.filter(row=>row[3]=="User missing");
this will filter on column g
since we have removed a,c,e
and column g
is in 3rd
position (starting from 0
). If you want to filter on a different column. Choose 0
for b
, 1
for d
, 2
for f
and 3
for g
.
Solution:
function filter2() { const sss = SpreadsheetApp.getActiveSpreadsheet(); //replace with source ID const ss = sss.getSheetByName('Users'); //replace with source Sheet tab name const rawData = ss.getRange("A:G").getValues().map(([,b,,d,,f,g]) => [b,d,f,g]); // new code (gets b,d,f,g) const frawData=rawData.filter(row=>row[3]=="User missing");// 3 means column G. [b,d,f,g] => [0,1,2,3] const tss = SpreadsheetApp.openById('hsgsklllssdllsls'); //replace with destination ID const ts = tss.getSheetByName('Summary'); //replace with destination Sheet tab name ts.getRange(ts.getLastRow()+1, 1, frawData.length, frawData[0].length).setValues(frawData); }