Displaying Odd and Even Row Data in Google Sheets

Have you ever wanted to split a list of Google Sheet rows in two by odd or even? Or had a time when you wanted to display all items in a range by odd or even ids in two separate lists?

You don’t have to do this manually, we can work smart and create a formula for each of these scenarios to easily separate your data.

In this tutorial, we will cover three scenarios:

  1. Separate a range of data by odd and even rows.
  2. Separate a range by odd and even where a selected column has a list of numerical ids.
  3. Separate a range by odd and even where a selected column contains a list of alphanumeric ids.

If you want to play along, you can get a copy of the starter sheet here:

Odd and Even | Starter Google Sheet*

*Contains Easter Eggs.

Continue reading “Displaying Odd and Even Row Data in Google Sheets”

GWAOw! 2 – ImportFromWeb by NoDataNoBusiness

In this episode of Google Workspace Add On Walkthroughs (GWAOw!), we take a look at ImportFromWeb by NoDataNoBusiness.

ImportFromWeb is a powerful web scraping tool for Google Sheets that allows you to grab data from any website. The creators call it IMPORTXML on steroids.

You can use ImportFromWeb as a Google Sheets function or use one of their incredible templates.

This tool is ideal for Google Workspace businesses that need to find and store live data from websites. Whether you are scouring the web for new client prospects or keeping an eye on your competition, this might be the tool for you.

In this walkthrough, we really just scratched the surface on ImportFromWeb’s capabilities. We walk through three practical examples covering retrieving a list of books from a web store, listing the first page of Google Workspace jobs on Indeed.com and then getting the latest Tweets for Google Workspace on Twitter. I’ve added the formulas below from the video for you to try out once you installed the add on.

I also managed to score us a 40% discount when you purchase ImportFromWeb after your trial, use the code YAGISANATODE40 at check out to get the discount or use this link:

ImportFromWeb

Check it out on the Google Workspace Marketplace: ImportFromWeb

Example 1 – Book List (xpath)

URL: https://www.booktopia.com.au/search.ep?keywords=Terry+Pratchett&productType=917504

Selector: //*[@id="product-results-p1"]/li

Example 2 – Job List (css selectors)

URL: https://www.indeed.com/jobs?q=%22Apps%20Script%22&vjk=4b000129055a847f

CSS Selector: .jobTitle

CSS Selector URL: .jcs-JobTitle/href

Example 3 – Twitter (jsRendering) – Playground

URL: https://twitter.com/search?q=%23GoogleWorkspace&src=typed_query&f=top

Selector: article

Check out more Episodes of GWAOw! here!

GWAOw! 1 – Workbook Statistics by Sourabh Choraria

In this episode of Google Workspace Add On Walkthroughs (GWAOw!), we take a look at Workbook Statistics by Sourabh Choraria.

Workbook Statistics is a FREE add-on that allows you to get intimate with all of your Google Sheets stats from your menu bar. You can grab stats on the number of rows, sheet names, pivot tables and much more from an individual Google Sheet to the entire workbook.

Check it out on the Google Workspace Marketplace: Workbooks Statistics

Sourabh, being the all-around awesome person that he is has also open-sourced the code for this Add-on. You can find it on his GitHub page.

Check out more Episodes of GWAOw! here!

Filtering IMPORTRANGE data in Google Sheets

Working with IMPORTRANGE data in Google Sheets can be a little tricky. It may feel at times that it does not play by the same rules as when you are building formulas with data in the same Google Sheet.

In this tutorial, we’ll go through two approaches to filtering and sorting your IMPORTRANGE data by using the FILTER and QUERY functions. We’ll run through some examples of each and look at some of their pros and cons.

Then, we’ll wrap things up with a walkthrough and example on how to build your very own dynamic data dropdown dashboard from IMPORTRANGE data that lets us look at a set of sales by any company from our imported data any sales rep that makes a sale to them.

I encourage you to play along with the examples. You can find a copy of the Google Sheet that we will be importing here:

Sales.sheet

Click on the ‘Make a copy’ button to create your very own copy of the sales sheet. There are heaps of bonus formulas in there too along with a few fun Easter Eggs for the curios. 

Continue reading “Filtering IMPORTRANGE data in Google Sheets”

Google Sheets IMPORTRANGE: Looking up data with the VLOOKUP function

Note! This is part of a series on using IMPORTRANGE in Google Sheets. If you don’t know how to use IMPORTRANGE or what it is, I encourage you to head back to the first tutorial.

When I first sat down to write this tutorial, I had a specific opinion that one approach to using VLOOKUP on IMPORTRANGE data was better than another. However, I wanted to be certain. I ran some basic tests comparing the two approaches to see how they both perform over large data sets, and you know what? I was surprised to find I couldn’t find a discernable difference in performance.

Next, I reached out to some of the other fellow Google Sheets nerds, who like to go way too far with software for all the wrong reasons, and they seemed to feel that those demi-gods of Google devs really understand the wayward predilections of their users and may have benevolently stored the IMPORTRANGE data locally in the sheet you have imported to.

Will we ever know for certain? Only ever perhaps in the lay of the tea leaves or roll of the bones. We can but only guess the ways of the Googler as they traverse the digital world in all their etheral glory.

What does this mean in short? There are two pretty solid ways to use VLOOKUP with IMPORTRANGE. I’ll cover both in this tutorial.

Continue reading “Google Sheets IMPORTRANGE: Looking up data with the VLOOKUP function”

%d bloggers like this: