Spreadsheet Tricks

A couple useful tools to consider when using Google Sheets.

Currently just a few contact related ones, but more to come!

itty.bitty.site - for downloading data

itty.bitty.site is able to render any data for downloading. All you need to do is provide the right mime-type and content.

I use this for contact cards, but it can probably be used to make calendars and much much more.

Downloading Contact Cards

You can create a calculation that generates a vCard content payload:

= ENCODEURL(JOIN(char(10),

"BEGIN:VCARD",

"VERSION:3.0",

"N:" & B3:B & ";" & A3:A,

"TEL:" & H3:H,

"EMAIL:" & I3:I,

"END:VCARD")))

And then use itty.bitty to download it.

= HYPERLINK("https://itty.bitty.app/#" & Contacts!A3 &"_"& Contacts!B3 & ".vcf/data:text/directory;charset=utf-8," & Sorted!X3, "Download Contact")

This tells itty bitty to treat it as a vcard file, and supplies the content that goes with it.

In the above example A3, B3 are the name, and other fields provide telephone and email.


Downloading Calendar Data

redirect.app - for triggering actions

redirect.app lets you use native urls, when Google Sheets usually ignores them. These include things like tel:, sms: and more.

Call

=HYPERLINK("https://redirect.app/#tel:" & Contacts!H3, "Call")

Text

=HYPERLINK("https://redirect.app/#sms:" & Contacts!H3, "Text")

Note on privacy

Both itty.bitty.site and redirect.app do all their work in your web browser. The server that hosts them does not have access to any of the data that is sent.

Using dynamic column lookup

This lets you filter a big sheet of data to just a few columns dynamically - Here's an example of data filtered from here.

Main Formula (Placed in cell A2)

=QUERY({OFFSET(DATA_RANGE,1,0)}, textjoin(" ", 1,

"SELECT", TEXTJOIN(",", 1, ARRAYFORMULA(

IF(ISNA(match(1:1, INDEX(DATA_RANGE,1,0), 0)), "'⚠️ " & IF(len(1:1), 1:1, REPT(CHAR(8203),column(1:1))) & "'",

"Col" & match(1:1, INDEX(DATA_RANGE,1,0), 0)

))),

"WHERE Col1<>''",

"ORDER by Col" & match("Alpha", INDEX(DATA_RANGE,1,0), 0),

),

0)

Text

=HYPERLINK("https://redirect.app/#sms:" & Contacts!H3, "Text")

Try it

Usage

  1. Select the pink cell and copy the formula. - Paste it into Cell A2 in a new sheet.

  2. Create a named range called “DATA_RANGE" and point it at your data (or replace that with the name of the sheet)

  3. Select the first row and choose Data Validation, then select the first row of your Data Range area