How to Use Google Sheets in Your Google Sites

This is a guest post from Kyle Horst of  Kirksville Web Design. 

Introduction

Google Sites is an amazing app for bringing together information across an organization’s Google Workspace domain. However, Google Sites doesn’t natively have a CMS, databases or even a decent option for basic tables. Therefore, Google Sheets is hugely important for building a Google Sites hub that serves up fresh data to teams.

Let’s discuss key use cases for Google Sheets in Google Sites and how to implement them. Also, we’ll learn about how to effectively utilize Google Sheets for organizing, sharing and managing information. There are some limitations when integrating Sheets into a Google Site, which we’ll also cover. 

How to Embed a Sheet and Adjust Settings

The process for inserting a Sheet into Google Sites is streamlined. You won’t have to deliberately publish the Sheet(s) or any special iframe code.

Here’s how easy it is:

  1. Enter editing mode on your site
  2. Select “Insert” from the right panel
  3. Choose “Drive” from the top four primary options
  4. Select the Google Sheets file from your Drive or Shared Items
  5. Drag and drop the sheet into place on the page and confirm sharing settings
  6. Check embedded widget settings to select the default displayed sheet tab

Your embedded Sheet may look different than what you natively see in the full Sheets version. For instance, not all custom fonts are supported for your embedded Sheet. So a tip is to stick to basic universal fonts like Arial, Georgia, or Trebuchet.

In addition to this in-page widget, you can use the Google Sites full page embed for a more full screen sheets experience. This user experience is very useful for comprehensive project trackers because the scrolling is easier to manage.

Charts from sites can also be embedded into Google Sites if you’ve already generated them within your Google Sheet. The sheets and charts stay synced (at least upon page load), so you only need to manage your data in one location.

Google Sites Insert From Drive

Google Sheets for Workspace Intranets Examples

1. Project Management and Task Tracking

A Google Sites intranet is designed to be a knowledge hub and a source of truth, so it makes sense that important information be accessible. Checklists and gantt charts created with Sheets can be embedded directly into intranet pages. There’s no need to launch Drive, dig in various folders or even use search.

Here’s an example of an intranet developed by Kirksville Web Design using Google Sites. In this case, you’ll see an onboarding portal’s checklist using emoji, dropdowns, checkboxes and images. Convert this type of sheet to a table for an even more polished look.

Google Sites Onboarding Checklist

2. People Directories and Org Charts

You can maintain an up-to-date employee directly really easily with Sheets. Common data points include phone numbers, email addresses, roles and departments. Employ smart chips in sheets for a more advanced directory.

Google Sheets itself also supports org charts, which rely on a role hierarchy to be generated. Once you have everything prepared in Sheets, drop it right into the Workspace company intranet powered by Google Sites. Power users can go a step further by utilizing Apps Script to import active directory contacts.

Google Sites Employee Directory

3. Deploy a Simple CMS (Content Management System)

Build a project tracker, requests tracker or other CMS using Sheets connected to Google Forms. Reorganize and reformat user submitted data sent to Sheets.

You can embed both the form and the resulting CMS sheet into a private Google Site. Use cases include survey results, job applications or event registrations.

You can even create a dashboard that integrates directly with a Sites company wiki.

Google Sites CMS

Caveats

Embedded Google Sheets don’t behave exactly like the in-app counterpart. For instance, tables won’t have the same easy filters installed on column headings. You can’t live edit spreadsheet data from the embedded version. Likewise, you won’t see a fully live, collaborative sheet inserted into your website or wiki.

Conclusion

Sheets + Sites is an intuitive low-cost option for businesses to consider for intranet solutions.

Gemini AI within Sheets also makes the whole experience that much easier to get off the ground.


Written By

Kyle Horst is a developer specializing in the Google Sites apps. He builds custom intranet solutions for enterprises to help increase employee knowledge, engagement, and performance.

Learn more about using Google Sites for intranet applications and explore Kyle’s professional templates here: Kirksville Web Design

Introducing The Sheets Insiders Membership Program

Sheets Insiders Graphic

DENIZENS OF THE SHEETS UNIVERSE:

I’m launching something special.

I’ve been thinking about it for over a year.

And today I’m excited to invite you to join the Sheets Insiders membership program.

In this post, I want to explain what Sheets Insiders is and why I’m launching a membership program.

What Is Sheets Insiders?

Sheets Insiders is a paid membership program for folks like you that rely on Google Sheets as part of their daily work.

I created it to help you do your job better.

Through weekly content, you’ll expand your skill set, keep up-to-date with the latest and greatest features of Google Sheets, flex your formula muscles with regular challenges, keep abreast of the AI + Sheets world, and continue to enjoy in-depth tutorials on key topics.

