آیا تا به حال با پیمایش یک صفحه گسترده بزرگ مشکل داشته اید؟ می‌توانید با استفاده از Google Apps Script، از پیمایش بی‌پایان کنار بگذارید و قدرت بازیابی داده‌های هدفمند را با یک فرم جستجوی سفارشی باز کنید.

در این راهنمای عملی، ما یک ابزار جستجو ایجاد می کنیم که به طور یکپارچه با صفحه گسترده شما ادغام می شود و به شما امکان می دهد:

جستجو در چندین برگه: به راحتی داده ها را از بخش های مختلف صفحه گسترده خود با فیلدهای ورودی اختصاصی پرس و جو کنید.

جستجوهای Master AND & OR: دقیقاً آنچه را که نیاز دارید با عملکردهای جستجوی AND و OR پیدا کنید و از تطابق دقیق یا انعطاف پذیر بر اساس نیازهای خود اطمینان حاصل کنید.

بهره وری خود را افزایش دهید: با حذف جستجوهای دستی و فیلتر کردن تنها نقاط داده مربوطه، در زمان ارزشمند خود صرفه جویی کنید.

برای تبدیل صفحه گسترده خود به یک مرکز جستجوی پویا آماده هستید؟ با کاوش در دنیای Google Apps Script همراه باشید و به شما قدرت دهیم که به یک استاد جستجوی صفحه گسترده تبدیل شوید!

شما می توانید تمام کدها و دارایی های مرتبط را در این مخزن GitHub پیدا کنید.

فهرست مطالب

درک ساختار صفحه گسترده

نحوه ساخت فرم جستجو – کشویی پویا و منطق

و در مقابل یا جستجو

نحوه ایجاد عملکرد جستجو با اسکریپت Google Apps

نحوه ایجاد تابع andsearch

نحوه تطبیق همه معیارها با و جستجو

زنده کردن نتایج جستجو

چگونه می توان داده ها را کپی کرد تا از صحت اطمینان حاصل شود

قرار دادن همه چیز در کنار هم - نمایش نتایج جستجوی شما

نحوه استفاده یا جستجو برای یافتن داده هایی که با هر اصطلاحی مطابقت دارند

آگاه نگه داشتن کاربران - پیام های تست برای جستجوی بدون درز

قرار دادن همه چیز در کنار هم - آزمایش فرم جستجوی شما

تبریک می گویم! شما یک موتور جستجوی قدرتمند در Google Sheets ساخته اید!

کاوش در گزینه های سفارشی سازی

درک ساختار صفحه گسترده

همانطور که در تصویر بالا نشان داده شده است، پنج برگه در صفحه گسترده وجود دارد. داده ها بر اساس سال به سه برگه تقسیم می شوند: 2021، 2022 و 2023.

تمام ستون ها در این سه تب یکسان هستند.

بیایید به ساختار برگه جستجوی اختصاصی شما نگاه کنیم. به دو بخش کلیدی تقسیم می شود:

فرم جستجو (ردیف های 1-7): اینجا جایی است که شما با داده های خود تعامل دارید. هر فیلد ورودی مربوط به یک ستون خاص در سایر برگه‌های صفحه‌گسترده شما است، که به شما امکان می‌دهد پرس و جوهای جستجوی خود را تنظیم کنید. آنها را به عنوان فیلترهایی در نظر بگیرید که به شما کمک می کنند اطلاعات مورد نیاز خود را افزایش دهید.

نتایج جستجو (ردیف 8+): این جایی است که داده های مورد نظر خود را پیدا خواهید کرد. هر نتیجه شامل اطلاعات مربوطه ای است که در جستجوی خود مشخص کرده اید، به همراه یک ستون اضافی به نام «نام Sprd - فهرست ردیف». این به عنوان یک نقشه مناسب عمل می کند و برگه و ردیف دقیق صفحه گسترده را مشخص می کند که هر نتیجه از آن منشاء می گیرد. دیگر نیازی به شکار در ردیف‌های بی‌پایان نیست - روی داده‌هایی که نیاز دارید تمرکز لیزری خواهید داشت.

