اصول اسکریپت برنامه‌ها با Google Sheets شماره 1: ماکروها و amp; توابع سفارشی

1. به Apps Script خوش آمدید

Apps Script چیست؟

Apps Script یک پلت فرم توسعه سریع برنامه است که به شما امکان خودکارسازی، سفارشی سازی و گسترش تجربه Google Workspace را می دهد. با Apps Script، می‌توانید با ساده‌سازی کارهای سخت یا پیچیده در Google Workspace در زمان و تلاش خود صرفه‌جویی کنید.

ویژگی های Apps Script شامل موارد زیر است:

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

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

سرویس صفحه گسترده

می‌توانید از Apps Script برای گسترش کاربرگ‌نگار Google برای صرفه‌جویی در زمان و تلاش استفاده کنید. Apps Script سرویس صفحه‌گسترده را ارائه می‌کند که به اسکریپت‌ها اجازه می‌دهد با فایل‌های Google Sheet و داده‌های موجود در آنها تعامل داشته باشند. می‌توانید از این سرویس برای خودکارسازی وظایف رایج صفحه‌گسترده زیر استفاده کنید:

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

چیزی که یاد خواهید گرفت

این لیست پخش همه موضوعاتی را که برای شروع استفاده از Apps Script با Google Sheets نیاز دارید را پوشش می دهد:

1. ماکروها و توابع سفارشی
2. صفحات گسترده، برگه ها و محدوده ها
3. کار با داده ها
4. قالب بندی داده ها
5. نمودار و ارائه داده در اسلایدها

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

برای کسب اطلاعات بیشتر در مورد محتویات این Codelab به بخش بعدی بروید.

2. مقدمه

به اولین لبه کد این لیست پخش خوش آمدید. در این لبه کد، اصول اولیه استفاده از Apps Script با Google Sheets را خواهید آموخت. به طور خاص، این کد لبه روی دو مفهوم کلیدی تمرکز دارد: ماکروها و توابع سفارشی .

ماکرو مجموعه ای از اقدامات ثبت شده در Google Sheets است. پس از ضبط، می توانید یک ماکرو را فعال کنید تا بعداً آن اقدامات را با یک آیتم منو یا کلید میانبر تکرار کنید. می‌توانید هم در Google Sheets و هم در ویرایشگر کد Apps Script ماکروهای خود را ایجاد و به‌روزرسانی کنید.

در ویرایشگر کد Apps Script، می‌توانید توابع سفارشی نیز ایجاد کنید. مشابه عملکردهای داخلی ارائه شده توسط Sheets (مانند SUM یا AVERAGE )، می‌توانید از Apps Script برای نوشتن توابع سفارشی خود برای عملیات ساده و خاص (مانند تبدیل یا الحاق رشته‌ها) استفاده کنید. پس از ایجاد، می‌توانید این توابع را مانند یک تابع داخلی در Sheets فراخوانی کنید. از توابع سفارشی نیز می توان در فرمول های سلولی که می نویسید استفاده کرد و در صورت نیاز آنها را با سایر توابع ترکیب کرد.

زیر را بخوانید تا بدانید که این کد لبه شامل چه مفاهیم و الزاماتی است.

چیزی که یاد خواهید گرفت

• نحوه ایجاد یک اسکریپت برای Google Sheets.
• نحوه پیمایش در ویرایشگر Apps Script.
• نحوه ایجاد و به روز رسانی ماکروها
• چگونه اولین تابع سفارشی Sheets خود را ایجاد کنیم.

آنچه شما نیاز دارید

• آشنایی اولیه با جاوا اسکریپت
• آشنایی اولیه با Google Sheets
• قابلیت خواندن Sheets A1 Notation

توجه: Apps Script بر اساس جاوا اسکریپت است. اگر با جاوا اسکریپت تازه کار هستید، Codecademy یک دوره آموزشی مقدماتی جاوا اسکریپت را ارائه می دهد. اگرچه گوگل این دوره آموزشی را ایجاد نکرده است، اما باید همه چیزهایی را که باید بدانید را پوشش دهد.

شما با معرفی تمام شده اید. برای شروع کار با ماکروها به بخش بعدی بروید.

3. یک ماکرو در Sheets ایجاد کنید

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

در این بخش، نحوه ساخت ماکرو در Sheets را یاد خواهید گرفت. در بخش بعدی، نحوه ساخت ماکروها با استفاده از Apps Script را خواهید دید.

قبل از شروع

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

یک کپی از صفحه‌گسترده نمونه برای استفاده در پوشه Google Drive شما قرار می‌گیرد و نام آن «کپی از 10 فیلم برتر (2018) است.

