How to Create a Search Form in Google Sheets – Google Apps Script Tutorial

Have you ever struggled with navigating an enormous spreadsheet? You can get rid of the countless scrolling and unlock the facility of focused knowledge retrieval with a customized search kind utilizing Google Apps Script.

In this hands-on information, we’ll craft a search software that seamlessly integrates together with your spreadsheet, permitting you to:

• Search throughout a number of tabs: Effortlessly question knowledge from totally different sections of your spreadsheet with devoted enter fields.
• Master AND & OR searches: Find precisely what you want with each AND and OR search functionalities, guaranteeing exact or versatile matching based mostly in your necessities.
• Boost your productiveness: Save helpful time by eliminating guide searches and filtering by way of solely the related knowledge factors.

Ready to remodel your spreadsheet right into a dynamic search hub? Follow alongside as we discover the world of Google Apps Script and empower you to turn into a spreadsheet search grasp!

You can discover all of the code and associated belongings in this GitHub repo.

Table Of Contents

Understanding the Spreadsheet Structure

As portrayed within the picture above, there are 5 tabs within the spreadsheet. The knowledge is split into three tabs by yr: 2021, 2022 & 2023.

All the columns are the identical in these three tabs.

Let’s have a look at the construction of your devoted search tab. It’s divided into two key sections:

1. Search Form (Rows 1-7): This is the place you work together together with your knowledge. Each enter subject corresponds to a particular column in your different spreadsheet tabs, permitting you to tailor your search queries. Think of them as filters, serving to you hone in on the knowledge you want.
2. Search Results (Rows 8+): This is the place you may discover the information you sought. Each end result contains the related data you laid out in your search, together with an extra column named “Sprd Name – Row Index.” This acts as a handy map, pinpointing the precise spreadsheet tab and row the place every end result originates from. No extra searching by way of countless rows – you may be laser-focused on the information you want.

By understanding this organized structure, you possibly can navigate your search expertise effectively and retrieve the knowledge you require swiftly.

How to Build the Search Form – Dynamic Dropdowns and Logic

The search kind has three enter fields: Client, Quantity, and Description. Each makes use of a dropdown menu robotically populated with distinctive values from the Config spreadsheet tab. But how does this magic occur?

Here’s what occurs 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 intelligent formulation merges these values right into a single, consolidated record.
3. Splitting it Up: This mixed record is then remodeled into an array, permitting particular person values to be accessed.
4. Rearranging the Data: Transposition magic turns the row of values right into a column, making them simpler to work with.
5. Duplicate Removal: The UNIQUE perform eliminates any repeated values, guaranteeing a concise and arranged record.
6. Sorting it Out: Finally, the remaining values are sorted alphabetically in your looking comfort.

Here’s the formulation used: SORT(UNIQUE(TRANSPOSE(break up(TEXTJOIN(",",TRUE,'2021'!A2:A1001)&","&TEXTJOIN(",",TRUE,'2022'!A2:A1001)&","&TEXTJOIN(",",TRUE,'2023'!A2:A1001),","))))

AND vs OR Search

A devoted checkbox (positioned in G4:G5) serves because the management heart in your search logic. When checked, it prompts the AND search, requiring all specified standards to be current within the outcomes.

Leaving it unchecked switches to the OR search, offering extra versatile outcomes so long as any criterion matches.

Remember, the downloadable spreadsheet retains all of the pre-configured formulation and knowledge validation guidelines, making setup a breeze. We’ll dive into crafting the magical search perform within the subsequent step!

How to the Create Search Function With Google Apps Script

Open script editor from Extensions>Apps Script

For this challenge, you may create two information search.gs and utils.gs within the editor.

Inside the search.gs file, let’s first fetch our spreadsheet and enter phrases.

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 title, description and amount columns for every tab
var nameRangeNotation = 'A2:A'
var descriptionRangeNotation = 'F2:F'
var quantityRangeNotation = 'E2:E'
// worth for enter bins
var shopperName = searchSheet.getRange('B2:C2').getValue();
var amount = searchSheet.getRange('E2').getValue();
var description = searchSheet.getRange('G2:H2').getValue();
var hasIncludeAllChosen = searchSheet.getRange('G4:G5').getValue();

Now beneath this code, we’ll create the perform search, which can orchestrate all the pieces from the highest.

/**
 * The principal perform assigned to look button within the spreadsheet. It orchestrates search opearaion.
 */
