اصول اسکریپت برنامه ها با برگه های Google شماره 4: قالب بندی داده ها

1. مقدمه

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

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

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

• نحوه اعمال انواع عملیات قالب بندی Google Sheets در Apps Script.
• نحوه تبدیل لیستی از اشیاء JSON و ویژگی های آنها به یک صفحه سازمان یافته از داده ها با Apps Script.

قبل از اینکه شروع کنی

این چهارمین لبه کد در فهرست پخش Fundamentals of Apps Script with Google Sheets است. قبل از شروع این کد لبه، حتماً کدهای قبلی را تکمیل کنید:

1. ماکروها و توابع سفارشی
2. صفحات گسترده، برگه ها و محدوده ها
3. کار با داده ها

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

• درک موضوعات اساسی Apps Script که در کدهای قبلی این لیست پخش کاوش شده است.
• آشنایی اولیه با ویرایشگر Apps Script
• آشنایی اولیه با Google Sheets
• قابلیت خواندن Sheets A1 Notation
• آشنایی اولیه با جاوا اسکریپت و ک��اس String آن

2. راه اندازی کنید

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

1. روی این پیوند کلیک کنید تا برگه داده را کپی کنید و سپس روی ایجاد یک کپی کلیک کنید. صفحه گسترده جدید در پوشه Google Drive شما قرار می گیرد و نام آن "Copy of Data Formatting" است.
2. روی عنوان صفحه‌گسترده کلیک کنید و آن را از «کپی قالب‌بندی داده» به «قالب‌بندی داده» تغییر دهید. برگه شما باید به این شکل باشد، با برخی اطلاعات اولیه در مورد سه فیلم اول جنگ ستارگان:

3. Extensions > Apps Script را انتخاب کنید تا ویرایشگر اسکریپت باز شود.
4. روی عنوان پروژه Apps Script کلیک کنید و آن را از «پروژه بدون عنوان» به «قالب‌بندی داده» تغییر دهید. روی تغییر نام کلیک کنید تا تغییر عنوان ذخیره شود.

با استفاده از این صفحه‌گسترده و پروژه، آماده راه‌اندازی Codelab هستید. برای شروع یادگیری قالب‌بندی اولیه در Apps Script، به بخش بعدی بروید.

3. یک منوی سفارشی ایجاد کنید

می‌توانید چندین روش قالب‌بندی اولیه در Apps Script را در Sheets خود اعمال کنید. تمرین های زیر چند روش برای قالب بندی داده ها را نشان می دهد. برای کمک به کنترل اقدامات قالب‌بندی، بیایید یک منوی سفارشی با مواردی که نیاز دارید ایجاد کنیم. فرآیند ایجاد منوهای سفارشی در لبه کد Working with data توضیح داده شد، اما ما دوباره آن را در اینجا خلاصه می کنیم.

پیاده سازی

بیایید یک منوی سفارشی ایجاد کنیم.

1. در ویرایشگر Apps Script، کد موجود در پروژه اسکریپت خود را با کد زیر جایگزین کنید:

/**
 * A special function that runs when the spreadsheet is opened
 * or reloaded, used to add a custom menu to the spreadsheet.
 */
function onOpen() {
  // Get the spreadsheet's user-interface object.
  var ui = SpreadsheetApp.getUi();

  // Create and add a named menu and its items to the menu bar.
  ui.createMenu('Quick formats')
   .addItem('Format row header', 'formatRowHeader')
   .addItem('Format column header', 'formatColumnHeader')
   .addItem('Format dataset', 'formatDataset') 
  .addToUi();
}

2. پروژه اسکریپت خود را ذخیره کنید
3. در ویرایشگر اسکریپت، onOpen را از لیست توابع انتخاب کنید و روی Run کلیک کنید. این روی onOpen() اجرا می‌شود تا منوی صفحه‌گسترده را بازسازی کند، بنابراین نیازی به بارگیری مجدد صفحه‌گسترده ندارید.

بررسی کد

بیایید این کد را بررسی کنیم تا بفهمیم چگونه کار می کند. در onOpen() خط اول از getUi() برای بدست آوردن یک شی Ui استفاده می کند که نمایانگر رابط کاربری صفحه گسترده فعالی است که این اسکریپت به آن محدود شده است.

خطوط بعدی یک منو ایجاد می‌کنند ( Quick formats )، آیتم‌های منو ( Format row header ، Format column header و Format dataset ) را به منو اضافه می‌کنند و سپس منو را به رابط صفحه‌گسترده اضافه می‌کنند. این کار به ترتیب با createMenu(caption) ، addItem(caption, functionName) و addToUi() انجام می شود.

