DEV Community: Marie Mosley

Sending questions to the future

Marie Mosley — Mon, 07 Sep 2020 06:22:09 +0000

2020 is my year of personal computing.

When I think “I wish there was an app for...”, I build myself that app with low/no-code tools. Now I have a bunch of nice single-purpose apps on my phone, custom-built for one user: me.

Most of the tools I use for this are built into the apps I already use every day. When I want to stitch apps together, Zapier fills the gap-iers.

I called my newest personal app “Ask Future Me”. It lets me write a journal prompt and send it to myself in the future, and it gives me a place to record my answer.

It’s a little act of belief in an interesting future, built with some of the magic of the contemporary computing age.

It uses Google Apps (Forms, Sheets, and Calendar) and one Zapier zap.

What it Does

Records a question I want to answer in a future journal entry, plus the date and time when I want to answer it
Sends me a push notification prompting me to answer the question on that date and time
The push notification includes a link to a form that is pre-filled with:
- the question
- the date it was originally asked
On that form, I can:
- Enter my answer
- Check a box to indicate that I have a longer answer to the prompt in my handwritten journal

How it was Built, Step by Step

I’m going to take you through the steps to create it in what I feel is the most efficient order.

It’s not the same order I took when I was building the app! I went through all the usual struggles of trying to tell a computer what to do. I’ll spare you that drama and just show you what worked.

⚠️ Before You Start ⚠️

This tutorial assumes you're already comfortable with Google Forms, Google Sheets, and Zapier. I won't go into detail on how to use the basic features of those apps.

I'll use the QUERY, SPLIT and JOIN functions in Google Sheets. You don't need to know how these specific functions work, but you should be comfortable with creating and editing formulas in Google Sheets.

Step One: Taking Questions

The first step is creating a Google Form to record questions for the future.

The form takes in a question, a date, and a time of day.

If you could use a refresher on how to set up a form, check out Google's documentation for forms.

This form automatically creates a new Sheet in Google Sheets that records form entries and a Timestamp column. This sheet is where things start to get interesting.

Step Two: Create a Formula-Ready Copy of the Questions Sheet

I’ll show you why I need to use formulas in step three, but for now let's get the sheet data ready to work with formulas.

Each Google Forms response creates a new row in the Sheet that records the responses. Which, unfortunately, means you can’t put formulas directly onto the responses Sheet. If you do, they’ll be erased by the responses as they’re added to the sheet.

To work around this, I create a second Sheet tab on the Sheet with the form responses.

I name the form entries tab "Questions" and the second sheet "Formula Sheet".

On "Formula Sheet", I use a QUERY function to copy over all of the the content from the first three columns of "Questions" into "Formula Sheet".

I like to use named ranges in QUERY formulas. In this example I named the columns on the Questions sheet "Questions", but you can use a different named range or the column + cell ranges.

At this point, the difference between the two Sheets is subtle, but important. On the "Questions" Sheet, every entry takes up a full row. On “Formula Sheet”, every entry is confined to its column, leaving space for formulas in the other columns.

Step Three: Format the Question and Asked-On Date for URL Parameters

Google Forms lets you prefill fields using URL parameters. This means I can build a link that will automatically fill some of the form fields on the "Answers" form with data from the "Questions" form. I will add this link to the Google Calendar event so it will send me straight to the prefilled answer form.

To make that work, the question and date from the Questions form needs to be in a URL-friendly format.

Google Forms’ prefill format takes dates in YYYY-MM-DD format, and text with a plus sign (+) in place of spaces.

So this date in the Timestamp column: 09/12/2020
Needs to be converted to: 2020-09-12
And this question: Which basil plant turned out better?
Needs to be converted to Which+basil+plant+turned+out+better?

I convert the Timestamp column to the YYYY-MM-DD format with Google Sheet’s built-in formatting tools.

YYYY-MM-DD is under Format > Number > More Formats > More date and time formats