با درک این طرح‌بندی سازمان‌یافته، می‌توانید تجربه جستجوی خود را به‌طور مؤثر مرور کنید و اطلاعات مورد نیاز خود را به سرعت بازیابی کنید.

نحوه ساخت فرم جستجو – کشویی پویا و منطق

فرم جستجو دارای سه فیلد ورودی است: Client، Quantity و Description. هر کدام از یک منوی کرکره‌ای استفاده می‌کنند که به طور خودکار با مقادیر منحصربه‌فرد از برگه صفحه‌گسترده پیکربندی پر می‌شود. اما این جادو چگونه اتفاق می افتد؟

آنچه در پشت صحنه اتفاق می افتد را در اینجا مشاهده می کنید:

منبع داده: مقادیر برای فهرست های بازشو به دقت از سه برگه جداگانه جمع آوری شده است: 2021، 2022، و 2023.

ترکیب نیروها: یک فرمول هوشمندانه این مقادیر را در یک فهرست واحد و یکپارچه ادغام می کند.

تقسیم آن: این فهرست ترکیبی سپس به یک آرایه تبدیل می شود و امکان دسترسی به مقادیر فردی را فراهم می کند.

مرتب سازی مجدد داده ها: جادوی جابجایی، ردیف مقادیر را به یک ستون تبدیل می کند و کار با آنها را آسان تر می کند.

حذف تکراری: تابع UNIQUE هر مقدار تکراری را حذف می‌کند و فهرستی مختصر و سازمان‌یافته را تضمین می‌کند.

مرتب سازی آن: در نهایت، مقادیر باقی مانده بر اساس حروف الفبا برای راحتی مرور شما مرتب می شوند.

SORT(UNIQUE(TRANSPOSE(split(TEXTJOIN(",",TRUE,'2021'!A2:A1001)&","&TEXTJOIN(",",TRUE,'2022'!A2:A1001)&","&TEXTJOIN(",",TRUE,'2023'!A2:A1001),","))))

AND در مقابل OR Search

یک چک باکس اختصاصی (واقع در G4:G5) به عنوان مرکز کنترل منطق جستجوی شما عمل می کند. هنگامی که علامت زده می شود، جستجوی AND را فعال می کند و نیاز به وجود تمام معیارهای مشخص شده در نتایج دارد.

بدون علامت گذاشتن آن به جستجوی OR تغییر می کند و تا زمانی که هر معیاری مطابقت داشته باشد، نتایج انعطاف پذیرتری ارائه می دهد.

به یاد داشته باشید، صفحه‌گسترده قابل دانلود، تمام فرمول‌های از پیش پیکربندی شده و قوانین اعتبارسنجی داده‌ها را حفظ می‌کند و راه‌اندازی را آسان می‌کند. ما در مرحله بعدی به ساخت تابع جستجوی جادویی خواهیم پرداخت!

نحوه ایجاد عملکرد جستجو با اسکریپت Google Apps

ویرایشگر اسکریپت را از Extensions>Apps Script باز کنید

برای این پروژه، دو فایل search.gs و utils.gs را در ویرایشگر ایجاد خواهید کرد.

در داخل فایل search.gs ، اجازه دهید ابتدا صفحه گسترده و عبارات ورودی خود را واکشی کنیم.

 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();