In a little more detail, here’s what you’ll get as a member:

  • Weekly members-only newsletter
  • Regular formula challenges and solution tutorials
  • Exclusive content such as templates and deep-dives
  • AI + Sheets updates and tutorials to stay on top of the AI revolution
  • Access to archives of all past Sheets Insiders issues and content
  • Behind-the-scenes insights
  • And more!

Become a Sheets Insiders today

What Does Sheets Insiders Cost?

Sheets Insiders will cost $199/yr.

During this launch week, Sheets Insiders will be available for $149 for the first year.

Why Sheets Insiders?

I published my first Google Sheets tutorial 10 years ago, in October 2014. Since then, I’ve written over 250 tutorials or 300,000 words and published them all for free on this site.

And since April 2017, I’ve been publishing my free, weekly Google Sheets Tips newsletter. Each week I share a tutorial and the latest news from the Sheets world. In the intervening 6 years, I’ve sent it every week (except holidays) and we’re now over 300 editions and 55,000 readers.

I’ve also launched 3 free online courses with over 70,000 students and ran a popular online Google Sheets conference with thousands of attendees.

We live in an amazing age where it’s possible for an individual contributor to reach a global audience with their ideas. I’m incredibly fortunate to be in that position.

Suffice to say, teaching has been the most fulfilling work of my career and I’m proud of what I’ve achieved.

I want to continue doing this for the next five years, then the next five after that.

This membership is a way you can support me.

So if you enjoy my work and want to see more of it, please consider becoming a Sheets Insiders Member.

Why A Membership Program Instead Of A Course?

For years I’ve created in-depth online courses, teaching folks how to use Google Sheets to analyze their data.

But when I sat down to create another new course earlier this year, I realized a few fundamental truths:

  1. Most online courses aren’t completed, so folks miss out on amazing content. I’ve heard from many of you, with busy work and family lives, how difficult it is to find the time to work though 50+ videos. It feels overwhelming. This new format is much more digestible so that you’ll learn something new and actionable each week.
  2. Technology, particularly AI, advances so quickly that courses that take months to create are out–of-date before they’re published. This membership format ensures that you’re getting new material, as soon as it’s readily available.
  3. My core mission is to help as many folks as possible with Google Sheets, data analysis, and automation. Over time, the courses have become increasingly specialized, which appeal to a smaller and smaller audience. This new format is much more flexible, so we can cover more topics that are relevant to you.

For these reasons, I set out to find a different model to best serve you. One that would deliver fresh content in a more timely and manageable way.

A business model that will be sustainable for me in the long run.

So I’m taking everything I’ve learned over the past 10 years of content creation and putting it to use in this new membership.

You’ll receive content in a weekly newsletter with exclusive, new resources. It’s a format that you can digest and implement quickly.

These weekly, discrete units of content will let me move quickly, jumping on new features and techniques to bring the latest ideas to you. Plus, I can more easily incorporate your feedback and suggestions, ensuring the content stays relevant.

Everything from the membership program will be included in the archive, which will become increasingly valuable over time.

Thank you for reading and supporting my work.

Much love Sheets amigos,
Ben ❤️

Multiple Selections in Drop Downs in Google Sheets

The drop down menu feature in Google Sheets is useful for efficient data entry. It can also be used to create dynamic spreadsheets, for example, dashboards that change based on choices a user makes.

At the end of July 2024, Google announced multiple selections in drop downs (scheduled to roll out in late-August/early-September). This is great news that will give us even more flexibility in our spreadsheets.

In this post, we’re going to take a look at this new drop down feature:

Multiple Dropdowns in Google Sheets

How to Enable Multiple Selections in Drop Downs

To enable multiple selections, create a regular dropdown and then check the box that says “Allow multiple selections”:

Allow Multiple Selections in Google Sheets dropdown

Boom! 💥 That’s it.

Working with Drop Down Multiple Selections in Formulas

If you select multiple options from a drop down menu, the output is a comma separated list:

Multiple Selections in Drop Downs in Google Sheets

To work with them in formulas, we use the SPLIT function to separate the choices into their separate parts.

Note the space after the comma. We need to be mindful of this in our formulas. Split out the comma-separated lists with this formula, which accounts for the space too:

=SPLIT(B2,", ",FALSE)

It looks like this in our Sheet:

Split formula for multiple Dropdowns

We can use a BYROW function to expand this SPLIT formula to work with a range of multi dropdowns. This single formula in cell C2 splits out all the cells in the range B2:B6 and outputs all the data in C2:E6 range:

=BYROW(B2:B6,LAMBDA(r,SPLIT(r,", ",FALSE)))

In our Sheet:

BYROW and SPLIT with multiple dropdowns

Finally, we could wrap this with a TOCOL function and a QUERY function to count our choices:

=QUERY(TOCOL(BYROW(B2:B6,LAMBDA(r,SPLIT(r,", ",FALSE)))),
"select Col1, count(Col1) where Col1<>'' group by Col1 order by count(Col1) desc label Col1 'Option', count(Col1) 'Count'",0)

In our Sheet:

Multiple Selections in Drop Downs with QUERY

GETPIVOTDATA Function in Google Sheets: How To Extract Data From Pivot Tables

The GETPIVOTDATA function is used to extract data from a pivot table.

Pivot tables are one of the most powerful and useful features in spreadsheets. We use them to summarize our data, by grouping, sorting and filtering it.

But pivot tables are dynamic elements. That means they can change size and shape when data is added or removed from the underlying dataset (e.g. when a new category is added). This makes it tricky to extract data consistently.

When a pivot table changes size, a regular cell reference (e.g. A12 or F34) might not point to the correct value in the pivot table anymore. However, the GETPIVOTDATA function will still extract the correct data.

The downside of GETPIVOTDATA is that it’s fiendishly difficult to use. It’s something of a dark art to identify the correct rows and columns inside the function.

In this post, we’re going to learn how this function works.

👇 Feel free to grab the template from the bottom of this article to follow along.
Continue reading GETPIVOTDATA Function in Google Sheets: How To Extract Data From Pivot Tables

The Complete Guide to Tables in Google Sheets

In this post, we’re looking at the new, powerful Tables in Google Sheets.

Tables In Google Sheets Animation

We’ll start with the basic benefits and features of Tables. Then we’ll look at how you can work with data with Tables using the new Views feature. After that, we’ll look at how to use Tables with formulas and structured table references.

What are Tables in Google Sheets?

Tables are a new feature in Google Sheets. They make it quick and easy to apply formatting and structural rules to a plain range of data.

There are a lot of benefits to using Tables to work with data (see below). I’m excited to use Tables in my Sheets workflows, especially the new Group By views.

Why should you use Tables?

There are many benefits to using Tables, including:

  • Quick and easy to apply formatting
  • Header row is automatically locked at the top of the Table
  • Nice selection of pre-built Table templates
  • Nice selection of pre-built styles
  • Super easy to add dropdowns or smart chips to columns
  • Formulas automatically applied down whole column
  • Formatting and calculations are applied automatically to new rows
  • Super easy access to filter and group by views
  • Some folks will love the Structured Table formula referencing
  • Built-in data validation with the column datatypes

When should you not use Tables?

Like any spreadsheet technique, Tables won’t be the best choice for every situation:

  • Tables might be confusing to people who are unfamiliar with them
  • They introduce additional complexity and clutter to Sheets that you might not want
  • The structured Table reference formulas can be confusing to the uninitiated
  • They work best with uniform underlying data. If your data has blank rows, subtotals, etc. then Tables may not interpret it correctly
  • It’s sometimes not possible to convert large, complex datasets into Tables

Tables Basics

Pre-Built Tables

Currently, when we open a new Google Sheet, we’re prompted to create a new Table with the Pre-Built Tables sidebar:

Pre-Built Tables in Google Sheets

Using this, we can insert a pre-built table with a single click.

I doubt this will always open by default. It’s a deliberate choice to expose more people to Tables since they’re a new feature.

I see this being very helpful for people earlier in their Sheets journey. It showcases some of the best features — dropdowns, smart chips, etc. — that many folks don’t know about.

Access the Pre-Built Tables sidebar from the menu: Insert > Tables

Insert Tables in Google Sheets

How to create Tables in Google Sheets

Now, suppose we already have this dataset in our Sheet:

Data in Google Sheets

Click on any cell in a dataset and convert it to a Table via this menu:

Format > Convert to table

Format menu in Google Sheets

The new Table looks like this:

Tables in Google Sheets

Now we can enjoy all the benefits of Tables that we mentioned above!

The main Table Menu

Table Menu in Google Sheets

Click on the down arrow next to the Table name in the top left corner of the Table to open the Table menu.

From this menu, we can rename the Table, change the formatting, apply custom formats, or even delete the Table.

Be warned: Deleting the Table also deletes the underlying dataset. Use the “Revert to unformatted data” if you want to return to your base data.

The Column Menu

Column Menu in Tables feature of Google Sheets

Click on the down arrow next to each column heading to open the Column Menu for that column.

Here, we can set the datatype for the column (i.e. is it text? A number? A smart chip?). This is a powerful setting so worth taking time to get to know.

For example, we can set a column to dropdown type and it will convert the entire column in a single click.

Dropdown option in Google Sheets Tables

We can also filter and group by columns, which we discuss below.

And, there’s an easy way to add or remove columns from this column menu.

Data Analysis Tools Within Tables

One of the main benefits to using Tables with our datasets is the built-in data analysis tools that we get.

Built-in Data Validation

Setting column types can feel like an additional burden, but there are benefits.

