We just published a course on the freeCodeCamp.org YouTube channel that will teach you all about Google Sheets from the ground up. This project-based course by Eamonn Cottrell is designed to take beginners through the fundamentals of Google Sheets and guide them toward mastering its core functionalities. By the end of the course, you’ll have hands-on experience with navigation, collaboration, data organization, advanced formulas, visualization tools, and even custom functions using Google Apps Script.

What You’ll Learn in This Course

This course offers a comprehensive and structured approach to learning Google Sheets. Here’s a breakdown of the topics covered:

1. Navigation and Interface Basics
   Learn how to navigate Google Sheets' interface, use its toolbar effectively, and explore the features that make it user-friendly.

2. Sharing and Collaboration
   Discover how to share spreadsheets, collaborate with others in real-time, and control permissions to ensure data integrity.

3. Functions and Custom Formulas
   Master essential functions and create custom formulas to automate calculations and streamline your workflows.

4. Data Validation and Formatting
   Use tools like conditional formatting, dropdown lists, and data validation to organize and highlight key information effectively.

5. Advanced Features
   Combine functions to solve complex problems, filter data dynamically, and use slicers and search bars for better data management.

6. Data Visualization
   Create and customize charts, including pie, column, and line charts, to present your data in visually compelling ways.

7. Apps Script for Customization
   Take your skills to the next level by learning how to build custom functions and menus using Google Apps Script.

Why Learn Google Sheets?

Google Sheets is not just for basic data entry; it’s a powerful tool for individuals and teams working with data. Its cloud-based nature enables seamless collaboration, while its integration with other Google Workspace tools and its extensive feature set make it a go-to solution for professionals across industries.

This course emphasizes practical, project-based learning to ensure you not only understand the concepts but also know how to apply them. By working on real-world tasks, you’ll build confidence and skills that are directly transferable to your personal or professional projects.

Whether you're entirely new to spreadsheets or looking to sharpen your skills, this course is an excellent opportunity to unlock the full potential of Google Sheets.

Watch the full course on the freeCodeCamp.org YouTube channel (2-hour watch).

Well, new is a bit of an overstatement. Excel has had proper tables for many, many years, and it's been a point of contention in the spreadsheet community.

In this article, I'll break down what exactly tables are, why they're important, and then see how Google Sheet's new tables stack up against Microsoft Excel's.

Here is a video walkthrough of everything we'll cover:

What's a Table?

A table is a way of structuring and formatting data in a spreadsheet. It groups together rows and columns of data so that they can be more easily filtered, grouped, and analyzed.

Many people would look at the following bit of data and wrongly assume that it's already in a table.

Data in Excel

This is merely well organized rows and columns of data in Excel. Each column is a separate category of information, that is ids, names, emails, job titles, and salaries.

Each row represents one entry of that data. So, you'd put your id, name, email, job title and salary going left to right in a row.

Simple, yes?

A table contains all the same data, but by formatting it as a table we can unlock a whole lot of additional functionality.

The first of which is the appearance itself. When we create a table, Excel immediately colors our data with a dark header row and bands of alternating colors.

Table in Excel

Sheets does the same thing, as we can see below.

Table in Google Sheets

So, a table is simply a way of managing and grouping data more easily. But it goes much further than just formatting, as we'll see.

Why are Tables Important?

Tables help reduce errors. When dealing with data, we are always making sure the data is clean and that we don't have errors in our formulas.

Tables help keep things orderly simply by being structured and formatted well. But as we'll see in the formula section in a moment, they also allow us to reduce errors in formulas by dynamically calculating things for us

How to Create a Table in Excel and Sheets

In Microsoft Excel, creating a table is as easy as clicking anywhere in the data range and pressing CTRL + T. Immediately, Excel will predict the data range for the table and ask you to confirm this.

Excel table data range

Alternatively, you can find the same create table option from the Insert Menu in the Ribbon at the top.

Excel insert menu

Over in Sheets, you'll need to either right click in a cell in the data range, or select the option from the Format menu to Convert to Table.

Convert to table options in Google Sheets

One caveat in Sheets: if you right click in a cell, you have to select the whole data range for it to convert to a table. Whereas, if you select Format - Convert to table from the menu, it is (like Excel) smart enough to predict the whole data range.

A small thing. But Excel takes the prize for ease of creation.

Convert to table in Google Sheets

⭐Winner: EXCEL

How to Format Tables in Excel and Sheets

As we saw initially, some formatting is done as soon as we create a table.

From here, though, both programs allow for further customization.

In Sheets, we can select the dropdown in the top left next to the table name to access a few options immediately. For the most part, we can simply change the alternating colors of the table.

Formatting options in Google Sheets

If we select Custom, it opens up the full alternating colors menu that is also accessible through the Format menu. This gives us more control over the colors, but it's all aesthetic.

Alternating colors menu in Google Sheets

Meanwhile in Excel, we have the same options with a few more toggle selections for styling. For instance, we can check the first column to bold the text in the id column or we toggle between banded columns and/or rows.

And on the far right of the Table Design tab in the Ribbon, there are a ton of prebuilt styles that we can toggle on and off.

Table design in Excel

Both programs give plenty of options here, and this is mostly to make the tables look good. But Excel comes out on top with more options.

⭐Winner: EXCEL

How to Sort Tables in Excel and Google Sheets

In both programs, there is a dropdown toggle button in each of the header row's cells. Selecting this in Excel allows us to sort ascending or descending...or even by color.

sort in excel

For instance, if we have some of our data using a blue font color, we can actually sort it by that color:

Sort by color in Excel

What about Google Sheets? Yep, same deal there. It will also detect when different colors are used and allow you to do the same type of sorting.

Sort by color in Sheets

Excel does have a Custom Sorting dialog box that can drill down into more detail. For instance, you can add levels of sorting.

Using our color example, we can first sort by the blue font colors in the email color and then by the red font colors in the job title column.

Multiple column sorting in Excel

Google Sheets can do the same thing, but not from the header drop downs. The header drop down sorting is restricted to one row at a time in Sheets.

But, if you select the entire table's data range and then Data - Sort Range - Advanced range sorting options, you are able to sort by multiple columns in Google Sheets.

Advanced sorting in Google Sheets

Sheets' advanced sorting is not as powerful as Excel's, though. You are only able to sort ascending or descending by value. Excel takes the cake on this one by a hair.

⭐Winner: EXCEL

How to Filter Tables in Excel and Google Sheets

Filtering works exactly the same as sorting. In both programs, click the dropdown selector in the header row to see the options for filtering.

In both programs, we have the same options. We can filter by color just like in our sorting. We can filter by values by either selecting all, none, or individual values. And we can filter by condition.

Here's Google Sheet's menu:

Filtering in Google Sheets

And here's Excel's menu. All the same options are available. Both programs allow for custom filter formulas to be entered as well.

Filtering in Excel

⭐Winner: TIE

How to Use Tables in Formulas in Excel and Google Sheets

One of the big reasons to use tables lies in formulas. Whether you use Excel or Sheets, you are likely taking advantage of built-in functions and the ability to create custom formulas for analyzing your data.

By holding your data in a table, your formulas can reference that data dynamically.

Meaning, if you add rows of data to your table, any formulas referencing those table values will update automatically.

The risk of breaking things by adding data decreases dramatically with the use of tables.

Here's a simple example. If we wanted to combine the first and last names into one cell, we could concatenate them with this formula =Salary[first_name]&" "&Salary[last_name].

In Excel, we reference a table by its name, in this case, Salary. Then within brackets, we reference a column name, [last_name].

Spill formula in Excel

We can do the exact same in Sheets.

Formula referencing in Sheets

There's one powerful difference, though. In Excel, the formula will spill down. Meaning, we only have to write it once at the very top, but because it sees that we're referencing values in a table, it will spill down to every row in the table.

In Google Sheets, we still have to drag the formula down.

Now, sometimes, we may not want things to spill down. In this case we can use different syntax in Excel. Instead of the column name within brackets, we can add an @ sign and another set of brackets. This tells Excel to only make the calculation on one row:

Formula referencing in Excel

Excel flexes on this one. It's much more powerful to use tables in formulas in Excel than in Sheets.

⭐Winner: EXCEL

How to Change Table Range in Excel and Google Sheets

What if we want to extend our table or remove data from it? Both Google Sheets and Excel allow us to do this easily.

Say we want to add a column for the full name of a person. In both programs, if we simply type in full_name in G1 to the right of our last column, that column will become a part of the table's data range.

Anytime we type in an adjacent column or row to our table data, the programs will assume the table needs to extend to include it.

Then, we can use a version of the formula from our previous example to insert the full names. Now that we are inside of the table, though, it's not necessary to include the title of the table in our formula.

Now, all that's needed in Excel is =[@[first_name]]&" "&[@[last_name]].

Reference table columns in Excel

For Google Sheets, it's the same inside the table as outside it: =Table2[first]&" "&Table2[last]. Sheets also requires us to drag the formula down. It does not handle spilling like Excel (yet).

Reference table columns in Sheets

To add columns within a table, we can right click the column name and select insert.

Insert column in Excel

Google has a slight edge here in that you can select whether to insert to the left or the right, whereby Excel only inserts to the left.

Insert column Google Sheets

Inserting rows is exactly the same. Excel allows for inserting rows above, while Sheets allows you to select above or below.

Inserting rows Google Sheets

In both programs, deleting rows and columns is as simple as selecting the row(s) or column(s), right clicking, and selecting delete.

In Excel you have the added benefit of a keyboard shortcut. CTRL + - will delete the selected rows or columns.

Both programs will also allow you to convert a table back to a regular data range. In Excel, it's the Convert to Range button in the Table Design tab of the menu

Convert to Range option in Excel

And in Google Sheets, it's the Revert to unformatted data option from the table dropdown menu.

Revert to unformatted data option in Sheets

⭐Winner: TIE

How to Add a Total Row in a Table

There's a good chance you'll want to total up the amounts in a column. How easy is this to add in a table?

You can do it in both programs, but...

Excel makes it incredibly easy. There's a toggle option for this in the Table Design menu in the Ribbon. Toggle this on, and a Total row is automatically included and calculated at the bottom.

Total row in Excel

Can you do the same in Sheets?

Yes, you've just got to do it yourself.

Total row in Sheets

⭐Winner: EXCEL

Who Wins?

Well, it's no surprise that Excel comes out on top. Sheets users (myself included) have a lot to be excited about with the ability to finally create proper tables. By and large, the functionality is just as powerful as Excel.

And much like many features compared between the two programs, Sheets can probably get the job done for most use cases.

Excel, as per normal, simply does more and does it a little bit better.

I'm Eamonn, and I'll help you get good at spreadsheets. Join my free newsletter, Got Sheet, here.

This tutorial will show you a solution that leverages Google Sheets as a makeshift database and React to build the frontend. This will let you effectively bypass the need for a dedicated server or traditional database system.

This approach not only reduces the overhead costs associated with web development, but also simplifies content updates and management. It's an ideal solution if you're looking to launch your own simple CMS without substantial investment.

This solution is suitable for freelancers at the beginning of their career and for clients who cannot invest much in their website.

Why Google Sheets?

Opting for Google Sheets as the backbone of your CMS comes down to its simplicity, flexibility, and cost-effectiveness.

Traditional web development requires a backend server to process data, a database to store information, and a frontend to display content. But each layer adds complexity and cost.

Google Sheets, on the other hand, acts as a highly accessible and intuitive interface that eliminates the need for a server and a database. It lets your users update content in real-time, much like any CMS, but without the usual setup and maintenance costs. This makes it an excellent choice for individuals, small businesses, or anyone looking to deploy a web application quickly and with minimal expense.

Getting Started

Before diving into the code, ensure you have Node.js and npm installed on your system. These tools will allow us to create a React application and manage its dependencies.

Let's start with Google Sheets now.

Step 1: Set Up Your Google Sheets

1. Go to your Google Sheets
2. Open the sheet you want to use or create a new one
3. Click on Extensions in the menu
4. Then click on Apps Script

In the Apps Script editor, you can write a script to serve as your endpoint. Here's a script that returns the contents of a Google Sheet in JSON format:

function convertRangeToJson(data) {
  var jsonArray = [];

  // Check if data is empty or doesn't contain enough rows for headers and at least one data row
  if (!data || data.length < 2) {
    // Return an empty array or a meaningful message as needed
    return jsonArray; // or return 'No data available';
  }

  var headers = data[0];
  for (var i = 1, length = data.length; i < length; i++) {
    var row = data[i];
    var record = {};

    for (var j = 0; j < row.length; j++) {
      record[headers[j]] = row[j];
    }

    jsonArray.push(record);
  }

  return jsonArray;
}

Then:

1. Click File > Save, and give your project a name
2. Click on Deploy > New deployment.
3. Click on Select type and choose Web app.
4. Fill in the details for your deployment. Under Execute as, choose whether the script should run as your account or as the user accessing the web app. Under Who has access, choose who can access your web app.
5. Click Deploy.

You may be asked to authorize the script to access your Google Sheets. Follow the prompts to do so.

After deploying, you'll be given a URL for your web app. This is your API endpoint.

To give you an idea of what you have done so far, this is your sheet structure:

How your sheet should currently look

And this is the JSON you get when you call the endpoint:

JSON

Step 2: Create Your React App

With your Google Sheets API ready, it's time to create the React app that will fetch and display this data.

First, go ahead and create a React app. Run the following command in your terminal to create a new React application:

npx create-react-app google-sheets-cards
cd google-sheets-cards
npm start

You can also use modern build tools like Vite for this purpose, as CRA is no longer the recommended way of building a React app.

Next, create the card component. Inside the src directory, create a file named Card.js. This component will be responsible for displaying each data record:

// src/Card.js
function Card({ title, content }) {
  return (
    <div className="card">
      <h1>{title}</h1>
      <p>{content}</p>
    </div>
  );
}

export default Card;

Now it's time to fetch and display your data in App.js. Modify the App.js file to include logic for fetching the data from your Google Sheets API and using the Card component to display it:

// src/App.js
import React, { useEffect, useState } from 'react';
import Card from './Card';
import './App.css'; // Make sure to create some basic styles for the cards in App.css

function App() {
  const [data, setData] = useState([]);

  useEffect(() => {
    fetch('YOUR_ENDPOINT_URL') // Replace with your actual endpoint URL
      .then(response => response.json())
      .then(data => setData(data))
      .catch(error => console.error('Error fetching data:', error));
  }, []);

  return (
    <div className="App">
      <h1>Data from Google Sheets</h1>
      <div className="cards-container">
        {data.map((item, index) => (
          <Card key={index} title={item.Title} content={item.Content} />
        ))}
      </div>
    </div>
  );
}

export default App;

Next, you can style your cards. Go ahead and add the below CSS in App.css for basic card styling:

.card {
  box-shadow: 0 4px 8px rgba(0, 0, 0, 0.2);
  margin: 10px;
  padding: 10px;
  display: inline-block;
  background: #f9f9f9;
}

.cards-container {
  display: flex;
  flex-wrap: wrap;
  justify-content: center;
}

Step 3: Run Your React App

With everything set up, you can now run your React application and see the data from Google Sheets displayed in your browser. To do this, follow these steps:

First, start the React app. In your terminal, navigate to the root directory of your React app if you're not already there. Run the following command to start the development server:

npm start

This command compiles your React application and opens it in your default web browser. You should see a webpage with a title "Data from Google Sheets", and below that, a series of cards, each displaying a title and content fetched from your Google Sheets data.

Here's, in fact, what we get:

Data from Google Sheets and Card 1, Card 2, and Card 3 displayed on the screen

Now you can view your data. Each card on the page corresponds to a row in your Google Sheets, with the title and content fields displayed as specified in your Card component. If you make any updates to your Google Sheets data, you can refresh the web page to see the changes reflected immediately.

You can deploy your React app to one of the many services you can find online such as Github Actions or Netlify. This is a simple and effective way to host your frontend application for free with significant performance.

Conclusion

Congratulations! You've created a dynamic web application that fetches data from a Google Sheet and displays it using React.

This approach offers a flexible and straightforward way to manage your application's content without needing a backend server or database.

