Naturally, I had to prepare my source code as per instruction. In this article, I will share how I packaged and submitted my Add-on's source code in Ubuntu OS.

But before that, let's talk a little about Tier 2 CASA assessment.

What is Tier 2 CASA Security Assessment?

The Tier 2 CASA (Cloud Application Security Assessment) is a self-service security assessment process for applicants seeking access to Google Workspace data or to comply with specific Google Workspace policies.

It allows developers to scan their applications and submit the results for verification without an external assessor accessing the code or infrastructure.

Importance Tier 2 CASA security assessment

Tier 2 CASA is important for several reasons:

• Security Assurance: It provides independent verification of your application's security posture, reducing the risk of data breaches and protecting user privacy.
• Compliance: It helps meet security requirements for accessing Google Workspace data or adhering to Google policies, like the Workspace Marketplace Terms of Service.
• Efficiency: It's a faster and more cost-effective alternative to Tier 1 assessments, which involves external assessors directly examining your application.
• Trust: If your addon is published without verification it'll display an "unverified" message to the clients while installing the addon, which creates distrust and can lead to the installation process of your addon to be abandoned.

In the context of my Google Workspace Addon Scan Me, the use of restrictive OAuth scope auth/drive of Google Drive API likely triggered the need for a Tier 2 assessment. This scope grants your addon access to see, edit, create, and delete all of your Google Drive files, which falls under Google's security and privacy requirements.

Additional Resources

• CASA Tier 2 Overview
• CASA Documentation
• Google Workspace Marketplace Terms of Service

Disclaimer: While I'll explain the Tier 2 CASA process, it's crucial to consult the official documentation and Google's security guidelines for specific requirements and guidance.

The assessment certification is free, by the way. To prepare your addon for the CASA assessment process follow the following steps.

Step 1 – Sign up for the new Assessment Procedure

If you're using restrictive scopes, you'll receive an email from Google's Verification team at some point requesting to verify the scopes after you've submitted your add-on for verification.

This email is the notification document. So, you need to download this email as a PDF, which must be submitted in the application form later on.

In that email, you'll find the following instructions for Tier 2 evaluation. You'll find a link to register or log-in to the CASA portal. Click the link and register to the site. Then click on Start New Assessment> Create New Assessment.

Fill in the information asked carefully. Upload the previously downloaded email where you're asked for a Tier 2 notification pdf.

Starting New CASA Assesment of the Addon

Note: For Google Workspace Addon, the type of application is Local App.

Caution: As shown in the image above, even though "Project ID" is asked in the input field, they are asking for the Project Number included in the email, not the Project ID of your Google Cloud Console project.

After you carefully fill in the details and submit the form, you'll arrive at a new screen – Application Screening – where there are two things that you should download:

Download Scan Cenral Package and Setup Insruction

1. Fortify Scan Central Package.
2. Instruction on compressing your application's source code for initial assessment.

Step 2 – Download and Setup Java JDK

To use the Scan Central package as mentioned in the instructions, a minimum of JDK 11 is required.

For setting up the path for the Java environment in Linux, I followed this instruction on StackOverflow.

Step 3 – Setup Path for Scan Central

Now let's add the path to the Scan Central in our system.

In your CLI, open .bashrc file with sudo nano ~/.bashrc. Append the following path at the end of the file:

# SCAN Central 
# Path looks like following
#/home/<username>/Fortify_ScanCentral_Client_22.2.1_x64/bin

 export PATH=$PATH:<Path To bin folder in Scan Central>

Save (CTRL+S) and exit (CTRL + X) the file.

Open .profile with sudo nano ~/.profile and add the same path as above. You can check the version of Scan Central in your CLI with the command scancentral -version, to make sure the setup was successful.

Step 4 – Packaging Source Code for Assessment

To package the source code for your Google Workspace Addon, go to the root directory of your project. If you're following the instruction manual, go to the section for JavaScript code packaging.

In the root directory run any of the following commands:

#cmd 1 
scancentral package -bt none -o myPackage.zip
# or cmd 2
scancentral package -bt none --scan-node-modules -o myPackage.zip

Note: The command scancentral.bat is for Windows users.

As mentioned in the instruction, command 2 increases the size of the package and is not necessary for Node.js or Angular. I created Workspace Addon so I don't have node-modules in my source code.

After that, you'll see a compressed package named myPackage in the directory where you ran the packaging operation.

Step 5 – Initiate the Scan Process

After packaging, go back to the CASA portal and click on your assessment ID link in the list, and open up the Application Screening window. Here:

1. Click the Begin Scan Process button.
2. Upload the package you just compressed.
3. Click the Upload File & Initiate Scan button.

Upload Source Code To Fortify Scan

This will initiate auto scanning of your application which is the beginning of assessment for your Addon.

Reminder: As I've personally experienced, if your source code uses the Math.random() method, then the auto-scanner will not pass your code.

If you pass this phase, the manual verification process will begin where you'll have to fill in forms for the survey. Go to this link for the questions that'll be asked in the CASA survey. Here, choose the Local App option for App Type for a Google Workspace Add-on. I want to remind you that they will change based on the answer provided.

Conclusion

Alright, I'm hoping this blog helped you reduce the time and confusion that I had to encounter when I was trying to assess my addon. And please don't give up midway during the evaluation otherwise your months of hard work will be in vain.

My addon Scan Me, scans the Google Drive and prepares an audit report in a spreadsheet file of your choosing in your Google Drive. It makes it extremely easy for you to analyze your Google Drive from one place, and it also offers a free quota. If you're looking for a similar addon I hope you'll try this addon.

This is Nibesh khadka, have a good day.

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.

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.

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.