I have improvised Ben's approach to simplify the query formula entry by creating two helper rows with the header names and the "Col#" reference derived from the XMATCH equation. Instead of calling out individual headernames in the select statement, the formula simplifies to: =query(RangeName,"select "&TEXTJOIN(", ",TRUE,A2:F2)&"") where A2:F2 stores the "Col#" results.
This is amazing, Ben Collins! I just found your channel on how to start with App Script, and I discovered this helpful video on using complex formulas. This will definitely boost my productivity at work. Thank you so much!
Great, clear video Ben. Thanks. But what a kerfuffle. The optimal solution would be to just use the column names, and it would keep the code so much shorter and clearer. Do you know if Google are planning to enable this feature in query strings any time soon?
Ben Collins, you are amazing! You have provided the most elegant, simplest, and easy to implement workaround to one of the epic fails in gSheets. I hope that you can influence Google Product team to address this obvious blunder where one cannot simply query using header names.
I have improvised Ben's approach to simplify the query formula entry by creating two helper rows with the header names and the "Col#" reference derived from the XMATCH equation. Instead of calling out individual headernames in the select statement, the formula simplifies to: =query(RangeName,"select "&TEXTJOIN(", ",TRUE,A2:F2)&"") where A2:F2 stores the "Col#" results.
This is amazing, Ben Collins! I just found your channel on how to start with App Script, and I discovered this helpful video on using complex formulas. This will definitely boost my productivity at work. Thank you so much!
Thanks Ben! Something tells me this will become my favorite RUclips channel...
Thanks! Appreciate your support 🙏
Great, clear video Ben. Thanks. But what a kerfuffle. The optimal solution would be to just use the column names, and it would keep the code so much shorter and clearer. Do you know if Google are planning to enable this feature in query strings any time soon?
Ben Collins, you are amazing! You have provided the most elegant, simplest, and easy to implement workaround to one of the epic fails in gSheets. I hope that you can influence Google Product team to address this obvious blunder where one cannot simply query using header names.
Great tutorial, this helped simplify a rostering system I built for a non-profit - well done!
Thanks Ben, a pretty simple solution. As you are aware, I developed a solution as well and used Named Functions to simplify the implementation.
Yes, I remember. Using named functions is a smart idea anytime you reuse complex functions over and over. Happy New Year!
I would love to have access to this Named Function! Do you mind sharing the code or point me to where you have it?
@@KevnReid Try "=concat("Col",xmatch(header_name,headers))" where header_name is the function's header name argument (e.g. "Total Sales").
Wow you fixed what Google Sheets were missing on QUERY :D
Woww best video sir🎉🎉
EXACTLY what I needed! Thank you!
I am trying to grab data from different work sheet the xmatch isn't working. Its saying the range name is unknown.
Very interesting application. Thank you.
Thanks. You're welcome.
Nice sr thanks
Nice 👍🏻
aamazing thanks
header keyword is not working
i just protect the headers 😅