Removing the spaces in the question and replacing them with plus signs calls for a formula using SPLIT and JOIN.

I create a new column on the Formula Sheet called “Concatenated Question” and enter this formula (sending it down the full column):

=IFERROR(JOIN(“+”,SPLIT(B2,” “)),””)

This SPLITs the question into individual words, then JOINs them back together with plus signs in place of the spaces.

I went into more depth on how these two functions can work together in this post about manipulating text in a spreadsheet cell if you’re interested in learning more about how this works.

I wrapped the split & join in an IFERROR function that sets the cell to blank if the split & join errors out. This prevents the sheet from filling up with a bunch of false-alarm errors when the B column is blank. It will be blank until there’s a new question on the sheet; that’s expected.

Here's what the Formula Sheet looks like once the date formatting and the split & join are done. Notice that the Timestamp date is in YYYY-MM-DD format, and the Concatenated Question field has a copy of the question with plus signs in place of the original spaces.

Step Four: Build the Answer Form

Now I need a form to accept the answers. I create a second Google Form with fields for:

The original question
The date when I asked the question
My answer to the question
A checkbox to indicate if there is a paper journal entry that corresponds to this question

Now that this form is created, I can use it to generate a prefillable URL that I can modify for use in the Zapier zap.

Step Five: Get the Prefillable URL

Every Google Form has a “Get pre-filled link” option in the 3-dot menu up top by the “Send” button.

To get a prefill link, I enter a test answer and date on the answer form, then click the "Get Link" button. That triggers a little popup that says "Share this link to include pre-filled responses COPY LINK".

Clicking "COPY LINK" gives me a URL that includes the unique IDs for each of the prefilled form fields.

The URL ends with a query string that looks this:

&entry.FIELDNUMBER=Example+answer&entry.FIELDNUMBER=2020-09-12

Where FIELDNUMBER is the unique ID number for the field to prefill. For now, I just copy and paste this URL to a scratchpad so it's handy when I need it in step eight.

When I add this prefilled form link in Zapier, I’ll modify it to insert the the "Concatenated Question" from the "Formula Sheet" in the “original question” field, and the formatted Timestamp for the date I asked the question in the “asked on” field.

Step Six: Connect Google Sheets and Google Calendar in Zapier

With both forms built, all the data in the right format, and field IDs discovered, I can now use Zapier to link up the Forms with Google Calendar.

I create a new calendar event that, on the date and time I enter on each question form, sends me the question with a link to a form to enter my answer. The link prefills the form with the question and the date the question was asked on. It's up to me to fill in the rest with my journal entry.

This is a single-task zap that can be built on the free Zapier tier.

The first step is to connect Google Sheets with Google Calendar. Set "When this happens..." to "New Spreadsheet Row in Google Sheets", and "Do this..." to "Create Detailed Event in Google Calendar".

Then, there are some configuration steps to get the zap up and running.

Here’s a quick walkthrough of configuring the zap.

Step Seven: Configure Google Sheets in the Zap

On the “Choose App & Event” step, select “Google Sheets” and “New Spreadsheet Row”
For “Choose Account”, choose the Google account that has your Forms.
For “Customize Spreadsheet”, choose “Ask Future Me” (or whatever you named your questions form) as the Spreadsheet, and “Formula Sheet” as the Worksheet.

Step Eight: Configure the Calendar Event in the Zap

On the “Choose App & Event” step, select “Google Calendar” and “Create Detailed Event”.
For “Choose Account”, choose the Google account that owns the Calendar you want to use for your reminders.

“Customize Detailed Event” is where we finally get to use those query parameters.

Choose the “Calendar” where you want to add the question. In the “Summary” field, I put “A question from past me”. This appears as the title of the event on the calendar and in push notifications.

Then, put your cursor in the Description field and select “Insert Data”. I inserted the question (from the Formula Sheet form column labeled “What should I ask myself?”) and the prefill URL to the answers form, including the query parameters.