perform search() {
  attempt {
     
    let standing;

    if (hasIncludeAllChosen) {
      //carry out AND search
      const newData = andSearch(shopperName, description, amount);
    
    }
    else {
         }

   

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

  }
}

In this challenge, we’ll construct our capabilities one step at a time. Let’s begin by figuring out the search sort based mostly on the checkbox in G4:G5.

If the field is checked, we’ll activate the AND search performance. This means all specified standards within the enter fields have to be current within the outcomes. To deal with this, we’ll name a devoted perform named andSearch().

We’ll create this perform subsequent, instantly beneath the prevailing search perform.

This strategy ensures our script adapts to the consumer’s chosen search sort, offering correct and related outcomes based mostly on their wants.

How to Create the andSearch() Function

/**
 * Performs "AND" seek for the given key phrases of their respective columns Last Name, Descroption and Quantity for 
 * tabs 2021, 2022, 2023. Returns new nested arrays for search outcomes to be crammed in search spreadsheet.
 * @param {String} title 
 * @param {String} description 
 * @param {String} amount 
 * @returns {Array<Array<String>>?} - [[],[],[]]
 */
perform andSearch(title = null, description = null, amount = null) {

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


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

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


 //.... proceed

}

This perform takes three parameters, every similar to a user-defined search time period: title, description and amount.

If any of those search phrases are clean, we merely assign an empty array because the end result. This effectively handles eventualities the place customers depart sure fields unfilled.

The core logic depends on the searchSheetByColumn perform. Think of it as an information detective that checks particular columns inside spreadsheet tabs. It takes three essential arguments:

• sheet: The particular tab to look inside (for instance: “2021”).
• varyNotation: The column vary to focus on (for instance: “A2:A”).
• searchVal: The worth to match throughout the chosen column (for instance: “Khadka”).

Using this data, searchSheetByColumn scans the designated column and returns an array containing the row indexes the place the searchVal is present in that sheet.

Searching Input Value In a Column

Let’s create the perform searchSheetByColumn within the utils.gs file now.

/**
 * Searches the given key phrase within the given column contained in the given spreadsheet tab.
 * It returns all of the matching indexes of information. Indexes are index from array not row.
 * @param {Spreadsheet} sheet - sheet to look from
 * @param {String} varyNotation - vary of the column within the given spreadsheet
 * @param {String} searchVal - key phrase to look
 * @returns {Array<quantity>} - [1,23,12,45,12] 
 */
perform searchSheetByColumn(sheet, varyNotation, searchVal) {
  const knowledge = sheet.getRange(varyNotation).getValues().flat().filter(String); // get knowledge
  if (knowledge.size < 1) return [];
  // filter solely matching rows indexes
  // acquired from https://stackoverflow.com/a/58980987/6163929
  const allIndexes = knowledge.map((val, index) => ({ val, index }))
    .filter(({ val, index }) => varyNotation === quantityRangeNotation ? Number(val) === Number(searchVal) : val.toLowerCase().contains(searchVal.toLowerCase())
    )
    .map(({ val, index }) =>
      index + 1
    ) // +1 as a result of we extract knowledge from second row in notation afterward need to match with entire knowledge array
  return allIndexes;
}

The code above does the next:

• Retrieves knowledge from the desired vary and sheet utilizing sheet.getRange(varyNotation).getValues().flat().
• Removes empty values by filtering with filter(String).
• Iterates by way of knowledge and indexes and applies map to create an array of objects with each values and their corresponding indexes.
• Converts each search time period and knowledge values to numbers utilizing Number().
• Filters for actual matches utilizing varyNotation === quantityRangeNotation ? Number(val) === Number(searchVal)
• Converts each search time period and knowledge values to lowercase.
• Filters for matches utilizing val.toLowerCase().contains(searchVal.toLowerCase())
• Extracts matching indexes utilizing map(({ val, index }) => index + 1).
• Adds 1 to right for beginning extraction from the second row.

How to Match All Criteria with AND Search

Add the next piece of code in andSearch perform.

