This was good overview on filters for Apps Script, following your methods I was able to get a filter function to work, but I was not able to get the function to filter based on a specific date. This returns me an empty array and I was wondering if you had any advice for using the filter function for a dataset that has dates associated with it?
Thanx a lot, man. You give a lot of good information to up GS skills. I am from Russia and here i cant find a lot of answeres for my questions. You help a lot. Keep going and good luck. :)
Hello. I would like to know if this is, like, automated? Like when you add data it will be added on the filtered data on the tab that your appscript created. Or do you need to click run again?
This is awesome, gave me exactly what I needed to know to get the data filtered. I also want to update the column next to my original data indicated that I have copied those rows that met the filter conditions. Any thoughts on how to do that? I filter and copy the data to another sheet, and I want the original sheet to now indicate "Submitted" to the lines that were copied over.
This would be great if it returned filtered items from a data validation drop down within the spreadsheet. Dig the videos, keep it up. Love the typos too XD
Great videos! Very helpful. How would you use filter or map to search the first row columns headings for a key word and then return all the rows that are in that column? I can pull all the rows with a filtered word, but not if the search is restricted to the first row. Any ideas? Thanks.
Thanks for this filter video guide. It is very useful. May I please know the scripts for the sale rep who are not "Jerry Jefferson"? I would greatly appreciate it if you could help me with this matter. Tony
Once again, thanks for another excellent tutorial. One question though. I’ve noticed that sometimes you use == and sometimes === in your formula. What’s the difference?
On a basic level: == checks for value only === checks for value and type example: 4 === "4" IS NOT TRUE, because one is a number, the other is text. So the type is not the same. 4 == "4" IS TRUE, because == doesn't check the type.
HI, Could you please let me know how can i copy data with hyperlink format suppose i have data in which some cells have hyperlink and if copy data and paste it another sheet then hyperlink format should be same, is there any way to do this activity please do let me know
Excellent video!! It helps me to solve searches efficiently. My query is the following, How can I get the row in which the entered value was found? Thank you!
Sir, suppose I have script like this: var leaveFilter = data.filter(data => {return data[1 == "Annual Leave" || data[1] == "Vacation leave" || data[1] == "Sick" || data[1] == "Important leave"}); How if I want make some iteration, or loop perhaps, so I just have to make that filter in array like this: filters = [ "Annual Leave", "Vacation leave", "Sick", "Important leave"] Which later I want that I could use that filters variable to do some iteration/loop in filter method above. Could you help to break this problem?
@@ExcelGoogleSheets I have already tried it, but this time we can't get the values of relevant rows as one column or as all columns which were filtered by color. Because, (firstly thanks for your video but) your video doesn't explain how to get Values of a row together with all columns if that row has relevant color which was expected to be found by filter. Also i want to make this filter as normal filter in same sheet by hiding the rows in which relevant value or background color are not found after filter query. Can you help me on this topic ?
Hi. I used the same method to filter data and the same method to create a new sheet and paste data using setvalues(). But it is throwing an error - TypeError: Cannot read property 'length' of undefined. I checked that the filtered data is not empty. On logging out the length, it is giving numeric values. Can you please help?
This is great tutorial .. IS there a way where we can have one single function which will take multiple search as array and corresponding columns in which to search as an other array and search ie i want to search for "yes" in col3 , completed in col4 , >50 in col5 , I might not have all these 3 everytime... so how do i have a generic function which will run as per length of the search item/column arrays. Hope I am making sense :(
Hi, thanks...but I have a problem with between date array. For example: in my spreadsheets I have a colum with date, it's work currently with getMonth, getFullYear, but not working with between date. This is a code: var sheet = SpreadsheetApp.getActive(); var ss = sheet.getSheetByName("Fatturato"); var data = ss.getRange(4, 4, ss.getLastRow(), 4).getValues(); var startDate = new Date("2017-01-01"); var endDate = new Date("2022-01-01"); var result = data.filter(function(row){ var d = new Date( row[0] ); return d.setDate() >= startDate && d.getDate
How if we want to filter by date which is first column in the video, Sir? I've tried something like this: var date1 = new Date("2/15/2017"); var dateFilter = values.filter(function(r) { return r[0] == date1; }); But it didn't work and I just got an empty [] How we could do that correctly Sir?
@@ExcelGoogleSheets Thanks for your feedback and code Sir. I did a little bit of modification, then changed my time zone setting, considering your advice.. And, great, it works. Again, thanks a lot Sir :)
I followed the advice in the video but I keep getting an error message TypeError: originalData.filter is not a function (line 8, file "Code") function myFunction() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var trn = ss.getSheetByName("Transactions"); var originalData = trn.getRange(2, 1, trn.getLastRow()-1,7).getValues; var salesRep = "Jerry Jefferson"; var data = originalData.filter(function(item){ return item[1] === salesRep && item[4] > 300; }); var targetSheet = ss.insertSheet(salesRep); targetSheet.getRange(2, 1, data.length, data[0].length).setValues(data); } Any help?
Thank you. This is almost exactly what I am looking for, but I do not understand why I am receiving this error - TypeError: Cannot find function filter in object [object Array].- on this line: var data = originalData.filter(function(item){ return item[1] === salesRep && item[4] > 300; });
@@ExcelGoogleSheets If I had a dozen sales amounts that I wanted to filter on, and those amounts were in Col J of Transactions (rows 2-13), instead of writing several ||/or statements, is there a way I could incorporate indexOf into the filter function? That way, if the amounts in Col J changed, I would not need to modify the script. I saw something about indexOf > -1 online, but I cannot apply it correctly to this example. Thank you.
#Deliting row based on condition or when the condition met and copying deleted row in another sheet. &How to get a particular array when the condition met (filter method doesn't go well as it returns a whole array of rows) Can anyone help me out with any inputs and video links? It will be appreciated.
It shown TypeError: originalData.filter is not a function (line 7, file "filter") /which is var data = originalData.filter(function(item){ return item[1] === "BA"; });
![กิโลเมตรที่ 0 เดินทางรอบโลกไม่ใช้เครื่องบิน [Around The World Ep. 1]](http://i.ytimg.com/vi/toJG9oofLFY/mqdefault.jpg)
You are a Perfect Teacher. You have taught me a lot. THANK YOU for all your lessons.
Thank you. This technique just saved me hours, days, months of frustration. Love the videos.
You are really doing a great job. Thumbs up for your efforts and easy to understand teaching style. Thanks a lot.
Amazing!!! Thanks for share!!
Very good!!!!! Congratulations!!!
Awesome! Awesome! Awesome!
This was good overview on filters for Apps Script, following your methods I was able to get a filter function to work, but I was not able to get the function to filter based on a specific date.
This returns me an empty array and I was wondering if you had any advice for using the filter function for a dataset that has dates associated with it?
Awesome. Thank you for sharing.
Thanx a lot, man. You give a lot of good information to up GS skills. I am from Russia and here i cant find a lot of answeres for my questions. You help a lot. Keep going and good luck. :)
Hello. I would like to know if this is, like, automated? Like when you add data it will be added on the filtered data on the tab that your appscript created. Or do you need to click run again?
Thank you for this video! I would like to know if there is a way of filtering by "value" and "background color". Thank you again!
your videos are the best on this field!
Glad you like them!
Great video, simplistic examples so it all makes sense!
This is awesome, gave me exactly what I needed to know to get the data filtered. I also want to update the column next to my original data indicated that I have copied those rows that met the filter conditions. Any thoughts on how to do that? I filter and copy the data to another sheet, and I want the original sheet to now indicate "Submitted" to the lines that were copied over.
This would be great if it returned filtered items from a data validation drop down within the spreadsheet. Dig the videos, keep it up. Love the typos too XD
Thank you for this. May I ask how to Reset all existing filters in a sheet using script?
I feel like a fog has been lifted. Thank you!!
Here I found what I was looking for, thank you so much for sharing ......
Fantastic!
Hi, excellent!
I implement it in reading my students' spreadsheet. I separate one class from another and define it in a cell.
Thank you!!
Awesome 😃 Thanks!
Absolutely super channel and content.
Great videos! Very helpful. How would you use filter or map to search the first row columns headings for a key word and then return all the rows that are in that column? I can pull all the rows with a filtered word, but not if the search is restricted to the first row. Any ideas? Thanks.
Nice video
Thanks for this filter video guide. It is very useful. May I please know the scripts for the sale rep who are not "Jerry Jefferson"? I would greatly appreciate it if you could help me with this matter.
Tony
Once again, thanks for another excellent tutorial. One question though. I’ve noticed that sometimes you use == and sometimes === in your formula. What’s the difference?
On a basic level:
== checks for value only
=== checks for value and type
example:
4 === "4" IS NOT TRUE, because one is a number, the other is text. So the type is not the same.
4 == "4" IS TRUE, because == doesn't check the type.
Learn Google Spreadsheets Makes perfect sense. Thanks for your fast response! 👌🏻
HI, Could you please let me know how can i copy data with hyperlink format suppose i have data in which some cells have hyperlink and if copy data and paste it another sheet then hyperlink format should be same, is there any way to do this activity please do let me know
MAN YOU DOING GREAT THING THANK YOU!
Impressive! Thank you very much for this tutorial. Do you still have your patreon?
Thank You! Yes www.patreon.com/chicagocomputerclasses
Excellent video!! It helps me to solve searches efficiently.
My query is the following, How can I get the row in which the entered value was found? Thank you!
Is there a way to place the results on an html form similar to your instructions for the search in a crud?
Sir, suppose I have script like this:
var leaveFilter = data.filter(data => {return data[1 == "Annual Leave" || data[1] == "Vacation leave" || data[1] == "Sick" || data[1] == "Important leave"});
How if I want make some iteration, or loop perhaps, so I just have to make that filter in array like this:
filters = [ "Annual Leave", "Vacation leave", "Sick", "Important leave"]
Which later I want that I could use that filters variable to do some iteration/loop in filter method above. Could you help to break this problem?
var leaveFilter = data.filter(data => {return [ "Annual Leave", "Vacation leave", "Sick", "Important leave"].includes(data[1])});
@@ExcelGoogleSheets Thanks a lot Sir. Perfectly works :D
Can this filter method can filter date between or filter date by criteria bigger than day , less than date ? How can do it . Thanks !
How do use multiple criteria but for each criterion that is blank have it disregard the data and filter only based on criteria that is non blank?
Nice video man, but just a question .
How can you filter de color of
The cell?
is you use getRange().getBackgrounds() instead of getRange().getValues() it will give you an array of colors in hex format.
@@ExcelGoogleSheets I have already tried it, but this time we can't get the values of relevant rows as one column or as all columns which were filtered by color. Because, (firstly thanks for your video but) your video doesn't explain how to get Values of a row together with all columns if that row has relevant color which was expected to be found by filter.
Also i want to make this filter as normal filter in same sheet by hiding the rows in which relevant value or background color are not found after filter query.
Can you help me on this topic ?
Thank you very much. As for the link of the sheet, when trying to navigate to script editor, it doesn't appear. Can you share the code?
Here's the code used the video. Hope this helps others
pastebin.com/k9sC42Lq
Hi, how can I filter using partial string?, like I want to return all contains "tex".
Hi. I used the same method to filter data and the same method to create a new sheet and paste data using setvalues(). But it is throwing an error - TypeError: Cannot read property 'length' of undefined. I checked that the filtered data is not empty. On logging out the length, it is giving numeric values. Can you please help?
Check your spelling, everything is case sensitive.
This is great tutorial .. IS there a way where we can have one single function which will take multiple search as array and corresponding columns in which to search as an other array and search ie i want to search for "yes" in col3 , completed in col4 , >50 in col5 , I might not have all these 3 everytime... so how do i have a generic function which will run as per length of the search item/column arrays. Hope I am making sense :(
Wow.. thank you very much
Thanks ..
Hi, thanks...but I have a problem with between date array. For example: in my spreadsheets I have a colum with date, it's work currently with getMonth, getFullYear, but not working with between date. This is a code:
var sheet = SpreadsheetApp.getActive();
var ss = sheet.getSheetByName("Fatturato");
var data = ss.getRange(4, 4, ss.getLastRow(), 4).getValues();
var startDate = new Date("2017-01-01");
var endDate = new Date("2022-01-01");
var result = data.filter(function(row){
var d = new Date( row[0] );
return d.setDate() >= startDate && d.getDate
@Learn Google Spreadsheets, if I would like to at the end show in new sheet only 1 column and 7 column, how can I do it?
Use .map method th-cam.com/video/WA8QotNEVc4/w-d-xo.html
Just amazing!!
Thanks!
I appreciate it!
🙏🏻
Hi
thanks for this tutorial,
when i insert a sheet i get an error message "ReferenceError: tab is not defined"
How if we want to filter by date which is first column in the video, Sir? I've tried something like this:
var date1 = new Date("2/15/2017");
var dateFilter = values.filter(function(r) { return r[0] == date1; });
But it didn't work and I just got an empty []
How we could do that correctly Sir?
var dateFilter = values.filter(function(r) { return r[0].getTime() == date1.getTime(); });
Be careful with time zones when you do this.
@@ExcelGoogleSheets Thanks for your feedback and code Sir. I did a little bit of modification, then changed my time zone setting, considering your advice.. And, great, it works. Again, thanks a lot Sir :)
Thank you very much !!!
Man thanks again!!
it's showing error like : TypeError: Cannot read property 'length' of undefined (line 12, file "Test")
how to filter between two dates match with it data
TypeError: Cannot read property '1' of undefined
pls help
This method is removing the header. It is possible to retain it?
let headers = data.shift()
data = data.filter(logic)
data.unshift(headers)
I followed the advice in the video but I keep getting an error message
TypeError: originalData.filter is not a function (line 8, file "Code")
function myFunction() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var trn = ss.getSheetByName("Transactions");
var originalData = trn.getRange(2, 1, trn.getLastRow()-1,7).getValues;
var salesRep = "Jerry Jefferson";
var data = originalData.filter(function(item){ return item[1] === salesRep && item[4] > 300; });
var targetSheet = ss.insertSheet(salesRep);
targetSheet.getRange(2, 1, data.length, data[0].length).setValues(data);
}
Any help?
👍🏻
Thank you. This is almost exactly what I am looking for, but I do not understand why I am receiving this error - TypeError: Cannot find function filter in object [object Array].- on this line: var data = originalData.filter(function(item){ return item[1] === salesRep && item[4] > 300; });
your error happens before that line.
I tried again and it worked. I do not know why it did not work the first time. Thanks again. Very helpful videos.
@@ExcelGoogleSheets If I had a dozen sales amounts that I wanted to filter on, and those amounts were in Col J of Transactions (rows 2-13), instead of writing several ||/or statements, is there a way I could incorporate indexOf into the filter function? That way, if the amounts in Col J changed, I would not need to modify the script. I saw something about indexOf > -1 online, but I cannot apply it correctly to this example. Thank you.
#Deliting row based on condition or when the condition met and copying deleted row in another sheet.
&How to get a particular array when the condition met (filter method doesn't go well as it returns a whole array of rows)
Can anyone help me out with any inputs and video links? It will be appreciated.
It shown TypeError: originalData.filter is not a function (line 7, file "filter") /which is var data = originalData.filter(function(item){ return item[1] === "BA"; });
That means check the line
var originalData = blah blah;
Wish it worked for me.