addItem(caption, functionName) ارتباطی بین برچسب آیتم منو و تابع Apps Script ایجاد می کند که با انتخاب آیتم منو اجرا می شود. برای مثال، انتخاب آیتم منوی Format row header باعث می‌شود که Sheets تلاش کند تا تابع formatRowHeader() را اجرا کند (که هنوز وجود ندارد).

نتایج

در صفحه گسترده خود، روی منوی Quick formats کلیک کنید تا آیتم‌های منوی جدید را مشاهده کنید:

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

4. یک ردیف سرصفحه را قالب بندی کنید

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

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

پیاده سازی

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

1. در ویرایشگر Apps Script، تابع زیر را به انتهای پروژه اسکریپت خود اضافه کنید:

/**
 * Formats top row of sheet using our header row style.
 */
function formatRowHeader() {
  // Get the current active sheet and the top row's range.
  var sheet = SpreadsheetApp.getActiveSheet();
  var headerRange = sheet.getRange(1, 1, 1, sheet.getLastColumn());
 
  // Apply each format to the top row: bold white text,
  // blue-green background, and a solid black border
  // around the cells.
  headerRange
    .setFontWeight('bold')
    .setFontColor('#ffffff')
    .setBackground('#007272')
    .setBorder(
      true, true, true, true, null, null,
      null,
      SpreadsheetApp.BorderStyle.SOLID_MEDIUM);

}

2. پروژه اسکریپت خود را ذخیره کنید

بررسی کد

مانند بسیاری از کارهای قالب‌بندی، کد Apps Script برای پیاده‌سازی آن ساده است. دو خط اول از روش‌هایی استفاده می‌کنند که قبلاً دیده‌اید تا به برگه فعال فعلی ( sheet ) و ردیف بالای برگه ( headerRange) اشاره کنند. Sheet.getRange(row, column, numRows, numColumns) سطر بالایی را مشخص می‌کند، فقط ستون‌هایی را که داده‌ها در آن‌ها وجود دارد. Sheet.getLastColumn() نمایه ستون آخرین ستونی را که حاوی داده ها در برگه است برمی گرداند. در مثال ما، ستون E ( url ) است.

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

• Range.setFontWeight(fontWeight) برای تنظیم وزن قلم روی پررنگ استفاده می شود.
• Range.setFontColor(color) برای تنظیم رنگ فونت به رنگ سفید استفاده می شود.
• Range.setBackground(color) برای تنظیم رنگ پس زمینه به رنگ آبی-سبز تیره استفاده می شود.
• setBorder(top, left, bottom, right, vertical, horizontal, color, style) یک حاشیه سیاه و سفید در اطراف سلول‌های محدوده قرار می‌دهد.

روش آخر چندین پارامتر دارد، بنابراین بیایید بررسی کنیم که هر کدام چه کاری انجام می دهند. چهار پارامتر اول در اینجا (همه روی true تنظیم شده‌اند) به Apps Script می‌گویند که حاشیه باید در بالا، پایین و سمت چپ و راست محدوده اضافه شود. پارامترهای پنجم و ششم ( null و null ) اسکریپت برنامه ها را هدایت می کنند تا از تغییر خطوط مرزی در محدوده انتخاب شده جلوگیری شود. پارامتر هفتم ( null ) نشان می دهد که رنگ حاشیه باید به طور پیش فرض سیاه باشد. در نهایت، آخرین پارامتر نوع سبک حاشیه مورد استفاده را مشخص می‌کند که از گزینه‌های ارائه‌شده توسط SpreadsheetApp.BorderStyle گرفته شده است.

نتایج

با انجام کارهای زیر می توانید عملکرد قالب بندی خود را در عمل مشاهده کنید:

1. اگر قبلاً این کار را نکرده‌اید، پروژه اسکریپت خود را در ویرایشگر Apps Script ذخیره کنید.
2. روی منوی قالب‌های سریع > قالب‌بندی سرصفحه ردیف کلیک کنید.

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

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

5. سرصفحه ستون را قالب بندی کنید

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

• پررنگ کردن متن
• کج کردن متن
• افزودن مرزهای سلولی
• درج لینک ها، با استفاده از محتویات ستون URL . هنگامی که این پیوندها را اضافه کردید، می توانید ستون url را حذف کنید تا به تمیز کردن برگه کمک کند.

در مرحله بعد، تابع formatColumnHeader() را برای اعمال این تغییرات در ستون اول برگه پیاده سازی خواهید کرد. برای کمک به آسان‌تر خواندن کد، دو تابع کمکی را نیز پیاده‌سازی خواهید کرد.