perform andSearch(title = null, description = null, amount = null) {

// ..... persevering with on from earlier 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 aim is to unearth knowledge factors that tick each field you have specified. To obtain this, we have to filter solely rows that include all of your search phrases – title, amount, and outline – throughout all three spreadsheets.

Enter the filterRowsIndexesWithAllSearchTerms perform, to be created within the utils.gs file. This helpful software combs by way of every row and ensures it matches each criterion you have laid out. So, how does it work its magic? We’ll discover the code subsequent!

/**
 * Function filters solely rows that consist all three key phrases offered by consumer enter
 * @param {Array<String>} arr1 
 * @param {Array<String>} arr2 
 * @param {Array<String>} arr3 
 * @returns {Array<String>?} 
 */
perform filterRowsIndexesWithAllSearchTerms(arr1, arr2, arr3) {
  // create a nested array
  const arr = [arr1.length > 0 ? [...arr1] : "", arr2.size > 0 ? [...arr2] : "", arr3.size > 0 ? [...arr3] : ""].filter(String);

  // return empty if the grasp arrays size is lesser than variety of search phrases
  if (arr.size < 1 || arr.size < quantityOfInputAreaEntered) return [];

  const matchingIndexes = [];

  if (arr.size === 3) {

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

    });

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

    });


  }
  else {

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

}

Here’s what the code does:

The perform takes three arrays as enter, every representing matching indexes from one spreadsheet based mostly in your search phrases. However, we perceive customers may not fill in each search subject.

To deal with this, the perform first creates a “grasp array” containing solely non-empty arrays from the three inputs. Think of it as filtering out any clean search outcomes. const arr = [arr1.length > 0 ? [...arr1] : "", arr2.size > 0 ? [...arr2] : "", arr3.size > 0 ? [...arr3] : ""].filter(String);

If the grasp array finally ends up being empty, it means no rows matched any of your search phrases – the perform merely returns an empty array, indicating no outcomes discovered.

Similarly, if the grasp array has fewer components than the full search phrases you entered, it signifies an incomplete AND search. In this case, the perform returns an empty array, letting you already know that no outcomes match all standards. arr.size < quantityOfInputAreaEntered

