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. :)
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?
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
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!
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.
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?
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.
@@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 ?
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
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
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
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?
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?
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?
#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.
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.
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"; });
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.
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. :)
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!
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?
your videos are the best on this field!
Glad you like them!
Awesome! Awesome! Awesome!
Very good!!!!! Congratulations!!!
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.
Thank you for this. May I ask how to Reset all existing filters in a sheet using script?
Great video, simplistic examples so it all makes sense!
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
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!
Awesome. Thank you for sharing.
Amazing!!! Thanks for share!!
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! 👌🏻
Fantastic!
Here I found what I was looking for, thank you so much for sharing ......
Awesome 😃 Thanks!
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?
MAN YOU DOING GREAT THING THANK YOU!
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.
I feel like a fog has been lifted. Thank you!!
Absolutely super channel and content.
Hi, excellent!
I implement it in reading my students' spreadsheet. I separate one class from another and define it in a cell.
Thank you!!
Nice video
Thanks ..
Impressive! Thank you very much for this tutorial. Do you still have your patreon?
Thank You! Yes www.patreon.com/chicagocomputerclasses
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 ?
Thanks!
I appreciate it!
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
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
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
Man thanks again!!
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?
Is there a way to place the results on an html form similar to your instructions for the search in a crud?
Can this filter method can filter date between or filter date by criteria bigger than day , less than date ? How can do it . Thanks !
Just amazing!!
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
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".
Thank you very much !!!
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
🙏🏻
Hi
thanks for this tutorial,
when i insert a sheet i get an error message "ReferenceError: tab is not defined"
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.
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 :)
how to filter between two dates match with it data
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?
it's showing error like : TypeError: Cannot read property 'length' of undefined (line 12, file "Test")
This method is removing the header. It is possible to retain it?
let headers = data.shift()
data = data.filter(logic)
data.unshift(headers)
@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 ruclips.net/video/WA8QotNEVc4/видео.html
TypeError: Cannot read property '1' of undefined
pls help
#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.
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.
👍🏻
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.