یک ماکرو ایجاد کنید

اکنون که یک صفحه گسترده برای کار با آن دارید، می توانید یک ماکرو را در Google Sheets ضبط کنید. برای این مثال، یک ماکرو ایجاد می‌کنید که یک ردیف سرصفحه را برای داده‌های شما قالب‌بندی می‌کند. فقط این مراحل را دنبال کنید:

1. روی سلول A1 کلیک کنید تا مکان نما در ردیف قرار گیرد. این ردیف سرصفحه شماست.
2. در منو، Extensions > Macros > Record Macros را انتخاب کنید.

هنگامی که شروع به ضبط می‌کنید، Google Sheets هر اقدامی را که در صفحه‌گسترده انجام می‌دهید به خاطر می‌آورد: برجسته کردن سلول‌ها، افزودن داده‌ها، جابجایی به برگه‌های مختلف، قالب‌بندی و غیره. این اقدامات به "اسکریپت" تبدیل می شوند که پس از ذخیره و فعال کردن ماکرو بعداً تکرار می شود.

3. در گفتگوی ماکرو، Relative reference را انتخاب کنید.

اصطلاحات کلیدی: ماکروها می توانند از دو نوع مرجع در Sheets برای ارجاع به سلول های خاص استفاده کنند. ارجاعات مطلق به مکان های دقیق ثبت شده ثابت می شوند (به عنوان مثال، سلول A5)، در حالی که مراجع نسبی با استفاده از انتخاب فعلی کاربر به عنوان نقطه شروع اعمال می شوند (به عنوان مثال، سلول چهارم از انتخاب فعلی پایین تر).

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

4. ردیف 1 را انتخاب کنید.

5. رنگ پر رنگ ردیف بالا را از سفید به سرخابی تیره 3 تغییر دهید.

6. رنگ متن ردیف بالا را از سیاه به سفید تغییر دهید.

7. برای پررنگ کردن متن، Ctrl+B (یا Cmd+B در macOS) را فشار دهید.
8. برای ثابت کردن ردیف بالا، View > Freeze > 1 row را انتخاب کنید.

9. روی Save در گفتگوی ماکرو کلیک کنید. یک گفتگوی جدید از شما می خواهد که ماکرو را نام ببرید. نام "Header" را وارد کنید و روی ذخیره کلیک کنید.

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

ماکرو خود را فعال کنید

با دنبال کردن این دستورالعمل‌ها می‌توانید ماکرو جدید خود را در Sheets اعمال کنید:

1. برای ایجاد یک برگه، روی Add Sheet کلیک کنید .

2. در برگه جدید، مقداری متن به A1:C2 اضافه کنید. به راحتی می توانید ورودی های مثال زیر را دنبال کنید:

3. ردیف اول را برجسته کنید.

4. برای اعمال ماکرو در ناحیه انتخاب شده، روی Extensions > Macros > Header کلیک کنید.
5. با دنبال کردن دستورالعمل‌های روی صفحه، ماکرو را مجاز کنید.

توجه: اگر از یک حساب gmail.com استفاده می کنید، ممکن است در اولین استفاده از اسکریپت خود یک گفتگوی برنامه تأیید نشده نیز دریافت کنید. Google از این برای هشدار دادن به کاربرانی که ممکن است از کد نویسندگان ناشناس یا نامعتبر استفاده می کنند، استفاده می کند. اگر این گفتگو را می بینید، اشکالی ندارد که ادامه دهید زیرا شما نویسنده اسکریپت هستید. برای ادامه دادن مجوز به اسکریپت، فرمان های روی صفحه را دنبال کنید. فرآیند دریافت این مجوز، مجوز نامیده می شود.

6. مرحله 4 را برای اجرای مجدد ماکرو تکرار کنید (مجوز کردن آن اولین اجرا را متوقف می کند).

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

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

4. ماکروها در ویرایشگر اسکریپت

همانطور که یک ماکرو ایجاد می کنید، Google Sheets اقدامات شما را به عنوان یک تابع Apps Script ذخیره می کند. وقتی ماکرو را فعال می‌کنید، Google Sheets تابع Apps Script را فراخوانی می‌کند تا آن اقدامات را به همان ترتیب اعمال کند.

ویرایشگر اسکریپت

اکنون که یک ماکرو ایجاد کرده اید، می توانید به کد آن نگاه کنید. برای مشاهده اسکریپت ماکرو، روی Extensions > Apps Script کلیک کنید تا ویرایشگر کد مرورگر برای Apps Script باز شود.