پیاده سازی

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

1. در ویرایشگر Apps Script، تابع formatColumnHeader() زیر را به انتهای پروژه اسکریپت خود اضافه کنید:

/**
 * Formats the column header of the active sheet.
 */ 
function formatColumnHeader() {  
  var sheet = SpreadsheetApp.getActiveSheet();
  
  // Get total number of rows in data range, not including
  // the header row.
  var numRows = sheet.getDataRange().getLastRow() - 1;
  
  // Get the range of the column header.
  var columnHeaderRange = sheet.getRange(2, 1, numRows, 1);
  
  // Apply text formatting and add borders.
  columnHeaderRange
    .setFontWeight('bold')
    .setFontStyle('italic')
    .setBorder(
      true, true, true, true, null, null,
      null,
      SpreadsheetApp.BorderStyle.SOLID_MEDIUM);
 
  // Call helper method to hyperlink the first column contents
  // to the url column contents.
  hyperlinkColumnHeaders_(columnHeaderRange, numRows); 
}

2. پس از تابع formatColumnHeader() توابع کمکی زیر را به انتهای پروژه اسکریپت خود اضافه کنید:

/**
 * Helper function that hyperlinks the column header with the
 * 'url' column contents. The function then removes the column.
 *
 * @param {object} headerRange The range of the column header
 *   to update.
 * @param {number} numRows The size of the column header.
 */
function hyperlinkColumnHeaders_(headerRange, numRows) {
  // Get header and url column indices.
  var headerColIndex = 1; 
  var urlColIndex = columnIndexOf_('url');  
  
  // Exit if the url column is missing.
  if(urlColIndex == -1)
    return; 
  
  // Get header and url cell values.
  var urlRange =
    headerRange.offset(0, urlColIndex - headerColIndex);
  var headerValues = headerRange.getValues();
  var urlValues = urlRange.getValues();
  
  // Updates header values to the hyperlinked header values.
  for(var row = 0; row < numRows; row++){
    headerValues[row][0] = '=HYPERLINK("' + urlValues[row]
      + '","' + headerValues[row] + '")';
  }
  headerRange.setValues(headerValues);
  
  // Delete the url column to clean up the sheet.
  SpreadsheetApp.getActiveSheet().deleteColumn(urlColIndex);
}

/**
 * Helper function that goes through the headers of all columns
 * and returns the index of the column with the specified name
 * in row 1. If a column with that name does not exist,
 * this function returns -1. If multiple columns have the same
 * name in row 1, the index of the first one discovered is
 * returned.
 * 
 * @param {string} colName The name to find in the column
 *   headers. 
 * @return The index of that column in the active sheet,
 *   or -1 if the name isn't found.
 */ 
function columnIndexOf_(colName) {
  // Get the current column names.
  var sheet = SpreadsheetApp.getActiveSheet();
  var columnHeaders =
    sheet.getRange(1, 1, 1, sheet.getLastColumn());
  var columnNames = columnHeaders.getValues();
  
  // Loops through every column and returns the column index
  // if the row 1 value of that column matches colName.
  for(var col = 1; col <= columnNames[0].length; col++)
  {
    if(columnNames[0][col-1] === colName)
      return col; 
  }

  // Returns -1 if a column named colName does not exist. 
  return -1; 
}

3. پروژه اسکریپت خود را ذخیره کنید

بررسی کد

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

formatColumnHeader()

همانطور که احتمالاً انتظار داشتید، چند خط اول این تابع متغیرهایی را تنظیم می کند که به صفحه و محدوده مورد علاقه ما اشاره می کند:

• برگه فعال در sheet ذخیره می شود.
• تعداد ردیف‌های سربرگ ستون محاسبه و در numRows ذخیره می‌شود. در اینجا کد یک را کم می کند تا تعداد ردیف شامل سرصفحه ستون نباشد: title .
• محدوده پوشش سر ستون در columnHeaderRange ذخیره می شود.

سپس کد مرزها و بولدینگ را در محدوده سرصفحه ستون اعمال می کند، درست مانند formatRowHeader() . در اینجا از Range.setFontStyle(fontStyle) نیز برای ایتالیک کردن متن استفاده می شود.

افزودن لینک‌ها به ستون هدر پیچیده‌تر است، بنابراین formatColumnHeader() hyperlinkColumnHeaders_(headerRange, numRows) را فراخوانی می‌کند. این به مرتب و خوانایی کد کمک می کند.

hyperlinkColumnHeaders_(headerRange, numRows)