After the equals sign (=) that follows each form field ID number, I insert data from the related field on the Formula Sheet.

Here I've inserted the contents of the "Concatenated Question" after the equals sign following the field ID for "The original question" field on the answer form, and the contents of the Timestamp field after the equals sign following the field ID for the "Asked On" field.

I've crossed out the form ID and field IDs from my own URL here because they're unique to every form. Yours will be different. The form ID is a very long alphanumeric string, and the field IDs are short numeric strings.

Now the zap is ready. Questions can be sent to the future 🚀

The Finishing Touch: Making the Questions Form Look Like an App on an iPhone

I can enter my questions for the future in any browser, but I like having a button I can push on my phone to open it up, app-like.

A Google Form is a webpage, and just like any webpage I can pin it to my home screen on my iPhone.

To do that, I airdropped the link to the form from my desktop computer to my iPhone and opened it up in Safari. From the “Share” menu, I selected “Add to Home Screen”. This gives me an app-style icon for the form so I can open it up in one click from my phone.

Here it is hanging out with the other journaling apps I use on my phone.

That's the works! Happy personal computing 👩🏼‍💻

Alphabetize a Comma-Separated List in Google Sheets

Marie Mosley — Mon, 20 Jan 2020 21:31:06 +0000

I subscribe to Ben Collins' "Google Sheets Tips" newsletter, and I learn so much from it!

This week's newsletter included a challenge: alphabetize a comma-separated list of words with a single formula.

As in, go from this in cell A2: Epsilon,Alpha,Gamma,Delta,Beta
To this in cell B2: Alpha,Beta,Delta,Epsilon,Gamma

Ben dropped a hint that the TRANSPOSE function would help solve the challenge.

Up to now, I've only used TRANSPOSE for one thing: filling a column with the contents of a row, or vice-versa, usually when referencing data from one sheet on another. So, I was a little puzzled about how it would help alphabetize values in a single cell — and how that could work in a one-liner 🤔

I took some time to pick apart the challenge and I was able to crack it in four steps with the help of TRANSPOSE and three other functions.

You can see the formula broken down in Google Sheets. Each part of the formula has its own sheet in there so you can see how this works step-by-step.

Step One: `SPLIT`

When I see a cell with comma-separated values in it, I know I'm probably gonna have to do a SPLIT 🍌

The SPLIT function breaks text in a cell into pieces based on a delimiter character, and puts each piece of the text into a separate cell in the same row.

In this case, the text in the cell is a series of comma-separated words, so the delimiter character is the comma.

I split the text in the cell into individual words with this function:

=SPLIT(A1,",")

Ok, cool, that breaks the comma-separated string into individual words. But how do I sort them into alphabetical order?

Ideally I'd use SORT, which can alphabetically sort words in a column.

But...these words aren't in a column. They're in a row.

Ah ha!💡This is where TRANSPOSE pitches in to help.

Step Two: `TRANSPOSE`

After the SPLIT I have all my words in their own cells across the row. But since I would like to SORT them, they should really be in a column. Remember how TRANSPOSE can turn a row into a column?

By wrapping the SPLIT function that broke the string into words in a TRANSPOSE function, the split-up words are arranged as a column.

=TRANSPOSE(SPLIT(A1,","))

That's a column that I can now SORT!

Step Three: `SORT`

SORT can do a lot more than just alphabetize, but that's all I need it for today.

When I wrap the split & transposed words in a SORT function, they are alphabetically sorted.

=SORT(TRANSPOSE(SPLIT(A1,",")))

The hard part is done! But they're still in a column, and the challenge was to return it all in one cell. So there's just one more thing left to do. JOIN the words back together.

Step Four: `JOIN`

JOIN is like the opposite of SPLIT. Instead of breaking a string up at a delimiter, it creates a string from values (aka, concatenates the values) with a delimiter.