ویرایشگر اسکریپت به شما امکان می‌دهد کد را در Apps Script بنویسید و آن اسکریپت‌ها را در سرورهای Google اجرا کنید.

توجه: ماکروها و اسکریپت‌هایی که در این لبه کد ایجاد می‌کنید به فایل Google Sheet که روی آن کار می‌کنند پیوست می‌شوند و می‌توانید در هر زمان از منوی Sheets Extensions > Apps Script به آنها دسترسی داشته باشید. اسکریپت‌های پیوست شده به صفحه Google مانند این ، Container Bound نامیده می‌شوند.

تجزیه و تحلیل macros.gs

اسکریپت فعلی را مرور کنید. Sheets فایل اسکریپت macros.gs را زمانی که ماکرو Header را ضبط کردید ایجاد کرد و آن را با یک تابع Apps Script مربوطه به نام Header پر کرد. هنگامی که ماکرو Header فعال می کنید، Sheets این عملکرد را اجرا می کند.

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

خط اول یک نظر حاشیه نویسی است که بر مجوز تأثیر می گذارد:

/** @OnlyCurrentDoc */

اکثر اسکریپت ها قبل از اجرا از کاربر مجوز می خواهند. این مجوزها آنچه را که کاربر به اسکریپت اجازه می دهد انجام دهد را کنترل می کند. هنگامی که نظر @OnlyCurrentDoc در یک پروژه اسکریپت وجود دارد، Apps Script فقط برای دسترسی و به‌روزرسانی صفحه‌گسترده فعلی اجازه می‌خواهد. بدون این نظر، Apps Script برای دسترسی و به‌روزرسانی همه صفحات گسترده کاربر اجازه می‌خواهد. هنگامی که فقط با یک فایل کار می کنید، همیشه بهترین تمرین این است که این حاشیه نویسی را اضافه کنید. ضبط کننده ماکرو این نظر را به صورت خودکار برای شما اضافه می کند.

برای درک اینکه چگونه Apps Script دستورالعمل های ماکرو شما را نشان می دهد، می توانید به تابع نگاه کنید:

function Header(){
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getActiveSheet();
  sheet.getRange(
    spreadsheet.getCurrentCell().getRow(),
    1, 1, sheet.getMaxColumns()).activate();
  spreadsheet.getActiveRangeList().setBackground('#4c1130')
  .setFontColor('#ffffff')
  .setFontWeight('bold');
  spreadsheet.getActiveSheet().setFrozenRows(1);
};

این کد زمانی اجرا می شود که ماکرو Header فعال می کنید. پس از function ، برچسب Header() نام تابع و پارامترهای آن را تعریف می کند. توجه داشته باشید که Header() به هیچ پارامتری نیاز ندارد زیرا توابع ماکرو در Apps Script نیازی به ورودی ندارند. بریس ها همیشه بدنه یک تابع را در Apps Script محصور می کنند.

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

var spreadsheet = SpreadsheetApp.getActive();

در اینجا، getActive() یک شی را برمی گرداند که فایل صفحه گسترده فعال فعلی را در Sheets نشان می دهد و آن را به spreadsheet متغیر جدید تنظیم می کند.

var sheet = spreadsheet.getActiveSheet();
sheet.getRange(
    spreadsheet.getCurrentCell().getRow(),
    1, 1, sheet.getMaxColumns()).activate();

این خطوط مربوط به عمل کلیک کردن روی ردیف اول برای برجسته کردن آن است. به این می گویند فعال سازی . خط اول برگه فعلی را در sheet متغیر ذخیره می کند، در حالی که خط دوم کل ردیف اول را با استفاده از متد getRange() دریافت می کند و سپس activate() برای فعال کردن آن فراخوانی می کند. سطر اول با استفاده از شماره سطر و ستون خاص مشخص می شود. فراخوانی spreadsheet.getCurrentCell().getRow() تعداد ردیف فعلی را برمی‌گرداند، در حالی که sheet.getMaxColumns() حداکثر تعداد ستون‌ها را در برگه برمی‌گرداند.

spreadsheet.getActiveRangeList().setBackground('#4c1130')
.setFontColor('#ffffff')
.setFontWeight('bold');

این بیت کد پیچیده تر می شود. برای فراخوانی مؤثر متدها با spreadsheet ، کد سه روش را روی getActiveRangeList() قرار می‌دهد تا از فراخوانی اضافی کد بیش از یک بار در این متد spreadsheet جلوگیری کند. همانطور که بیشتر با استفاده از Apps Script کدنویسی می کنید، با این قرارداد فراخوانی چندین روش در یک کلاس (همچنین به عنوان زنجیره متد نیز شناخته می شود) بیشتر آشنا خواهید شد. در حال حاضر، می توانید برای توضیحات مختصر در مورد هر روش در بلوک کد، موارد زیر را بخوانید:

• getActiveRangeList() RangeList فعال فعلی را در spreadsheet برمی گرداند. در این مورد، به سادگی اولین ردیفی است که خط قبلی فعال شده است.
• هر دو روش setBackground(color) و setFontColor(color) ویژگی های رنگ سلول ها را در محدوده فعال تغییر می دهند.
• setFontWeight(fontWeight) وزن فونت را برای سلول های محدوده فعال تنظیم می کند.

در نهایت، خط نهایی اولین ردیف ماکرو را ثابت می کند:

spreadsheet.getActiveSheet().setFrozenRows(1);

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

بخش بعدی بر روی دستکاری کد تابع Header() تمرکز دارد تا نشان دهد چگونه می توانید از ویرایشگر اسکریپت برای شخصی سازی بیشتر ماکروها استفاده کنید.

ماکروها را با Apps Script سفارشی کنید

ویرایشگر Apps Script ماکرویی را که قبلاً در Google Sheets ایجاد کرده‌اید نشان می‌دهد. با تنظیم محتویات بدنه عملکرد، می توانید دستورالعمل های ماکرو خود را برای انجام اقدامات مختلف یا اضافی سفارشی کنید. تمرین‌های زیر راه‌های مختلفی برای دستکاری ماکروها با ویرایشگر اسکریپت را نشان می‌دهند.

سلول های آسیب دیده را تغییر دهید

فرض کنید می خواهید ماکرو خود را طوری تغییر دهید که به جای کل سطر، فقط 10 ستون اول سطر اول را تحت تأثیر قرار دهد. می توانید ماکرو را حذف کرده و دوباره آن را ضبط کنید. اما، با استفاده از ویرایشگر Apps Script، می توانید آن تغییرات را مستقیماً انجام دهید. در اینجا یک راه برای انجام آن وجود دارد:

1. در ویرایشگر اسکریپت، sheet.getMaxColumns() با 10 جایگزین کنید. این ویرایش محدوده سلول‌هایی را که ماکرو روی صفحه‌گسترده تأثیر می‌گذارد تغییر می‌دهد.

/** @OnlyCurrentDoc */

function Header(){
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getActiveSheet();
  sheet.getRange(
    spreadsheet.getCurrentCell().getRow(),
    1, 1, 10).activate();
    /* sheet.getMaxColumns() replaced with 10.*/
  spreadsheet.getActiveRangeList().setBackground('#4c1130')
  .setFontColor('#ffffff')
  .setFontWeight('bold');
  spreadsheet.getActiveSheet().setFrozenRows(1);
}

2. برای ذخیره اسکریپت خود، روی ذخیره کلیک کنید .
3. برای تغییر نام پروژه خود، "Macros and Custom Functions" را به عنوان نام پروژه جدید وارد کنید و روی تغییر نام کلیک کنید.
4. برای ایجاد یک برگه، در Sheets، روی Add Sheet کلیک کنید .

5. در ویرایشگر اسکریپت، از لیست توابع، Header انتخاب کرده و روی Run کلیک کنید.

در برگه جدید خود، باید نتیجه زیر را مشاهده کنید:

با تغییر محدوده فعال یا هدف، ماکرو شما اکنون تنها بخشی از ردیف اول را تحت تأثیر قرار می دهد. بسیاری از روش‌های Apps Script یک محدوده یا نماد A1 را به عنوان پارامتر تعیین می‌کنند تا مشخص کنند روی کدام سلول‌ها باید عمل کنند.

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

رنگ های ماکرو خود را تغییر دهید

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

این دستورالعمل‌ها بر تغییر رنگ پس‌زمینه ماکرو تمرکز دارند:

1. در برگه‌ها، به برگه‌ای که حاوی داده‌های اصلی است (برگ 1) برگردید.
2. روی ردیف اول کلیک کنید تا برجسته شود.
3. در ویرایشگر اسکریپت، رنگ پس زمینه #4c1130 با #afeeee جایگزین کنید. این مقادیر رنگ های مختلف را با استفاده از نماد سه گانه هگز نشان می دهند.

/** @OnlyCurrentDoc */