این تابع کمکی ابتدا شاخص های ستون هدر (فرض شده اندیس 1) و ستون url را شناسایی می کند. columnIndexOf_('url') را برای بدست آوردن نمایه ستون url فرا می خواند. اگر ستون url پیدا نشد، روش بدون تغییر هیچ داده ای خارج می شود.

تابع یک محدوده جدید ( urlRange ) دریافت می کند که آدرس های اینترنتی مربوط به ردیف های ستون سرصفحه را پوشش می دهد. این کار با روش Range.offset(rowOffset, columnOffset) انجام می شود که تضمین می کند اندازه دو محدوده یکسان خواهد بود. سپس مقادیر در ستون headerColumn و url بازیابی می شوند ( headerValues ​​و urlValues ).

سپس این تابع روی هر مقدار سلول سرصفحه ستون حلقه می زند و آن را با فرمول =HYPERLINK() Sheets که با محتوای ستون سرصفحه و url ساخته شده است جایگزین می کند. سپس مقادیر هدر اصلاح شده با استفاده از Range.setValues(values) در برگه درج می شود.

در نهایت، برای کمک به تمیز نگه داشتن برگه و حذف اطلاعات اضافی، Sheet.deleteColumn(columnPosition) برای حذف ستون url فراخوانی می شود.

columnIndexOf_(colName)

این تابع کمکی فقط یک تابع ساده است که ردیف اول برگه را برای یک نام خاص جستجو می کند. سه خط اول از روش‌هایی استفاده می‌کنند که قبلاً دیده‌اید تا فهرستی از نام‌های سرصفحه ستون‌ها را از ردیف 1 صفحه‌گسترده دریافت کنید. این نام ها در متغیر ستونNames ذخیره می شوند.

سپس تابع هر نام را به ترتیب بررسی می کند. اگر نامی را پیدا کند که با نام مورد جستجو مطابقت داشته باشد، متوقف می شود و فهرست ستون را برمی گرداند. اگر ب��ون یافتن ��ام ��ه ��ن��های لیست نام برسد، -1 را برمی‌گرداند تا نشان دهد نام پیدا نشد.

نتایج

با انجام کارهای زیر می توانید عملکرد قالب بندی خود را در عمل مشاهده کنید:

1. اگر قبلاً این کار را نکرده‌اید، پروژه اسکریپت خود را در ویرایشگر Apps Script ذخیره کنید.
2. روی منوی قالب‌های سریع > قالب‌بندی سرصفحه ستون کلیک کنید.

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

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

6. مجموعه داده خود را قالب بندی کنید

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

• رنگ های متناوب پس زمینه ردیف (معروف به نواربندی )
• تغییر فرمت های تاریخ
• اعمال مرزها
• اندازه خودکار تمام ستون ها و ردیف ها

اکنون یک تابع formatDataset() و یک روش کمکی اضافی برای اعمال این فرمت ها در داده های برگه خود ایجاد خواهید کرد.

پیاده سازی

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

1. در ویرایشگر Apps Script، تابع formatDataset() زیر را به انتهای پروژه اسکریپت خود اضافه کنید:

/**
 * Formats the sheet data, excluding the header row and column.
 * Applies the border and banding, formats the 'release_date'
 * column, and autosizes the columns and rows.
 */
function formatDataset() {
  // Get the active sheet and data range.
  var sheet = SpreadsheetApp.getActiveSheet(); 
  var fullDataRange = sheet.getDataRange();

  // Apply row banding to the data, excluding the header
  // row and column. Only apply the banding if the range
  // doesn't already have banding set.
  var noHeadersRange = fullDataRange.offset(
    1, 1,
    fullDataRange.getNumRows() - 1,
    fullDataRange.getNumColumns() - 1);

  if (! noHeadersRange.getBandings()[0]) {
    // The range doesn't already have banding, so it's
    // safe to apply it.
    noHeadersRange.applyRowBanding(
      SpreadsheetApp.BandingTheme.LIGHT_GREY,
      false, false);
  }

  // Call a helper function to apply date formatting
  // to the column labeled 'release_date'.
  formatDates_( columnIndexOf_('release_date') );
  
  // Set a border around all the data, and resize the
  // columns and rows to fit.
  fullDataRange.setBorder(
    true, true, true, true, null, null,
    null,
    SpreadsheetApp.BorderStyle.SOLID_MEDIUM);

  sheet.autoResizeColumns(1, fullDataRange.getNumColumns());
  sheet.autoResizeRows(1, fullDataRange.getNumRows());
}

2. ��ابع کمکی زیر را در انتهای پروژه اسکریپت خود، بعد از تابع formatDataset() اضافه کنید:

/** 
 * Helper method that applies a
 * "Month Day, Year (Day of Week)" date format to the
 * indicated column in the active sheet. 
 *
 * @param {number} colIndex The index of the column
 *   to format.
 */ 
function formatDates_(colIndex) {
  // Exit if the given column index is -1, indicating
  // the column to format isn't present in the sheet.
  if (colIndex < 0)
    return; 

  // Set the date format for the date column, excluding
  // the header row.
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange(2, colIndex, sheet.getLastRow() - 1, 1)
    .setNumberFormat("mmmm dd, yyyy (dddd)");
}

3. پروژه اسکریپت خود را ذخیره کنید

بررسی کد

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

formatDataset()

این تابع از الگوی مشابه توابع قالب قبلی که قبلاً پیاده سازی کرده اید پیروی می کند. ابتدا، متغیرهایی را برای نگهداری ارجاعات به برگه فعال (برگ) و محدوده داده (fullDataRange) دریافت می کند.

دوم، از Range.offset(rowOffset, columnOffset, numRows, numColumns) برای ایجاد یک محدوده ( noHeadersRange ) استفاده می‌کند که تمام داده‌های صفحه را پوشش می‌دهد، به استثنای سرصفحه‌های ستون و ردیف. سپس کد تأیید می کند که آیا این محدوده جدید دارای باند بندی موجود است (با استفاده از Range.getBandings() ). این ضروری است زیرا اگر بخواهید باندبندی جدیدی را در جایی که وجود دارد اعمال کنید، اسکریپت Apps خطا می دهد. اگر باندبندی وجود نداشته باشد، تابع با استفاده از Range.applyRowBanding(bandingTheme, showHeader, showFooter) یک نوار خاکستری روشن اضافه می کند. در غیر این صورت، عملکرد ادامه می یابد.

مرحله بعدی تابع کمکی formatDates_(colIndex) را فراخوانی می کند تا تاریخ ها را در ستونی با عنوان ' release_date ' (در زیر توضیح داده شده) فرمت کند. ستون با استفاده از تابع کمکی columnIndexOf_(colName) که قبلاً پیاده‌سازی کرده‌اید مشخص می‌شود.

در نهایت، قالب‌بندی با افزودن یک حاشیه دیگر (مانند قبل) به پایان می‌رسد و به‌طور خودکار اندازه هر ستون و ردیف را تغییر می‌دهد تا با داده‌های موجود در آنها با استفاده از Sheet.autoResizeColumns(columnPosition) و Sheet.autoResizeColumns(columnPosition) کند.

formatDates_(colIndex)

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

ابتدا، تابع تأیید می کند که شاخص ستون ارائه شده معتبر است (یعنی 0 یا بیشتر). اگر نه، بدون انجام کاری برمی گردد. این بررسی از خطاهایی که ممکن است ایجاد شوند، برای مثال، اگر برگه دارای ستون ' release_date ' نباشد، جلوگیری می کند.

هنگامی که شاخص ستون اعتبار سنجی شد، تابع محدوده ای را که آن ستون را پوشش می دهد (به استثنای ردیف سرصفحه آن) دریافت می کند و از Range.setNumberFormat(numberFormat) برای اعمال قالب بندی استفاده می کند.

نتایج

با انجام کارهای زیر می توانید عملکرد قالب بندی خود را در عمل مشاهده کنید:

1. اگر قبلاً این کار را نکرده‌اید، پروژه اسکریپت خود را در ویرایشگر Apps Script ذخیره کنید.
2. روی آیتم منوی قالب‌های سریع > قالب‌بندی مجموعه داده کلیک کنید.

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

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

7. داده های API را واکشی و قالب بندی کنید

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

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

کد شما با API تماس می گیرد تا حجم زیادی از داده های JSON را دریافت کند، پاسخ را تجزیه کند، داده ها را در یک صفحه جدید قرار دهد و سپس برگه را فرمت کند.

پیاده سازی

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

اقدامات زیر را انجام دهید:

1. در ویرایشگر Apps Script، تابع onOpen() خود را در پروژه اسکریپت خود به‌روزرسانی کنید تا با موارد زیر مطابقت داشته باشد:

/**
 * A special function that runs when the spreadsheet is opened
 * or reloaded, used to add a custom menu to the spreadsheet.
 */
