Referring to Columns By Name in Google Sheets Query()

How to query a Google Sheet by referring to columns by name? This is an issue we particularly experience when importing external CSV files (Comma-Separated Values). I will show a better way to import and link CSV files in a Google spreadsheet. I will also explain a simple technique for querying data from an external CSV using the powerful query() function to create more resilient, robust spreadsheets.

Google Sheets, an important component of Google G Suite (Google Workspace), is becoming an essential tool for many people to automate repetitive tasks or create MVPs (Minimum Viable Products). Many times, we find Google Sheets to be the quickest and most approachable tool to automate a new business process. In many cases, that involves importing one or more CSV files from an external system (e.g., your eCommerce or CRM platforms) to generate reports and trigger certain actions. In a rapidly evolving context, the structure of the CSV files we need to import can frequently change. Any change to the CSV structure can disrupt our Google spreadsheets that link to it. But don’t worry, there is a simple technique to protect the consistency of a spreadsheet even when the CSV file’s structure changes significantly.

Can we update CSV files without breaking our formulas?

Importing CSV files into a spreadsheet makes new data accessible to popular formulas like lookup() or the most advanced query(). The latest is a de facto standard for generating complex reports within a Google spreadsheet. While spreadsheets are very powerful tools, they suffer from many limitations and vulnerabilities. One recurring issue is the fragility of the query() formula. The SQL-like command in the formula assumes that the CSV structure is static and doesn’t change when refreshing the file’s content. The assumption is that each field can be accessed by its column position, e.g., column B, column E. This is not always the case, as many of us learned the hard way when importing the same CSV data. The sources of our data occasionally change the column position of fields by adding or removing columns. Every while, the source of our CSV even renames the columns with new labels. While these changes can be justified and presented to us as improvements, there is always the risk that they will break our formulas, forcing us to manually revise the entire spreadsheet by updating each field and formula with the new position. It’s a long, cumbersome process that I don’t really enjoy doing. That’s why I designed a system to make my spreadsheets more resilient to this kind of CSV changes.

A better way to import and query CSV files

Let’s describe a very simple Let’s describe a straightforward case of a spreadsheet with 3 tabs:

• “CSV” tab will host the imported data from a CSV file (sample data courtesy of SpatialKey)
• “Report” tab will contain a sample report generated by a query() function working on the CSV data
• Finally, a “DataMap” tab where we will put a simple table to make our spreadsheet resilient to CSV changes.

To generate the simple report, I’m using a basic query to extract all the records where Country=’United States’. This assumes the “Country” field will always be in column H of the CSV tab.

=query(
    CSV!1:999,
    "SELECT * WHERE H ='United States'", 
    -1)  

Every change that moves the field “Country” to a different column will break my formula.

Step 1: Going from Column Letter to Column Position with Literal Arrays {}

A solution is to create a map that maps column positions to a variable (a named range in Google Sheets) and then uses that variable in the query formula. To get there, we first need to change the way we refer to the field position to a literal array by enclosing the first argument of the query() formula in brackets {}. This will allow us to refer to the columns by column number (Col1, Col2, …) rather than by column letter (A, B, C, …).
Here is the same formula with the new notation:

=query(
    {CSV!1:999},
    "SELECT * WHERE Col8 ='United States'", 
    -1)  

Step 2: Implementing a Data Map

The next step is to create a map between the column headers of the CSV tab and the Column number. We will put this map in a tab called “DataMap”.

The items in the 2nd column are the column labels I plan to use for my queries. I don’t need to map all the headers, only the ones I’m gonna use in my formulas.
The items in the first column are calculated by the spreadsheet using the following simple formula. What the formula does is to search, using the match() formula, for a header in the CSV tab that matches the label defined in the cell to the right of the formula.

= "Col" & 
  match(
    B2, CSV!1:1, 0)

Step 3: Creating a named range to use in our formulas

We need to create a “variable” to help us not only write more resilient query() formulas but also create formulas that are easier to understand than those based on the column position. To do that, we add a 3rd column where we assign a name to each field. We can be very creative here, but I recommend sticking to a simple naming convention, like starting every field name with “F_” to make it immediately recognizable. Once we have completed the 3rd column, we can name each cell of the first column with those exact names. For example, the 8th cell of the first column (excluding the header) should be called F_COUNTRY. We can do that by using the “Define Named Range” command of Google Sheets, as shown in the picture below.

Step 4: Making query() formulas resilient and easier to read

Now we can go back to our query() formula and apply those changes to make the formula much easier to read and resilient to CSV changes. The new formula will be:

=query(
    {CSV!1:999},
    "SELECT * WHERE " & F_COUNTRY & "='United States'", 
    -1)  

This should do the trick. We can now move the “Country” column without breaking our formula. This should do the trick. If the “Country” column changes position inside the CSV file and tab, it will not break our formulas. Also, our spreadsheet can quickly adapt to label changes. If in the future the label “Country” changes in the CSV, e.g., to “Customer Country,” we can promptly repair our spreadsheet by merely changing the Country label in our map (cell B9) as shown in the image below:

Download the sample Spreadsheet

I’ve shared the spreadsheet described in this article, and it’s now available to download. If you like to test the solution described in this article, make a copy of the spreadsheet and make your own changes.

Conclusions

I hope this simple technique can help you create more robust and manageable spreadsheets. If you find this article useful or have any recommendations to improve this solution, please let me know by leaving a comment in the area below.

Similar Articles

Connecting Google Maps and Google Sheets to Solve the Traveling-Salesman Problem

Use Google Sheets and and Google Maps to find the shortest route among a list of addresses. Learn how to use the power of Google Maps from Google Sheets. It’ss not that complicated.

A better use of lookup(), vlookup(), and hlookup() with examples for Google Sheets

There is a better way to use Google Sheets lookup() functions With a few simple examples you will learn how to hide your data (hash tables) and use the functions in a more advanced yet simple way.