function Header(){
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getActiveSheet();
  sheet.getRange(
    spreadsheet.getCurrentCell().getRow(),
    1, 1, 10).activate();
  spreadsheet.getActiveRangeList().setBackground('#afeeee')
    /* #4c1130 replaced with #afeeee.*/
  .setFontColor('#ffffff')
  .setFontWeight('bold');
  spreadsheet.getActiveSheet().setFrozenRows(1);
}

4. برای ذخیره اسکریپت خود، روی ذخیره کلیک کنید .
5. از لیست توابع، Header انتخاب کرده و روی Run کلیک کنید.

در Sheets، پس‌زمینه 10 ستون اول در ردیف اول به رنگ فیروزه‌ای سفارشی تغییر رنگ می‌دهد:

با تغییر نماد رنگ هگزا در پارامترهای setBackground(color) از #4c1130 (ارغوانی تیره 3) به #afeeee (فیروزه ای کم رنگ، گزینه ای که در منوی رنگی پیش فرض Sheets قابل دسترسی نیست)، ویژگی رنگ رنگ پس زمینه ماکرو خود را تغییر می دهید.

اکنون رنگ پس زمینه تنظیم شده توسط ماکرو خود را تغییر داده اید. اگر می خواهید رنگ متن را نیز تغییر دهید، کد رنگ دوم را تغییر دهید.

1. در کاربرگ‌نگار، روی اولین ردیف کلیک کنید تا مطمئن شوید که هنوز برجسته است.
2. در ویرایشگر اسکریپت، رنگ فونت #ffffff با #191970 جایگزین کنید. این باعث می شود که ماکرو رنگ فونت آبی سرمه ای را تنظیم کند.

/** @OnlyCurrentDoc */

function Header(){
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getActiveSheet();
  sheet.getRange(
    spreadsheet.getCurrentCell().getRow(),
    1, 1, 10).activate();
  spreadsheet.getActiveRangeList().setBackground('#afeeee')
  .setFontColor('#191970')/* #ffffff replaced with #191970.*/
  .setFontWeight('bold');
  spreadsheet.getActiveSheet().setFrozenRows(1);
}

3. برای ذخیره اسکریپت خود، روی ذخیره کلیک کنید .
4. از لیست توابع، Header انتخاب کرده و روی Run کلیک کنید.

برای دیدن رنگ متن ردیف سرصفحه اکنون به رنگ آبی سرمه ای به Sheets بازگردید.

اکنون مشاهده کرده‌اید که چگونه ماکروها در واقع اقدامات Sheets هستند که به عنوان کد Apps Script ثبت می‌شوند. در بخش بعدی، می‌توانید روش دیگری را مشاهده کنید که Apps Script می‌تواند به شما در کار با Google Sheets کمک کند: توابع سفارشی .

5. اولین اسکریپت خود را کد کنید: توابع سفارشی

مانند اکثر برنامه‌های صفحه‌گسترده، Google Sheets دارای چندین تابع فرمول داخلی مانند =SUM() است که محاسبات سریع روی داده‌های صفحه‌گسترده را امکان‌پذیر می‌کند. توابع سفارشی به سادگی توابعی هستند که با استفاده از Apps Script مشخص می کنید. هنگامی که یک تابع سفارشی را تعریف کردید، می توانید از آن در هر نقطه از صفحه گسترده خود استفاده کنید، درست مانند یک تابع داخلی.

این بخش به شما نشان می دهد که چگونه یک تابع سفارشی در Apps Script ایجاد کنید که تبدیل پولی را انجام می دهد.

یک فایل اسکریپت ایجاد کنید

با استفاده از همان صفحه گسترده و پروژه اسکریپت از بخش Macros، این دستورالعمل ها را دنبال کنید تا یاد بگیرید چگونه یک اسکریپت جدید بسازید (که در نهایت می توانید برای ایجاد اولین تابع سفارشی خود از آن استفاده کنید):

1. برای ایجاد یک فایل Apps Script، به ویرایشگر اسکریپت برگردید.
2. در کنار فایل‌ها ، روی افزودن فایل کلیک کنید > اسکریپت
3. نام فایل اسکریپت جدید را customFunctions و Enter را فشار دهید. (Apps Script به طور خودکار یک پسوند .gs به نام فایل اسکریپت اضافه می کند.)

یک برگه جدید به نام customFunctions.gs در ویرایشگر ظاهر می شود.

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

دلار آمریکا را به فرانک سوئیس تبدیل کنید

