Google Sheets و ساخت توابع شخصی در آن

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

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

توابع «توکار» (Built-in)

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

فیلم آموزش گوگل شیت – اسپرید شیت گوگل Google Sheets – پیشرفته در فرادرس

کلیک کنید

برای مثال از نمونه‌هایی از توابع توکار می‌توان به توابع «Sum» یا «Average» اشاره کرد که به ترتیب برای جمع کردن و میانگین گرفتن استفاده می‌شوند.

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

1'=if(A2="PA",B2*0.06,if(A2="CA",B2*0.0625,B2*0))'

حالا فرض کنید یک لیست بلند دارید که باید در این دستور اعمال کنید. خیلی زود مدیریت این دستور از دستتان خارج خواهد شد. البته این کار را می‌توان به روش‌های دیگری نیز انجام داد. مثلا می‌توانید اطلاعات مورد نیاز را در یک جدول دیگر ذخیره کرده و از تابع «Vlookup» استفاده کنید، ولی زمانی که یاد بگیرید توابع شخصی خود را در گوگل اسکریپت بنویسید، ذهنتان به دنیای جدیدی باز خواهد شد. در ادامه به چگونگی نوشتن اسکریپت‌ها و توابع شخصی‌سازی شده خواهیم پرداخت.

ساخت تابع شخصی

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

گام اول: Script Editor را باز کنید

در برگه‌ی خود، از منوی «Tools» گزینه‌ی «Script Editor» را انتخاب نمایید.

گام دوم: تابع خود را بسازید

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

برای مثالِ مالیات که در بالا به آن اشاره کردیم، می‌توانید کد زیر را در «Script Editor» وارد نمایید:

1 
2function tax(input, location) {
3    var rate = 0 ;
4    switch (location) {
5        case 'PA':
6            rate = 0.06;
7            break;
8        case 'CA':
9            rate = 0.0625;
10            break;
11        default:
12            rate = 0;
13    }
14    return (input * rate);
15}

همانطور که مشاهده می‌کنید، در این مثال تنها دو محل را در محاسبه‌ی مالیات ملاک قرار داده‌ایم. در صورت نیاز می‌توانید با استفاده از دستورات «Case» و «Break» اضافی، جاهای بیشتری را در دستور خود قرار دهید. این کار می‌تواند یک تمرین نیز برایتان باشد.

گام سوم: تابع خود را ذخیره کنید

از منوی File گزینه‌ی «Save» را انتخاب کرده و بعد از وارد کردن یک نام برای پروژه، روی «OK» کلیک نمایید.

استفاده از توابع شخصی

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

فیلم آموزش گوگل شیت – اسپرید شیت گوگل Google Sheets – پیشرفته در فرادرس

کلیک کنید

در سلولی که قصد فراخوانی تابع را دارید، همانند زیر فرمول خود را وارد نمایید:

1=yourFunctionName(inputs)

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

1=tax(B2,A2)

در این فرمول، «B2» مبلغ محصول، و «A2» محلی است که می‌خواهیم میزان مالیات آن را محاسبه کنیم.

همانند توابع توکار خود گوگل شیت، می‌توانید با استفاده از «AutoFill» تابع‌تان را در تمام سطرهای مورد نظر اعمال نمایید.

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

استفاده‌ی مجدد از توابع نوشته شده

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

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

۲. توابع خود را از برگه‌ای به برگه‌ای دیگر کپی کنید. شاید این کار خسته کننده به نظر برسد، ولی پاسخگوی نیازهای شما خواهد بود. تنها کافی است «Script Editor» را در برگه‌ی مبدا باز کرده و توابع نوشته شده را به «Script Editor» مقصد منتقل کنید.

۳. برگه‌ی خود را در «گالری قالب‌های گوگل» ذخیره کنید. توجه داشته باشید که با این کار، سند شما برای دیگران در دسترس خواهد بود.

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

«Google Script» از فرمت «JSDoc» پشتیبانی می‌کند که با استفاده از آن، امکان نوشتن توضیحات برای فرمول خود را خواهید داشت. این کار در آینده به فهم کدهای شما کمک خواهد کرد.

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

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

• آموزش Google Spreadsheets – مدیریت صفحات گسترده
• Google Sheets — چهار اسکریپت که باعث قدرت هر چه بیشتر آن می‌شوند
• آموزش اصول و مبانی برنامه نویسی
• مجموعه آموزش‌های برنامه‌نویسی
• Google Sheets و پنج قابلیت کلیدی که باید حتماً آنها را بلد باشید

^^