=JOIN(",",SORT(TRANSPOSE(SPLIT(A1,","))))

That brings the split, transposed, and sorted list of values together into a single, comma-separated list — all in one cell 😁

Thanks to Ben Collins for the challenge, I'm looking forward to the next one!

Hitting the SWITCH in Spreadsheets

Marie Mosley — Wed, 04 Dec 2019 21:07:04 +0000

The SWITCH spreadsheet function is a concise way to write what could otherwise be a convoluted IF/OR function. I'll show you two possible uses for SWITCH in this post. I know you will find many more uses on your own once you know it 😁

You can follow along with the examples in this post in my SWITCH function demo in Google Sheets.

A SWITCH Example

Let’s say you’re running a fitness center with a daily yoga class. There are 3 yoga instructors. They work on this schedule:

Tom teaches on Saturday, Sunday, and Thursday
Alexis teaches on Monday and Friday
Marianne teaches on Tuesday and Wednesday

How would you automatically show which teacher is teaching the class based on the day of the week in a spreadsheet column?

You could do this with nested IFs, and maybe some ORs, but that’s complicated and messy, especially if you ever need to update the schedule!

This is where the SWITCH function shines. You can write the whole thing inside one set of parentheses, no nesting required.

Here’s how you could write a SWITCH function to automatically show the right instructor for each day of the week:

=SWITCH([cell],"Saturday", "Tom", "Sunday", "Tom", "Monday", "Alexis", "Tuesday", "Marianne", "Wednesday", "Marianne", "Thursday", "Tom", "Friday", "Alexis")

Replace [cell] with the first cell in the column that contains the values you want to switch. In this screenshot I'm staring with C2.

I wrote the function in day of the week order, but that’s not required. What matters is that the possible value from the selected column goes first, followed by what you want to show as the result for that value.

So in this case, "Saturday", which would be in the day of the week column, is followed by "Tom" because I want to show "Tom" in the formula column anytime "Saturday" shows up in the days of the week column.

I couldn't do "Tom" followed by "Saturday" because "Tom" wouldn't be in the days of the week column — or at least, he shouldn't! If this SWITCH found a "Tom" in the days of the week column it would throw an error.

SWITCH Benefits