فرض کنید می‌خواهید داده‌های «10 فیلم برتر 2018» را تغییر دهید تا نه تنها ارزش ناخالص جهانی را به دلار آمریکا، بلکه به فرانک سوئیس نیز نشان دهد. با توابع سفارشی، می توانید این کار را به راحتی انجام دهید. تمرین زیر نحوه ایجاد یک تابع سفارشی برای تبدیل ریاضی ارزش دلار به ارزش فرانک را نشان می دهد.

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

1. در Sheets، روی ستون H کلیک راست کنید.
2. در منوی به دست آمده، روی Insert 1 به سمت راست کلیک کنید.

3. ستون "Worldwide_Gross (فرانک سوئیس)" را در سلول I1 برچسب بزنید.

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

4. در customFunctions.gs ، کد myFunction() را با کد زیر جایگزین کنید:

/**
 * Converts US dollars to Swiss francs.
 *
 * @param {number} dollars The total number of dollars.
 * @return {number} swissFrancs The converted total of Swiss francs.
 * @customfunction
 */
function USDTOCHF(dollars){
  var swissFrancs = dollars * .99; 
  return swissFrancs;
}

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

5. برای ذخیره اسکریپت خود، روی ذخیره کلیک کنید .
6. در Sheets، سلول I2 را انتخاب کنید.
7. در نوار تابع، =USDTOCHF(H2) را وارد کنید.

برای اعمال فرمول به بقیه سلول های ستون:

8. مکان نما خود را به گوشه سمت راست پایین سلول I2 ببرید و کادر کوچک آبی را انتخاب کنید (مکان نما باید به وقتی به کادر آبی اشاره می کنید).
9. کادر آبی را به سمت پایین بکشید تا محدوده I3 : I11 برجسته شود.

اکنون ستون I تبدیل فرانک سوئیس به ارزش دلار آمریکا را در ستون H فهرست می کند.

تبریک، شما اولین تابع سفارشی خود را ایجاد کردید. بخش بعدی کدی را که شامل USDTOCHF() است توضیح می دهد.

تجزیه و تحلیل USDTOCHF()

نظرات اولیه هدف کد را شرح می دهد:

/**
 * Converts US dollars to Swiss francs.
 *
 * @param {number} dollars The total number of dollars.
 * @return {number} swissFrancs The provided value in Swiss francs.
 * @customfunction
 */

بلوک های نظر مانند این اغلب در برنامه نویسی برای توضیح عملکرد توابع استفاده می شوند.

در این نظر می توانید دو بخش را شناسایی کنید: توضیحات تابع (برای تبدیل دلار به فرانک) و حاشیه نویسی که پارامترهای تابع و نوع بازگشت را توصیف می کند.

با حاشیه نویسی، Apps Script از JSDoc برای کمک به مستندسازی و ایجاد نکات تکمیل خودکار برای کد خود استفاده می کند. می‌توانید در زیر بخوانید که چگونه هر حاشیه‌نویسی مورد استفاده در USDTOCHF() به شما در توسعه Apps Script کمک می‌کند:

• @param : می توانید از حاشیه نویسی @param برای توصیف هر پارامتر ارسال شده به تابع استفاده کنید.
• @return : می‌توانید از حاشیه‌نویسی @return برای توصیف آنچه که تابع برمی‌گرداند استفاده کنید.
• @customfunction : همیشه باید @customfunction در نظر سند هر تابع سفارشی اضافه کنید. این حاشیه‌نویسی به Sheets اطلاع می‌دهد تا عملکرد سفارشی شما را به‌طور خودکار تکمیل کنند، درست همانطور که Sheets عملکردهای داخلی را هنگامی که نام تابعی را در یک سلول وارد می‌کنید، همانطور که در زیر مشاهده می‌کنید، تکمیل می‌کند:

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

سپس روی کد تابع USDTOCHF() تمرکز کنید:

function USDTOCHF(dollars){
  var swissFrancs = dollars * .99; 
  return swissFrancs;
}

همانطور که قبلا ذکر شد، USDTOCHF() متغیر عددی دلار را می گیرد، آن را در نرخ مبادله ثابت ضرب می کند و مقدار تبدیل شده به فرانک سوئیس را در متغیر عددی swissFrancs برمی گرداند. پارامتر ورودی مقدار موجود در سلولی است که هنگام افزودن تابع سفارشی به یک سلول مشخص شده است. در این مثال، مقادیر دلار ورودی از ستون H می آید. مقدار خروجی swissFrancs در سلول تابع قرار می گیرد (ستون I در این مثال).

همانطور که در بخش بعدی خواهید دید، توابع سفارشی می توانند با مقادیر عددی یا رشته ای کار کنند.

یک پیشوند رشته را به هم متصل کنید