اکنون در زیر این کد، search تابع را ایجاد می کنیم که همه چیز را از بالا هماهنگ می کند.

 /** * 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) } }

در این پروژه، ما توابع خود را یک مرحله در یک زمان ایجاد خواهیم کرد. بیایید با تعیین نوع جستجو بر اساس چک باکس در G4:G5 شروع کنیم.

اگر کادر علامت زده شود، عملکرد AND جستجو را فعال می کنیم. این بدان معناست که تمام معیارهای مشخص شده در فیلدهای ورودی باید در نتایج موجود باشد. برای انجام این کار، یک تابع اختصاصی به نام andSearch() را فراخوانی می کنیم.

ما این تابع را در ادامه، مستقیماً زیر تابع search موجود ایجاد خواهیم کرد.

این رویکرد تضمین می کند که اسکریپت ما با نوع جستجوی انتخابی کاربر سازگار است و نتایج دقیق و مرتبطی را بر اساس نیازهای آنها ارائه می دهد.

نحوه ایجاد تابع andSearch()

 /** * 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 }

این تابع سه پارامتر دارد که هر کدام مربوط به یک عبارت جستجو شده توسط کاربر است: نام، توضیحات و کمیت.

اگر هر یک از این عبارات جستجو خالی باشد، به سادگی یک آرایه خالی را به عنوان نتیجه اختصاص می دهیم. این به طور موثر سناریوهایی را کنترل می کند که در آن کاربران فیلدهای خاصی را خالی می گذارند.

منطق اصلی به تابع searchSheetByColumn متکی است. آن را به عنوان یک کارآگاه داده در نظر بگیرید که ستون های خاصی را در برگه های صفحه گسترده تحلیل می کند. به سه استدلال مهم نیاز دارد:

sheet : برگه خاص برای جستجو (به عنوان مثال: "2021").

rangeNotation : محدوده ستون مورد نظر (به عنوان مثال: "A2:A").

searchVal : مقداری که باید در ستون انتخابی مطابقت داشته باشد (به عنوان مثال: "Khadka").

با استفاده از این اطلاعات، searchSheetByColumn ستون تعیین‌شده را اسکن می‌کند و آرایه‌ای حاوی فهرست‌های ردیفی که searchVal در آن صفحه یافت می‌شود، برمی‌گرداند.

جستجوی مقدار ورودی در یک ستون

بیایید اکنون تابع searchSheetByColumn را در فایل utils.gs ایجاد کنیم.

 /** * 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; }

کد بالا کارهای زیر را انجام می دهد:

داده ها را از محدوده و صفحه مشخص شده با استفاده از sheet.getRange(rangeNotation).getValues().flat() بازیابی می کند.

مقادیر خالی را با فیلتر کردن با filter(String) حذف می کند.

از طریق داده ها و شاخص ها تکرار می شود و map برای ایجاد آرایه ای از اشیاء با هر دو مقدار و شاخص های مربوط به آنها اعمال می کند.

با استفاده از Number() هم مقادیر عبارت جستجو و هم مقادیر داده را به اعداد تبدیل می کند.

فیلترهایی برای مطابقت دقیق با استفاده از rangeNotation === quantityRangeNotation ? Number(val) === Number(searchVal)

هر دو عبارت جستجو و مقادیر داده را به حروف کوچک تبدیل می کند.

فیلترهای منطبق با استفاده از val.toLowerCase().includes(searchVal.toLowerCase())

نمایه های منطبق را با استفاده از map(({ val, index }) => index + 1) استخراج می کند.

1 را برای تصحیح شروع استخراج از ردیف دوم اضافه می کند.

چگونه همه معیارها را با جستجو و جستجو مطابقت دهیم

کد زیر را در تابع andSearch اضافه کنید.

 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); //.......... }

جستجو و جستجو را به خاطر دارید؟ هدف آن کشف نقاط داده ای است که هر کادری را که مشخص کرده اید علامت بزنید. برای رسیدن به این هدف، ما باید فقط ردیف‌هایی را فیلتر کنیم که شامل تمام عبارات جستجوی شما - نام، کمیت و توضیحات - در هر سه صفحه‌گسترده هستند.

تابع filterRowsIndexesWithAllSearchTerms را وارد کنید تا در فایل utils.gs ایجاد شود. این ابزار مفید، هر ردیف را رد می کند و تضمین می کند که با هر معیاری که شما تعیین کرده اید مطابقت دارد. پس ، چگونه جادوی خود را کار می کند؟ در ادامه کد را تحلیل خواهیم کرد!

 /** * 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(); }

این چیزی است که کد انجام می دهد:

این تابع سه آرایه را به عنوان ورودی می گیرد که هر کدام نمایه های منطبق بر یک صفحه گسترده را بر اساس عبارات جستجوی شما نشان می دهد. با این حال، ما می دانیم که کاربران ممکن است هر قسمت جستجو را پر نکنند.

برای انجام این کار، تابع ابتدا یک "آرایه اصلی" ایجاد می کند که فقط شامل آرایه های غیر خالی از سه ورودی است. به آن به عنوان فیلتر کردن نتایج جستجوی خالی فکر کنید. const arr = [arr1.length > 0 ? [...arr1] : "", arr2.length > 0 ? [...arr2] : "", arr3.length > 0 ? [...arr3] : ""].filter(String);

اگر آرایه اصلی خالی باشد، به این معنی است که هیچ ردیفی با هیچ یک از عبارات جستجوی شما مطابقت ندارد - این تابع به سادگی یک آرایه خالی را برمی‌گرداند، که نشان می‌دهد هیچ نتیجه‌ای پیدا نشد.

به طور مشابه، اگر آرایه اصلی دارای عناصر کمتری نسبت به کل عبارات جستجویی باشد که وارد کرده‌اید، به معنای جستجوی ناقص و ناقص است. در این حالت، تابع یک آرایه خالی برمی گرداند و به شما اطلاع می دهد که هیچ نتیجه ای با همه معیارها مطابقت ندارد. arr.length < numberOfInputFieldEntered

اما وقتی هر سه آرایه مطابقت داشته باشند، تابع کار خود را شروع می کند، از طریق آرایه اول تکرار می شود، و به دقت تحلیل می کند که آیا هر مقدار شاخص در هر دو آرایه دوم و سوم وجود دارد یا خیر. اگر اینطور باشد، آن شاخص یک تطابق در نظر گرفته می شود و به آرایه جداگانه "matchingIndexes" اضافه می شود. این تضمین می‌کند که فقط ردیف‌هایی که حاوی همه عبارت‌های جستجوی شما هستند گنجانده شوند arr[0].forEach((val) => { if (arr[1].includes(val) && arr[2].includes(val)) { matchingIndexes.push(val)}

اگر فقط دو آرایه مطابقت داشته باشند، تابع تحلیل ساده‌تری را انجام می‌دهد و تحلیل می‌کند که آیا هر مقدار در آرایه اول در آرایه دوم وجود دارد یا خیر. هر تطابقی به "matchingIndexes" اضافه می شود. arr[0].forEach((val) => if (arr[1].includes(val)) { matchingIndexes.push(val)} .

در غیر این صورت، اگر فقط یک آرایه وجود داشته باشد، تابع به سادگی از آن آرایه استفاده می کند.

به طور خلاصه، این تابع تضمین می‌کند که فقط ردیف‌هایی که شامل تمام عبارات جستجوی انتخابی شما هستند زنده می‌مانند - ابزاری قدرتمند برای بازیابی دقیق داده‌ها!

سپس، در فایل search.gs خود درست بعد از اینکه متغیر hasIncludeAllSelected برای چک باکس اعلام کردید، شمارنده مقدار ورودی را ایجاد کنید.

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

با این کار، اکنون فهرست هایی برای ردیف های جستجوی AND داریم. اکنون به تابع andSearch خود ادامه دهید و از آن شاخص ها داده ها را دریافت کنید.

زنده کردن نتایج جستجو

 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) }

اکنون که شاخص های ردیف منطبق را داریم، زمان بازیابی داده های واقعی فرا رسیده است. تابع fetchDataByRowIndexes را که در فایل utils قرار دارد وارد کنید. این ابزار مفید به عنوان بازیابی اطلاعات شما عمل می کند و اطلاعات را بر اساس نمایه های ارائه شده واکشی می کند.

 /** * 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; }

داده های بازیابی شده فقط در برگه جستجو ریخته نمی شوند - آن را لمس خاصی می شود. این تابع یک مقدار اضافی برای ستونی به نام Sprd Name - Row Indexes اضافه می کند با `${sheet.getName()} - ${indexes[i] + 1}` .

این ستون مانند یک نقشه راه عمل می کند و نام صفحه گسترده اولیه و شاخص ردیف مربوطه را برای هر نتیجه نمایش می دهد. پس ، در یک نگاه، دقیقاً می دانید که هر قطعه داده از کجا آمده است.

به یاد داشته باشید، این اطلاعات اضافی به عنوان آخرین ستون در صفحه جستجو اضافه می شود. با این زمینه ارزشمند، نتایج جستجو حتی آموزنده تر می شوند و پیمایش آنها آسان تر می شود.

چگونه داده ها را کپی کنیم تا از صحت اطمینان حاصل شود

گام بعدی این است که اطمینان حاصل کنیم که نتایج جستجوی ما، بدون توجه به برگه‌ای که منشا گرفته‌اند، فاقد موارد تکراری هستند. بالاخره چه کسی می خواهد یک مورد را دو بار ببیند؟ پس ، این کد را در تابع andSearch اضافه کنید:

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

برای ایجاد این تابع، اجازه دهید دوباره به فایل utils.gs برگردیم.

 /** * 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; }

این چیزی است که ما انجام دادیم:

ایجاد یک اثر انگشت منحصر به فرد: ما با ساختن یک «محدود کننده» ویژه شروع کردیم، ترکیبی از کاراکترهایی که بسیار بعید است در داده های واقعی شما ظاهر شوند. آن را به عنوان یک برچسب منحصر به فرد برای هر ردیف در نظر بگیرید. const delimiter = "*---*--*";

نیروهای پیوستن: در مرحله بعد، هر ردیف را نگاشت کردیم و عناصر آن را با این جداکننده به هم پیوستیم تا یک رشته ایجاد کنیم. این به ما اجازه می دهد تا رشته ها را برای منحصر به فرد بودن به جای نقاط داده جداگانه مقایسه کنیم. const strArr = arr.map(row => row.join(delimiter)).flat();

Duplicate Detective: ما از قدرت شیء Set جاوا اسکریپت استفاده کردیم که به دلیل توانایی آن در نگهداری فقط مقادیر منحصر به فرد مشهور است. با تبدیل آرایه رشته ای خود به یک مجموعه، ما به طور موثر هر ورودی یکسان را حذف می کنیم: [...new Set(strArr)]

بازگشت به ریشه‌های ما: در نهایت، رشته‌های منحصربه‌فرد را دوباره به آرایه‌ای از آرایه‌ها تبدیل کردیم و آنها را با استفاده از همان جداکننده‌ای که قبلاً استفاده کردیم، تقسیم کردیم. این به ما مجموعه ای تمیز و بدون تکرار از نتایج را می دهد. map(str => str.split(delimiter))

توجه: این منحصربه‌فرد بودن بر اساس مقدار ترکیبی " Sprd Name - Row Indexes " است که از یکتایی واقعی در صفحات گسترده اطمینان می‌دهد. بدون این، موارد تکراری می توانند به طور طبیعی وجود داشته باشند.

با این مرحله نهایی، به نتایج جستجوی دقیق و ساده‌ای دست یافته‌ایم که آماده ترکیب و ارائه از تابع andSearch هستند.

 // inside andSearch function append at last const andSearchResult = [..._2023SheetMatchingUniqueRows, ..._2022SheetMatchingUniqueRows, ..._2021SheetMatchingUniqueRows] if (andSearchResult.length < 0) return; return andSearchResult; }

تابع کامل andSearch را در زیر پیدا کنید.

 /** * 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; }

قرار دادن همه چیز در کنار هم - نمایش نتایج جستجوی شما

اکنون که می‌توانیم نتایج جستجو را بر اساس معیارهای "AND" شما بازیابی کنیم، وقت آن است که آنها را در عملکرد search خود ادغام کنیم.

از جایی که متوقف کردیم ادامه می دهیم. در بلوک if کد زیر را اضافه کنید.

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

بیایید یک تابع جدید ایجاد کنیم، fillSearchWithResults ، که در فایل utils.gs قرار دارد:

 /** * 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; }

تابع دو ورودی کلیدی می گیرد:

داده‌های برگه جستجوی فعلی: اطلاعات موجود در صفحه جستجوی شما را نشان می‌دهد.

نتایج جستجوی جدید: این اطلاعات تازه ای است که با استفاده از توابع توضیح داده شده قبلی بازیابی شده است.

در اینجا چیزی است که گام به گام اتفاق می افتد:

پاک کردن عرشه ها: اگر یک نتیجه جستجوی قبلی وجود داشته باشد (از ردیف 8 شروع می شود)، تابع آن را پاک می کند تا فضایی برای یافته های جدید ایجاد کند. if (oldData.length >= 8) { searchSheet.getRange(8, 1, oldData.length - 7, 9).clear(); }

نتایج خالی؟ مشکلی وجود ندارد: اگر نتایج جستجوی تازه بازیابی شده خالی باشند، تابع یک کد ویژه را برمی‌گرداند: 400. این کد که بعداً از آن استفاده خواهیم کرد، به کاربر نشان می‌دهد که هیچ داده منطبقی پیدا نشده است. if (newData.length < 1) return 400

زمان نمایش داده ها!: اگر واقعاً نتایجی وجود داشته باشد، تابع آنها را در صفحه جستجو ذخیره می کند که از ردیف 8 شروع می شود. علاوه بر این، کد دیگری را برمی گرداند: 200. این کد نشان دهنده یک عملیات موفقیت آمیز است و ما از آن برای نشان دادن استفاده خواهیم کرد. پیام های موفقیت به کاربر

با قرار دادن این قطعه نهایی، جستجوهای «AND» شما بدون زحمت داده‌های مرتبط را به نوک انگشتان شما می‌آورد، که به‌خوبی در برگه جستجوی شما ارائه می‌شوند.

نحوه استفاده از OR Search برای یافتن داده هایی که با هر اصطلاحی مطابقت دارند

سفر ما ادامه دارد! پس از راه‌اندازی جستجوی «AND»، اکنون می‌توانیم جستجوی «OR» را غلبه کنیم و به شما این امکان را می‌دهد که داده‌های حاوی هر یک از عبارات مشخص‌شده خود را پیدا کنید.

در بلوک else تابع search ، تابع orSearch را داریم. هدف آن این است که داده های شما را غربال کند و ردیف هایی را که حداقل یکی از عبارات جستجوی شما را شامل می شود شناسایی کند.

به این فکر کنید که شبکه گسترده‌تری را ایجاد می‌کنید و مسابقاتی را به دست می‌آورید که هر یک از معیارهای شما را برآورده می‌کنند.

 else { //perform OR serach let newData = orSearch(clientName, description, quantity); status = fillSearchWithResults(searchSheet.getDataRange().getValues(), newData) }

تابع orSearch تابع جستجو را در زیر ایجاد کنید andSearch در فایل جستجو جستجو کنید.

 /** * 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; }

حالا اگر بعضی چیزها آشنا به نظر می رسند تعجب نکنید! ساختار کلی تابع orSearch شبیه همتای "AND" آن است. با این حال، یک تفاوت کلیدی آنها را متمایز می کند:

از آنجایی که جستجوی "OR" فقط به یک عبارت منطبق نیاز دارد، می توانیم از تابع filterRowsIndexesWithAllSearchTerms خلاص شویم. به یاد بیاورید که این تابع تضمین می کرد که همه اصطلاحات وجود داشته باشند، که در اینجا صدق نمی کند.

در اصل، تابع orSearch با تکرار در میان هر عبارت جستجو و نمایه های منطبق با آن کار می کند. برای هر عبارت، داده ها را از صفحه گسترده با استفاده از تابع آشنا fetchDataByRowIndexes بازیابی می کند.

در نهایت، داده‌های بازیابی شده را برای همه اصطلاحات ادغام می‌کند و مجموعه یکپارچه از نتایج را ایجاد می‌کند، حتی اگر از صفحات گسترده مختلف آمده باشند.

با استفاده از این ابزار قدرتمند در زرادخانه خود، می توانید نقاط داده ای را که ممکن است با جستجوی "AND" ظاهر نشده اند، کشف کنید، قابلیت های جستجوی خود را گسترش داده و تجربه کاوش داده های خود را غنی می کند.

آگاه نگه داشتن کاربران - پیام های نان تست برای جستجوی یکپارچه

اکنون که عملکردهای جستجوی ما کامل است ، بیایید یک عنصر مهم اضافه کنیم: بازخورد کاربر! از این گذشته ، اطلاع رسانی کاربران در طول فرآیند جستجو منجر به تجربه ای نرم تر می شود.

برای جلوگیری از سردرگمی ، عملکرد جستجو را با این یکی جایگزین کنید:

 /** * 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!'); } } }

ما از روش نان تست ارائه شده توسط صفحه گسترده برای نمایش پیام های مختصر و غیر تهاجمی مستقیماً در رابط صفحه گسترده استفاده خواهیم کرد. این چیزی است که ما به آن خواهیم رسید:

جستجو آغاز شد: به محض اینکه کاربر روی دکمه جستجو کلیک کرد ، یک پیام نان تست ظاهر می شود: "جستجو از طریق پایگاه داده خود ..." این به آنها اجازه می دهد تا بدانند که جستجو در حال انجام است ، از سردرگمی یا انتظار غیر ضروری جلوگیری می کند. SpreadsheetApp.getActiveSpreadsheet().toast("Searching Through Your Database...", 'Searching');

داستانهای موفقیت: اگر جستجو نتیجه ای را برگرداند (که توسط کد وضعیت 200 نشان داده شده است) ، یک پیام نان تست مثبت ظاهر می شود: "جستجو موفقیت آمیز بود!" این کار تکمیل عملیات را تأیید می کند و کاربر را اطمینان می دهد که داده های مربوطه پیدا شده است. if (status === 200) { SpreadsheetApp.getActiveSpreadsheet().toast(SEARCH_STATUS.SEARCH_SUCCESFULL, 'Success'); }

یافته های خالی: در حالی که از نظر فنی خطایی نیست ، یک نتیجه جستجوی خالی (کد وضعیت 400) پیام کمی متفاوت را ایجاد می کند: "هیچ موردی با معیارهای داده شده یافت نمی شود." این امر کاربر را در مورد نتیجه بدون ایجاد زنگ خطر به کاربر اطلاع می دهد. if (status === 400) { throw new Error(SEARCH_STATUS.SEARCH_FAILURE); }

در اینجا آنچه در پشت صحنه اتفاق می افتد آمده است:

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

"enum" به نام SEARCH_STATUS در پرونده upils.gs این رشته های پیام را برای دسترسی و نگهداری آسان ذخیره می کند.

"Catch Block" هرگونه خطای غیر منتظره را برطرف می کند ، و اطمینان می دهد که کاربر حتی در شرایط غیرمعمول بازخورد مناسبی را دریافت می کند.

با وجود این پیام های نان تست ، عملکرد جستجوی شما کاربر پسند تر و شفاف تر می شود. به یاد داشته باشید ، ارتباطات شفاف منجر به تجربه کاربری شاد می شود!

همه اینها را کنار هم قرار دهید - فرم جستجوی خود را آزمایش کنید

اکنون که عملکردهای جستجوی قدرتمند خود را ساخته اید ، وقت آن است که آنها را در عمل ببینید! این مراحل را دنبال کنید تا فرم جستجوی خود را مستقیماً در صفحه گسترده خود آزمایش کنید:

اسکریپت های خود را ذخیره کنید: قبل از ادامه کار حتماً تمام پرونده های کد خود ( upils.gs و search.gs ) را ذخیره کرده اید.

عملکرد جستجو را اختصاص دهید: بر روی دکمه جستجو در فرم خود کلیک راست کرده و "Assign Script" را انتخاب کنید. در پنجره پنجره ، نام عملکرد search را تایپ کرده و روی "OK" کلیک کنید. این دکمه را به کد شما پیوند می دهد.

آماده ، تنظیم ، جستجو: در صفحه گسترده خود ، با ترکیب های مختلف جستجو آزمایش کنید. سعی کنید شرایط را در ترکیب های مختلف وارد کنید تا ببینید که چگونه و یا جستجو ها نتایج متفاوتی دارند.

تبریک می گویم! شما یک موتور جستجوگر قدرتمند را در برگه های Google ایجاد کرده اید!

شما با موفقیت یک شاهکار چشمگیر را انجام داده اید: ساختن یک موتور جستجوی سفارشی در صفحات Google خود. بیایید دستاوردهای شما را یادآوری کنیم:

فرم جستجوی یکپارچه: شما یک فرم جستجوی کاربر پسند را مستقیماً در صفحه گسترده خود ایجاد کرده اید و اکتشاف داده ها را ساده می کنید.

Power Scriptable: شما از قدرت اسکریپت برنامه ها استفاده کرده اید تا کارکردهایی را انجام دهید که عملکردهای مختلف جستجوی را در پشت صحنه انجام می دهند.

و و یا تسلط: شما هم ویژگی های و یا جستجو را اجرا کرده اید و به کاربران انعطاف پذیری در یافتن داده های مربوطه می دهید.

تطبیق دقیق: شما تابعی را طراحی کرده اید که ردیف های حاوی تمام اصطلاحات جستجو مشخص شده را انتخاب می کند و از نتایج دقیق اطمینان می دهد.

حذف تکراری: شما مکانیسمی را برای از بین بردن ورودی های تکراری ، تمیز نگه داشتن نتایج جستجوی خود انجام داده اید.

بازخورد آموزنده: شما پیام های نان تست کاربر پسند را برای اطلاع رسانی به کاربران در مورد پیشرفت و نتایج جستجو.

تحلیل گزینه های سفارشی سازی

شما یک موتور جستجوی خارق العاده ساخته اید ، اما به یاد داشته باشید ، سفر به اینجا پایان نمی یابد! با کمی ترفند ، می توانید این ابزار را کاملاً متناسب با نیازها و گردش کار خود تطبیق دهید. در اینجا برخی از امکانات هیجان انگیز برای در نظر گرفتن:

متنوع کردن داده های خود: از محدوده یک صفحه گسترده جدا شوید! ادغام با منابع داده های متنوع مانند سیستم های مدیریت موجودی ، بانکهای اطلاعاتی مالیاتی یا حتی تحلیل رستوران ها را کاوش کنید. با برخی تنظیمات در کد خود ، می توانید اطلاعات زیادی را در سیستم عامل های مختلف باز کنید.

ورودی های جستجوی پویا: در معیارهای جستجوی خود به انعطاف پذیری بیشتری نیاز دارید؟ بر اساس نیازهای در حال تحول خود ، قسمتهای ورودی را اضافه یا حذف کنید. این امکان را برای جستجوهای متناسب تر فراهم می کند و روند اکتشاف داده های شما را ساده تر می کند.

سیاهههای مربوط به جستجوی دقیق: تاریخچه جستجوی خود را پیگیری کنید! یک کادر ورود به سیستم را پیاده سازی کنید تا به طور خودکار آخرین شرایط جستجوی خود و تعداد نتایج یافت شده را ضبط کنید. این می تواند برای تجدید نظر در جستجوهای گذشته و تجزیه و تحلیل روندها بسیار ارزشمند باشد.

موارد جذابیت بصری: با ارائه فرم جستجوی خود ، تجربه کاربر را تقویت کنید. برای ایجاد یک رابط جذاب تر و بصری با رنگ ، قلم و چیدمان بازی کنید.

بهینه سازی سرعت: هر ثانیه حساب می شود! روشهای بهینه سازی توابع جستجوی خود را برای زمان پاسخ سریعتر کشف کنید. این ممکن است شامل پالایش کد ، نمایه سازی داده ها یا استراتژی های ذخیره سازی استفاده باشد.

تنظیم مجموعه داده های بزرگ: کار با پایگاه داده های عظیم؟ نگران نباشید ، شما گزینه هایی دارید! منطق را برای غلبه بر محدودیت 6 دقیقه ای اجرا توابع اسکریپت Google Apps پیاده سازی کنید.

با کاوش در این راهها ، می توانید عملکرد اصلی جستجوی خود را به یک ابزار اکتشاف داده های قدرتمند و شخصی تبدیل کنید. به یاد داشته باشید ، امکانات بی پایان است!

PS: با این توانایی جدید چقدر مولد (یا به تعویق انداختن) خواهید بود؟

من نیبش خادکا هستم. این وبلاگ را به اشتراک بگذارید و در صورت مفید فیلم را دوست دارید! بیشتر مطالب من را در Script-Portal (متوسط) و در کانال YouTube من پیدا کنید: CodingWithNibesh .

خبرکاو