function onOpen() {
  // Get the Ui object.
  var ui = SpreadsheetApp.getUi();

  // Create and add a named menu and its items to the menu bar.
  ui.createMenu('Quick formats')
    .addItem('Format row header', 'formatRowHeader')
    .addItem('Format column header', 'formatColumnHeader')
    .addItem('Format dataset', 'formatDataset')
    .addSeparator()
    .addSubMenu(ui.createMenu('Create character sheet')
                .addItem('Episode IV', 'createPeopleSheetIV')
                .addItem('Episode V', 'createPeopleSheetV')
                .addItem('Episode VI', 'createPeopleSheetVI')
                )
    .addToUi();
}

2. پروژه اسکریپت خود را ذخیره کنید
3. در ویرایشگر اسکریپت، onOpen را از لیست توابع انتخاب کنید و روی Run کلیک کنید. این روی onOpen() اجرا می شود تا منوی صفحه گسترده را با گزینه های جدیدی که اضافه کرده اید بازسازی کند.
4. برای ایجاد یک فایل Apps Script، در کنار Files روی Add a file کلیک کنید > اسکریپت
5. نام اسکریپت جدید را "API" بگذارید و Enter را فشار دهید. (Apps Script به طور خودکار یک پسوند .gs را به نام فایل اسکریپت اضافه می کند.)
6. کد موجود در فایل API.gs جدید را با کد زیر جایگزین کنید:

/**
 * Wrapper function that passes arguments to create a
 * resource sheet describing the characters from Episode IV.
 */
function createPeopleSheetIV() {
  createResourceSheet_('characters', 1, "IV");
}

/**
 * Wrapper function that passes arguments to create a
 * resource sheet describing the characters from Episode V.
 */
function createPeopleSheetV() {
  createResourceSheet_('characters', 2, "V");
}

/**
 * Wrapper function that passes arguments to create a
 * resource sheet describing the characters from Episode VI.
 */
function createPeopleSheetVI() {
  createResourceSheet_('characters', 3, "VI");
}

/** 
 * Creates a formatted sheet filled with user-specified
 * information from the Star Wars API. If the sheet with
 * this data exists, the sheet is overwritten with the API
 * information.
 *
 * @param {string} resourceType The type of resource.
 * @param {number} idNumber The identification number of the film.
 * @param {number} episodeNumber The Star Wars film episode number.
 *   This is only used in the sheet name.
 */
function createResourceSheet_(
    resourceType, idNumber, episodeNumber) { 
  
  // Fetch the basic film data from the API. 
  var filmData = fetchApiResourceObject_(
      "https://swapi.dev/api/films/" + idNumber);

  // Extract the API URLs for each resource so the code can
  // call the API to get more data about each individually.
  var resourceUrls = filmData[resourceType];
  
  // Fetch each resource from the API individually and push
  // them into a new object list.
  var resourceDataList = []; 
  for(var i = 0; i < resourceUrls.length; i++){
    resourceDataList.push(
      fetchApiResourceObject_(resourceUrls[i])
    ); 
  } 
  
  // Get the keys used to reference each part of data within
  // the resources. The keys are assumed to be identical for
  // each object since they're all the same resource type.
  var resourceObjectKeys = Object.keys(resourceDataList[0]);
  
  // Create the sheet with the appropriate name. It
  // automatically becomes the active sheet when it's created.
  var resourceSheet = createNewSheet_(
      "Episode " + episodeNumber + " " + resourceType);
  
  // Add the API data to the new sheet, using each object
  // key as a column header. 
  fillSheetWithData_(resourceSheet, resourceObjectKeys, resourceDataList);
  
  // Format the new sheet using the same styles the
  // 'Quick Formats' menu items apply. These methods all
  // act on the active sheet, which is the one just created.
  formatRowHeader();
  formatColumnHeader();   
  formatDataset();

}

6. توابع کمکی زیر را به انتهای فایل پروژه اسکریپت API.gs اضافه کنید:

/** 
 * Helper function that retrieves a JSON object containing a
 * response from a public API.
 *
 * @param {string} url The URL of the API object being fetched.
 * @return {object} resourceObject The JSON object fetched
 *   from the URL request to the API.
 */
function fetchApiResourceObject_(url) {
  // Make request to API and get response.
  var response =
    UrlFetchApp.fetch(url, {'muteHttpExceptions': true});
  
  // Parse and return the response as a JSON object.
  var json = response.getContentText();
  var responseObject = JSON.parse(json); 
  return responseObject; 
}

/** 
 * Helper function that creates a sheet or returns an existing
 * sheet with the same name.
 *
 * @param {string} name The name of the sheet.
 * @return {object} The created or existing sheet
 *   of the same name. This sheet becomes active.
 */ 