But when all three arrays have matches, the perform begins its work, it iterates by way of the primary array, meticulously checking if every index worth exists in each the second and third arrays. If it does, that index is taken into account a match and added to a separate “matchingIndexes” array. This ensures that solely rows containing all of your search phrases are included: arr[0].forEach((val) => { if (arr[1].contains(val) && arr[2].contains(val)) { matchingIndexes.push(val)}

If solely two arrays have matches, the perform performs an easier test, verifying if every worth within the first array exists within the second. Any match is added to “matchingIndexes.” arr[0].forEach((val) => if (arr[1].contains(val)) { matchingIndexes.push(val)}.

Else if just one array is current, the perform merely makes use of that array instantly.

In abstract, the perform ensures that solely rows containing all of your chosen search phrases survive – a strong software for exact knowledge retrieval!

Next, in your search.gs file proper after you declared the variable hasIncludeAllChosen for the checkbox, create enter worth counter.

var quantityOfInputAreaEntered = [clientName, description, quantity].filter(String).size;

With this, we now have indexes for the rows from the AND search. Now, proceed together with your andSearch perform and get knowledge from these indexes.

Bringing Search Results to Life

perform andSearch(title = null, description = null, amount = null) {
//.... contnung from above
  // get knowledge from row indexes
  const _2021SheetMatchingRows = fetchDataByRowIndexes(_2021Sheet, _2021SheetMatchingRowsIndexes)
  const _2022SheetMatchingRows = fetchDataByRowIndexes(_2022Sheet, _2022SheetMatchingRowsIndexes)
  const _2023SheetMatchingRows = fetchDataByRowIndexes(_2023Sheet, _2023SheetMatchingRowsIndexes)
}

Now that we’ve the matching row indexes, it is time to retrieve the precise knowledge. Enter the fetchDataByRowIndexes perform, residing within the utils file. This helpful software serves as your knowledge retriever, fetching data based mostly on the offered indexes.

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

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

  const knowledge = sheet.getDataVary().getValues();
  const newData = [];

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

The retrieved knowledge is not simply dumped onto the search sheet – it will get a particular contact. The perform provides an additional worth for the column named Sprd Name - Row Indexes with `${sheet.getName()} - ${indexes[i] + 1}` .

This column acts like a roadmap, displaying each the originating spreadsheet title and the corresponding row index for every end result. So, at a look, you already know precisely the place each bit of information got here from.

Remember, this extra data is added because the final column within the search sheet. With this helpful context, search outcomes turn into much more informative and simpler to navigate.

How to Deduplicate Data to Ensure Accuracy

The subsequent step is to make sure that our search outcomes are freed from duplicates, irrespective of from the sheet they originated. After all, who needs to see the identical merchandise twice? So, append this code within the andSearch perform:

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

To create this perform let’s bounce again to the file utils.gs once more.

/**
 * Takes Duplicate knowledge that may have resulted from totally different particular person column searches and solely returns distinctive rows 
 * in every column from the serach outcomes.
 * @param {Array<String>} arr 
 * @returns {Array<String>}- [[],[]]
 */
perform filterDuplicateRows(arr) {
  if (arr.size < 1) return [];
  const delimiter = "*---*--*";
  // console.log("Inside filterDuplicateRows() arr to test:" + arr)

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

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

  return distinctiveArrays;

}

Here’s what we did:

• Creating a Unique Fingerprint: We started by crafting a particular “delimiter,” a mix of characters extremely unlikely to seem inside your precise knowledge. Think of it as a novel tag for every row.const delimiter = "*---*--*";
• Joining Forces: Next, we mapped by way of every row, becoming a member of its components with this delimiter to create a single string. This permits us to match strings for uniqueness as an alternative of particular person knowledge factors.const strArr = arr.map(row => row.be a part of(delimiter)).flat();
• Duplicate Detective: We leveraged the facility of JavaScript’s Set object, famend for its capability to carry solely distinctive values. By changing our string array right into a Set, we successfully take away any equivalent entries: [...new Set(strArr)]
• Back to Our Roots: Finally, we transformed the distinctive strings again into an array of arrays, splitting them utilizing the identical delimiter we used earlier. This offers us a clear, deduplicated set of outcomes. map(str => str.break up(delimiter))

Note: This uniqueness relies on the mixed “Sprd Name - Row Indexes” worth, guaranteeing true uniqueness throughout spreadsheets. Without this, duplicates can exist naturally.

With this ultimate step, we have achieved correct and streamlined search outcomes, able to be mixed and offered from the andSearch perform.

// inside andSearch perform append eventually

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

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

Find the finished andSearch perform beneath.

/**
 * Performs "AND" seek for the given key phrases of their respective columns Last Name, Descroption and Quantity for 
 * tabs 2021, 2022, 2023. Returns new nested arrays for search outcomes to be crammed in search spreadsheet.
 * @param {String} title 
 * @param {String} description 
 * @param {String} amount 
 * @returns {Array<Array<String>>?} - [[],[],[]]
 */
perform andSearch(title = null, description = null, amount = null) {

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


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

  const _2023SheetNameSearchIndexes = title === "" ? [] : searchSheetByColumn(_2023Sheet, nameRangeNotation, title);
  const _2023SheetQuantitySearchIndexes = amount === "" ? [] : searchSheetByColumn(_2023Sheet, quantityRangeNotation, amount);
  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 knowledge 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.size < 0) return;

  return andSearchResult;

}

Putting It All Together – Displaying Your Search Results

Now that we will retrieve search outcomes based mostly in your “AND” standards, it is time to combine them into your search perform.

We’ll proceed from the place we left off. In the if block, add the next code.

 if (hasIncludeAllChosen) {
      //carry out AND search
      const newData = andSearch(shopperName, description, amount);
      // ..........................
      // new peice of code 
       standing = fillSearchWithResults(searchSheet.getDataVary().getValues(), newData)
       // ................................................
}

Let’s create a brand new perform, fillSearchWithResults, residing within the utils.gs file:

/**
 * To Fill search sheet with values
 * @param {Array<Array<Srting>>}  outdatedData - earlier search outcomes knowledge
 * @param {Array<Array<Srting>>}  newData - new search end result to fill
 */
perform fillSearchWithResults(outdatedData, newData) {
  // console.log("Inside fillSearchWithResults() outdated knowledge:", outdatedData.size);
  if (outdatedData.size >= 8) {
    searchSheet.getRange(8, 1, outdatedData.size - 7, 9).clear(); // clear till final crammed knowledge
  }
  SpreadsheetApp.flush();
  Utilities.sleep(1000);
  // console.log("Inside fillSearchWithResults() new Data:", newData);
  if (newData.size < 1) return 400;
  searchSheet.getRange(8, 1, newData.size, 9).setValues(newData);
  return 200;
}

The perform takes two key inputs:

• Current Search Sheet Data: This represents the prevailing data displayed in your search sheet.
• New Search Results: This is the contemporary knowledge retrieved utilizing the beforehand defined capabilities.

Here’s what occurs step-by-step:

1. Clearing the Decks: If a earlier search end result exists (ranging from row 8), the perform clears it out to create space for the brand new findings.  if (outdatedData.size >= 8) { searchSheet.getRange(8, 1, outdatedData.size - 7, 9).clear(); }
2. Empty Results? No Problem: If the newly retrieved search outcomes are empty, the perform returns a particular code: 400. This code, which we’ll use later, signifies to the consumer that no matching knowledge was discovered. if (newData.size < 1) return 400
3. Data Display Time!: If there are certainly outcomes, the perform saves them into the search sheet, ranging from row 8. Additionally, it returns a unique code: 200. This code signifies a profitable operation, and we’ll use it to indicate success messages to the consumer.

With this ultimate piece in place, your “AND” searches will effortlessly deliver related knowledge to your fingertips, offered neatly in your search sheet.

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

Our journey continues! After establishing the “AND” search, we will now conquer the “OR” search, permitting you to search out knowledge containing any of your specified phrases.

In the search perform’s else block, we’ve the orSearch perform. Its function is to sift by way of your knowledge and determine rows containing at the least one in every of your search phrases.

Think of it as casting a wider web, capturing matches that meet any of your standards.

  else {
      //carry out OR serach
      let newData = orSearch(shopperName, description, amount);

      standing = fillSearchWithResults(searchSheet.getDataVary().getValues(), newData)
}

Create the perform orSearch perform beneath andSearch within the search file.

/**
 * Performs "OR" seek for the given key phrases of their respective columns Last Name, Descroption and Quantity for 
 * tabs 2021, 2022, 2023. Returns new nested arrays for search outcomes to be crammed in search spreadsheet.
 * @param {String} title 
 * @param {String} description 
 * @param {String} amount 
 * @returns {Array<Array<String>>?} - [[],[],[]]
 */
perform orSearch(title = null, description = null, amount = null) {
  // get matching index for every sheet.
  const _2021SheetNameSearchIndexes = title === "" ? [] : searchSheetByColumn(_2021Sheet, nameRangeNotation, title);
  const _2021SheetQuantitySearchIndexes = amount === "" ? [] : searchSheetByColumn(_2021Sheet, quantityRangeNotation, amount);
  const _2021SheetDescriptionSearchIndexes = description === "" ? [] : searchSheetByColumn(_2021Sheet, descriptionRangeNotation, description);

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

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

  // get values from these 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.size < 0) return;

  return orSearchResult;

}

Now, do not be shocked if some issues look acquainted! The total construction of the orSearch perform resembles its “AND” counterpart. However, a key distinction units them aside:

Since an “OR” search requires only one matching time period, we will eliminate the filterRowsIndexesWithAllSearchTerms perform. Recall that perform ensured all phrases had been current, which is not the case right here.

In essence, the orSearch perform works by iterating by way of every search time period and its corresponding matching indexes. For every time period, it retrieves the information from the spreadsheet utilizing the acquainted fetchDataByRowIndexes perform.

Finally, it merges the retrieved knowledge for all phrases, making a unified set of outcomes, even when they arrive from totally different spreadsheets.

With this highly effective software in your arsenal, you possibly can uncover knowledge factors that may not have surfaced with an “AND” search, increasing your search capabilities and enriching your knowledge exploration expertise.

Keeping Users Informed – Toast Messages for Seamless Search

Now that our search capabilities are full, let’s add a vital aspect: consumer suggestions! After all, maintaining customers knowledgeable all through the search course of results in a smoother expertise.

To keep away from confusion, substitute the search perform with this one:

/**
 * The principal perform assigned to look button within the spreadsheet. It orchestrates search opearaion.
 */
perform search() {
  attempt {
    SpreadsheetApp.getActiveSpreadsheet().toast("Searching Through Your Database...", 'Searching');
   
    let standing;

    if (hasIncludeAllChosen) {
      //carry out AND search
      const newData = andSearch(shopperName, description, amount);



      standing = fillSearchWithResults(searchSheet.getDataVary().getValues(), newData)
      // console.log(standing);
      if (standing === 400) { throw new Error(SEARCH_STATUS.SEARCH_FAILURE); }
    }
    else {
      //carry out OR serach
      let newData = orSearch(shopperName, description, amount);

      standing = fillSearchWithResults(searchSheet.getDataVary().getValues(), newData)
      // console.log(standing);

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

    if (standing === 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 technique offered by SpreadsheetApp to show transient, non-intrusive messages instantly throughout the spreadsheet interface. Here’s what we’ll obtain:

Search Initiated: As quickly because the consumer clicks the search button, a toast message seems: “Searching Through Your Database…” This lets them know the search is underway, stopping confusion or pointless ready. SpreadsheetApp.getActiveSpreadsheet().toast("Searching Through Your Database...", 'Searching');

Success Stories: If the search returns any end result (indicated by a standing code of 200), a optimistic toast message pops up: “Search Was Successful!” This confirms the operation’s completion and reassures the consumer that related knowledge was discovered.  if (standing === 200) { SpreadsheetApp.getActiveSpreadsheet().toast(SEARCH_STATUS.SEARCH_SUCCESFULL, 'Success'); }

Empty Findings: While not technically an error, an empty search end result (standing code of 400) triggers a barely totally different message: “No gadgets discovered with the given standards.” This informs the consumer in regards to the final result with out inflicting alarm. if (standing === 400) { throw new Error(SEARCH_STATUS.SEARCH_FAILURE); }

Here’s what occurs behind the scenes:

const SEARCH_STATUS = {
  SEARCH_SUCCESFULL: "Search Was Successfull!",
  SEARCH_FAILURE: "No gadgets discovered with the given standards.",
}

• An “enum” referred to as SEARCH_STATUS within the utils.gs file shops these message strings for simple entry and upkeep.
• A “catch block” handles any sudden errors, guaranteeing the consumer receives acceptable suggestions even in uncommon conditions.

With these toast messages in place, your search performance turns into extra user-friendly and clear. Remember, clear communication results in pleased consumer expertise!

Putting It All Together – Testing Your Search Form

Now that you’ve got constructed your highly effective search capabilities, it is time to see them in motion! Follow these steps to check your search kind instantly inside your spreadsheet:

1. Save Your Scripts: Make certain you have saved all of your code information (utils.gs and search.gs) earlier than continuing.
2. Assign the Search Function: Right-click on the Search button in your kind and choose “Assign script.” In the popup window, sort the title of the search perform and click on “OK.” This hyperlinks the button to your code.
3. Ready, Set, Search: In your spreadsheet, experiment with totally different search combos. Try getting into phrases in varied combos to see how the AND and OR searches yield totally different outcomes.

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

You’ve efficiently achieved a powerful feat: crafting a personalized search engine inside your Google Sheets. Let’s recap your achievements:

• Seamless Search Form: You created a user-friendly search kind instantly in your spreadsheet, simplifying knowledge exploration.
• Scriptable Power: You harnessed the facility of Apps Script to develop capabilities that deal with varied search operations behind the scenes.
• AND & OR Mastery: You carried out each AND and OR search functionalities, giving customers flexibility find related knowledge.
• Precise Matching: You designed a perform that selects rows containing all specified search phrases, guaranteeing correct outcomes.
• Duplicate Removal: You carried out a mechanism to remove duplicate entries, maintaining your search outcomes clear and concise.
• Informative Feedback: You built-in user-friendly toast messages to inform customers about search progress and outcomes.

Exploring Customization Options

You’ve constructed a improbable search engine, however keep in mind, the journey does not finish right here! With a little bit of tweaking, you possibly can adapt this software to completely fit your particular wants and workflows. Here are some thrilling prospects to contemplate:

Diversifying Your Data: Break free from the confines of a single spreadsheet! Explore integrating with numerous knowledge sources like stock administration methods, tax databases, and even restaurant critiques. With some changes to your code, you possibly can unlock a wealth of data throughout totally different platforms.

Dynamic Search Inputs: Need extra flexibility in your search standards? Consider including or eradicating enter fields based mostly in your evolving wants. This permits for extra tailor-made searches and streamlines your knowledge exploration course of.

Detailed Search Logs: Keep monitor of your search historical past! Implement a log field to robotically report your newest search phrases and the variety of outcomes discovered. This may be invaluable for revisiting previous searches and analyzing developments.

Visual Appeal Matters: Enhance the consumer expertise by giving your search kind a visible makeover. Play with colours, fonts, and structure to create a extra partaking and intuitive interface.

Speed Optimizations: Every second counts! Explore methods to optimize your search capabilities for sooner response instances. This would possibly contain code refinement, knowledge indexing, or leveraging caching methods.

Taming Large Datasets: Working with huge databases? Don’t fear, you have acquired choices! Implement logic to beat the 6-minute runtime restrict of Google Apps Script capabilities.

By exploring these avenues, you possibly can rework your primary search perform into a strong and customized knowledge exploration software. Remember, the chances are countless!

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

I’m Nibesh Khadka. Share this weblog & just like the video if useful! Find extra of my contents at Script-Portal (Medium) & on my YouTube channel: CodingWithNibesh.