Скачать или смотреть How to Check if a Substring Exists in a String in Google Sheets with JavaScript

Learn how to effectively check for substrings in your Google Sheets data using JavaScript and the correct method to display row numbers when a match is found.
---
This video is based on the question https://stackoverflow.com/q/68612681/ asked by the user 'SuggestMeUsernames' ( https://stackoverflow.com/u/15994269/ ) and on the answer https://stackoverflow.com/a/68613045/ provided by the user 'Cooper' ( https://stackoverflow.com/u/7215091/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Issue if string includes substring

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Check if a Substring Exists in a String in Google Sheets with JavaScript

If you're working with Google Sheets and want to check whether a specific substring is present in the data you've pulled from your sheet, you might encounter a few hurdles. This is especially true when you’re trying to dynamically check user input against the data in your spreadsheet. In this post, we will explore a specific issue encountered by users and demonstrate the correct method to solve it using JavaScript.

The Problem

Suppose you have a spreadsheet where you want to check if a specific substring, for instance, "I14", is included in the string values located in the 12th column (Column L) of your Google Sheets. As a user enters this substring through an HTML form, you want your script to alert the user with the row number where this substring was found. However, users face issues where the code sometimes erroneously displays rows that do not contain the substring, while others that do are overlooked, causing confusion.

Example Scenario

Imagine a user has written this JavaScript code:

[[See Video to Reveal this Text or Code Snippet]]

Despite seemingly correct logic, the includes() function does not always yield the expected results. So, what’s going wrong here?

The Solution

The main issue in this scenario is that the code is calling getValues() multiple times within the loop. This will lead to inconsistent results and unnecessary resource usage. Instead, the solution is straightforward: call getValues() once and store the result in a variable.

Step-by-Step Fix

Store Values Once: Instead of calling getValues() each time within the loop, store the values in a constant variable at the start of the function.

Check for the Substring: Use the includes() method on the retrieved value to check if the substring exists.

Alert the User: Display the correct row number.

Updated Code

Here’s the revised version of the code that efficiently implements these changes:

[[See Video to Reveal this Text or Code Snippet]]

Explanation of the Code

const values = sheetDataRange.getValues();: This line fetches all the data from your specified range in the sheet and stores it in memory.

for (var i = 0; i < values.length; i+ + ) {: The loop iterates through each row of the sheet data.

if (values[i][11].toString().includes("I14")) {: Here, we ensure that we convert the value to a string to avoid any type mismatches and check if it contains our substring.

ui.alert(i + 5);: This alerts the user with the correct row number, accounting for the fact that our data starts at the 5th row.

Conclusion

By implementing this clean and efficient approach, you will bypass the issues previously encountered with your Google Sheets JavaScript code. This method not only improves performance but also ensures that your substring checks yield reliable and accurate results. Now you can effectively manage strings in your spreadsheets and respond to user input dynamically!

Feel free to reach out if you have any further questions or need additional assistance with your Google Sheets projects.