function createNewSheet_(name) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  
  // Returns an existing sheet if it has the specified
  // name. Activates the sheet before returning.
  var sheet = ss.getSheetByName(name);
  if (sheet) {
    return sheet.activate();
  }
  
  // Otherwise it makes a sheet, set its name, and returns it.
  // New sheets created this way automatically become the active
  // sheet.
  sheet = ss.insertSheet(name); 
  return sheet; 
}

/** 
 * Helper function that adds API data to the sheet.
 * Each object key is used as a column header in the new sheet.
 *
 * @param {object} resourceSheet The sheet object being modified.
 * @param {object} objectKeys The list of keys for the resources.
 * @param {object} resourceDataList The list of API
 *   resource objects containing data to add to the sheet.
 */
function fillSheetWithData_(
    resourceSheet, objectKeys, resourceDataList) {
  // Set the dimensions of the data range being added to the sheet.
  var numRows = resourceDataList.length;
  var numColumns = objectKeys.length;
  
  // Get the resource range and associated values array. Add an
  // extra row for the column headers.
  var resourceRange =
    resourceSheet.getRange(1, 1, numRows + 1, numColumns);
  var resourceValues = resourceRange.getValues(); 
  
  // Loop over each key value and resource, extracting data to
  // place in the 2D resourceValues array.
  for (var column = 0; column < numColumns; column++) {

    // Set the column header.
    var columnHeader = objectKeys[column];
    resourceValues[0][column] = columnHeader;
    
    // Read and set each row in this column.
    for (var row = 1; row < numRows + 1; row++) {
      var resource = resourceDataList[row - 1];
      var value = resource[columnHeader];
      resourceValues[row][column] = value;
    }
  }
  
  // Remove any existing data in the sheet and set the new values.
  resourceSheet.clear()
  resourceRange.setValues(resourceValues);
}

7. پروژه اسکریپت خود را ذخیره کنید

بررسی کد

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

onOpen()

در اینجا شما چند آیتم منو را به منوی Quick formats خود اضافه کرده اید. شما یک خط جداکننده تنظیم کرده اید و سپس از روش Menu.addSubMenu(menu) برای ایجاد یک ساختار منوی تودرتو با سه آیتم جدید استفاده کرده اید. موارد جدید با روش Menu.addItem(caption, functionName) اضافه می شوند.

عملکردهای لفاف دار

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

نوشتن یک تابع برای ایجاد برگه راحت است و تابع یک پارامتر برای تعیین اینکه چه فیلمی باید استفاده شود را بپذیرد. با این حال، Menu.addItem(caption, functionName) به شما اجازه نمی دهد پارامترهایی را هنگام فراخوانی توسط منو به آن ارسال کنید. بنابراین، چگونه از نوشتن یک کد سه بار اجتناب کنید؟

پاسخ توابع wrapper است . اینها توابع سبک وزنی هستند که می توانید آنها ��ا فراخوانی کنید و فوراً تابع دیگری را با مجموعه پارامترهای خاص فراخوانی کنید.

در اینجا، کد از سه تابع wrapper استفاده می کند: createPeopleSheetIV() , createPeopleSheetV() و createPeopleSheetVI() . آیتم های منو به این توابع مرتبط هستند. وقتی روی آیتم منو کلیک می‌شود، تابع wrapper اجرا می‌شود و فوراً تابع سازنده برگه اصلی createResourceSheet_(resourceType, idNumber, episodeNumber) را اجرا می‌کند و پارامترهای مناسب برای آیتم منو را ارسال می‌کند. در این مورد، به این معنی است که از تابع سازنده برگه بخواهید یک صفحه پر از داده های شخصیت اصلی یکی از فیلم های جنگ ستارگان ایجاد کند.

createResourceSheet_(resourceType, idNumber, episodeNumber)

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

ابتدا، این تابع از fetchApiResourceObject_(url) برای درخواست از API برای بازیابی اطلاعات اولیه فیلم استفاده می کند. پاسخ API شامل مجموعه‌ای از آدرس‌های اینترنتی است که کد می‌تواند برای دریافت جزئیات بیشتر درباره افراد خاص (که در اینجا به عنوان منابع شناخته می‌شوند) از فیلم‌ها استفاده کند. کد همه را در آرایه resourceUrls جمع آوری می کند.

در مرحله بعد، کد از fetchApiResourceObject_(url) مکرراً برای فراخوانی API برای هر URL منبع در resourceUrls کند. نتایج در آرایه resourceDataList ذخیره می شوند. هر عنصر این آرایه یک شی است که شخصیت متفاوتی از فیلم را توصیف می کند.