Google Sheets serves as an accessible and collaborative platform for managing data, while React allows you to build a responsive and interactive user interface. Together, they provide a powerful combination for creating web applications that can be quickly updated and easily maintained.

In this hands-on guide, we'll craft a search tool that seamlessly integrates with your spreadsheet, allowing you to:

• Search across multiple tabs: Effortlessly query data from different sections of your spreadsheet with dedicated input fields.
• Master AND & OR searches: Find exactly what you need with both AND and OR search functionalities, ensuring precise or flexible matching based on your requirements.
• Boost your productivity: Save valuable time by eliminating manual searches and filtering through only the relevant data points.

Ready to transform your spreadsheet into a dynamic search hub? Follow along as we explore the world of Google Apps Script and empower you to become a spreadsheet search master!

You can find all the code and related assets in this GitHub repo.

Table Of Contents

• Understanding The Spreadsheet Structure
• How To Build The Search Form – Dynamic Dropdowns And Logic
• AND Vs OR Search
• How To The Create Search Function With Google Apps Script
• How To Create The andsearch Function
• How To Match All Criteria with And Search
• Bringing Search Results To Life
• How To Deduplicate Data To Ensure Accuracy
• Putting It All Together – Displaying Your Search Results
• How To Use Or Search To Find Data that Matches Any Term
• Keeping Users Informed – Toast Messages for Seamless Search
• Putting It All Together – Testing Your Search Form
• Congratulations! You've Built a Powerful Search Engine in Google Sheets!
• Exploring Customization Options

Understanding the Spreadsheet Structure

Structure Of The Spreadsheet

Different Tabs In Spreadsheets

As portrayed in the image above, there are five tabs in the spreadsheet. The data is divided into three tabs by year: 2021, 2022 & 2023.

Columns in 2021, 2022 & 2023 in Spreadsheet

All the columns are the same in these three tabs.

Let's look at the structure of your dedicated search tab. It's divided into two key sections:

1. Search Form (Rows 1-7): This is where you interact with your data. Each input field corresponds to a specific column in your other spreadsheet tabs, allowing you to tailor your search queries. Think of them as filters, helping you hone in on the information you need.
2. Search Results (Rows 8+): This is where you'll find the data you sought. Each result includes the relevant information you specified in your search, along with an additional column named "Sprd Name - Row Index." This acts as a convenient map, pinpointing the exact spreadsheet tab and row where each result originates from. No more hunting through endless rows – you'll be laser-focused on the data you need.

By understanding this organized layout, you can navigate your search experience efficiently and retrieve the information you require swiftly.

Search Form

###

How to Build the Search Form – Dynamic Dropdowns and Logic

The search form has three input fields: Client, Quantity, and Description. Each utilizes a dropdown menu automatically populated with unique values from the Config spreadsheet tab. But how does this magic happen?

Data Validation Of Dropdown Inputs

Here's what happens behind the scenes:

1. Data Source: Values for the dropdowns are meticulously collected from three separate tabs: 2021, 2022, and 2023.
2. Combining Forces: A clever formula merges these values into a single, consolidated list.
3. Splitting it Up: This combined list is then transformed into an array, allowing individual values to be accessed.
4. Rearranging the Data: Transposition magic turns the row of values into a column, making them easier to work with.
5. Duplicate Removal: The UNIQUE function eliminates any repeated values, ensuring a concise and organized list.
6. Sorting it Out: Finally, the remaining values are sorted alphabetically for your browsing convenience.

Here's the formula used: SORT(UNIQUE(TRANSPOSE(split(TEXTJOIN(",",TRUE,'2021'!A2:A1001)&","&TEXTJOIN(",",TRUE,'2022'!A2:A1001)&","&TEXTJOIN(",",TRUE,'2023'!A2:A1001),","))))

Config Sheets

AND vs OR Search

A dedicated checkbox (located in G4:G5) serves as the control center for your search logic. When checked, it activates the AND search, requiring all specified criteria to be present in the results.

Leaving it unchecked switches to the OR search, providing more flexible results as long as any criterion matches.

Remember, the downloadable spreadsheet retains all the pre-configured formulas and data validation rules, making setup a breeze. We'll dive into crafting the magical search function in the next step!

How to the Create Search Function With Google Apps Script

Open script editor from Extensions>Apps Script

Open Apps Script From Sheets

For this project, you'll create two files search.gs and utils.gs in the editor.

Inside the search.gs file, let's first fetch our spreadsheet and input terms.

var ss = SpreadsheetApp.getActiveSpreadsheet();
var searchSheet = ss.getSheetByName("search");
var _2023Sheet = ss.getSheetByName("2023");
var _2022Sheet = ss.getSheetByName("2022");
var _2021Sheet = ss.getSheetByName("2021");
// ranges for name, description and quantity columns for each tab
var nameRangeNotation = 'A2:A'
var descriptionRangeNotation = 'F2:F'
var quantityRangeNotation = 'E2:E'
// value for input boxes
var clientName = searchSheet.getRange('B2:C2').getValue();
var quantity = searchSheet.getRange('E2').getValue();
var description = searchSheet.getRange('G2:H2').getValue();
var hasIncludeAllSelected = searchSheet.getRange('G4:G5').getValue();

Now below this code, we'll create the function search, which will orchestrate everything from the top.

/**
 * The main function assigned to search button in the spreadsheet. It orchestrates search opearaion.
 */
function search() {
  try {

    let status;

    if (hasIncludeAllSelected) {
      //perform AND search
      const newData = andSearch(clientName, description, quantity);

    }
    else {
         }



  } catch (e) {
    console.log(e)

  }
}

In this project, we'll build our functions one step at a time. Let's start by determining the search type based on the checkbox in G4:G5.

If the box is checked, we'll activate the AND search functionality. This means all specified criteria in the input fields must be present in the results. To handle this, we'll call a dedicated function named andSearch().

We'll create this function next, directly below the existing search function.

This approach ensures our script adapts to the user's chosen search type, providing accurate and relevant results based on their needs.

How to Create the andSearch() Function

/**
 * Performs "AND" search for the given keywords in their respective columns Last Name, Descroption and Quantity for 
 * tabs 2021, 2022, 2023. Returns new nested arrays for search results to be filled in search spreadsheet.
 * @param {String} name 
 * @param {String} description 
 * @param {String} quantity 
 * @returns {Array<Array<String>>?} - [[],[],[]]
 */
function andSearch(name = null, description = null, quantity = null) {

  // get matching index for each sheet.
  const _2021SheetNameSearchIndexes = name === "" ? [] : searchSheetByColumn(_2021Sheet, nameRangeNotation, name);
  const _2021SheetQuantitySearchIndexes = quantity === "" ? [] : searchSheetByColumn(_2021Sheet, quantityRangeNotation, quantity);
  const _2021SheetDescriptionSearchIndexes = description === "" ? [] : searchSheetByColumn(_2021Sheet, descriptionRangeNotation, description);


  const _2022SheetNameSearchIndexes = name === "" ? [] : searchSheetByColumn(_2022Sheet, nameRangeNotation, name);
  const _2022SheetQuantitySearchIndexes = quantity === "" ? [] : searchSheetByColumn(_2022Sheet, quantityRangeNotation, quantity);
  const _2022SheetDescriptionSearchIndexes = description === "" ? [] : searchSheetByColumn(_2022Sheet, descriptionRangeNotation, description);

  const _2023SheetNameSearchIndexes = name === "" ? [] : searchSheetByColumn(_2023Sheet, nameRangeNotation, name);
  const _2023SheetQuantitySearchIndexes = quantity === "" ? [] : searchSheetByColumn(_2023Sheet, quantityRangeNotation, quantity);
  const _2023SheetDescriptionSearchIndexes = description === "" ? [] : searchSheetByColumn(_2023Sheet, descriptionRangeNotation, description);


 //.... continue

}

This function takes three parameters, each corresponding to a user-defined search term: name, description and quantity.

If any of these search terms are blank, we simply assign an empty array as the result. This efficiently handles scenarios where users leave certain fields unfilled.

The core logic relies on the searchSheetByColumn function. Think of it as a data detective that checks specific columns within spreadsheet tabs. It takes three crucial arguments:

• sheet: The specific tab to search within (for example: "2021").
• rangeNotation: The column range to target (for example: "A2:A").
• searchVal: The value to match within the chosen column (for example: "Khadka").

Using this information, searchSheetByColumn scans the designated column and returns an array containing the row indexes where the searchVal is found in that sheet.

Searching Input Value In a Column

Let's create the function searchSheetByColumn in the utils.gs file now.

/**
 * Searches the given keyword in the given column inside the given spreadsheet tab.
 * It returns all the matching indexes of data. Indexes are index from array not row.
 * @param {Spreadsheet} sheet - sheet to search from
 * @param {String} rangeNotation - range of the column in the given spreadsheet
 * @param {String} searchVal - keyword to search
 * @returns {Array<number>} - [1,23,12,45,12] 
 */
function searchSheetByColumn(sheet, rangeNotation, searchVal) {
  const data = sheet.getRange(rangeNotation).getValues().flat().filter(String); // get data
  if (data.length < 1) return [];
  // filter only matching rows indexes
  // got from https://stackoverflow.com/a/58980987/6163929
  const allIndexes = data.map((val, index) => ({ val, index }))
    .filter(({ val, index }) => rangeNotation === quantityRangeNotation ? Number(val) === Number(searchVal) : val.toLowerCase().includes(searchVal.toLowerCase())
    )
    .map(({ val, index }) =>
      index + 1
    ) // +1 because we extract data from second row in notation later on have to match with whole data array
  return allIndexes;
}

The code above does the following:

• Retrieves data from the specified range and sheet using sheet.getRange(rangeNotation).getValues().flat().
• Removes empty values by filtering with filter(String).
• Iterates through data and indexes and applies map to create an array of objects with both values and their corresponding indexes.
• Converts both search term and data values to numbers using Number().
• Filters for exact matches using rangeNotation === quantityRangeNotation ? Number(val) === Number(searchVal)
• Converts both search term and data values to lowercase.
• Filters for matches using val.toLowerCase().includes(searchVal.toLowerCase())
• Extracts matching indexes using map(({ val, index }) => index + 1).
• Adds 1 to correct for starting extraction from the second row.

How to Match All Criteria with AND Search

Add the following piece of code in andSearch function.

function andSearch(name = null, description = null, quantity = null) {

// ..... continuing on from previous codes
  // matching indexes of rows in AND search
  const _2021SheetMatchingRowsIndexes = filterRowsIndexesWithAllSearchTerms(_2021SheetNameSearchIndexes, _2021SheetQuantitySearchIndexes, _2021SheetDescriptionSearchIndexes);
  const _2022SheetMatchingRowsIndexes = filterRowsIndexesWithAllSearchTerms(_2022SheetNameSearchIndexes, _2022SheetQuantitySearchIndexes, _2022SheetDescriptionSearchIndexes);
  const _2023SheetMatchingRowsIndexes = filterRowsIndexesWithAllSearchTerms(_2023SheetNameSearchIndexes, _2023SheetQuantitySearchIndexes, _2023SheetDescriptionSearchIndexes);
//..........
}

Remember the AND search? Its goal is to unearth data points that tick every box you've specified. To achieve this, we need to filter only rows that contain all your search terms – name, quantity, and description – across all three spreadsheets.

Enter the filterRowsIndexesWithAllSearchTerms function, to be created in the utils.gs file. This handy tool combs through each row and ensures it matches every criterion you've laid out. So, how does it work its magic? We'll explore the code next!

/**
 * Function filters only rows that consist all three keywords provided by user input
 * @param {Array<String>} arr1 
 * @param {Array<String>} arr2 
 * @param {Array<String>} arr3 
 * @returns {Array<String>?} 
 */
function filterRowsIndexesWithAllSearchTerms(arr1, arr2, arr3) {
  // create a nested array
  const arr = [arr1.length > 0 ? [...arr1] : "", arr2.length > 0 ? [...arr2] : "", arr3.length > 0 ? [...arr3] : ""].filter(String);

  // return empty if the master arrays length is lesser than number of search terms
  if (arr.length < 1 || arr.length < numberOfInputFieldEntered) return [];

  const matchingIndexes = [];

  if (arr.length === 3) {

    arr[0].forEach((val) => {
      if (arr[1].includes(val) && arr[2].includes(val)) {
        matchingIndexes.push(val)
      }

    });

  }
  else if (arr.length === 2) {
    arr[0].forEach((val) => {
      if (arr[1].includes(val)) {
        matchingIndexes.push(val)
      }

    });


  }
  else {

    matchingIndexes.push(arr[0]) //just push the array thats not empty
  }
  return matchingIndexes.flat();

}

Here's what the code does:

The function takes three arrays as input, each representing matching indexes from one spreadsheet based on your search terms. However, we understand users might not fill in every search field.

To handle this, the function first creates a "master array" containing only non-empty arrays from the three inputs. Think of it as filtering out any blank search results. const arr = [arr1.length > 0 ? [...arr1] : "", arr2.length > 0 ? [...arr2] : "", arr3.length > 0 ? [...arr3] : ""].filter(String);

If the master array ends up being empty, it means no rows matched any of your search terms – the function simply returns an empty array, indicating no results found.

Similarly, if the master array has fewer elements than the total search terms you entered, it signifies an incomplete AND search. In this case, the function returns an empty array, letting you know that no results match all criteria. arr.length < numberOfInputFieldEntered

