Replied to

Thanks Ben for sharing this explanation about the difference between QUERY and CHOOSECOL. It was the first thing I though when reading your newsletter.
Bookmarked How to change Google Sheets date format and convert dates to numbers and text by Natalia Sharashova (ablebits.com)
I never cease to be surprised with what I can do with the QUERY formula in Google Sheets. I had a problem today where I wanted to match two datasets using the date column. The problem was that they were in two different formats. So I started searching. Low and behond, I discovered that you can in fact format using the QUERY format. The formula looks something like this:

=QUERY(A1:C7,"select * format B 'd-mmm-yy (ddd)'")

These are the available values:

  • d | Day without a leading zero for 1-9 (i.e. 7)
  • dd | Day with a leading zero for 1-9 (i.e. 07
  • ddd | Day as an abbreviation (i.e. Wed)
  • dddd | Day as a full name (i.e. Wednesday)
  • m (if not preceded or followed by hours or seconds) | Month without a leading zero (i.e. 8)
  • mm (if not preceded or followed by hours or seconds) | Month with a leading zero (i.e. 08)
  • mmm | Month as an abbreviation (i.e. Aug)
  • mmmm | Month as a full name (i.e. August)
  • mmmmm | First letter of the month (i.e. A)
  • y or yy | Two digit year (i.e. 19)
  • yyy or yyyy | Full numeric year (i.e. 2019)

I know Ben Collins has spoken about formatting dates before, but I never knew there were all these options.

Bookmarked Creating an xml file with a script from google spreadsheet (Stack Overflow)

You need to create an xml file with a script from google spreadsheets. I use the code from these two topics:

Google Script Export Spreadsheet to XML File

Looking to create an XML feed from a Google Sheet Table

I was asked about the ability to export the results from a Google Form as an XML file. I found this script which gets me part of the way there, I just need to work out how do produce child elements.
Replied to

Ben, I love the use of grouping columns, but found it problematic when sharing a Google Sheet as it is not unique to the user.
Replied to Creating a Catalogue in Google Sheets by Aaron DavisAaron Davis (readwriterespond.com)

To create theΒ SWITCH, I created a UNIQUE list of all the folders and then pasted them as values so that I could delete those not required to be displayed in the directory. After culling the list, I then added the area in the cell next to each folder. Once I had these two columns complete, I usedΒ this formulaΒ to add quotation marks to them all:

=ArrayFormula(“”””&Sheet1!A:D&””””)

I then copied this list into the SWITCH formula and added in the commas

I have found a cleaner method for adding in the commas and quotations required for the SWITCH formula:

=char(34)&join(“””,”””,ARRAYFORMULA(QUERY(A1:A,”SELECT A WHERE A IS NOT NULL”)&”””,”””&QUERY(B1:B,”SELECT B WHERE B IS NOT NULL”)))&char(34)

Bookmarked Sheets Tip 212: Single Formula Year Calendar! (ckarchive.com)

At the heart of today’s tip is the fantasticΒ SEQUENCE function.

In a blank Google Sheet, enter this formula into cell A2:

=SEQUENCE(53,7,DATE(2022,1,1),1)

It outputs a year of dates across 7 columns!

Add the days of the week as a header row and you’re set.

Ben Collins shares a means of creating a year calendar:

=SEQUENCE(53,7,DATE(2022,1,1),1)

Bookmarked

Ben Collins provides a formula for generating a list of IDs or numbers dependent on how many items are in the list using the Sequence formula:

=ArrayFormula(SEQUENCE(MAX(ROW(B2:B)*(B2:B<>""))-1))

Replied to A Guide To Named Functions In Google Sheets (New For 2022!) – by Ben Ben (benlcollins.com)

Named functions in Google Sheets let you create and name custom functions built with regular formulas, and reuse them in other Sheets.

Ben, am I right to say that Named Functions allows the functionality that is available via Google Scripts, it just means that you do not have to go through the effort of creating the script? I am assuming that if I made a copy of a Sheet with a Named Function that this comes with the copy? I also assume that unlike with Google Scripts, there is no need to provide permission for Named Functions? I am left wondering about combining the use of macros and Named Functions.
Bookmarked Send email when a Google Sheet is edited (spreadsheet.dev)

Suppose you’re a school teacher and you’re planning an upcoming Parent-Teacher conference. Instead of using a printed sign up sheet, you decide to use a Google Sheets spreadsheet where parents can sign up for a meeting slot.

In the past you’ve had parents forget which slot they signed up for so you’re hoping that a shared Google Sheet that they can access at any time will help solve this problem. You also want to receive an email notification whenever a parent signs up for a slot. This will help you keep track of who hasn’t signed up yet so you can remind them when they drop off or pick up their kids.

In this tutorial, I’ll show you how to send email notifications whenever a Google Sheets spreadsheet is edited. We’ll use Google Sheets and some Google Apps Script code to create this “application”.

Although there is a built in Notification Rules within Google Sheets, one of the limitations is that it does not actually stipulates what has changed. I subseqently found this guide for creating a script to send an email when a Google Sheet is edited, as well as this solution to sending to multiple users.

"example@gmail.com" + "," + "example@yahoo.com";

The next step is to think about how this information can be made clearer, possibly listing the specific changes, rather than just highlighting the particular school updated.

Replied to

Not sure about Excel, but pretty confident I could do it with Google Sheets using QUERY formula. Are the datasets public on the web?
Bookmarked Font weight BOLD using formula in cell (Stack Overflow)

How to style from formula in cell?

Two solutions provided for bolding text in Google Sheets.

The first using SUBSTITUTE of one font to another:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(E3,"A","𝐀"),"B","𝐁"),"C","𝐂"),"D","𝐃"),"E","𝐄"),"F","𝐅"),"G","𝐆"),"H","𝐇"),"I","𝐈"),"J","𝐉"),"K","𝐊"),"L","𝐋"),"M","𝐌"),"N","𝐍"),"O","𝐎"),"P","𝐏"),"Q","𝐐"),"R","𝐑"),"S","𝐒"),"T","𝐓"),"U","𝐔"),"V","𝐕"),"W","𝐖"),"X","𝐗"),"Y","𝐘"),"Z","𝐙"),"0","𝟎"),"1","𝟏"),"2","𝟐"),"3","πŸ‘"),"4","πŸ’"),"5","πŸ“"),"6","πŸ”"),"7","πŸ•"),"8","πŸ–"),"9","πŸ—")

The second solution combines REGEX and UNICODE:

=ARRAYFORMULA(JOIN("", UNICHAR(QUERY(UNICODE(SPLIT(TRANSPOSE(SPLIT(
REGEXREPLACE(
REGEXREPLACE(REGEXREPLACE(REGEXREPLACE(REGEXREPLACE(REGEXREPLACE(A2&""
,"([^a-zA-Z0-9])","$1"&UNICHAR(160)&UNICHAR(1)&CHAR(127))
,"'","''")
,"([a-z])","$1"&UNICHAR(160)&UNICHAR(119738)&CHAR(127))
,"([A-Z])","$1"&UNICHAR(160)&UNICHAR(119744)&CHAR(127))
,"([0-9])","$1"&UNICHAR(160)&UNICHAR(120735)&CHAR(127))
,"'","''")
,CHAR(127))), UNICHAR(160))), "select Col1+Col2-1 label Col1+Col2-1 ''",0))))

I prefer the second solution. However, the first is easier to apply to a whole column using the ARRAYFORMULA.

Replied to

Ben, I am intrigued on your QUERY / MATCH combo. Have you written about adjusting the columns? I would love to see a full formula. I have long tried to make a formula that allows me to select which columns to include via a checkbox. However, I can never quite get it to work.
Replied to How To Create A Select All Checkbox in Google Sheets (benlcollins.com)

Learn how to create a select all checkbox in Google Sheets using a clever IF formula and checkboxes with custom values.

I really like checkboxes and have found myself using them more and more. I found this script which I use to clear all my selections, but may need to rethink my solution based on your solution.
Replied to Google Sheets COUNTIF Function (benlcollins.com)

COUNTIF Google Sheets supports three wildcards, *, ?, and ~.

The star * matches zero or more characters.

The question mark ? matches exactly one character.

The tilde ~ is an escape character that lets you search for a * or ?, instead of using them as wildcards.

Ben, I really need to do some work on picking the right tool for the task, so often I just use the QUERY formula to adjust my criterion out of habit. Using a COUNTIF seems a lot cleaner.
Replied to How To Set Default Values For Cells In Google Sheets (benlcollins.com)

Learn how to set default values for cells in Google Sheets without using code. Users can input values or leave at the default.

I always wondered how I could set up a spreadsheet with default values. I initially thought of apps script, but then it becomes another activity for the end user. Using Array Literals seems so much simpler. Another trick to add to the toolbox.
Replied to

Ben, I really like your suggestion about writing a list of spot checks. I recently created a template that was copied over 200 times. I then discovered I had overlooked something and had to open all the copies and make the update.
Bookmarked Amit Agarwal (labnol.org)

Google Forms makes it relatively easy to add such advanced date validation rules to individual fields through Regular Expressions (or regex or regexp). Think of them as search patterns and every character entered in a form field is matched against that pattern – the form can only be submitted if the patter and the user-input matches.

Amit Agarwal provides a list of Regex formulas to use to validate data within Google Forms.