How Do I Deliver Custom Data To My Client

[jonathonbyrdziak]

I want to be able to provide my client with a clean google sheet that has the very specific data I'm selling to them. Let's say I'm selling the name, email, address, phone, and credit range.

How do I go about that, while protecting the rest of the data in google sheets from being accessed?

[jonathonbyrdziak]

In this video tutorial I deliver a custom formatted google sheet to our fake client.

We're able to share this document with VIEW access to our clients email, making it possible to show how many of the different kinds of data we've identified from their anonymous website traffic.

Watch the video tutorial

Sample Docs I was working on

Sample Sheet - For My Client
Sample Sheet - The Ads Lab Full Google Sheet

The Formulas I used

VLOOKUP

Google Docs for VLOOKUP

It would make sense to use LOOKUP instead of VLOOKUP, however... there's a bug/feature in LOOKUP where it doesn't always return the exact match. Instead, it will return the "best" match which is a major problem when we only want exact match. So for that reason we use VLOOKUP.

=VLOOKUP(search_key, range, index, [is_sorted])
=VLOOKUP(A2, 'Consumer Data'!G2:RS, 1, false)

The index value is telling Vlookup which column within the range value to display. So if we wanted to return G2 then the index would be 1. If we wanted to return H2, the index would be 2.

COLUMNS

Docs for Columns

To make this easier when we have a very large index, we use COLUMNS to do the count for us. It looks like this.

COLUMNS('Consumer Data'!G2:H) // which returns the integer 2
=VLOOKUP(A2, 'Consumer Data'!G2:RS, COLUMNS('Consumer Data'!G2:RS), false)

ISNA and IF

When a returned value is Not Available then the system will return #NA in the cell. Not only does this make the cells look unprofessional and even confusing to read, it throws off any COUNTUNIQUE function we might want to use when calculating billing. For that reason we us a combination of IF and ISNA to hide the Not Available values.

Docs for ISNA
Docs for IF

 = IF(iSNA(VLOOKUP(A2, 'Consumer Data'!$G$2:$RS, COLUMNS('Consumer Data'!$G$2:$RS), false)), "", VLOOKUP(A2, 'Consumer Data'!$G$2:$RS, COLUMNS('Consumer Data'!$G$2:$RS), false))

Remember to always wrap strings with double quotes " or it will cause an error.

IMPORTRANGE

Finally, when we've completed the organized tab of data we'll want to import it into a new empty Google Spreadsheet that we can share with the end client. This is where IMPORTRANGE allows us to import an entire tab from another document.

= IMPORTRANGE("https://docs.google.com/spreadsheets/d/1wN9gJhc8soH3FiiPBykPpM7DxZpOWBFRm-_6hsWGZAw", "A1:Z")

$

When dragging your forumla across multiple rows and cells it will automatically update the values to stay consistent. If you don't want values to update relative to their dragged position, then declare them absolute values by placing a $ sign in front of the range.

Placing a $ in front of the letter will make the column drag absolute and in the same way, placing a $ before any range number will make the row an absolute value that does not get updated on drag.