Spreadsheet Tricks
A couple useful tools to consider when using Google Sheets.
Currently just a few contact related ones, but more to come!
Embedded Files
itty.bitty.site - for downloading data
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
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
Downloading Calendar Data
redirect.app - for triggering actions
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
Call
=HYPERLINK("https://redirect.app/#tel:" & Contacts!H3, "Call")
Text
Text
=HYPERLINK("https://redirect.app/#sms:" & Contacts!H3, "Text")
Note on privacy
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
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)
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
Text
=HYPERLINK("https://redirect.app/#sms:" & Contacts!H3, "Text")
Usage
Usage
Select the pink cell and copy the formula. - Paste it into Cell A2 in a new sheet.
Create a named range called “DATA_RANGE" and point it at your data (or replace that with the name of the sheet)
Select the first row and choose Data Validation, then select the first row of your Data Range area
Report abuse