اشیاء داده منبع دارای چندین کلید مشترک هستند که به اطلاعات مربوط به آن کاراکتر نگاشت می شوند. به عنوان مثال، کلید " name " با نام شخصیت فیلم مطابقت دارد. ما فرض می کنیم که کلیدهای هر شی داده منبع همگی یکسان هستند، زیرا قرار است از ساختارهای شی مشترک استفاده کنند. لیست کلیدها بعدا مورد نیاز است، بنابراین کد با استفاده از متد () Object.keys ، فهرست کلیدها را در resourceObjectKeys ذخیره می کند.

در مرحله بعد، تابع سازنده، تابع createNewSheet_(name) فراخوانی می کند تا برگه ای را ایجاد کند که داده های جدید در آن قرار می گیرند. فراخوانی این تابع کمکی برگه جدید را نیز فعال می کند.

پس از ایجاد شیت، تابع کمکی fillSheetWithData_(resourceSheet, objectKeys, resourceDataList) فراخوانی می شود تا تمام داده های API را به برگه اضافه کند.

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

fetchApiResourceObject_(url)

این تابع کمکی مشابه تابع کمکی fetchBookData_(ISBN) است که در لبه کد قبلی کار با داده استفاده شده است. URL داده شده را می گیرد و از روش UrlFetchApp.fetch(url, params) برای دریافت پاسخ استفاده می کند. سپس پاسخ با استفاده از روش های HTTPResponse.getContextText() و JavaScript JSON.parse(json) در یک شی JSON تجزیه می شود. سپس شیء JSON بدست آمده برگردانده می شود.

createNewSheet_(name)

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

اگر برگه وجود نداشته باشد، تابع آن را با Spreadsheet.insertSheet(sheetName) ایجاد می کند، آن را فعال می کند و برگه جدید را برمی گرداند.

fillSheetWithData_(resourceSheet, objectKeys, resourceDataList)

این تابع کمکی مسئول پر کردن برگه جدید با داده های API است. برگه جدید، لیست کلیدهای شی و فهرست اشیاء منبع API را به عنوان پارامتر می گیرد. هر کلید شیء نمایانگر یک ستون در شیت جدید و هر شی منبع نمایانگر یک ردیف است.

ابتدا، تابع تعداد سطرها و ستون های مورد نیاز برای ارائه داده های API جدید را محاسبه می کند. این به ترتیب اندازه فهرست منابع و کلیدها است. سپس تابع یک محدوده خروجی ( resourceRange ) را تعریف می کند که در آن داده ها قرار می گیرند و یک ردیف اضافی برای نگه داشتن سرصفحه های ستون اضافه می کند. متغیر resourceValues ​​دارای یک آرایه مقادیر دو بعدی است که از resourceRange استخراج شده است.

سپس این تابع روی هر کلید شی در لیست objectKeys حلقه می زند. کلید به عنوان سربرگ ستون تنظیم می شود و سپس یک حلقه دوم از هر شی منبع عبور می کند. برای هر جفت (ردیف، ستونی)، اطلاعات API مربوطه در عنصر resourceValues[row][column] کپی می‌شود.

پس از پر شدن resourceValues ، صفحه مقصد با استفاده از Sheet.clear() پاک می‌شود، در صورتی که حاوی داده‌هایی از کلیک‌های قبلی آیتم‌های منو باشد. در نهایت مقادیر جدید در برگه نوشته می شود.

نتایج

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

1. اگر قبلاً این کار را نکرده‌اید، پروژه اسکریپت خود را در ویرایشگر Apps Script ذخیره کنید.
2. روی قالب‌های سریع > ایجاد برگه کاراکتر > مورد منوی قسمت چهارم کلیک کنید.

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

اکنون کدی را برای وارد کردن داده‌ها به کاربرگ‌نگار و قالب‌بندی خودکار آن نوشته‌اید.

8. نتیجه گیری

بابت تکمیل این کد لبه تبریک می گویم. شما برخی از گزینه‌های قالب‌بندی Sheets را که می‌توانید در پروژه‌های Apps Script خود بگنجانید، مشاهده کرده‌اید، و یک برنامه کاربردی موثر ساخته‌اید که مجموعه داده‌های API بزرگ را وارد و قالب‌بندی می‌کند.

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

• نحوه اعمال عملیات قالب بندی برگه های مختلف با Apps Script.
• نحوه ایجاد زیر منوها با تابع onOpen() .
• نحوه قالب‌بندی فهرست واکشی شده از اشیاء JSON در یک برگه داده جدید با Apps Script.

بعدش چیه

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

آزمایشگاه کد بعدی را در نمودار پیدا کنید و داده ها را در اسلایدها ارائه دهید.