My Two Favorite Google Sheets Functions
Ironically, I didn’t fully appreciate the power of Google Sheets until I got to Pinterest, even though I’d worked at Google.
In particular, there are two functions I still think are underused, especially for anyone who wants to create lightweight workflows:
Let me share a recent example of why these functions are so useful and how nicely they can work together.
A real-life use case
Our Associate General Counsel keeps a running list of what they’re working on in a Google Sheet, with items grouped by category.
Recently, our Partnerships team wanted visibility into that queue so they could check in on the status of their deals without constantly pinging Legal.
The QUERY function is basically SQL for Sheets. You write a SELECT statement over a range of cells.
On a separate tab, I filtered the master list down to just the Partnerships-related rows:
=QUERY('Full List'!A:I,"SELECT A,B,C,D,E,F,I WHERE A = 'Contract: Partner' ORDER BY D")No surprise: if you try to include a column in the SELECT statement that’s not in the provided range, the formula errors out.
At that point, I could have simply shared the AGC’s sheet and restricted access to the “Full List” tab. But that doc had other tabs, and it’s cleaner and safer to keep the “viewer” version separate.
That’s where IMPORTRANGE comes in. It pulls data from one Google Sheet into another automatically.
In a brand-new Google Sheet, I imported the filtered tab:
=IMPORTRANGE("https://docs.google.com/spreadsheets/XX","Partnerships!A1:J200")The result was a dedicated sheet the Partnerships team could bookmark and check anytime for the current status, with no extra work needed from Legal.
Note: when you use IMPORTRANGE, Google Sheets will return #REF! and prompt you to “Allow access.” That’s normal.
What AI suggested
I was happy to find that AI (Claude, in this case) landed on the same solution I did.
It first suggested creating a Partnerships view using QUERY or FILTER. Once I explained that the source spreadsheet had other tabs I didn’t want exposed, it added the next step: use IMPORTRANGE to pull that filtered view into a brand-new sheet.
I hadn’t discussed this exact scenario with Claude beforehand, so this wasn’t it just parroting my own answer back to me.
A quick note on Gemini in Sheets
Since Gemini is built into Google Sheets, I’m really curious to see where this goes.
I’ve been testing it out, and it’s pretty cool that you can ask it to do things you previously would have had to do manually. For example, I asked it to conditionally format a column, and it did.
But it’s not fully there yet. I also asked: “Can you make a tab isolating just the Contract: Partner values?” The result was nonsensical.
Gemini will surely make workflows like this easier to build in the future. Until then, knowing a few formulas is still a real advantage.