One of the main benefits is the automatic data validation it provides.

Here’s how it works.

Suppose we set our column to be a Currency type column. This is shown by a small icon next to the column name (the first red arrow).

Then along comes a colleague who enters a new row. But they accidentally type a name (or other word) into that number column.

The Table will add a red warning flag and error message to indicate invalid data in that column:

Data validation in Tables in Google Sheets

Table Views Menu

The Table Views menu is accessed from the Table icon next to the Table name, in the top left of the Table:

Table Views Menu in Google Sheets

Opening the Table view menu lets us create Group By or Filter Views, or access previously built views.

Group By Views

The new Group By View aggregates data into categories based on a selected column.

For example, we could group our data by property type with Group By View so that we can see all the groups together:

Group By Views in Google Sheets Tables

(One feature that is lacking with Group By Views is subtotal values for these groups. Fingers crossed we’ll get this in the future.)

Group By views can be created from either the Table View menu or from the selected Column menu.

To save a View (Group By or Filter) click on the “Save view” button:

Save View in Google Sheets Tables

Give the view a name in the subsequent popup:

Name this view in Google Sheets Tables

Now, this view will always be accessible from the main Table Views menu:

Group By View in Tables menu

To close a view and return to the main Table view, press the “x” button on the right side of the green view bar between the formula bar and the Sheet.

Close button for Table view

Tables Filters

Filters are a HUGELY useful tool for exploring our data. They let us select subsets of data to review, based on categories (e.g. all the records for Client A) or conditions (e.g. all values over $100).

But many people aren’t aware they exist or forget to use them.

With Tables, they are applied automatically so that we can start using them immediately.

Access the Filter options inside the Column menu.

Tables Filter Views

Suppose we create a specific filter (all transactions with Client A over $100 in value) that we want to review over and over. Or share easily with colleagues.

We can create a Filter view and save this particular set of filter conditions to return to in the future.

Formulas In Tables

If you add a formula to the first row of a Table, you’re prompted to fill the entire column with Suggested Autofill:

Autofill Column in Table Google Sheets

(Note: This is the same behavior as regular non-Table formatted data.)

Table Reference Syntax

Table References are a special way to access data inside a Table. Instead of A1-style cell references, we can use the Table name and Column headings in our formulas.

Let’s use this simple Table to illustrate the Table Referencing syntax:

Tables in Google Sheets

I’ve named this Table Properties.

(Note: Table names can only contain letters and numbers and can’t start with a digit or have a space.)

Here’s how the Table Referencing works:

  1. Properties[#All] –> entire Table AND column headers
  2. Properties –> gets Table data only, NO column headers
  3. Properties[Column Name] –> gets data in named column only

(Note: column heading names CAN contain spaces, but must be unique within that Table.)

It’s easier to understand visually and I’ve added formulas to show how they work:

Table References in Google Sheets

If you’re familiar with ​Named Ranges​, then you’ll feel right at home using Table referencing.

(Note: you can still access cells inside Tables using regular cell references, e.g. =A1.)

Benefits of using Table References

There are a number of benefits to using Table References over standard A1-style cell references:

  • The formulas automatically update to include any new rows of data added to the Table
  • If you create, rename, insert, or delete columns in a Table, any Table References in formulas will automatically update too
  • Easier to create than regular cell references. It’s easier to type “Properties[Value]” than e.g. “Sheet6!B2:B6”
  • When you start typing a Table or Column name, the auto-complete box will show any Table References that you can quickly click

Using Table References in Formulas

Now we understand structured table references, let’s see them in action with a practical example.

Let’s say we have a Table of real estate transactions. We call it RealEstate2024 (remember, no spaces allowed in Table names).

Now suppose we want to lookup a client name from that Table to retrieve transaction details.

To do that, we’ll use an ​XLOOKUP formula​.

With our search term in cell G2 and using Table References, our formula is:

=XLOOKUP( G2 , RealEstate2024[Client] , RealEstate2024[Sales price] )

RealEstate2024[Client] refers to the “Client” column inside the “RealEstate2024” Table. Similarly, RealEstate2024[Sales price] refers to the “Sales price” column inside that same table.

In our Sheet, it might look like this:

Table References in Google Sheets

And here’s the equivalent A1-style formula:

=XLOOKUP( G2 , 'Copy of Sheet4'!D2:D21 , 'Copy of Sheet4'!E2:E21 )

The Table Reference version is definitely cleaner and easier to understand.

Plus, any new rows of data added to the Table will be automatically included by the formula. Whereas, with the A1-style formula, we’ll need to remember to update the range references.

Now, I’m not about to tell you that you can throw away your A1-style referencing!

Tables References are super useful and worth using if you have your data in a Table. However, A1-style references are more flexible and still necessary for more complex formulas.

Further Resoures

Tables Documentation from Google