SWITCH can save you from having to make a complicated series of nested IFs when you have a finite list of possible combinations.
It’s case-insensitive on the value, so you don’t have to worry if the stuff in the value column isn’t all in the same case (in fact, that's a good job for a SWITCH! You can use it to standardize capitalization in a new column when the text in the value column is in mixed case 😉).
You can create a “default” value for when you don’t have a match for a value. That's useful for spotting missing data or other unaccounted-for situations in your sheet. I'll show you how to do that in the next example.
You can use it in Excel, Google Sheets, and Airtable.

SWITCH Limitations

SWITCH can't do comparisons like greater than/less than on its own.
It can’t do “fuzzy” matches. Exact spelling matters!
It’s not available in Numbers for Mac (a huge bummer for me, personally).

Another Example: SWITCH with a Default Value

Let’s say you’re on a product team where tickets with a specific tag always get assigned to one person. I’m using my 7-person team at CodePen in this example.

In this demo we have 10 different possible ticket tags:

Payment, Documentation, Design, Redux, DB, React, CSS, AWS, jQuery, GraphQL, Ruby

And 7 different possible ticket owners:
Tim, Alex, Chris, Marie (that’s me!), Rach, Klare, Stephen

Tim handles: AWS and DB
Alex handles: Payment
Chris handles: CSS and jQuery
Marie handles: Documentation
Rach handles: GraphQL and Redux
Klare handles: Design
Stephen handles: React

Nobody has been assigned to handle Ruby or DNS tickets yet (and SWITCH is going to let us know that!)

Notice that some people have more than one ticket type that should be assigned to them.

The SWITCH would go like this:

=SWITCH([cell],"AWS", “Tim","DB","Tim","Payment","Alex","CSS","Chris","jQuery","Chris","Documentation","Marie","Redux", “Rach", “GraphQL","Rach","Design","Klare","React","Stephen","UNASSIGNED")

Again, be sure to use the actual cell reference in your formula. In this screenshot I'm starting with G2.

That last value, UNASSIGNED, is the optional default value. It’s displayed when the function encounters a tag that doesn’t have a match in the switch list. That tells us we need to figure out who will take ownership of those tickets. When we decide who that will be, we can edit the function to include the new name.

Documentation

Google Sheets documentation for SWITCH
Excel documentation for SWITCH

Ok you’re all set, go flip a SWITCH! 💡

Recreating Excel’s ISNUMBER in a Numbers Spreadsheet

Marie Mosley — Sun, 02 Jun 2019 17:45:13 +0000

The other day I had to go through a bunch of strings in a .csv file and find which ones ended with two digits.

They were all short strings created from a limited character set that included all the letters of the English alphabet, numbers 0-9, dashes, and underscores. Like:

Rolf01
layout_final_final_final
menu-v2
Sally06
Marius48
header_large_v2
michelle-78
Heather78
group-in-august

I opened the file in Numbers for Mac, fully planning to use ISNUMBER to find if the last two characters of each string were numbers. Excel and Google Sheets both have that function. But as it turns out, Numbers doesn't 😬

The Formula and the Filter

After a little digging in the Numbers docs I came up with an alternative.

I put together this formula, which I added to column B.

NOT(ISERROR(VALUE(RIGHT(A2,”2”))))

This printed TRUE in the cell if the last two characters were numbers, or FALSE if the last two characters were not numbers.

Then, using Numbers’ “Organize” feature, I filtered the sheet by Column B, where the text is not "FALSE".

That filtered out all of the rows where the strings in column A didn’t end in two digits.

How the Formula Works

This formula is a series of questions about the strings in column A. The questions start in the innermost function and works outward.

My first question is: what are the last 2 characters of the string in column A? As a function that’s:

RIGHT(A2,”2”)

This function returns a new string made up of the two characters at the right end of the string in cell A2. That’s another way of saying the “last two” characters in a string read left to right.

Once I had those two characters, the question I ask with the next two steps of the function is “are those two characters numbers?”

VALUE(RIGHT(A2,”2”))

VALUE returns the “number value” of a string. It’s handy for things like getting just the number from a price string that includes a currency symbol. For example, it would return 100 from a string that read $100.

When the string does not have a number value—for example, because it’s a string of letters—the VALUE function will throw an error.

So at this point in the function, I’m using it to return the numeric value of the last 2 characters of the string in column A.

If I stopped here, I would get numbers in the cells where the string had a number value, and errors in the cells where the strings did not have a number value.

That’s technically enough to sort the sheet — I could filter out all rows where column B’s value is not within the range of 0 - 99. But it's messy.

To keep things tidy, I added a check for if the VALUE function returned an error.

ISERROR(VALUE(RIGHT(A2,”2”)))

ISERROR returns TRUE if the expression inside it returns an error, or FALSE if the expression inside does not return an error.

Here, I’m asking it to check if the numeric VALUE of the two characters at the RIGHT end of the string in A2 returns an error. If it does, the question ISERROR is answered with TRUE.

That answers my question about whether or not the last two characters are numbers: TRUE means there was an error with getting the number value of the last two characters, so they are not numbers. FALSE means there was no problem with getting their number value, so they are numbers.

And, it’s enough of an answer to organize my sheet. I could filter out the rows where this formula returned TRUE .

That’s a nice short formula that gave the answer I needed, but I felt that formula brevity got in the way of spreadsheet clarity here.

Saying Yes with TRUE

Since the question I’m asking this sheet is “does this string end with two digits?”, Answering ”yes” by returning TRUE feels better than answering “yes” with FALSE.

And that’s where the last piece of the formula comes in: the NOT function.