But when all three arrays have matches, the function begins its work, it iterates through the first array, meticulously checking if each index value exists in both the second and third arrays. If it does, that index is considered a match and added to a separate "matchingIndexes" array. This guarantees that only rows containing all your search terms are included: arr[0].forEach((val) => { if (arr[1].includes(val) && arr[2].includes(val)) { matchingIndexes.push(val)}

If only two arrays have matches, the function performs a simpler check, verifying if each value in the first array exists in the second. Any match is added to "matchingIndexes." arr[0].forEach((val) => if (arr[1].includes(val)) { matchingIndexes.push(val)}.

Else if only one array is present, the function simply uses that array directly.

In summary, the function ensures that only rows containing all your chosen search terms survive – a powerful tool for precise data retrieval!

Next, in your search.gs file right after you declared the variable hasIncludeAllSelected for the checkbox, create input value counter.

var numberOfInputFieldEntered = [clientName, description, quantity].filter(String).length;

With this, we now have indexes for the rows from the AND search. Now, continue with your andSearch function and get data from those indexes.

Bringing Search Results to Life

function andSearch(name = null, description = null, quantity = null) {
//.... contnung from above
  // get data from row indexes
  const _2021SheetMatchingRows = fetchDataByRowIndexes(_2021Sheet, _2021SheetMatchingRowsIndexes)
  const _2022SheetMatchingRows = fetchDataByRowIndexes(_2022Sheet, _2022SheetMatchingRowsIndexes)
  const _2023SheetMatchingRows = fetchDataByRowIndexes(_2023Sheet, _2023SheetMatchingRowsIndexes)
}

Now that we have the matching row indexes, it's time to retrieve the actual data. Enter the fetchDataByRowIndexes function, residing in the utils file. This handy tool serves as your data retriever, fetching information based on the provided indexes.

/**
 * Funciton extracts the rows of provided indexes+1, from the given spreadsheet tab.
 * @param {Spreadsheet} sheet - sheet to search from
 * @param {Array<number>} indexes - indexes of row to extract values.
 * @returns {Array<Array<Srting>>} - Arrays of nested rows in the indexes from the given sheet.
 */
function fetchDataByRowIndexes(sheet = _2021Sheet, indexes = []) {
  // console.log("Inside fetchDataByRowIndexes() provided indexes are:" + indexes)

  if (indexes.length < 1) return [];

  const data = sheet.getDataRange().getValues();
  const newData = [];

  for (let i = 0; i < indexes.length; i++) {
    newData.push([...data[indexes[i]], `${sheet.getName()} - ${indexes[i] + 1}`])
  }
  // console.log("Inside fetchDataByRowIndexes() data from procvided indexes:" + newData)
  return newData;
}

The retrieved data isn't just dumped onto the search sheet – it gets a special touch. The function adds an extra value for the column named Sprd Name - Row Indexes with ${sheet.getName()} - ${indexes[i] + 1} .

This column acts like a roadmap, displaying both the originating spreadsheet name and the corresponding row index for each result. So, at a glance, you know exactly where each piece of data came from.

Remember, this additional information is added as the last column in the search sheet. With this valuable context, search results become even more informative and easier to navigate.

How to Deduplicate Data to Ensure Accuracy

The next step is to ensure that our search results are free of duplicates, no matter from the sheet they originated. After all, who wants to see the same item twice? So, append this code in the andSearch function:

//.. continue inside andSearch Function
 // filter duplicate rows
  const _2021SheetMatchingUniqueRows = filterDuplicateRows(_2021SheetMatchingRows);
  const _2022SheetMatchingUniqueRows = filterDuplicateRows(_2022SheetMatchingRows);
  const _2023SheetMatchingUniqueRows = filterDuplicateRows(_2023SheetMatchingRows);

To create this function let's jump back to the file utils.gs again.

/**
 * Takes Duplicate data that might have resulted from different individual column searches and only returns unique rows 
 * in each column from the serach results.
 * @param {Array<String>} arr 
 * @returns {Array<String>}- [[],[]]
 */
function filterDuplicateRows(arr) {
  if (arr.length < 1) return [];
  const delimiter = "*---*--*";
  // console.log("Inside filterDuplicateRows() arr to check:" + arr)

  const strArr = arr.map(row => row.join(delimiter)).flat();
  // console.log("Inside filterDuplicateRows() strArr:" + strArr)

  const uniqueArrays = [...new Set(strArr)].map(str => str.split(delimiter))
  // console.log("Inside filterDuplicateRows() uniqueArrays:" + uniqueArrays)

  return uniqueArrays;

}

Here's what we did:

• Creating a Unique Fingerprint: We began by crafting a special "delimiter," a combination of characters highly unlikely to appear within your actual data. Think of it as a unique tag for each row.const delimiter = "*---*--*";
• Joining Forces: Next, we mapped through each row, joining its elements with this delimiter to create a single string. This allows us to compare strings for uniqueness instead of individual data points.const strArr = arr.map(row => row.join(delimiter)).flat();
• Duplicate Detective: We leveraged the power of JavaScript's Set object, renowned for its ability to hold only unique values. By converting our string array into a Set, we effectively remove any identical entries: [...new Set(strArr)]
• Back to Our Roots: Finally, we converted the unique strings back into an array of arrays, splitting them using the same delimiter we used earlier. This gives us a clean, deduplicated set of results. map(str => str.split(delimiter))

Note: This uniqueness is based on the combined "Sprd Name - Row Indexes" value, ensuring true uniqueness across spreadsheets. Without this, duplicates can exist naturally.

With this final step, we've achieved accurate and streamlined search results, ready to be combined and presented from the andSearch function.

// inside andSearch function append at last

  const andSearchResult = [..._2023SheetMatchingUniqueRows, ..._2022SheetMatchingUniqueRows, ..._2021SheetMatchingUniqueRows]

  if (andSearchResult.length < 0) return;
  return andSearchResult;
}

Find the completed andSearch function below.

/**
 * Performs "AND" search for the given keywords in their respective columns Last Name, Descroption and Quantity for 
 * tabs 2021, 2022, 2023. Returns new nested arrays for search results to be filled in search spreadsheet.
 * @param {String} name 
 * @param {String} description 
 * @param {String} quantity 
 * @returns {Array<Array<String>>?} - [[],[],[]]
 */
function andSearch(name = null, description = null, quantity = null) {

  // get matching index for each sheet.
  const _2021SheetNameSearchIndexes = name === "" ? [] : searchSheetByColumn(_2021Sheet, nameRangeNotation, name);
  const _2021SheetQuantitySearchIndexes = quantity === "" ? [] : searchSheetByColumn(_2021Sheet, quantityRangeNotation, quantity);
  const _2021SheetDescriptionSearchIndexes = description === "" ? [] : searchSheetByColumn(_2021Sheet, descriptionRangeNotation, description);


  const _2022SheetNameSearchIndexes = name === "" ? [] : searchSheetByColumn(_2022Sheet, nameRangeNotation, name);
  const _2022SheetQuantitySearchIndexes = quantity === "" ? [] : searchSheetByColumn(_2022Sheet, quantityRangeNotation, quantity);
  const _2022SheetDescriptionSearchIndexes = description === "" ? [] : searchSheetByColumn(_2022Sheet, descriptionRangeNotation, description);

  const _2023SheetNameSearchIndexes = name === "" ? [] : searchSheetByColumn(_2023Sheet, nameRangeNotation, name);
  const _2023SheetQuantitySearchIndexes = quantity === "" ? [] : searchSheetByColumn(_2023Sheet, quantityRangeNotation, quantity);
  const _2023SheetDescriptionSearchIndexes = description === "" ? [] : searchSheetByColumn(_2023Sheet, descriptionRangeNotation, description);


  // matching indexes of rows in AND search
  const _2021SheetMatchingRowsIndexes = filterRowsIndexesWithAllSearchTerms(_2021SheetNameSearchIndexes, _2021SheetQuantitySearchIndexes, _2021SheetDescriptionSearchIndexes);
  const _2022SheetMatchingRowsIndexes = filterRowsIndexesWithAllSearchTerms(_2022SheetNameSearchIndexes, _2022SheetQuantitySearchIndexes, _2022SheetDescriptionSearchIndexes);
  const _2023SheetMatchingRowsIndexes = filterRowsIndexesWithAllSearchTerms(_2023SheetNameSearchIndexes, _2023SheetQuantitySearchIndexes, _2023SheetDescriptionSearchIndexes);

  // get data from row indexes
  const _2021SheetMatchingRows = fetchDataByRowIndexes(_2021Sheet, _2021SheetMatchingRowsIndexes)
  const _2022SheetMatchingRows = fetchDataByRowIndexes(_2022Sheet, _2022SheetMatchingRowsIndexes)
  const _2023SheetMatchingRows = fetchDataByRowIndexes(_2023Sheet, _2023SheetMatchingRowsIndexes)

  // filter duplicate rows
  const _2021SheetMatchingUniqueRows = filterDuplicateRows(_2021SheetMatchingRows);
  const _2022SheetMatchingUniqueRows = filterDuplicateRows(_2022SheetMatchingRows);
  const _2023SheetMatchingUniqueRows = filterDuplicateRows(_2023SheetMatchingRows);


  const andSearchResult = [..._2023SheetMatchingUniqueRows, ..._2022SheetMatchingUniqueRows, ..._2021SheetMatchingUniqueRows]

  if (andSearchResult.length < 0) return;

  return andSearchResult;

}

Putting It All Together – Displaying Your Search Results

Now that we can retrieve search results based on your "AND" criteria, it's time to integrate them into your search function.

We'll continue from where we left off. In the if block, add the following code.

 if (hasIncludeAllSelected) {
      //perform AND search
      const newData = andSearch(clientName, description, quantity);
      // ..........................
      // new peice of code 
       status = fillSearchWithResults(searchSheet.getDataRange().getValues(), newData)
       // ................................................
}

Let's create a new function, fillSearchWithResults, residing in the utils.gs file:

/**
 * To Fill search sheet with values
 * @param {Array<Array<Srting>>}  oldData - previous search results data
 * @param {Array<Array<Srting>>}  newData - new search result to fill
 */
function fillSearchWithResults(oldData, newData) {
  // console.log("Inside fillSearchWithResults() old data:", oldData.length);
  if (oldData.length >= 8) {
    searchSheet.getRange(8, 1, oldData.length - 7, 9).clear(); // clear until last filled data
  }
  SpreadsheetApp.flush();
  Utilities.sleep(1000);
  // console.log("Inside fillSearchWithResults() new Data:", newData);
  if (newData.length < 1) return 400;
  searchSheet.getRange(8, 1, newData.length, 9).setValues(newData);
  return 200;
}

The function takes two key inputs:

• Current Search Sheet Data: This represents the existing information displayed in your search sheet.
• New Search Results: This is the fresh data retrieved using the previously explained functions.

Here's what happens step-by-step:

1. Clearing the Decks: If a previous search result exists (starting from row 8), the function clears it out to make space for the new findings. if (oldData.length >= 8) { searchSheet.getRange(8, 1, oldData.length - 7, 9).clear(); }
2. Empty Results? No Problem: If the newly retrieved search results are empty, the function returns a special code: 400. This code, which we'll use later, indicates to the user that no matching data was found. if (newData.length < 1) return 400
3. Data Display Time!: If there are indeed results, the function saves them into the search sheet, starting from row 8. Additionally, it returns a different code: 200. This code signifies a successful operation, and we'll use it to show success messages to the user.

With this final piece in place, your "AND" searches will effortlessly bring relevant data to your fingertips, presented neatly in your search sheet.

How to Use OR Search to Find Data that Matches Any Term

Our journey continues! After setting up the "AND" search, we can now conquer the "OR" search, allowing you to find data containing any of your specified terms.

In the search function's else block, we have the orSearch function. Its purpose is to sift through your data and identify rows containing at least one of your search terms.

Think of it as casting a wider net, capturing matches that meet any of your criteria.

  else {
      //perform OR serach
      let newData = orSearch(clientName, description, quantity);

      status = fillSearchWithResults(searchSheet.getDataRange().getValues(), newData)
}

Create the function orSearch function below andSearch in the search file.

/**
 * Performs "OR" search for the given keywords in their respective columns Last Name, Descroption and Quantity for 
 * tabs 2021, 2022, 2023. Returns new nested arrays for search results to be filled in search spreadsheet.
 * @param {String} name 
 * @param {String} description 
 * @param {String} quantity 
 * @returns {Array<Array<String>>?} - [[],[],[]]
 */
function orSearch(name = null, description = null, quantity = null) {
  // get matching index for each sheet.
  const _2021SheetNameSearchIndexes = name === "" ? [] : searchSheetByColumn(_2021Sheet, nameRangeNotation, name);
  const _2021SheetQuantitySearchIndexes = quantity === "" ? [] : searchSheetByColumn(_2021Sheet, quantityRangeNotation, quantity);
  const _2021SheetDescriptionSearchIndexes = description === "" ? [] : searchSheetByColumn(_2021Sheet, descriptionRangeNotation, description);

  const _2022SheetNameSearchIndexes = name === "" ? [] : searchSheetByColumn(_2022Sheet, nameRangeNotation, name);
  const _2022SheetQuantitySearchIndexes = quantity === "" ? [] : searchSheetByColumn(_2022Sheet, quantityRangeNotation, quantity);
  const _2022SheetDescriptionSearchIndexes = description === "" ? [] : searchSheetByColumn(_2022Sheet, descriptionRangeNotation, description);

  const _2023SheetNameSearchIndexes = name === "" ? [] : searchSheetByColumn(_2023Sheet, nameRangeNotation, name);
  const _2023SheetQuantitySearchIndexes = quantity === "" ? [] : searchSheetByColumn(_2023Sheet, quantityRangeNotation, quantity);
  const _2023SheetDescriptionSearchIndexes = description === "" ? [] : searchSheetByColumn(_2023Sheet, descriptionRangeNotation, description);

  // get values from those indexes
  const _2021SheetNameSearch = fetchDataByRowIndexes(_2021Sheet, _2021SheetNameSearchIndexes);
  const _2021SheetQuantitySearch = fetchDataByRowIndexes(_2021Sheet, _2021SheetQuantitySearchIndexes);
  const _2021SheetDescriptionSearch = fetchDataByRowIndexes(_2021Sheet, _2021SheetDescriptionSearchIndexes);

  const _2022SheetNameSearch = fetchDataByRowIndexes(_2022Sheet, _2022SheetNameSearchIndexes);
  const _2022SheetQuantitySearch = fetchDataByRowIndexes(_2022Sheet, _2022SheetQuantitySearchIndexes);
  const _2022SheetDescriptionSearch = fetchDataByRowIndexes(_2022Sheet, _2022SheetDescriptionSearchIndexes);

  const _2023SheetNameSearch = fetchDataByRowIndexes(_2023Sheet, _2023SheetNameSearchIndexes);
  const _2023SheetQuantitySearch = fetchDataByRowIndexes(_2023Sheet, _2023SheetQuantitySearchIndexes);
  const _2023SheetDescriptionSearch = fetchDataByRowIndexes(_2023Sheet, _2023SheetDescriptionSearchIndexes);



  // filter duplicate rows
  const _2021SheetMatchingUniqueRows = filterDuplicateRows([..._2021SheetNameSearch, ..._2021SheetQuantitySearch, ..._2021SheetDescriptionSearch]);
  const _2022SheetMatchingUniqueRows = filterDuplicateRows([..._2022SheetNameSearch, ..._2022SheetQuantitySearch, ..._2022SheetDescriptionSearch]);
  const _2023SheetMatchingUniqueRows = filterDuplicateRows([..._2023SheetNameSearch, ..._2023SheetQuantitySearch, ..._2023SheetDescriptionSearch]);

  const orSearchResult = [..._2021SheetMatchingUniqueRows, ..._2022SheetMatchingUniqueRows, ..._2023SheetMatchingUniqueRows]

  if (orSearchResult.length < 0) return;

  return orSearchResult;

}

Now, don't be surprised if some things look familiar! The overall structure of the orSearch function resembles its "AND" counterpart. However, a key difference sets them apart:

Since an "OR" search requires just one matching term, we can get rid of the filterRowsIndexesWithAllSearchTerms function. Recall that function ensured all terms were present, which isn't the case here.

In essence, the orSearch function works by iterating through each search term and its corresponding matching indexes. For each term, it retrieves the data from the spreadsheet using the familiar fetchDataByRowIndexes function.

Finally, it merges the retrieved data for all terms, creating a unified set of results, even if they come from different spreadsheets.

With this powerful tool in your arsenal, you can discover data points that might not have surfaced with an "AND" search, expanding your search capabilities and enriching your data exploration experience.

Keeping Users Informed – Toast Messages for Seamless Search

Now that our search functions are complete, let's add a crucial element: user feedback! After all, keeping users informed throughout the search process leads to a smoother experience.

To avoid confusion, replace the search function with this one:

/**
 * The main function assigned to search button in the spreadsheet. It orchestrates search opearaion.
 */
function search() {
  try {
    SpreadsheetApp.getActiveSpreadsheet().toast("Searching Through Your Database...", 'Searching');

    let status;

    if (hasIncludeAllSelected) {
      //perform AND search
      const newData = andSearch(clientName, description, quantity);



      status = fillSearchWithResults(searchSheet.getDataRange().getValues(), newData)
      // console.log(status);
      if (status === 400) { throw new Error(SEARCH_STATUS.SEARCH_FAILURE); }
    }
    else {
      //perform OR serach
      let newData = orSearch(clientName, description, quantity);

      status = fillSearchWithResults(searchSheet.getDataRange().getValues(), newData)
      // console.log(status);

      if (status === 400) { throw new Error(SEARCH_STATUS.SEARCH_FAILURE); }
    }

    if (status === 200) {
      SpreadsheetApp.getActiveSpreadsheet().toast(SEARCH_STATUS.SEARCH_SUCCESFULL, 'Success');
    }

  } catch (e) {
    // console.log(e)
    if (e.Error === SEARCH_STATUS.SEARCH_FAILURE) {
      SpreadsheetApp.getActiveSpreadsheet().toast(SEARCH_STATUS.SEARCH_FAILURE, 'Not Found!');

    } else {
      SpreadsheetApp.getActiveSpreadsheet().toast(e, 'Error!');

    }

  }
}

We'll leverage the toast method provided by SpreadsheetApp to display brief, non-intrusive messages directly within the spreadsheet interface. Here's what we'll achieve:

Search Initiated: As soon as the user clicks the search button, a toast message appears: "Searching Through Your Database..." This lets them know the search is underway, preventing confusion or unnecessary waiting. SpreadsheetApp.getActiveSpreadsheet().toast("Searching Through Your Database...", 'Searching');

Success Stories: If the search returns any result (indicated by a status code of 200), a positive toast message pops up: "Search Was Successful!" This confirms the operation's completion and reassures the user that relevant data was found. if (status === 200) { SpreadsheetApp.getActiveSpreadsheet().toast(SEARCH_STATUS.SEARCH_SUCCESFULL, 'Success'); }

Empty Findings: While not technically an error, an empty search result (status code of 400) triggers a slightly different message: "No items found with the given criteria." This informs the user about the outcome without causing alarm. if (status === 400) { throw new Error(SEARCH_STATUS.SEARCH_FAILURE); }

Here's what happens behind the scenes:

const SEARCH_STATUS = {
  SEARCH_SUCCESFULL: "Search Was Successfull!",
  SEARCH_FAILURE: "No items found with the given criteria.",
}

• An "enum" called SEARCH_STATUS in the utils.gs file stores these message strings for easy access and maintenance.
• A "catch block" handles any unexpected errors, ensuring the user receives appropriate feedback even in unusual situations.

With these toast messages in place, your search functionality becomes more user-friendly and transparent. Remember, clear communication leads to happy user experience!

Putting It All Together – Testing Your Search Form

Now that you've built your powerful search functions, it's time to see them in action! Follow these steps to test your search form directly within your spreadsheet:

1. Save Your Scripts: Make sure you've saved all your code files (utils.gs and search.gs) before proceeding.
2. Assign the Search Function: Right-click on the Search button in your form and select "Assign script." In the popup window, type the name of the search function and click "OK." This links the button to your code.
3. Ready, Set, Search: In your spreadsheet, experiment with different search combinations. Try entering terms in various combinations to see how the AND and OR searches yield different results.

Assign a function to button in Google Sheets

Congratulations! You've Built a Powerful Search Engine in Google Sheets!

You've successfully accomplished an impressive feat: crafting a customized search engine within your Google Sheets. Let's recap your achievements:

• Seamless Search Form: You created a user-friendly search form directly in your spreadsheet, simplifying data exploration.
• Scriptable Power: You harnessed the power of Apps Script to develop functions that handle various search operations behind the scenes.
• AND & OR Mastery: You implemented both AND and OR search functionalities, giving users flexibility in finding relevant data.
• Precise Matching: You designed a function that selects rows containing all specified search terms, ensuring accurate results.
• Duplicate Removal: You implemented a mechanism to eliminate duplicate entries, keeping your search results clean and concise.
• Informative Feedback: You integrated user-friendly toast messages to notify users about search progress and outcomes.

Exploring Customization Options

You've built a fantastic search engine, but remember, the journey doesn't end here! With a bit of tweaking, you can adapt this tool to perfectly suit your specific needs and workflows. Here are some exciting possibilities to consider:

Diversifying Your Data: Break free from the confines of a single spreadsheet! Explore integrating with diverse data sources like inventory management systems, tax databases, or even restaurant reviews. With some adjustments to your code, you can unlock a wealth of information across different platforms.

Dynamic Search Inputs: Need more flexibility in your search criteria? Consider adding or removing input fields based on your evolving needs. This allows for more tailored searches and streamlines your data exploration process.

Detailed Search Logs: Keep track of your search history! Implement a log box to automatically record your latest search terms and the number of results found. This can be invaluable for revisiting past searches and analyzing trends.

Visual Appeal Matters: Enhance the user experience by giving your search form a visual makeover. Play with colors, fonts, and layout to create a more engaging and intuitive interface.

Speed Optimizations: Every second counts! Explore ways to optimize your search functions for faster response times. This might involve code refinement, data indexing, or leveraging caching strategies.

Taming Large Datasets: Working with massive databases? Don't worry, you've got options! Implement logic to overcome the 6-minute runtime limit of Google Apps Script functions.

By exploring these avenues, you can transform your basic search function into a powerful and personalized data exploration tool. Remember, the possibilities are endless!

PS: How much more productive (or procrastinating) will you be with this new ability?

I am Nibesh Khadka. Share this blog & like the video if helpful! Find more of my contents at Script-Portal (Medium) & on my YouTube channel: CodingWithNibesh.

Creating a grid of alphabets helps illustrate this practice. If the first letter in your text is A and the first letter in your key is L, you would find the encrypted letter, L, where the plaintext and key intersect:

screenshot of a grid of alphabets

So your text to encrypt might look like this: attackatdawn, your key might be this: LEMONLEMONLE, and the ciphertext would be this: LXFOPVEFRNHR.

This is the same example used on the Wikipedia page which I'll walk you through in this article.

You could create a cipher encoder/decoder with any number of programming languages. But spreadsheets are a nice tool to use for this kind of project because they come with some pretty robust built-in functions that will help us dissect the cipher.

I've chosen Google Sheets because it's a hair simpler to jump into, especially if you already have a Gmail account. But you can do all the same things in Microsoft Excel.

Here's the Google Sheet I used.

If you'd like to check out a video walkthrough, here you go 👇

Spreadsheet Preparation

This isn't too complicated. We need a grid of alphabets. No, you don't have to type them in over and over, either.

In fact, you don't even have to type the whole first alphabet. Let's use Unicode characters. For capital letters, the Unicode for the alphabet starts with 65.

The CHAR() function in spreadsheets converts a number into a character according to the current Unicode table.

The COLUMN() function returns a number corresponding to the current column where column A returns 1, column B returns 2, and so on.

So if we start our alphabet in column B, we can access each letter by adding 63 to the COLUMN() function and wrapping it in the CHAR() function:

=CHAR(COLUMN()+63)

Then we can drag this over to the right until we've got the full alphabet.

screenshot of CHAR and COLUMN functions

The same thing applies going down. We'll just use the ROW() function instead:

=CHAR(ROW()+63)

screenshot of CHAR and ROW functions

Then we can reference the cell above and to the right for every cell below to fill out the full alphabet grid:

screenshot of spreadsheet cell

The only one that will be different will be in the last column where we'll need to reference the first column to have the alphabet wrap:

screenshot of spreadsheet cell

We only have to do this on the second line. Then, select the full second row and drag the formulas down to fill out the grid:

screenshot of spreadsheet grid

At this point, you can copy CTRL + C and paste values only CTRL + SHIFT + V for all the alphabet. If you add or shift rows, the alphabets may get skewed since their formulas reference fixed columns.

The Game Plan

Now the fun stuff. We want our text to be encrypted automatically. Let's highlight three cells: one for the text to encrypt, one for a key, and then a blank cell for our encrypted text:

screenshot of text to encrypt

Note: the key needs to be the same length as the text to encrypt/decrypt. In this example, the key is the word LEMON, but we extend it by repeating it for every character we need to encrypt.

Now we need to do three things:

1. Split out each character of our text into its own cell
2. Loop through each character and encrypt it based on the corresponding key character.
3. Join all those characters together and place the result in our encrypted text cell.

Split Text to Setup

Interestingly, we cannot use the SPLIT() - or if you're in Excel, the TEXTSPLIT() - function without a delimiter. That is, we can't just tell it to split each character without there being something in between the characters.

So, we have to get fancy. In the video walkthrough, I explore a Google Sheets-specific approach using regular expressions...which are hard fun.

Here's what it looks like, and it involves creating character groups, inserting something in between each one, and then splitting using that same something as the delimiter. I used a blank space as the thing which I inserted and then split by:

=SPLIT(REGEXREPLACE(AG6,"(.)","$1 ")," ")

gif of woman saying, that's just hard

Excel users and mortals, fear not – there's a more elegant way that I'll show you here that can be done in either program by using the MID() and COLUMNS() functions.

The MID() function extracts a segment of a string, and COLUMNS() returns the number of columns in an array. By nesting COLUMNS() inside MID() we can one by one extract each character from the string:

=MID($AG$6,COLUMNS($AG6:AG6),1)

screenshot of MID and COLUMNS functions

By locking $AG6 as the first part of the array referenced in the COLUMNS() function, this number adds one for every column we drag our formula over.

Drag this over until you reach the end of the text to encrypt. Every character should be in its own cell now. Do the same for the key.

screenshot of spreadsheet cells

How to Use XLOOKUP() to Encrypt

Below each pairing of plaintext and key text, we will now do a double XLOOKUP().

Yes, it's as awesome as it sounds.

Here's what it looks like, and let's walkthrough what's happening:

=XLOOKUP(AD9,$B$2:$B$27,XLOOKUP(AD10,$B$2:$AA$2,$B$2:$AA$27))

screenshot of double XLOOKUP functions

For the beginning XLOOKUP(), we are looking up the plaintext and we're referencing the first plaintext alphabet for our lookup range. But then for our result range, we're opening up another XLOOKUP()...

This is because we still have to use the correct key alphabet to return an encrypted value.

So, we need the second XLOOKUP() to return a full alphabet based on the position of the key character.

We're using the key character as our lookup value, we're using the first alphabet along the top key row for our lookup range, and then we're returning the whole grid as our result range. This will let us return a full alphabet which is in turn referenced as the result range for our first XLOOKUP().

screenshot of alphabet grid and XLOOKUP example

You can see the second XLOOKUP() by itself in the picture above. The formula is in the top left. We're using the first letter of key text as the lookup value. The lookup range is the top alphabet in the purple box. The result range is the whole grid in the blue box. And the returned alphabet is the one that starts with L in the red box.

You can see it being returned underneath the formula where there is an arrow in the picture.

Drag the double XLOOKUP() function over to encrypt every character of the plaintext with the key text.

Join Text for Final Solution

Now that we've encrypted each letter, we want them to be joined into one string and returned in our encrypted text cell.

To do this, we can use the JOIN() function - or TEXTJOIN() for Excel - with an empty quote as the delimiter.

=JOIN("",AD11:AO11)

screenshot of JOIN function

And voilà! We've just encrypted a string using a double XLOOKUP().

How to Decrypt Text

Decryption works exactly the same. The only difference is the placement of the second XLOOKUP(). Instead of using it as the result range, we're using it as the lookup range:

=XLOOKUP(AD20,XLOOKUP(AD21,$B$2:$AA$2,$B$2:$AA$27),$B$2:$B$27)

screenshot of decryption double XLOOKUP example

Thanks for reading

I hope this was helpful for you and that you learned something new!

You can find more of my tutorials on YouTube, and please sign up for my newsletter about coding and spreadsheets here.

In this article, we'll create our own dark mode. One way to do that would be by selecting all the cells and manually changing their background color and font color. This will get the job done, but we can automate the process and add more style options to choose from.

gif of cartoon character grabbing a computer

Here’s what we’ll build: a style selector that has four different styles triggered by either a new dropdown menu or by clicking a custom button icon.

screenshot of Google Sheets with different background modes

And here's the video walkthrough if you'd like to follow along with that instead:

How to Create Dark Mode in Apps Script

We’ll create four functions, one for each style mode. Each of our functions will do the following:

1. Set background color
2. Set font color
3. Set font family
4. Set border color and style

Let’s walk through how to create a darkMode function in Apps Script. We'll define each function with the function keyword, followed by whatever we’ll name it.

Because the functions take no arguments (they just run without needing more info from us). That is, we have open and closed parentheses with nothing inside them followed by an open curly brace.

All of our code for the function goes between the darkMode function's curly braces:

function darkMode() {
  SpreadsheetApp.getActive().getRange('A1:Z')
    .setBackground("#333333")
    .setFontColor("white")
    .setFontFamily("Google Sans")
    .setBorder(false,false,false,false,true,
     true,"#444444",SpreadsheetApp.BorderStyle.SOLID)
}

To select all the cells, we used the built-in methods from the SpreadsheetApp class: getActive() and getRange(). These select the active sheet as well as a given range.

In our case, we’ll plug in A1:Z as the range, but you can extend this further if you’d like. For instance, A1:AZ, would add columns AA:AZ and then apply our styling to them.

The four lines that follow are simply dot notation extensions telling what styles to apply. You can write this on one line if you’d like, but it’s good practice to have line breaks to make the code easy to read.

gif of man saying, that’s clever

How to Set Colors and Fonts

You'd notice that we used both setBackground(#333333) and setFontColor("white") in the code. This is because we can use CSS notation colors in either hex format or by using the color’s name.

Using setFontFamily("Google Sans"), we gave it the font family name within quotations. Being a Google product, you can use any of the Google Fonts as well as Google’s own Google Sans font as I found out making this project.

How to set the Border

The setBorder(false,false,false,false,true, true,"#444444",SpreadsheetApp.BorderStyle.SOLID) function let’s you enter true or false values for the top, left, bottom, right, vertical, or horizontal borders, in that order, followed by the color and style.

To set the style, we had to invoke a built in Enum Attribute — BorderStyle — to change the style of the border.

How to Create the Style Menu

To be able to select any of the styles we’re making from the actual spreadsheet, we need a menu.

To add the menu, we'll create a new function called onOpen() that runs as soon as the spreadsheet opens and then the built-in methods from the getUi() to build our custom menu.

We can create the menu with .createMenu() and then add each of our functions to the menu with the addItem() function.

Here's the code:

function onOpen(){
  SpreadsheetApp.getUi()
    .createMenu('Style')
    .addItem("Dark","darkMode")
    .addItem("Papyrus","papyrusMode")
    .addItem("Light","lightMode")
    .addItem("Synth","synthMode")
    .addToUi();
}

Google Apps Script automatically integrates with Google Workspace apps (like Google Sheets) so the functions we've added in the code will make the functionalities accessible in your Google Sheets.

How to Add Icon Buttons

As a bonus, I added four icon images to the sheet, and by selecting Assign script we can have these icons act as buttons to trigger any of the four styles we’ve built:

screenshot of icons in spreadsheet

When you type the function name into the Assign script dialog box, make sure you do not to use parentheses. You should only enter the name of the function itself:

screenshot of assigning script to image

And bingo! We’ve got ourselves a style picker. You can add or edit these to create any number of combinations that you can easily toggle on and off in your own Google Sheet!

screenshot of Synth Mode Style

Conclusion

This article shows how you can create can code dark, and other background modes for your Google Sheets using Apps Script and JavaScript.

I hope this has been useful for you!

More Resources😄

▶ Find all my video tutorials and walkthroughs on YouTube: https://www.youtube.com/@eamonncottrell?sub_confirmation=1

▶ Connect with me on LinkedIn where I share daily tips: https://www.linkedin.com/in/eamonncottrell/

Google Sheets has five built-in functions that help you import data from other sheets and other web pages. We'll walk through all of them in order from easiest (most limited) to hardest (most powerful).

Here they are, and you can click each function to skip down to its dedicated section. I've made a video as well that walks through everything:

Section Shortcuts

• How to use the IMPORTRANGE function
• How to use the IMPORTDATA function
• How to use the IMPORTFEED function
• How to use the IMPORTHTML function
• How to use the IMPORTXML function

Here's the Google Sheet we'll be using to demo each function.

If you'd like to edit it, make a copy by selecting File - Make a copy when you open it.

screenshot of Google Sheet

How to use the IMPORTRANGE function

This is the only function that imports a range from another sheet rather than data from another web page. So, if you've got another Google Sheet, you can link the two sheets together and import the data you need from one sheet into the other sheet.

For instance, here's a sheet with a bunch of random Samsung Galaxy data in it.

You can see that we have a few hundred rows of data about phones. If we want to import this data into another spreadsheet, we can use IMPORTRANGE(). This is the simplest to use of the five functions we'll look at. All it needs is a URL for a Google Sheet and the range we want to import.

Check out the tab for IMPORTRANGE in the Google Sheet here, and you'll see that in cell A5, we've got the function =IMPORTRANGE(B4,"data!a1:K"). This is pulling in the range A1:K from the data tab of our second spreadsheet whose URL is in cell B4.

screenshot of IMPORTRANGE function

Once your data is pulled into your spreadsheet, you can do one of two things.

1. Leave it linked through the IMPORTRANGE function. This way, if your data source is going to be updated, you'll pull in the updated data.
2. Copy and CTRL+SHIFT+V to paste values only. This way, you have the raw data in your new spreadsheet and you won't have to be dependent on something changing with the URL down the road.

How to use the IMPORTDATA function

This is pretty straightforward. It'll import .csv or .tsv data from anywhere on the internet. These stand for Comma Separated Values and Tab Separated Values.

.csv is the most commonly used file type for financial data that needs to be imported into spreadsheets and other programs.

Like IMPORTRANGE, we only need a couple pieces of information for IMPORTDATA: the URL where the file lives, and the delimiter. There's also an optional variable for locale, but I found that it was unnecessary.

In fact, Google Sheets is pretty smart – you can leave off the delimiter too, and it will usually decipher what type of data (.csv or .tsv) lives at the URL.

You can see that I've found a New York government data website where there lives some winning lottery number data. I've put the URL for a .csv file in A5, and then used the function =IMPORTDATA(A5,",") to pull in the data from the .csv file.

Screenshot of IMPORTDATA function

You could alternatively download the .csv file and then select File - Import to bring in this data. But in the event that you do not have download permissions or simply want to get it straight from a site, IMPORTDATA works great.

How to use the IMPORTFEED function

This imports RSS feed data. If you're familiar with podcasting, you may recognize the term. Every podcast has an RSS feed which is a structured file full of XML data.

Using the URL for the RSS feed, IMPORTFEED will pull in data about a podcast, news article, or blog from its RSS information.

This is the first function that begins to have a few more options at its disposal, too.

All that's required is the URL of a feed, and it'll bring in data from that feed. However, we can specify a few other parameters if we like. The options include:

1. [query]: this specifies which pieces of data to pull from the feed. We can select from options like "feed " where type can be title, description, author or URL. Same deal with "items " where type can be title, summary, URL or created.
2. [headers]: this will either bring in headers (TRUE) or not (FALSE)
3. [num_items]: this will specify how many items to return when using Query. (The docs state that if this isn't specified, all items currently published are returned, but I did not find this to be the case. I had to specify a larger number to get back more than a dozen or so).

You can see from the screenshots below that I am querying one of my feeds to pull in the episode titles and URLs.

First, to get all the titles, I used IMPORTFEED(A3, "items title", TRUE, 50:

Screenshot of IMPORTFEED

Then, similarly for the URLs, I used IMPORTFEED(A3, "items url", TRUE, 50):

Screenshot of IMPORTFEED #2

How to use the IMPORTHTML function

Now we're getting into scraping data straight off of a web site. This will take a URL and then a query parameter where we specify to look for either a "table" or a "list".

This is followed by an index value representing which table or list to look for if there are multiple on the page. It is zero indexed, so input zero if you're looking for the first one.

IMPORTHTML looks through the HTML code on a website for <table> and <list> HTML elements.

<!--Here's what a simple table looks like:-->
<table>
    <thead>
        <tr>
            <th>table header 1</th>
            <th>table header 2</th>
        </tr>
    </thead>
    <tbody>
        <tr>
            <td>table data row 1 cell1</td>
            <td>table data row 1 cell2</td>
        </tr>
        <tr>
            <td>table data row 2 cell1</td>
            <td>table data row 2 cell2</td>
        </tr>
    </tbody>
</table>

<!--Here's what an ordered list looks like:-->
<ol>
    <li>ordered item 1</li>
    <li>ordered item 2</li>
    <li>ordered item 2</li>
</ol>
<!--Here's what an unordered list looks like:-->
<ul>
    <li>unordered item 1</li>
    <li>unordered item 2</li>
    <li>unordered item 3</li>
</ul>

In the sample sheet, I've got the URL for some stats about the Barkley Marathons in cell B3 and am then referencing that in A4's function: =IMPORTHTML(B3,"table",0).

Screenshot of IMPORTHTML

FYI, freeCodeCamp created ScrapePark as a place to practice web scraping, so you can use it for IMPORTHTML and IMPORTXML coming up next👇.

How to use the IMPORTXML function

We saved the best for last. This will look through websites and scrape darn near anything we want it too. It's complicated, though, because instead of importing all the table or list data like with IMPORTHTML, we write our queries using what's called XPath.

XPath is an expression language itself used to query XML documents. We can write XPath expressions to have IMPORTXML scrape all kinds of things from an HTML page.

There are many resources to find the proper XPath expressions. Here's one that I used for this project.

screenshot of XPath cheat sheet

In the sheet for IMPORTHTML, I have several examples that I encourage you to click through and check out.

For example, using the function =IMPORTXML(A11,"//*[@class='post-card-title']") allows us to bring in all the titles of my articles because from inspecting the HTML on my author page here, I found that they all have the class post-card-title.

screenshot of inspecting a web page with dev tools

In the same way, we can use the function =IMPORTXML(A11,"//*[@class='post-card-title']//a/@href") to grab the URL slug of each of those articles.

screenshot of IMPORTXML

You'll notice that it does bring in the full URL, so as a bonus, we can simply prepend the domain to each of these. Here's the function for the first row which we can drag down to get all those proper URLs: ="https://www.freecodecamp.org"&B13

screenshot of prepending domain to slug

Follow Me

I hope this was helpful for you! I learned a lot myself, and enjoyed putting the video together.

You can find me on YouTube: https://www.youtube.com/@eamonncottrell

And, I've got a newsletter here: https://got-sheet.beehiiv.com/

A while ago I wrote an article on how to remove empty rows and columns from Google Sheets.

I recently revisited that article and I now present you this revised version.

I also have a video version of this topic which you can check out below:

What We Will Cover

You'll create two functions: deleteExternalEmptyRowsNColumns() and deleteInternalEmptyRowsNColumns().

The first function will delete empty rows and columns from the range that are outside of the range returned by the method getDataRange().

The second function will delete the rows and columns that are empty which are inside of the range returned by getDataRange().

We will also create a menu so that we can execute these functions from the spreadsheet itself.

How to Prep the Sheet

My spreadsheet currently looks like the image below:

Spreadsheet With Lots Of Empty Rows and Columns

It has a couple of columns and rows with data, with lots of blank rows and columns.

Let's make the spreadsheets look more presentable like the following image:

Final Clean Spreadsheet Version

How to Open Apps Script Project

Next, let's open our Apps Script project from the Extensions tab in the spreadsheet:

Open Apps Script Project From Spreadsheet's Tab

How to Create a Function to Delete Empty Rows and Columns Outside of DataRange

We'll create a function named deleteExternalEmptyRowsNColumns().

This function will be responsible for deleting any empty rows and columns that are outside of the range of getDataRange():

/**
 * Delete the empty rows and columns outside of the DataRange()
 */
function deleteExternalEmptyRowsNColumns() {
  // get sheets and data
  const ss = SpreadsheetApp.getActiveSheet();
  const data = ss.getDataRange().getValues();

  //console.log(data);

  // determine last row and column
  const lastRow = data.length;
  const lastCol = data[0].length;

  // get maximum rows and columns sss
  const maxRows = ss.getMaxRows();
  const maxCols = ss.getMaxColumns();

  // only remove rows and columns if there are empty rows or columns beyond last row and columns
  if (maxRows > lastRow) {
    ss.deleteRows(lastRow + 1, maxRows - lastRow);
  }
  if (maxCols > lastCol) {
    ss.deleteColumns(lastCol + 1, maxCols - lastCol);
  }

}

We're using maximum rows and maximum columns because these values will return the last row and the last column of the spreadsheet regardless of the content.

This means they also include empty rows and empty columns beyond the data range.

Next, we only remove the columns and rows if they are outside of the range.

This means that if the maximum row is greater than the last row, then we will remove the rows. The same goes for the columns.

We're using the deleteRows() method to remove those rows, which takes two parameters.

The first one is the index of the row from where the rows should be deleted, lastRow + 1 in our case.

The second parameter is the number of rows we should remove, which is maxRows - lastRow in our case.

For columns, we'll use the deleteColumns() method. The way this method works is the same as the way deleteRows() works but on columns.

If you run this function your spreadsheet will look similar to the following image:

Spreadsheet with Out Of Bounds Columns and Rows Removed

You'll see the columns and rows that were outside of the range returned by getDataRange() have now been removed by the function.

How to Create a Function to Delete Empty Rows and Columns Inside of DataRange

Now we will create another function: deleteInternalEmptyRowsNColumns().

This will be responsible for removing empty rows and columns which are included in the getDataRange() method, with this function below:

/**
 * Deletes the empty rows and columns inside of DataRange()
 */
function deleteInternalEmptyRowsNColumns() {
  // get sheets and data
  const ss = SpreadsheetApp.getActiveSheet();
  const data = ss.getDataRange().getValues();

  const lastRow = data.length;
  const lastCol = data[0].length;

  // lets check if there're any empty columns during the beginning which is included in data
  const emptyColumnIndexes = [];
  for (let i = 1; i <= lastCol; i++) {
    if (ss.getRange(1, i, lastRow, 1).getValues().flat().join("") === "") {
      // subtract length before pushes value with less than 1 of original index
      // because later on when we delete colums one by one the indexes 
      //will be out of bounds/wrong due to sprd being updated to new indexes
      emptyColumnIndexes.push(i - emptyColumnIndexes.length);

    }

  }

  // lets delete these columns
  if (emptyColumnIndexes.length > 0) {
    // delete column
    emptyColumnIndexes.forEach(ind => ss.deleteColumn(ind));

  }

  //***************Remove Internal empty rows */
  // convert nested arrays to string and remove empty strings with filter
  const newData = ss.getDataRange().getValues().filter((arr) => arr.join("") !== "")

  const newLastRow = newData.length;
  const newLastCol = newData[0].length;

  // clear previous values
  ss.clearContents();

  // set new values
  ss.getRange(1, 1, newLastRow, newLastCol).setValues(newData);

// now delete empty rows and columns 
 deleteExternalEmptyRowsNColumns();
}

Let's explain what the function does in the following sections.

How to Remove Empty Columns

First, we'll work on empty columns. After that, we'll remove the empty rows.

 const emptyColumnIndexes = [];
  for (let i = 1; i <= lastCol; i++) {
    if (ss.getRange(1, i, lastRow, 1).getValues().flat().join("") === "") {
      // subtract length before pushes value with less than 1 of original index
      // because later on when we delete colums one by one, the indexes 
      //will be out of bounds/wrong due to sprd being updated to new indexes
      emptyColumnIndexes.push(i - emptyColumnIndexes.length);

    }

  }

  // lets delete these columns
  if (emptyColumnIndexes.length > 0) {
    // delete column
    emptyColumnIndexes.forEach(ind => ss.deleteColumn(ind));

  }

Let's create an array named emptyCoiumnIndexes. It'll hold all the indexes of the columns that are empty.

To check if the columns are empty or not, we will loop through each column with a for loop starting from the first column.

Next, we will fetch the values of a column. In every loop, it will return a nested array and we will flatten the array.

After that, we will join the array with an empty string ("").

If the joined string is actually empty we know that this is an empty column so we will push that index to the empty column indexes array with the following code: ss.getRange(1, i, lastRow, 1).getValues().flat().join("") === "" .

But before pushing the index, we'll subtract the length of the emptyColumnIndexes array from the index itself each time.

That's because later on, when we delete this column, we have to delete each column one by one.

While doing so, we will find out that if we delete the first column, the structure of the spreadsheet changes, and the columns that come after the deleted column will have their index changed.

Spreadsheet with Inbounds Empty Columns and Rows

For instance, from the preceding image, after we delete column "A" the index of column "F" will get changed to "E".

After this, if the emptyColumnIndexes is not empty then we will go through each value using forEach().

Then we will delete the column with deleteColumn() method.

Now, run this function and you'll see a similar result as the following image, where all the empty columns have been removed:

Spreadsheet with Inbounds Empty Columns Removed

How to Remove Empty Rows

Now we will work on removing empty rows from our spreadsheet.

To do so, we will filter all the non-empty rows using the same process we used earlier by joining them with empty string.

If they are not just an empty quote (""), we'll return them as the array items in the newData with const newData = ss.getDataRange().getValues().filter((arr) => arr.join("") !== "").

Now, we'll save those values to our spreadsheet after clearing previous contents.

But if you run this function right now, you'll find out that this alone will not remove empty columns but accumulate rows into one place like the image below:

Spreadsheet with Outbound Empty Rows

This is not what we want.

So, to remove those extra rows, we will just call the function deleteExternalEmptyRowsNColumns() , we created earlier because now these extra spaces are outside of the range of getDataRange().

Let's run the function again, and now we're be able to accomplish what we initially wanted:

Clean Spreadsheet

How to Create a Custom Menu for the Spreadsheet

Finally, we will create a menu so that we can run these functions from the spreadsheet itself.

For this create a new script file in your project named menu:

/**
 * Menu creates menu UI in spreadsheet.
 */
function createCustomMenu() {
  let menu = SpreadsheetApp.getUi().createMenu("Delete Empty Rows N Columns");

  menu.addItem("Delete External Empty Rows and Columns", "deleteExternalEmptyRowsNColumns");
  menu.addItem("Delete Internal Empty Rows", "deleteInternalEmptyRowsNColumns");
  menu.addToUi();
}

/**
 * OnOpen trigger that creates menu
 * @param {Dictionary} e
 */
function onOpen() {
  createCustomMenu();
}

After saving the script, we will go to the spreadsheet and reload it.

After a moment, you'll be able to see a menu in the spreadsheet tab like the image below:

Spreadsheet Menu

Congrats!

Now you just need to copy and paste the script from the tutorial or this GitHub repo and you'll be able to clean your sheets instantly.

Conclusion

In this tutorial, we created two functions: deleteExternalEmptyRowsNColumns() and deleteInternalEmptyRowsNColumns().

We cleared empty rows and columns that were out of bounds and later deleted empty rows and columns that were in bounds of data.

Next, we created a menu that provides easy access to even non-coders to execute the functions mentioned above from the spreadsheet's tab.

Now, all that is left is for you to share this article. If you're also watching the video version I hope you'll subscribe to my channel as well.

I am Nibesh Khadka, a freelancer specializing in automating Google products with Apps Script. Contact me if you need my services at me@nibeshkhadka.com.

gif of guy saying aren't ordinary people adorable

And, these days, it's actually giving Excel a run for its money. It's picked up a lot of features and processing power over the years that used to be exclusive to Excel.

But we're here to talk about the basics today.

🧾I'm going to cover:

1. How to create new Google Sheets
2. Intro to Templates
3. Menu and toolbar overview
4. Basic data entry and calculations
5. Basic formatting
6. How to create a table
7. How to sort and filter data
8. Intro to formulas and functions

Video Walkthrough

I've made a video walkthrough of the things we'll be covering in this article. You can check it out below:

How to Create a New Sheet

Spreadsheets can be intimidating even in their most basic form.

Don't be scared, though.👇

To use Google Sheets, you need a free Google account. (If you're using Gmail, you already have this.) Go here to sign up if you don't have one yet.

pic of the Google Sheets homepage

Go ahead and go to sheets.new to create a brand new Google Sheet. It will also prompt you to sign up if you don't have an account yet.

💥This is what you'll see:

pic of a blank spreadsheet

😓Some of you may already have begun perspiring because a spreadsheet looks like an unapproachable blank slate reserved for data analysts, financial gurus, and overly ambitious content creators.

gif of man stressing out

💪Fear not.

A spreadsheet, and particularly a Google Sheet, is ripe with possibilities for the average person.

You can use them to enhance productivity after learning only a few basic things.

📊A spreadsheet is a big grid made up of columns labeled with letters and rows labeled with numbers. Each of the rectangles of the grid are called a cell, and the active cell is the one with the blue outline.

If you start typing, whatever you type will appear in the active cell.

The cells can contain numbers, words, formulas, dates, pretty much anything...

pic of spreadsheet cell

Google Sheets Templates

Google has supplied us with a respectable amount of templates to get started with.

I will not be covering these in any detail because it's important to get the basics down first. I just want you to be aware of them if you need a starting point after you've gotten comfortable with Sheets.

You can look through the template gallery here.

pic of Google Sheets templates

Menu and Toolbar Overview

pic of Google Sheets menu and toolbar

You will likely be familiar with the menu and toolbar setup. As with most modern applications, they are at the top of the page. The menu has many familiar options like File, Edit, View, Insert, and so on. And the toolbar below it consists of mostly icons related to formatting and text options.

The File menu has options to share, download, copy, import, rename and other such things related to the whole spreadsheet.

pic of file menu

Edit and View have familiar options regarding copy/paste and choosing different levels of visibility for your spreadsheet.

Insert gives us a host of options of things to import into our sheet like charts, pivot tables, checkboxes, emojis, dropdown lists, and many more.

pic of insert menu

🎨Format contains many options for formatting our sheet. We can add color, borders, tables styles, and more from here.

The Data menu has a lot of spreadsheet specific functions. It contains shortcuts to sort and filter data, to protect different ranges of cells, to group ranges together by naming them, data validation, and advanced items like connecting data sources.

pic of the Data menu in Google Sheets

Tools and Extensions will be lesser used menus, at least in the beginning. But some really exciting stuff is possible by leveraging the power of Google Apps Script through the Extensions menu. This lets us write programs in a language similar to JavaScript all while in a spreadsheet.

But, that's for later – or check out some of my other videos and articles to get a taste of Apps Script.

Basic Data Entry and Calculations

What is data? According to my Google search, data are "facts and statistics collected together for reference or analysis."

Spreadsheets thrive on data. Yes, think numbers, dates, percentages...things that are easily calculable.

In Google Sheets, we can enter in some data. Say, a list of names, dollar amounts, and dates.

pic of some data

Now, we have a simple list of data. Because it's small, we can scan it and analyze it ourselves pretty easily.

1. We can see that it's ordered by date
2. We can see that Paul either has or owes the most
3. We can see that Sara either has or owes the least

But imagine this is a list of thousands of records (each row of an amount, a name, and a date can be referred to as one record).

Suddenly it gets a lot harder to analyze or make sense of the data.

Then add a dozen or a hundred more columns of data for each record. It becomes virtually impossible for a human to draw anything meaningful from the data without the help of a spreadsheet or computer program.

Next, we'll see how some organization and simple spreadsheet operations can help us draw insights from our data.👇

gif of woman saying this is so easy

Basic Formatting

Highlight the table. You can do this by clicking and dragging across the whole range of cells. Now, you should have an active range instead of an active cell:

pic of Google Sheets table

Take a moment and click through some of the formatting options on the toolbar. I've changed the text, the font size, and then made bold the first header row.

I've also highlighted only the dollar amounts, and changed their format to currency instead of just a number.

pic of Google Sheets formatting

Already, the information is a little more readable. In the next steps we will go further.👇

gif of Alice falling down rabbit hole

How to Create a Table in Sheets

Alas, we don't (yet) have a swift shortcut like Excel does to make a table. But if we highlight our data again, and select Format -> Alternating Colors from the toolbar, we can create table formatting for enhanced readability.

pic of Alternating Colors table style in Google Sheets

Now, we have banded rows of alternating colors. This becomes very useful in large data sets, but is equally pleasing in our small example. Once satisfied with your color selections, click done.

Now, if we add rows of data to the bottom of our table, Google Sheets is smart enough to know that we probably want to extend the table downward. It will extend the alternating color formatting as we add two more lines:

pic of extended Google Sheets table

How to Sort Data with a Filter in Sheets

Tables are useful because, with some small steps, we can sort and filter data in ways that make it easier to draw conclusions or extract meaning from our data.

Click anywhere in the range of data, and select Data -> Create a filter from the Menu bar.

pic of creating a filter in Google Sheets

Again, Google Sheets knows to create a filter for the entire data set, and you can see two things visually represented:

1. The rows and columns of the data set are now given a highlight color to show that a filter has been applied.
2. To the right of each header label, there are three horizontal lines that look like an upside-down pyramid. These are the filters.

pic of filter on a Google Sheets table

From here, we can both sort and/or filter our data. If we sort, for instance, Z -> A, we can display each row from highest to lowest dollar amount.

sorted table

If we click the data column's pyramid, we can see that for filtering options, we can either filter by condition or by value.

pic of filtering options in Google Sheets

Filtering by values lets us toggle on or off certain dates. Filtering by condition allows for all sorts of options. We can choose dates that are before or after certain dates.

pic of filter options in Google Sheets

Or we can select "Is between" and then write in a period of dates we want to filter for.

pic of filter options in Google Sheets

This becomes immensely helpful to narrow down a field of results for analysis. Now we can focus on only the subset of filtered results.

Intro to Formulas and Functions

The final piece we'll touch on is the ability to write formulas and use functions.

Formulas in Google Sheets

Formulas are instructions we manually type into a cell to manipulate data. If we wanted to add the values in cells A16 and A17, we could write a formula that did this like so: =A16+A17.

pic of a formula in Google Sheets

We can do basic math very easily like this, but there's an even better way to do this by using functions.

Functions in Google Sheets

Functions are built-in formulas that we use by typing their name. Again, we start out by typing the equals sign, but then we type the name of the function, in this case SUM: =SUM(A16:A17).

When we begin typing a function, a tooltip will pop up giving us options for the different functions available. You can hit the TAB key to select a function, click one from the list, or complete the spelling of the function and type an open parentheses to select a function.

pic of available functions in Google Sheets

Once selected, the tooltip will display helper text about that function in case we need an explanation of what it does and/or the variables it needs:

pic of the SUM function in Google Sheets

These are straightforward examples of adding numbers, but there are over 400 built-in functions that range greatly in complexity.

What's Next?

As you've probably figured out, this barely scratches the surface of Google Sheets. Once you're comfortable with the basics, it's time to dive deeper and use Sheets to solve some problems of your own.

Check out the templates I've linked above for inspiration, spin up a personal finance tracker, make a workout calendar, track stock prices, build a cost of goods sheet for a small business, build an amortization sheet to see if you can afford a house, track your time with a project management sheet...the possibilities are vast.

🔗Check out my YouTube channel and newsletter for more Sheets content and projects.

🔗Connect with me on LinkedIn.

Hope you have a great one!

gif of Gosling waving bye

We will create two custom formulas, GPT_SUMMARY and GPT_SIMPLIFY. You can use GPT_SUMMARY to summarize a large passage or text into a few bullet points for easy reading. And you can use GPT_SIMPLIFY to simplify English into easy-to-read English.

We will also create menus with access to functions that perform the same tasks as formulas. We will then discuss the pros and cons of using formulas versus menus.

By the end of this tutorial, you will understand how to use ChatGPT in Google Sheets with Google Apps Script. You will also be able to modify the formulas and menus to meet your own needs, such as creating CVs, social media posts, or cover letters.

You can find the source code for this project in this GitHub repo.

If you want to follow along with a video version of this article, here you go:

Pre-requisites

This tutorial is not meant for beginners in Apps Script or JavaScript. I will not be explaining every method or classes used in the code. This is also not a tutorial on how to use and optimize ChatGPT – instead we'll focus on how to integrate GPT in Google Sheets.

Who is this tutorial for?

This tutorial is for intermediate to advanced users who have a basic understanding of Apps Script and JavaScript. If you are new to either of these, I recommend that you start with a beginner tutorial before attempting this one.

Step 1 – Get the ChatGPT API Key

Get ChatGpt API Key

First, if you don't already have an account with OpenAI, you'll need to create one. Once you have an account, you can create a new API key by going to the API keys section under the User tab.

Click the Create new secret key button and copy the key after it has been created. You will not be able to see this API key again, so be sure to copy it somewhere safe.

Step 2 – Fetch the Data From the ChatGpt API with Apps Script

Spreadsheet Sample

I have named my spreadsheet GPT_Integration with three columns: Passage, Simplified Passage, and Summarized Text.

Let's open the app script for this spreadsheet, rename it to GPT_integration, and also rename the existing file to utils.gs. We'll create a function called fetchData here.

const CHAT_GPT_API_KEY = "paste your API key here";
const BASE_URL = "https://api.openai.com/v1/chat/completions";


function fetchData(systemContent, userContent) {
  try {
    const headers = {
      "Content-Type": "application/json",
      "Authorization": `Bearer ${CHAT_GPT_API_KEY}`
    };

    const options = {
      headers,
      method: "GET",
      muteHttpExceptions: true,
      payload: JSON.stringify({
        "model": "gpt-3.5-turbo",
        "messages": [{
          "role": "system",
          "content": systemContent,
        },
        {
          "role": "user",
          "content": userContent
        },
        ],
        "temperature": 0.7
      })
    };

    const response = JSON.parse(UrlFetchApp.fetch(BASE_URL, options));
    //console.log(response);
    //console.log(response.choices[0].message.content)
    return response.choices[0].message.content;
  } catch (e) {
    console.log(e)
    SpreadsheetApp.getActiveSpreadsheet().toast("Some Error Occured Please check your formula or try again later.");
    return "Some Error Occured Please check your formula or try again later.";
  }
}

Here are some key points to notice in the code above:

1. Paste the API key that you created earlier inside the quotes.
2. We will be using the Chat Completions API. You can find more details about it here.
3. ChatGPT models have different roles, such as system, user, and assistant.
4. The systemContent parameter is where you provide roleplay for the GPT system. For example, you could say "You're an expert algebra teacher" or "You're an expert CV writer".
5. The userContent parameter is where you provide tasks to perform for the model. In our case, we will provide long passages from the spreadsheet to summarize and simplify.
6. We will be using the GPT 3.5 turbo model.
7. We are muting HTTPExceptions so that we can use our own error message in the catch block.
8. The error string is useful when we face errors such as Rate Limit Exceed.

We're returning the content from the response object of GPT that'll be later on handled by our formulas.

The response object from ChatGPT has following structure:

{
  "id": "chatcmpl-123",
  "object": "chat.completion",
  "created": 1677652288,
  "choices": [{
    "index": 0,
    "message": {
      "role": "assistant",
      "content": "\n\nHello there, how may I assist you today?",
    },
    "finish_reason": "stop"
  }],
  "usage": {
    "prompt_tokens": 9,
    "completion_tokens": 12,
    "total_tokens": 21
  }
}

Read more on how to use URLFetchApp from here.

Step 3 – Integrate ChatGpt as a Sheets Formula

GPT SIMPLIFY Formula

Again, for the custom formula, we'll create a new file named formula and then we will create a function named GPT_SIMPLIFY.

/**
 * Simplifies the given paragraph in layman's term.
 * @param {String} input The value to simplify.
 * @return Simplified Text.
 * @customfunction
 */
function GPT_SIMPLIFY(input) {
  console.log(input)
  const systemContent = "Simplify the given text in layman's term. Remember reader is not an expert in english.";
  return Array.isArray(input) ?
    input.flat().map(text => fetchData(systemContent, text)) :
    fetchData(systemContent, input);

}

1. The GPT_SIMPLIFY formula simplifies whatever text is provided as input. The input to this function is data that is coming from the spreadsheet. When you select a range, a cell, or multiple cells, the data in the range will be automatically provided by the spreadsheet to this formula.
2. The systemContent is defined to be passed as the first parameter to the fetchData(systemContent,userContent) function.
3. We are checking if the input is an Array because the data passed to this function can either be a nested array or just a string if we select multiple cells or single cell, respectively, in the spreadsheet.

You can read more on custom functions on this page.

Now go ahead and apply this formula in your spreadsheet. I copied some text from a book I'm reading in the first column and applied the formula in the second column named "Simplify Passage", like this =GPT_SIMPLIFY(A2) for the second cell.

_Appilcation of GPTSIMPLIFY Formula

Note: Make sure to refresh the spreadsheet before you apply the formula to sync with the latest changes in the script.

GPT SUMMARIZE

To summarize the formula we'll just copy the simplify function and some other things, as you can see in the code below.

/**
 * Summarzies the given paragraph. It provides from 3-5 bullet points
 *
 * @param {String} input The value to summarize.
 * @return summarize Text.
 * @customfunction
 */
function GPT_SUMMARY(input) {
  console.log(input)
  const systemContent = "Summarize the given text. Provide atleast 3 and atmost 5 bullet points.";
  return Array.isArray(input) ?
    input.flat().map(text => fetchData(systemContent, text)) :
    fetchData(systemContent, input);

}

The main thing to notice here is the different system content.

Note: Since this is not a tutorial on how to use ChatGpt optimally, I provided instructions as the system content instead of role-play, and then just provided data in the user content. You can improvise this by providing roles in system content, and tasks as well as data as two different user roles in our FetchData() function.

GPT Rate Limit Error

For free users, the rate limit to use the API is 3/minute. As such, when you apply these formulas in more than three cells you'll encounter the error. Luckily the execution won't stop because we're returning an error string from fetch data which will be saved into those cells.

API rate limit error

Auto Refresh and Error

Moreover, the auto-refresh feature of the formula can force the re-application of the formula on cells that already have satisfying values whenever source cells are updated, in our case cells in column "A".

When we add a rate limit on top of auto-refresh it can cause a conundrum. You can technically make changes in custom functions to accommodate such circumstances but, I like to keep formulas light and efficient. So, I recommend we instead create custom menus and apply these functions manually.

Step 4 – Integrate GPT Chat API in Spreadsheet Menu Functions

GPT Simplify Menu

First, let's create another file named menu. Then we'll create the gptSimplifyMenu function which will be an alternative to the GPT_SIMPLIFY formula.

/**
 * Simplifies the given paragraph in layman's term.
 * @customfunction
 */
function gptSimplifyMenu() {
  try {
    // get sheets and data
    const ss = SpreadsheetApp.getActiveSheet();
    const data = ss.getDataRange().getValues();
    const lastRow = data.length;
    const lastCol = data[0].length;

    // define gpt's role play
    const systemContent = "Simplify the given text in layman's term. Remember reader is not an expert in english.";


    for (let i = 1; i < data.length; i++) {
      // only simplify if not already simplified or error occured previously
      if (data[i][1] === "" || data[i][1] === "Some Error Occured Please check your formula or try again later.") {
        data[i][1] = fetchData(systemContent, data[i][0]);
        console.log(data[i][1]);

      }
    }

    ss.getRange(1, 1, lastRow, lastCol).setValues(data);
  } catch (e) {
    console.log(e)
    SpreadsheetApp.getActiveSpreadsheet().toast("Some Error Occured Please check your formula or try again later.");

  }
}

Key points that are different to understand in this code are:

1. We're hardcoding the data sources, as such data[i][1], which refers to the second column (that is "Simplified Passage") as shown in the spreadsheet image above. This means that if you're using some other columns to save data from ChatGPT to, then you'll have to make changes according to it.
2. We only fetch data when the target cell is empty or contains an error message. This helps to avoid unnecessary API calls.

Add a Custom Function as a Spreadsheet Menu

The function is ready to be tested, but it still will not appear in the spreadsheet. To do so, we'll need to provide the following instructions.

/**
 * Menu creates menu UI in spreadsheet.
 */
function createCustomMenu() {
   // define menu ui 
  let menu = SpreadsheetApp.getUi().createMenu("GPT Functions");
   // add function to the menu
   menu.addItem("GPT SIMPLIFY", "gptSimplifyMenu");
   // add menu to the spreadsheet ui
  menu.addToUi();
}

/**
 * OnOpen trigger that creates menu
 * @param {Dictionary} e
 */
function onOpen(e) {
  createCustomMenu();
}

In createCustomMenu():

1. We define menu with SpreadsheetApp.getUi().createMenu("GPT Functions") as GPT Functions the title appearing in the spreadsheet tab.
2. We add a function to the menu with the menu.addItem("GPT SIMPLIFY", "gptSimplifyMenu"), where the first parameter is the title for display and the second is the function to call when pressed.
3. Add the menu to the UI with menu.addToUi().

The onOpen trigger runs automatically whenever the document the script is attached to reloads and as such will add a menu to the spreadsheet as shown in the image below.

Go ahead and try the formula – it'll only be applied if the cell is either empty or prefilled with an error message.

GPT Summarize Menu

We'll make some minor changes after copying the simplify function as shown below:

/**
 * Summarzies the given paragraph. It provides from 3-5 bullet points
 * @customfunction
 */
function gptSummaryMenu() {
  try {
    // get sheets and data
    const ss = SpreadsheetApp.getActiveSheet();
    const data = ss.getDataRange().getValues();
    const lastRow = data.length;
    const lastCol = data[0].length;

    // define gpt's role play
    const systemContent = "Summarize the given text. Provide atleast 3 and atmost 5 bullet points.";


    for (let i = 0; i < data.length; i++) {
      console.log(`Inside gptSummaryMenu() for loop`)

      if (i == 0) continue;
      // only summarize if not already summarized or error occured previously
      if (data[i][2] === "" || data[i][2] === "Some Error Occured Please check your formula or try again later.") {
        data[i][2] = fetchData(systemContent, data[i][0]);
        console.log(data[i][2]);
      }
    }

    ss.getRange(1, 1, lastRow, lastCol).setValues(data);
  } catch (e) {
    console.log(e)
    SpreadsheetApp.getActiveSpreadsheet().toast("Some Error Occured Please check your formula or try again later.");
  }
}

1. The system role has been changed to address summary instruction.
2. The target column to save data is now the third column.
3. The doc string has been adjusted as well.

As for adding this function to the menu I'll leave it to you.

Tips to Modify the Code

All you need to make your own formula like =GPT_COVER_LETTER_CREATOR() are the following modifications:

To FetchData

You can change System Content Description should to address your needs, like "You write an expert cover letter for software developers".

Add one more instruction in the messages array:

// from this 
[{
          "role": "system",
          "content": systemContent,
        },
        {
          "role": "user",
          "content": userContent
        },
        ], 

// to 
[{
          "role": "system",
          "content": "You write an expert cover letter for software developers",
        },
        {
          "role": "user",
          "content": "Write me a cover letter for this given job advertisement"
        },
        {
          "role": "user",
          "content": userContent // this is job ad from spreadsheet
        },
        ],

You can also add another list item to include your skills and experiences.

Other Functions

Just make sure that your source cell/columns and target cell/columns are accurately indexed (for instance, if you're not using the first column as the source cell and the second to save the data).

Summary

In this tutorial, you learned how to use Google Apps Script to fetch ChatGPT responses from the API and save them into spreadsheets using custom formulas and custom menus.

We started by creating a new Google Apps Script project and adding the ChatGPT API. Then, we wrote a script that would fetch a ChatGPT response for a given prompt. We saved the response using a custom formula into a spreadsheet cell.

We also created a custom menu item that would allow us to fetch a ChatGPT response from any cell in the spreadsheet. This menu item would open a button to fetch the response.

The final step was to share the spreadsheet with others so that they could use the custom formulas and menus to fetch ChatGPT responses.

I hope you enjoyed this article and found it helpful. If you have any questions, just let me know.

I am Nibesh Khadka, Freelancer specializing in automating Google products with Apps Script. Contact me if you need my services at me@nibeshkhadka.com.

Coefficient produced a Google sheets extension with the ability to use OpenAI's GPT models from within a spreadsheet. It’s called GPT Copilot and it’s available to try for free.

screenshot of Coefficient's GPT in Sheets page

I have been putting off looking into this tool for quite some time, though I've been following Coefficient's Google Sheets work for a while now.

To be honest, I'd gotten a little tired of all the AI talk and was wary of yet another effort in that realm. Do I really need to use AI in spreadsheets?

🤷Yes and no.

Video Example

I made a quick video walking through a handful of the GPTx functions, and I'll detail all of them in the article below. If you have a sec, check out the 2.5 min video and give it a 👍.

The extension is pretty cool.

AI is not some thing that is going to do all of my work for me. But it is a pretty powerful tool with which I can do work faster, more efficiently, and in some cases get some unique inspiration from.

gif of ted lasso saying, smells like potential

GPT Copilot Functions

In the GPT Copilot extension on Google Sheets, we are given a list of several built-in GPTX functions we can now use directly in the sheet itself.

Here is a document with all the functions listed out, and we'll go through each below.

And here's the spreadsheet I used for all my samples and the video.

The first function, =GPTX(prompt) simply lets us generate text using Open AI's GPT model.

I used this to generate a subject line for an email below. This generic GPTX function can be used to query ChatAI's model for anything you can think of:

Screenshot of GPTX() function

Then we get **=GPTX_LIST(prompt)** which lets us generate a list of values in the same way.

This is similar to the first example, but the list of items will appear in separate cells so it's handy if you want...say, the top 10 video games by sales in Korea:

_Screenshot of GPTXLIST() function

**=GPTX_EDIT(text,[task])** was useful as I went through and created email body messages based on a prompt that I fed into the function. So it lets us perform a task or transformation on an input text.

_screenshot of GPTXEDIT function

**GPTX_MAP(search_keys, inputs)** performs a fuzzy search, given a list of search keys in the list of input values, and then outputs the most similar search for each input value.

If we have a list of games and platform companies, it can map the company values to the correct game:

_Screenshot of GPTXMAP()

**GPTX_FILL(text, [task])** fills in missing information in the table based on example rows.

This will let you feed it some example data and have GPTX fill in the rest:

_Screenshot of GPTXFILL

**GPTX_TABLE(prompt, [header_row])** generates a table of values. This one was particularly cool because you can give it a real query to get data from, or have it fill out a table with dummy data.

Here we can pull in some population info on the top 5 cities in Tennessee: 😀

_Screenshot of GPTXTABLE()

**GPTX_FORMAT(text, language)** converts input values into the specified format.

**GPTX_TAG(text, tags)** does what it sounds like: it applies one or more tags matching a piece text.

If you have a list of movies, and a list of genres, you can tag them here. Interestingly:

1. GPT doesn't seem to have a sense of humor as it didn't classify Waterworld as a flop.
2. It also won't blindly fill in genres that are missing from the list (Goldeneye and Seven)

_Screenshot of GPTXTAG()

Another cautionary tale, though...when I added crime to see if it would fill in Seven's blank, it actually came back with different answers on some of the others...and left off Casper and Jumanji this time. So, while nothing is really tagged incorrectly, it does have varying results.

In this case, it does behave like a human, in a way. You'll get different people answering this type of tagging question in different ways too. 🤷‍♂️

**GPTX_CLASSIFY(text, labels)**, in the same way, classifies text given a set of labels or categories.

**GPTX_EXTRACT(text, info_to_extract)** extracts the desired information from the input text. So if you have an address in a cell, it can extract the city name from the address.

Caution though; I encountered several instances while writing these GPTX functions where I got #ERROR!s and had to refresh the page, close it and reopen, or simply wait a while...

**GPTX_SUMMARIZE(text, language)** summarizes the input text according to the given format.

I tried to be clever here. I pasted the first chapter of The Great Gatsby into a cell. Pretty quickly got a "too large" error 😂)

_screenshot of GPTXSummarize error

But! Reducing down to the first 726 words was sufficient to have it actually summarize the beginning of this awesome novel:

_Screenshot of GPTXSUMMARIZE() summarizing the start of The Great Gatsby

**GPTX_TRANSLATE(text, language)** translates the input text into the specified language.

This one's straightfoward and not incredibly useful for my spreadsheets...but nonetheless, it's pretty neat:

_Screenshot of GPTXTRANSLATE()

**GPTX_CONVERT(text, format)** converts the input text into the specified structured format.

**GPTX_CODE(task, language)**generates code in a specified language, which performs the specified task.

GPTX_CODE() was really neat. I also see the most chances for problems here because novices will be tempted to blindly use code not knowing how or if it works.

This seems most beneficial as a helper for when I know how the code should work, but can prompt for the basic skeleton instead of needing to search for methods on my own.

Limits to GPT Copilot

Are there limits to the number of prompts you can feed these functions? Yes. Of course there are. 😆

To stay free, you get 10,000 executions. Monthly plans start at 100,000 executions per month.

screenshot from the Coefficient GPT CopilotFAQ

What I Think

I walk through some real basic stuff that I could have generated on my own in the example video that I encourage you to watch. But I do see the potential for this to be a very helpful tool as the ecosystem matures.

Insofar as dummy data is concerned, it is really helpful to just generate stuff in a spreadsheet quickly.

And it works great to fill in the blanks where you previously might do a little bit more manual labor, extracting values, formatting things, classifying things, and so on.

In addition to these built-in functions, the Coefficient add-on also contains a pivot builder, a formula builder, and a chart builder tool.

screenshot of Coefficient's extension

These allow you to feed a prompt into the extension based on the data in your spreadsheet, and it will generate either a pivot table, a formula, or a chart.

These are kind of crutches for doing some pretty basic operations in Google Sheets. But they do have an interesting utility and are pretty reliable when you give well-written prompts to them.

Wrapping Up

🤔 AI tools can be a big time sink. It's pretty easy to spend more time getting mediocre results from them than it would have taken to do things manually.

But, that's part of the learning curve for any toolkit. Upfront investments can certainly pay off in the long run. I'll keep using these tools to hopefully make better spreadsheets more efficiently.

❓What do you think? Is this another example of over-engineering simple things? Or is it the next big thing in Google Sheets?

Come join the conversation on my YouTube channel: https://www.youtube.com/@eamonncottrell?sub_confirmation=1

Or my LinkedIn: https://www.linkedin.com/in/eamonncottrell/

Have a great one! 👋

I bet I've used the CTRL + F shortcut more than any other keyboard shortcut in my life. CTRL + Z probably comes close, but I use CTRL + F to find things...

💥ALL 💥

💥💥THE 💥💥

💥💥💥TIME 💥💥💥

gif of man saying we need more

And yes, it'll work just fine in a Google Sheet to find information.

But sometimes I want to display a range of results based on a word I'm searching for. To do that, we'll create a dynamic search bar in our Google Sheet.

You can also follow along in this walkthrough video:

The Search Bar

Our search bar is nothing more than a cell or range of cells. In the example below, it starts in J2.

Screenshot of a search bar on Google Sheets

By adding a =QUERY() function in J5 we can look at whatever is typed into J2 (the red circle) and display the search results below it (the blue rectangle).

In my example, I'm searching through a bunch of personal finance transactions (with randomized amounts😀) that are in columns A:F.

Screenshot of finance data on Google Sheets

The Query Function

The =QUERY() function looks in the Transactions range (which is that A3:F range where all the transactions live).

And it grabs all the info in either column B or column D that CONTAINS what we type in J2.

So it searches through all our transaction descriptions in column B and categories in column D for whatever we type in J2. The LOWER command turns the info in B and D into lowercase. This makes it easier to search because the CONTAINS command is case-sensitive.

=QUERY(Transactions, 
       "SELECT A,B,C,D,E WHERE LOWER(B) CONTAINS '"&J2&"' OR LOWER(D) CONTAINS '"&J2&"'")

• the only caveat is that if you type in uppercase in the search bar, it won't work properly.

The Filter Function

By using the =FILTER() function in combination with the =SEARCH() function, we can do the same thing in a little bit shorter formula and without having to worry about case sensitivity.

=IF(ISBLANK(J2),"",FILTER(Transactions,SEARCH(J2,B3:B225)))

The tradeoff here is that when we want to add multiple conditions like we did in the =QUERY() statement, it breaks down. Both =FIND() and =SEARCH() did not work properly when trying to use them more than once inside the =FILTER().

I was able to find a work around by using the plus operator and constructing the formula in this way:

=IF(ISBLANK(J2),"",FILTER(Transactions,(B3:B225=J2)+(D3:D225=J2)))

Unfortunately when you filter in this way, partial matches are not included in the search results.

In the case of Query, partial answers are always returned.

So, when we enter "hom" all the lines with "home" in it would be returned. When using multiple conditions with filter, nothing would return unless you entered the whole word "home".

What About XLOOKUP?

The issue with XLOOKUP is twofold. One, it doesn't handle partial matches well unless you add wildcard characters:

=XLOOKUP("*"&J2&"*",B3:B225,A3:F225,,2)

This increases complexity but still works.

The more important difference is that it will only return one result so it's not going to work at all for us for this use case.

The Winner is Query

Query takes the prize simply because it doesn't need further manipulation to add multiple conditions, and it will return all the values that meet our search criteria.

It may take you a minute to wrap your head around the syntax, but it's just as powerful and more versatile than Filter in the long haul.

The only thing to make sure to remember is case sensitivity. If you're using the LOWER command in your query, don't use any uppercase search letters.

Make it Neat

In the full formula, I've added an =IF() function at the start to handle the blank search bar. We want to return nothing in that case:

=IF(ISBLANK(J2),"", QUERY(Transactions "SELECT A,B,C,D,E WHERE LOWER(B) CONTAINS '"&J2&"' OR LOWER(D) CONTAINS '"&J2&"'")

Screenshot of a blank search bar in Google Sheets

Follow Along

Come follow me over on YouTube as I make tutorials each week.

Sign up here to get my newsletter in your email each week.

Eamonn's Sheets | Coding | Education logo

By using a very short custom function in Google Apps Script, we can add a list of events from a Google Sheet to a Google Calendar. 🤯

And we'll even have it email our guests as well. 🔥

Here's the video walkthrough to accompany the article:

Google Sheets Setup

Our sheet is quite straightforward. We have event names, dates, start times, end times and guest emails.

The only curious thing is the formatting of our dates and times - I'll cover this as we go on, but you can see that columns B and C are repeating information from columns D, E and F...

Google Calendar needs to receive the start and end times in the form of a full date/time object. But in the Google Sheet, there's not an easy way to create a dropdown data validation for users to select a date/time object.

In column D, I've put data validation to select a valid date.

And in columns E and F, I've created a dropdown list of valid times.

Columns B and C combine these together into a format that's useable to send to Google Calendar by using the =TEXT() function to concatenate the date and times together.

I promise it'll make more sense in a second! 😃

Calendar Setup

Let's make a new calendar in Google Calendar.

Underneath your calendars on the left sidebar of Google Calendar, click the plus icon to add a new one.

Give it a name and a description if you want, and then we're ready to roll.

Scroll down a bit in the calendar's settings to the Integrate Calendar section. Copy the calendar ID. This is how we'll get Apps Script talking to Calendar!

Apps Script + CalendarApp

Apps Script is awesome. 👏

The Class CalendarApp allows a script to access a user's Google Calendar and make changes to it.

Here is the full script, and we'll walk through what's going on below.

// Creates an events variable which is an array of arrays
function createCalendarEvent() {
    let events = SpreadsheetApp.getActiveSpreadsheet().getRangeByName("events").getValues();

    // Creates an event for each item in events array

    events.forEach(function(e){
        CalendarApp.getCalendarById("f7574e7b4d1ad00c9ecd7f1eba5bed329e8600e317cd387a400748d67f301d06@group.calendar.google.com").createEvent(
          e[0],
          new Date(e[1]),
          new Date(e[2]),
          {guests: e[6],sendInvites: true}
    );
  })
}

I've named the range A3:B8 as "events". Then in Apps Script, we create a variable named events that grabs all the values in that whole range. We used a small range, but you could make this as many rows long as need be.

let events = SpreadsheetApp.getActiveSpreadsheet().getRangeByName("events").getValues();

Then, we loop through each item and add the events to our calendar.

The first part is where we use that calendar ID string we grabbed from Google Calendar for the getCalendarById method.

Then we use the createEvent method to pull data from each row in our Google Sheet and make new events.

Here's the createEvent description from the developers page:

You can think of each row of data in the Google Sheet as an array of values. In the zero position is the event name, in the one position is the event date and start time, and so on.

By using e[0] we can access the element that is in the zero position for every time we loop through the forEach loop...effectively looping through each row of data.

And this is where the funky stuff we did with the start and end times comes into play.

Because the values in columns B and C are strings since we concatenated them together, we need to turn them back into complete date objects now.

That's why we're passing new Date(e[1]) and new Date (e[2]) into our createEvent function.

It's a bit of a cumbersome way to allow ourselves to use those dropdown selections in Google Sheets rather than painfully typing in a full date/time object.

User experience > code. 👍

And lastly, we add an optional parameter to send invites to guests.

Send with Button

That's all there is to the Apps Script. 🎉

As an added feature, we've attached a script to the rounded rectangle drawing to make it function like a button. Any time this is pressed, the events in the Google Sheet will populate the Google Calendar.

I hope this has been useful for you!

Please come check out and subscribe to my YouTube channel where I'm making weekly videos on coding and spreadsheets.

If you'd like my newsletter in your inbox, check it out here.

Yes, you can use them for finance dashboards, personal budgets, and project management (and we'll cover these as future topics). But in this article, I'll go through the basics of Apps Script by building a simple and playable Tic Tac Toe gameboard.

Here's the link to the spreadsheet we'll be making if you'd like to check it out while you follow along:

Let's go gif

Video Walkthrough Available

If you'd like to check out a video walkthrough of the Google Sheet, here you go:

Project Setup

Create a new Google Sheet by either going to your Google Drive and selecting NEW -> Google Sheet or by simply typing in sheets.new in the URL bar of your browser.

screenshot of Google Drive

Since this is a gameboard, we are going to give it a little bit of formatting to look good, add data validation and conditional formatting to add functionality to the game, and create useable buttons for our scorecard.

Here's what we'll end up with:

screenshot of Tic Tac Toe Google Sheet

Let's remove gridlines, add borders to the gameboard and the scorecard, and set an alternative font for the board.

To get rid of gridlines, select View -> Show -> Gridlines to uncheck this option.

screenshot of Google Sheets view options

To get a nice square grid with large X's and O's, I set the row height and column width of rows 2 - 4 and columns B - D by highlighting them, right clicking, and selecting the resize options.

screenshot of resizing columns in Google Sheets

I chose 150 pixels for the height and width. You'll need to do these separately – you cannot change both the row height and column width at the same time.

screenshot of resizing columns in Google Sheets

For the font size of the board, select 100, and for the Font, I am using Lexend. You can add additional Google Fonts from the toolbar dropdown:

screenshot of Fonts options in Google Sheets

Add a border to the board and the scorecard areas by highlighting the cells and then selecting the border options from the toolbar.

Click and drag over cells to select the whole range, and hold down the CTRL button to click and drag a second area. You can style these at the same time.

screenshot of border options in Google Sheets

Data Validation

Highlight the gameboard (B2:D4) and select Data -> Data validation from the menu.

This allows us to select Dropdown as the Criteria and add X and O as the two options to select.

Then click Advanced options and select Reject the input if the data is invalid, and plain text for the display style. This will keep the dropdown chips and handles from cluttering the gameboard.

screenshot of Data validation menu in Google Sheets

Conditional Formatting

We'll use conditional formatting for our gameboard also. We need to check for all the winning conditions, and if one of the players gets three in a row, we will highlight those cells.

Keeping the gameboard highlighted, select Format -> Conditional formatting.

screenshot of Format window in Google Sheets

There are eight conditions we'll check for three-in-a-row: going across x3, going down x3, and diagonal x2.

But we only need to write four formulas (two for the diagonals, one for across, and one for down) since we can use dollar ($) signs to drag the formula down and across for those.

For the down three-in-a-row:

//Apply to range B2:D2 
=AND($B2=$C2,$B2=$D2,ISTEXT($B2))

For the across three-in-a-row:

//Apply to range B2:D2
=AND(B$2=B$3,B$2=B$4,ISTEXT(B$3))

For the diagonals we have to define them separately:

//Apply to range B2, C3, D4 
=AND($B$2=$C$3,$B$2=$D$4,ISTEXT($B$2))

//Apply to range B4, C3, D2 
=AND($B$4=$C$3,$B$4=$D$2,ISTEXT($B$4))

We test for equality of each cell and whether there is anything in the cell with the =ISTEXT() function. By wrapping each item in an =AND() function, we'll only apply the formatting if all conditions are met.

I selected a green background for the conditional formatting.

screenshot of conditional formatting window

Apps Script

Now for the scorecard logic. Let's open Apps Script by selecting Extensions -> Apps Script from the menu:

screenshot of Apps Script menu

We'll write four functions to handle our logic:

1. xScore() will increment X's score in the scorecard
2. oScore() will increment O's score in the scorecard
3. clearBoard() will clear the board but keep the scores
4. reset() will clear the board and set the scores back to zero

To make things more legible, let's set a few named ranges.

Highlight the gameboard again and select Data -> Named ranges. Give this range a name of Board. Do the same for cells G4 and H4 for xScore and oScore, respectively.

Now for the scores, we'll have the exact same function for each using only the two different ranges: xScore for X and oScore for O. Here's how those will look using the xScore as an example:

xScore() & oScore():

function xScore() {
    var sheet = SpreadsheetApp.getActive(); 
    var xScore = sheet.getRangeByName('xScore').getValue();
    sheet.getRangeByName('xScore').setValue(xScore+1); clearBoard();
}

• Line 1: This sets a variable (we'll do this in each function) for the active spreadsheet.
• Line 2: This sets a variable for xScore as the value in the named range xScore (cell G4)
• Line 3: This sets a new value for the xScore cell as whatever it was plus 1.
• Line 4: This runs the clearBoard() function which we'll write next...

clearBoard():

This will simply clear the gameboard but leave the score board untouched.

function clearBoard() {
    let sheet = SpreadsheetApp.getActive();
    let board = sheet.getRangeByName('Board');
    board.clearContent(); 
}

• Line 1: Our sheet variable again.
• Line 2: Our board variable. This is grabbing the range B2:D4 which we named 'Board'
• Line3: This built-in clearContent() method simply clears everything in those cells. Pretty simple.

reset():

Now we need a function to set the score board and gameboard back to their original states.

function reset() {
    let sheet = SpreadsheetApp.getActive(); 
    sheet.getRangeByName('xScore').setValue(0);
    sheet.getRangeByName('oScore').setValue(0); clearBoard(); 
}

• Line 1: our active sheet
• Line 2: we grab our xScore range and set its value to 0.
• Line 3: we do the same for our oScore
• Line 4: we run the reset function to handle the gameboard.

And that's it! Now we can run any of these functions from within the Apps Script editor and see that they work.

screenshot of running code within Apps Script editor

How to Create Buttons

It would be a lot nicer to have buttons in our actual spreadsheet to be able to run the functions.

To do this, we'll draw a button and then assign a script to it.

Select Insert -> Drawing from the menu.

Screenshot of Insert menu in Google Sheets

You can draw anything you'd like, but I chose the basic rounded rectangle.

Screenshot of shapes in Google Sheets drawings menu

Double click in the shape to add text, and resize, recolor, restyle as needed.

Screenshot of button in Google Sheets

Once you've created your button, click Save and Close. Then resize and position it where you'd like it in the Google Sheet. I've put mine right under the scorecard, and I made one for each score as well as a reset button.

Finally, to make the button work, click the three little dots at the top right of the button and select assign script. Then type in the name of the script (without the parentheses).

Screenshot of assigning a script to a button in Google Sheets

Now, all you've got to do is click one of the buttons and the assigned script will run 🔥.

Two notes:

1. The first time you run a script, there will be a pop-up dialog box asking you to accept the security allowances. It's a safety net to make sure you know you're running the code that's written in Apps Script, and to examine it if you didn't write it. You'll need to click through those and accept the risk to allow it to run.
2. If you need to move a button after assigning the script, you may get frustrated when clicking it doesn't bring up the three dots for the menu and only runs the script. To get around this and allow for movement and the three dot menu, right click the button.

Wrapping Up

I hope this has been helpful for you!

Please subscribe to my YouTube channel here for more content like this.

Have a great one!

In this Apps Script tutorial, we'll build a paint by numbers spreadsheet using conditional formatting and a script that "paints" a blank spreadsheet.

You'll learn how to:

1. Import data
2. Apply proper data visualization formatting to it
3. Code a couple of Apps Script functions to make it interactive.

Let's do it 🎨

Tenacious D rocking out

Video Walkthrough

Yes, I've got a full walkthrough for you. Pull this up as you read to reference and follow along 👇

Demo sheet with Pikachu: https://docs.google.com/spreadsheets/d/1Zu0B0dE_N4UrgAAzlWKqbpmz2TL_qr9GYWS451O7UL0/edit#gid=0

Demo sheet with Volcano: https://docs.google.com/spreadsheets/d/11lOVseXtpB6xWxhrmZr1LfImI75TBDbof6mkFzz0ck4/edit#gid=0

You can make an editable copy of either of these by selecting File -> Make a copy.

Project Setup

Everything we're doing today is built on some simple formatting. We are going to have cells turn certain colors based on the number inside them.

See the pic below where all the blue cells have the number 15 in them. By setting the color of the font and the background to blue, we can create the effect of the cells being a solid color.

Picture of Pikachu pixel artwork

We can make our own number grid, but there are a ton available. I print these for my kids to color, and we can import them to our spreadsheet with a couple clicks.

Here's the volcano grid I used in the walkthrough video.

Picture of volcano color by number grid

When I first recorded the walkthrough video, I was unable to copy and paste from the PDF. When I did, it pasted every number in one cell.

Instead, by opening in Microsoft Word first and then copying and pasting from there, I was able to bring the number grid into the Google Sheet.

Since then, I've also found that when copying and pasting from the PDF, sometimes it will bring the numbers in to the first cell in each row:

picture of Google Sheets number grid

This doesn't work, either, because we need each number in its own cell. But, by applying the =SPLIT() function, we can achieve this easily.

=SPLIT(A1," ") will split each value in the cell by the empty spaces. So, all the numbers are pulled out into their own cells in the row.

Picture of Split function in Google Sheets

Once all the numbers are in individual cells, apply some formatting to the spreadsheet so that every cell is a square. Resize as big or as small as you'd like. I chose a row and column height of 30px.

To do this, select the column headers by clicking and dragging from A all the way to the end of the columns. Right click anywhere in the range, and select Resize columns.

Picture of resizing columns in Google Sheets

Do the same for the rows, specifying 30px for each.

Picture of resizing rows in Google Sheets

Turn off the gridlines by selecting View -> Show -> Gridlines.

Picture of View options in Google Sheets

Conditional Formatting

Select the entire range where all the numbers are and then click Format -> Conditional formatting.

Click Add new rule and under Format rules, select Is equal to from the dropdown menu.

Picture of conditional formatting in Google Sheets

Under Formatting style, follow the color key from the coloring page you selected and adjust the font and background colors according to each number.

In our example, all the number 10s need to be blue, so we enter 10 and then have the same blue for both background and font colors:

Picture of color options in Google Sheets

⭐Important Note

Because of the script we're writing and how we're triggering it, you need to alter the HEX code for one of these two numbers. If they are the exact same, it will cause an error later.

So, first enter the same color for both, then open one and select the plus icon in the custom color swatch.

Picture of custom colors in Google Sheets

Manually change one value in the HEX code by one digit. In the example, I changed it from #0b5294 to #0b5394. Visually, it will still look the same. If this is confusing, be sure to check out the walkthrough video at 02:39.

Picture of custom colors in Google Sheets

Do this for each color in your piece of art, and you'll have a gorgeous piece of pixel artwork in your spreadsheet. This alone is rewarding! 😀

Picture of volcano pixel art in Google Sheets

Apps Script Setup

Name the sheet that we're on by double clicking Sheet1 at the bottom. We'll call it "art". Then make a new sheet by clicking the plus icon on the bottom bar. Name it "canvas".

Picture of sheet names in Google Sheets

Setup the canvas in the same way we did at the beginning, only without the conditional formatting. Make everything the same size, remove the gridlines, and add a border around the B2:T21 range that will serve as a frame.

Now, we need to make buttons to toggle in each cell. In Google Sheets, the way to do this is by adding checkboxes to all the cells. Checkboxes will hold either a true or false value, and when we click them, they'll change back and forth.

Select our full range again, and select Data -> Data validation. Change the criteria to Checkbox and under Advanced options select Reject the input.

Picture of data validation rules in Google Sheets

This will give our script something to be triggered by.

Format these checkboxes in the same way we did our conditional formatting: make the background white: #ffffff, and the font color just slightly different: #fffeff. Then, give them a huge font size, like 200. This will allow for us to click anywhere in the cell and not run the risk of clicking just outside the border of the box itself.

Now, let's open our code editor by selecting Extensions -> Apps Script.

Picture of Extensions menu in Google Sheets

Script Logic

We need to copy and paste the formatting of individual cells every time we click the blank cells in our canvas.

To do this, we'll use an onEdit(e) trigger method built into Apps Script.

function onEdit(e) {
  //get current sheet
  var sheet = SpreadsheetApp.getActiveSheet();

  // if we're not on the art sheet...
  if(sheet.getName() != "art"){

First, we'll grab the active sheet as a variable. Then, making sure we're not on the "art" sheet, we'll go through the steps to grab and paste the formatting we need...

// get the active cell and it's row, column reference
var activeRange = sheet.getActiveCell();
var row = activeRange.getRow();
var column = activeRange.getColumn();

Within our conditional if statement, we'll make three more variables so that we can grab the position of the cell we're in.

Then we need to go to our "art" sheet and grab the formatting from the corresponding cell.

var artRange = SpreadsheetApp.getActive().getSheetByName("art").getRange(row,column);
// get the background color from the same reference in art sheet
var backgroundColor = artRange.getBackground();
var fontColor = artRange.getFontColor();

We'll make another three variables: one for the artRange which grabs the range from the "art" sheet using the row and column that we're on in the "canvas" sheet. And then two variables for the colors: one for background and one for font.

Now all we need to do is set the "canvas" sheet's cell to the colors we just grabbed. I've also chosen to make it toggle back to a blank white cell if it's already been colored. So we'll use another if statement to handle that:

trueFalse = activeRange.getValue();
if(trueFalse){
      // set activeRange with that backgroundColor
      activeRange.setBackground(backgroundColor);
      activeRange.setFontColor(fontColor);
    }
    else{
      activeRange.setBackground('#ffffff');
      activeRange.setFontColor('#fffeff');
    }

First, we set a trueFalse variable equal to the activeRange's value. This is either true or false depending on the state of the checkbox.

If it's false (the checkbox isn't checked), then we set the background and font colors using the variables we grabbed from our "art" sheet.

Here's the full onEdit(e) code:

function onEdit(e) {
  //get current sheet
  var sheet = SpreadsheetApp.getActiveSheet();

  // if we're not on the art sheet...
  if(sheet.getName() != "art"){

    // get the active cell and it's row, column reference
    var activeRange = sheet.getActiveCell();
    var row = activeRange.getRow();
    var column = activeRange.getColumn();

    var artRange = SpreadsheetApp.getActive().getSheetByName("art").getRange(row,column);

    // get the background color from the same reference in art sheet
    var backgroundColor = artRange.getBackground();
    var fontColor = artRange.getFontColor();

    Logger.log(backgroundColor)
    Logger.log(fontColor)

    trueFalse = activeRange.getValue();

    if(trueFalse){
      // set activeRange with that backgroundColor
      activeRange.setBackground(backgroundColor);
      activeRange.setFontColor(fontColor);
    }
    else{
      activeRange.setBackground('#ffffff');
      activeRange.setFontColor('#fffeff');
    }
  }
}

Reset Function

As an added feature, we'll add an actual button to reset the canvas. To do this, we'll make a new function in our Apps Script code editor.

We'll grab the sheet and all the checkboxes as variables. To get the checkboxes, we'll use the getRangebyName() method on our 'canvasArt' range.

Then, Apps Script makes it pretty easy with built in methods. We set the value of all the checkboxes to false, the background color to #ffffff, and the font color to #fffeff.

Here's the full reset() code:

function reset(){
  var sheet = SpreadsheetApp.getActive();
  var checkboxes = sheet.getRangeByName('canvasArt');
  checkboxes.setValue(false);
  checkboxes.setBackground("#ffffff");
  checkboxes.setFontColor("#fffeff");
}

Trigger with Button

To make a button in the spreadsheet, select Insert -> Drawing.

Picture of Insert menu in Google Sheets

Select the rounded rectangle shape and drag it onto the grid.

Picture of Shapes menu in Google Sheets

Double click into the shape to write "CLEAR". Adjust the font and colors as you see fit.

Picture of button drawing in Google Sheets

Click Save and Close and then drag it to re-size and reposition onto your sheet at the bottom of the canvas.

Once you've positioned it, click the three circles in the top right, select Assign script, and type in the name of the script you'd like it to trigger (in our case, reset).

Picture of assigning script to button in Google Sheets

Now, when you click this button, that script will run and clear the whole art canvas.

Conclusion

I hope this has been helpful for you! I had a great time making this, and I have more game-type spreadsheet content coming soon.

Come follow me on YouTube, and say hey over on LinkedIn.

Have a great one! 👋