از اسکریپت برنامههای گوگل برای گسترش Sheets استفاده کنید. منوها ، دیالوگها و نوارهای کناری سفارشی را به Sheets اضافه کنید. توابع سفارشی برای Sheets بنویسید و آن را با سایر سرویسهای گوگل مانند تقویم گوگل، گوگل درایو و جیمیل ادغام کنید.
بیشتر اسکریپتهایی که برای Sheets طراحی شدهاند، آرایهها را دستکاری میکنند تا با سلولها، ردیفها و ستونهای یک صفحه گسترده تعامل داشته باشند. اگر با آرایهها در جاوا اسکریپت آشنا نیستید، Codecademy یک ماژول آموزشی عالی برای آرایهها ارائه میدهد. این دوره توسط گوگل توسعه داده نشده و به آن وابسته نیست.
برای آشنایی سریع با نحوهی استفاده از Apps Script با Sheets، به راهنمای شروع سریع ۵ دقیقهای برای ماکروها، منوها و توابع سفارشی مراجعه کنید.
شروع کنید
Apps Script شامل APIهای ویژهای برای ایجاد، خواندن و ویرایش Sheets به صورت برنامهنویسی شده است. Apps Script به دو روش با Sheets تعامل دارد: هر اسکریپتی میتواند در صورت داشتن مجوزهای مناسب برای آن، یک صفحه گسترده ایجاد یا تغییر دهد، و یک اسکریپت همچنین میتواند به یک صفحه گسترده متصل شود. اسکریپتهای متصل تواناییهای ویژهای برای تغییر رابط کاربری یا پاسخ دادن هنگام باز شدن صفحه گسترده دارند. برای ایجاد یک اسکریپت متصل، Extensions > Apps Script را از داخل Sheets انتخاب کنید.
سرویس Spreadsheet با Sheets به عنوان یک شبکه رفتار میکند و با آرایههای دوبعدی کار میکند. برای بازیابی دادهها از صفحه گسترده، به صفحه گستردهای که دادهها در آن ذخیره شدهاند دسترسی پیدا کنید، محدودهای را که دادهها را در خود جای داده است دریافت کنید و سپس مقادیر سلولها را دریافت کنید. Apps Script با خواندن دادههای ساختاریافته در صفحه گسترده و ایجاد اشیاء جاوا اسکریپت برای آنها، دسترسی به دادهها را تسهیل میکند.
خواندن دادهها
فرض کنید فهرستی از نام و شماره محصول دارید که آن را در یک صفحه گسترده ذخیره میکنید، همانطور که در تصویر زیر نشان داده شده است.
مثال زیر نحوه بازیابی و ثبت نام و شماره محصولات را نشان میدهد.
function logProductInfo() {
const sheet = SpreadsheetApp.getActiveSheet();
const data = sheet.getDataRange().getValues();
for (let i = 0; i < data.length; i++) {
Logger.log('Product name: ' + data[i][0]);
Logger.log('Product number: ' + data[i][1]);
}
}
مشاهده گزارشها
برای مشاهده دادههای ثبتشده، در بالای ویرایشگر اسکریپت، روی Execution log کلیک کنید.
نوشتن داده
برای ذخیره دادهها، مانند نام و شماره محصول جدید در صفحه گسترده، کد زیر را به انتهای اسکریپت اضافه کنید.
function addProduct() {
const sheet = SpreadsheetApp.getActiveSheet();
sheet.appendRow(['Cotton Sweatshirt XL', 'css004']);
}
کد قبلی یک ردیف جدید در پایین صفحه گسترده، با مقادیر مشخص شده، اضافه میکند. اگر این تابع را اجرا کنید، یک ردیف جدید به صفحه گسترده اضافه میشود.
منوها و رابطهای کاربری سفارشی
با افزودن منوها، پنجرههای محاورهای و نوارهای کناری سفارشی، صفحات را سفارشی کنید. برای یادگیری اصول اولیه ایجاد منوها، به راهنمای منوها مراجعه کنید. برای یادگیری سفارشیسازی محتوای یک پنجره محاورهای، به راهنمای سرویس HTML مراجعه کنید.
یک تابع اسکریپت را به یک تصویر یا نقاشی در یک صفحه گسترده متصل کنید؛ این تابع زمانی اجرا میشود که کاربر روی تصویر یا نقاشی کلیک کند. برای کسب اطلاعات بیشتر، به بخش تصاویر و نقاشیها در صفحات گسترده مراجعه کنید.
اگر قصد دارید رابط کاربری سفارشی خود را به عنوان بخشی از یک افزونه منتشر کنید، برای هماهنگی با سبک و طرحبندی ویرایشگر Sheets، راهنمای سبک را دنبال کنید.
اتصال به فرمهای گوگل
فرمهای گوگل را از طریق سرویسهای Forms و Spreadsheet به Sheets متصل کنید. این ویژگی به طور خودکار یک فرم گوگل را بر اساس دادههای موجود در یک صفحه گسترده ایجاد میکند. Apps Script همچنین به شما امکان میدهد از triggerها ، مانند onFormSubmit برای انجام یک عمل خاص پس از پاسخ کاربر به فرم استفاده کنید. برای کسب اطلاعات بیشتر در مورد اتصال Sheets به Forms، راهنمای سریع ۵ دقیقهای مدیریت پاسخها برای Forms را امتحان کنید.
قالببندی دادهها
کلاس Range متدهایی مانند setBackground برای دسترسی و تغییر قالب یک سلول یا محدودهای از سلولها دارد. مثال زیر سبک فونت یک محدوده را تنظیم میکند:
function formatMySpreadsheet() {
// Set the font style of the cells in the range of B2:C2 to be italic.
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheets()[0];
const cell = sheet.getRange('B2:C2');
cell.setFontStyle('italic');
}
اعتبارسنجی دادهها
به قوانین اعتبارسنجی دادههای موجود در Sheets دسترسی پیدا کنید یا قوانین جدیدی ایجاد کنید. به عنوان مثال، نمونه زیر نحوه تنظیم یک قانون اعتبارسنجی دادهها را نشان میدهد که فقط اعداد بین ۱ تا ۱۰۰ را در یک سلول مجاز میکند.
function validateMySpreadsheet() {
// Set a rule for the cell B4 to be a number between 1 and 100.
const cell = SpreadsheetApp.getActive().getRange('B4');
const rule = SpreadsheetApp.newDataValidation()
.requireNumberBetween(1, 100)
.setAllowInvalid(false)
.setHelpText('Number must be between 1 and 100.')
.build();
cell.setDataValidation(rule);
}
برای جزئیات بیشتر در مورد کار با قوانین اعتبارسنجی دادهها، به SpreadsheetApp.newDataValidation ، DataValidationBuilder و Range.setDataValidation مراجعه کنید.
نمودارها
نمودارهایی را در یک صفحه گسترده جاسازی کنید که دادهها را در یک محدوده خاص نشان میدهند. مثال زیر یک نمودار میلهای جاسازی شده ایجاد میکند، با فرض اینکه دادههای قابل رسم نمودار در سلولهای A1:B15 دارید:
function newChart() {
// Generate a chart representing the data in the range of A1:B15.
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheets()[0];
const chart = sheet.newChart()
.setChartType(Charts.ChartType.BAR)
.addRange(sheet.getRange('A1:B15'))
.setPosition(5, 5, 0, 0)
.build();
sheet.insertChart(chart);
}
برای کسب اطلاعات بیشتر در مورد جاسازی نمودار در صفحه گسترده خود، به EmbeddedChart و سازندگان نمودار خاص، مانند EmbeddedPieChartBuilder ، مراجعه کنید.
توابع سفارشی در گوگل شیت
یک تابع سفارشی مشابه یک تابع صفحه گسترده داخلی مانند =SUM(A1:A5) است، با این تفاوت که شما رفتار تابع را با Apps Script تعریف میکنید. برای مثال، میتوانید یک تابع سفارشی به in2mm() ایجاد کنید که مقداری را از اینچ به میلیمتر تبدیل میکند، سپس با تایپ کردن =in2mm(A1) یا =in2mm(10) در یک سلول، از فرمول موجود در صفحه گسترده خود استفاده کنید.
برای کسب اطلاعات بیشتر در مورد توابع سفارشی، راهنمای سریع ۵ دقیقهای «منوها» و «توابع سفارشی» را امتحان کنید، یا به راهنمای جامعتر توابع سفارشی نگاهی بیندازید.
ماکروها
ماکروها روش دیگری برای اجرای کد اسکریپت برنامهها از رابط کاربری Sheets هستند. برخلاف توابع سفارشی، میتوانید آنها را با یک میانبر صفحه کلید یا از طریق منوی Sheets فعال کنید. برای اطلاعات بیشتر، به بخش ماکروهای Sheets مراجعه کنید.
افزونههای گوگل شیت
افزونهها، پروژههای Apps Script بستهبندیشدهی ویژهای هستند که درون Sheets اجرا میشوند و میتوانند از فروشگاه افزونههای Sheets نصب شوند. اگر اسکریپتی برای Sheets توسعه دادهاید و میخواهید آن را به اشتراک بگذارید، Apps Script به شما امکان میدهد اسکریپت خود را به عنوان یک افزونه منتشر کنید تا سایر کاربران بتوانند آن را نصب کنند.
عملکرد و مقیاسپذیری
با افزایش مجموعه دادههای شما، ممکن است با مشکلات عملکردی مواجه شوید. برای بهینهسازی صفحه گسترده و اسکریپتهای خود:
- از بهترین شیوهها پیروی کنید : برای نکاتی در مورد به حداقل رساندن تماسهای خدماتی و استفاده از عملیات دستهای، راهنمای بهترین شیوهها را مطالعه کنید.
- بهینهسازی فرمولها : اگر صفحهگسترده شما به دلیل فرمولهای پیچیده (مانند
VLOOKUP،ARRAYFORMULAیاIMPORTRANGE) دچار کندی شده است، استفاده از Apps Script را برای انجام این محاسبات در حافظه و نوشتن نتایج به صورت دستهای در نظر بگیرید. - گزینههای پایگاه داده را در نظر بگیرید : برای مجموعه دادههای بسیار بزرگ (نزدیک به ۱۰ میلیون سلول) یا ورود دادههای با فرکانس بالا (مثلاً فرمهای متصل به هم زیاد)، استفاده از Google Cloud SQL با استفاده از JDBC یا BigQuery را در نظر بگیرید.
محرکها
اسکریپتهایی که به یک فایل Sheets متصل هستند میتوانند از triggerهای سادهای مانند توابع onOpen() و onEdit() برای پاسخ خودکار به زمانی که کاربری که دسترسی ویرایش به صفحه گسترده دارد، صفحه گسترده را باز یا ویرایش میکند، استفاده کنند. مانند triggerهای ساده، triggerهای قابل نصب به Sheets اجازه میدهند هنگام وقوع یک رویداد خاص، یک تابع را به طور خودکار اجرا کند. با این حال، triggerهای قابل نصب، انعطافپذیری بیشتری نسبت به triggerهای ساده ارائه میدهند و از رویدادهای زیر پشتیبانی میکنند: باز کردن، ویرایش، تغییر، ارسال فرم و رویدادهای زمانمحور (ساعت).