فرض کنید می‌خواهید خروجی عددی تابع USDTOCHF() شامل پیشوند فرانک سوئیس CHF باشد. همانطور که در دستورالعمل های زیر نشان داده شده است , می توانید این کار را با Apps Script با استفاده از عملگر الحاق ( + ) انجام دهید:

1. در ویرایشگر اسکریپت، حاشیه نویسی @return را به روز کنید تا رشته را به جای عدد برگرداند.
2. return swissFrancs به return 'CHF' + swissFrancs تغییر دهید.

عملگر + رشته CHF به جلوی مقدار موجود در swissFrancs اضافه می کند. اکنون کد شما باید به شکل زیر باشد:

/**
 * Converts US dollars to Swiss francs.
 *
 * @param {number} dollars The total number of dollars.
 * @return {string} swissFrancs The provided value in Swiss francs.
 * @customfunction
 */
function USDTOCHF(dollars){
  var swissFrancs = dollars * .99;
  return 'CHF' + swissFrancs;
}

3. برای ذخیره اسکریپت خود، روی ذخیره کلیک کنید .

رشته فرانک سوئیس اکنون مقادیر ستون I را پیشوند می دهد:

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

پیشرفته: واکشی داده های خارجی

این شروع خوبی برای یک تابع سفارشی اولیه است، اما در این مثال نرخ تبدیل دلار به فرانک سوئیس ثابت است. فرض کنید در عوض می‌خواهید از نرخ ارز فعلی استفاده کنید، بنابراین هر زمان که برگه مجدداً بارگذاری می‌شود، مقادیر برای نشان دادن تبدیل فعلی دوباره محاسبه می‌شوند؟ برای انجام این کار، به ابزاری برای کشف نرخ ارز فعلی نیاز دارید. این اطلاعات به راحتی در Google Sheets در دسترس نیست، اما خوشبختانه می توانید از Apps Script برای دریافت آن استفاده کنید.

برای دریافت نرخ تبدیل کنونی فرانک سوئیس به دلار آمریکا می توانید از کدی مانند زیر استفاده کنید:

function USDTOCHF(dollars){
  // Gets a cache that is common to all users of the script.
  var cache = CacheService.getScriptCache();

  // Accesses the memory location (rates.CHF) of the script cache.
  var rate = cache.get('rates.CHF');

  // If a cache miss occurs, the program fetches the current
  // CHF rate from an API and stores the rate in the cache
  // for later convenience.
  if (!rate) {
    var response =
UrlFetchApp.fetch('https://api.exchangeratesapi.io/latest?base=USD');
    var result = JSON.parse(response.getContentText());
    rate = result.rates.CHF;
    cache.put('rates.CHF', rate);
  }
  // Converts dollars to CHF according to the latest rate.
  var swissFrancs = dollars * rate;
  // Returns the CHF value.
  return 'CHF' + swissFrancs;
}

این کد نرخ ارز فعلی را از یک سرور اطلاعات مالی با استفاده از API نرخ ارز شخص ثالث دریافت می کند. این کار با استفاده از سرویس‌های Apps Script مانند UrlFetchApp و CacheService انجام می‌شود. این مفاهیم پیشرفته خارج از محدوده این کد خاص هستند، اما می‌توانید تطبیق‌پذیری Apps Script را برای خودکارسازی کارهای پیچیده در Google Sheets مشاهده کنید.

دستورالعمل های عملکرد سفارشی

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

• توابع سفارشی که نیاز به مجوز کاربر دارند ایجاد نکنید . در عوض، توابع سفارشی خود را بسازید تا وظایف ساده‌تری مانند محاسبات داده‌های نمونه، ویرایش متن و غیره را انجام دهید. به استفاده از خدمات برنامه‌های اسکریپت بروید.
• یک تابع سفارشی را مانند یک تابع داخلی دیگر نامگذاری نکنید، یا نام آن را با خط زیر ختم نکنید. دستورالعمل های نامگذاری را مرور کنید.
• آرگومان های متغیر را به توابع سفارشی منتقل نکنید. شما فقط می توانید مقادیر قطعی (ثابت) را به عنوان آرگومان به توابع سفارشی ارسال کنید. ارسال آرگومان های متغیر، مانند نتیجه =RAND() باعث شکسته شدن تابع سفارشی می شود. رهنمودهای Arguments را ببینید.
• عملکردهایی را ایجاد نکنید که تکمیل آنها بیش از 30 ثانیه طول می کشد. اگر بیشتر طول بکشد، خطایی رخ می دهد، بنابراین کد تابع را ساده و محدود نگه دارید. بهتر است محاسبات انجام شده در توابع سفارشی تا حد امکان ساده باشد. دستورالعمل های مقادیر بازگشتی را مشاهده کنید.

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

6. نتیجه گیری

شما اولین لبه کد اسکریپت اصول برنامه‌ها را با کاربرگ‌نگار Google تکمیل کرده‌اید. با ایجاد و ویرایش ماکروهای Sheets و توابع سفارشی، مفاهیم اولیه Apps Script را یاد گرفتید. می‌توانید دانش Apps Script خود را در بخش کد بعدی بیشتر گسترش دهید.

آیا این کد لبه را مفید یافتید؟

بله خیر

آنچه را پوشش داده ایم

• مفاهیم اولیه اسکریپت برنامه ها.
• نحوه پیمایش در ویرایشگر اسکریپت
• نحوه ایجاد و به روز رسانی ماکروهای Sheets.
• نحوه ایجاد توابع سفارشی برای Google Sheets.

شرایط کلیدی

• مرجع مطلق : یک تنظیم ضبط ماکرو که اقدامات ماکرو ضبط شده را مشخص می کند، روی سلول های دقیق استفاده شده در ضبط اعمال می شود.
• فعال (وضعیت) : نشان می‌دهد صفحه‌گسترده، صفحه، محدوده، یا سلولی که کاربر صفحه‌گسترده در حال حاضر در حال مشاهده یا برجسته کردن آن است.
• Apps Script : یک پلتفرم توسعه سریع برنامه، مبتنی بر جاوا اسکریپت، که گسترش سریع و آسان برنامه‌های Google Workspace و خودکار کردن کار را آسان می‌کند.
• مجوز : فرآیندی که کاربر اجازه می دهد تا به Apps Script اجازه دهد به داده های کاربر در خدمات مربوطه Google دسترسی داشته باشد یا اقدامات دیگری از طرف کاربر انجام دهد.
• اسکریپت محدود به کانتینر : هر اسکریپتی که به یک سند Google Workspace، مانند Google Sheet یا Google Doc، متصل شده و از آن ایجاد شده است.
• توابع سفارشی : فرمول های تعریف شده توسط کاربر در Apps Script که در صفحات گسترده برای عملیات ساده استفاده می شود.
• JSDoc : یک زبان نشانه گذاری که برای نوشتن یادداشت های توضیحی بر روی فایل های منبع مبتنی بر جاوا اسکریپت استفاده می شود.
• ماکرو : مخفف «آموزش کلان». مجموعه‌ای از اقدامات Google Sheets ضبط شده که می‌توانید با استفاده از یک آیتم منو یا کلید میانبر تکرار کنید.
• روش زنجیره‌ای : یک اصطلاح برنامه‌نویسی برای فراخوانی چند متد با تکیه بر این واقعیت است که هر فراخوانی متد، شی را برمی‌گرداند.
• محدوده : گروهی از یک یا چند سلول مجاور در یک صفحه.
• مرجع نسبی : یک تنظیم ضبط ماکرو که اقدامات ماکرو ضبط شده را مشخص می‌کند، نسبت به انتخاب سلول فعلی روی سلول‌ها اعمال می‌شود.
• ویرایشگر اسکریپت : ویرایشگر کد درون مرورگر برای Apps Script.
• Sheet : یک صفحه از یک صفحه گسترده (شبیه به کتاب های کاری مایکروسافت اکسل). گاهی اوقات برای ارجاع به فایل Google Sheets در Google Drive استفاده می شود.
• SpreadsheetApp : این کلاس به عنوان کلاس والد برای سرویس Spreadsheet عمل می کند و نقطه شروعی را برای کدهایی که داده های Google Sheets را می خواند یا دستکاری می کند، ارائه می دهد.
• صفحه گسترده : یک فایل Google Sheets که در Google Drive قرار دارد.
• سرویس صفحه‌گسترده : یک سرویس Apps Script که به اسکریپت‌ها اجازه ایجاد، دسترسی و اصلاح فایل‌های Google Sheets را می‌دهد.

در مستندات Apps Script درباره Apps Script بیشتر بیاموزید.

بعدش چی

کدهای بعدی در این لیست پخش، کلاس های اصلی و اصطلاحات سرویس Apps Script Spreadsheet را ارائه می دهد. این سرویس به شما امکان می دهد تا با استفاده از Apps Script مقادیر و ارائه داده ها را در Google Sheets به خوبی کنترل کنید.

کدهای بعدی را در صفحات گسترده، برگه‌ها و محدوده‌ها پیدا کنید.