أساسيات "برمجة تطبيقات Google" مع "جداول بيانات Google" #4: تنسيق البيانات
لمحة عن هذا الدرس التطبيقي حول الترميز
1. مقدمة
مرحبًا بك في الجزء الرابع من قائمة تشغيل "أساسيات برمجة تطبيقات Google" مع برنامج codelab "جداول بيانات Google".
من خلال إكمال هذا الدرس التطبيقي حول الترميز، يمكنك التعرّف على كيفية تنسيق بيانات جدول البيانات في "برمجة التطبيقات"، وكيفية كتابة دوال لإنشاء جداول بيانات منظَّمة ومليئة بالبيانات المنسَّقة التي يتم استرجاعها من واجهة برمجة تطبيقات عامة.
أهداف الدورة التعليمية
- كيفية تطبيق عمليات التنسيق المختلفة في "جداول بيانات Google" باستخدام Apps Script
- كيفية تحويل قائمة بعناصر JSON وسماتها إلى ورقة بيانات منظَّمة باستخدام "برمجة تطبيقات Google"
قبل البدء
هذا هو الدرس العملي الرابع في قائمة تشغيل "أساسيات برمجة تطبيقات Google باستخدام "جداول بيانات Google". قبل البدء في هذا الدرس العملي، احرص على إكمال الدروس العملية السابقة:
المتطلبات
- فهم مواضيع "برمجة تطبيقات Google" الأساسية التي تم استكشافها في دروس البرمجة السابقة ضمن قائمة التشغيل هذه
- معرفة أساسية بأداة تعديل رموز برمجة التطبيقات
- معرفة أساسية بـ جداول بيانات Google
- إمكانية قراءة ترميز A1 في "جداول بيانات Google"
- معرفة أساسية بلغة JavaScript وفئة
String
2. إعداد
قبل المتابعة، تحتاج إلى جدول بيانات يتضمّن بعض البيانات. كما في السابق، قدّمنا ورقة بيانات يمكنك نسخها لهذه التمارين. يُرجى تنفيذ ما يلي:
- انقر على هذا الرابط لنسخ ورقة البيانات، ثم انقر على إنشاء نسخة. يتم وضع جدول البيانات الجديد في مجلد Google Drive وتسميته "نسخة من تنسيق البيانات".
- انقر على عنوان جدول البيانات وغيِّره من "نسخة من تنسيق البيانات" إلى "تنسيق البيانات". يجب أن يبدو جدول البيانات على النحو التالي، مع تضمين بعض المعلومات الأساسية عن أول ثلاثة أفلام من سلسلة Star Wars:
- انقر على الإضافات > برمجة تطبيقات لفتح "محرّر النصوص البرمجية".
- انقر على عنوان مشروع Apps Script وغيِّره من "مشروع بلا عنوان" إلى "تنسيق البيانات". انقر على إعادة تسمية لحفظ تغيير العنوان.
باستخدام جدول البيانات والمشروع هذا، ستكون جاهزًا لبدء الدرس التطبيقي حول الترميز. انتقِل إلى القسم التالي للبدء في التعرّف على التنسيق الأساسي في Apps Script.
3. إنشاء قائمة مخصّصة
يمكنك تطبيق العديد من طرق التنسيق الأساسية في "برنامج Apps Script" على "جداول بيانات Google". توضّح التمارين التالية بعض طرق تنسيق البيانات. للمساعدة في التحكّم في إجراءات التنسيق، لننشئ قائمة مخصّصة تتضمّن العناصر التي تحتاج إليها. تمت مناقشة عملية إنشاء قوائم مخصّصة في الدرس التطبيقي حول الترميز العمل مع البيانات، ولكن سنلخّصها هنا مرة أخرى.
التنفيذ
لننشئ قائمة مخصّصة.
- في "محرِّر برمجة التطبيقات"، استبدِل الرمز في مشروع النص البرمجي بما يلي:
/**
* 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();
}
- احفظ مشروع النص البرمجي.
- في محرّر النصوص البرمجية، اختَر
onOpenمن قائمة الدوال وانقر على تشغيل. يؤدي ذلك إلى تشغيل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 إلى محاولة "جداول بيانات Google" تنفيذ الدالة formatRowHeader() (التي لم يتم إنشاؤها بعد).
النتائج
في جدول البيانات، انقر على قائمة Quick formats لعرض عناصر القائمة الجديدة:
سيؤدي النقر على هذه العناصر إلى حدوث خطأ لأنّك لم تنفّذ وظائفها المقابلة، لذا لننفّذها في الخطوة التالية.
4. تنسيق صف العنوان
تحتوي مجموعات البيانات في جداول البيانات غالبًا على صفوف عناوين لتحديد البيانات في كل عمود. من المستحسن تنسيق صفوف العناوين لفصلها بصريًا عن بقية البيانات في جدول البيانات.
في الدرس العملي الأول، أنشأتَ وحدة ماكرو للعنوان وعدّلتَ الرمز الخاص بها. في هذا القسم، ستنسّق صف رأس من البداية باستخدام "برمجة تطبيقات Google". سيؤدي صف العنوان الذي ستنشئه إلى جعل نص العنوان غامقًا، وتلوين الخلفية باللون الأزرق الداكن المخضر، وتلوين النص باللون الأبيض، وإضافة بعض خطوط الحدود الصلبة.
التنفيذ
لتنفيذ عملية التنسيق، ستستخدم طرق خدمة جداول البيانات نفسها التي استخدمتها من قبل، ولكنك ستستخدم الآن أيضًا بعض طرق التنسيق الخاصة بالخدمة. يُرجى تنفيذ ما يلي:
- في "محرِّر برمجة التطبيقات"، أضِف الدالة التالية إلى نهاية مشروع النص البرمجي:
/**
* 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);
}
- احفظ مشروع النص البرمجي.
مراجعة الرموز البرمجية
وكما هو الحال مع العديد من مهام التنسيق، فإنّ رمز 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) خدمة Apps Script إلى تجنُّب تغيير أي خطوط حدود ضمن النطاق المحدّد. تشير المَعلمة السابعة (null) إلى أنّ لون الحدّ يجب أن يكون أسود تلقائيًا. أخيرًا، تحدّد المَعلمة الأخيرة نوع نمط الحدود المطلوب استخدامه، ويتم اختيارها من الخيارات التي يوفّرها SpreadsheetApp.BorderStyle.
النتائج
يمكنك الاطّلاع على دالة التنسيق أثناء عملها باتّباع الخطوات التالية:
- إذا لم يسبق لك ذلك، احفظ مشروع النص البرمجي في "محرِّر برمجة التطبيقات".
- انقر على عنصر القائمة التنسيقات السريعة > تنسيق عنوان الصف.
يجب أن تبدو النتائج على النحو التالي:
لقد أتممت الآن مهمة تنسيق بشكل آلي. يطبّق القسم التالي الأسلوب نفسه لإنشاء نمط تنسيق مختلف لعناوين الأعمدة.
5. تنسيق عنوان عمود
إذا كان بإمكانك إنشاء عنوان صف مخصّص، يمكنك إنشاء عنوان عمود أيضًا. تزيد عناوين الأعمدة من إمكانية قراءة مجموعات بيانات معيّنة. على سبيل المثال، يمكن تحسين عمود العناوين في جدول البيانات هذا باستخدام خيارات التنسيق التالية:
- تغميق النص
- استخدام الخط المائل للنص
- إضافة حدود الخلايا
- إدراج روابط تشعّبية باستخدام محتوى العمود url بعد إضافة هذه الروابط التشعبية، يمكنك إزالة عمود عنوان URL للمساعدة في ترتيب جدول البيانات.
بعد ذلك، ستنفّذ الدالة formatColumnHeader() لتطبيق هذه التغييرات على العمود الأول في ورقة البيانات. للمساعدة في تسهيل قراءة الرمز البرمجي، ستنفّذ أيضًا دالتَين مساعدتَين.
التنفيذ
كما في السابق، عليك إضافة دالة لأتمتة تنسيق عناوين الأعمدة. يُرجى تنفيذ ما يلي:
- في محرِّر "برمجة تطبيقات Google"، أضِف الدالة
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);
}
- أضِف دوال المساعدة التالية إلى نهاية مشروع النص البرمجي، بعد الدالة
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;
}
- احفظ مشروع النص البرمجي.
مراجعة الرموز البرمجية
لنراجع التعليمات البرمجية في كل من هذه الدوال الثلاث بشكل منفصل:
formatColumnHeader()
كما هو متوقّع، تحدّد الأسطر القليلة الأولى من هذه الدالة متغيرات تشير إلى ورقة البيانات والنطاق الذي يهمّنا:
- يتم تخزين الورقة النشطة في
sheet. - يتم احتساب عدد الصفوف في عنوان العمود وحفظه في
numRows. يطرح الرمز هنا الرقم واحد حتى لا يتضمّن عدد الصفوف عنوان العمود:title. - يتم تخزين النطاق الذي يغطي عنوان العمود في
columnHeaderRange.
يطبّق الرمز بعد ذلك الحدود والتنسيق الغامق على نطاق عناوين الأعمدة، تمامًا كما في formatRowHeader(). هنا، يتم استخدام Range.setFontStyle(fontStyle) أيضًا لجعل النص مائلاً.
تتطلّب إضافة الروابط التشعبية إلى عمود العناوين خطوات أكثر تعقيدًا، لذا تستدعي الدالة formatColumnHeader() الدالة hyperlinkColumnHeaders_(headerRange, numRows) لتنفيذ المهمة. يساعد ذلك في الحفاظ على ترتيب التعليمات البرمجية وسهولة قراءتها.
hyperlinkColumnHeaders_(headerRange, numRows)
تحدّد دالة المساعدة هذه أولاً فهارس أعمدة العنوان (يُفترض أن يكون الفهرس 1) وعمود url. يتم استدعاء columnIndexOf_('url') للحصول على فهرس عمود عنوان URL. إذا لم يتم العثور على عمود url، سيتم إنهاء الطريقة بدون تعديل أي بيانات.
تحصل الدالة على نطاق جديد (urlRange) يغطي عناوين URL التي تتوافق مع صفوف عمود العنوان. يتم ذلك باستخدام الطريقة Range.offset(rowOffset, columnOffset)، التي تضمن أن يكون النطاقان متساويَين في الحجم. بعد ذلك، يتم استرداد القيم في كلّ من العمود headerColumn والعمود url (headerValues وurlValues).
بعد ذلك، تتكرّر الدالة على قيمة كل خلية من خلايا عناوين الأعمدة وتستبدلها بصيغة =HYPERLINK() في "جداول بيانات Google" تم إنشاؤها باستخدام العنوان ومحتوى العمود url. يتم بعد ذلك إدراج قيم العناوين المعدَّلة في ورقة البيانات باستخدام Range.setValues(values).
أخيرًا، للمساعدة في الحفاظ على نظافة الورقة وإزالة المعلومات المكرّرة، يتم استدعاء Sheet.deleteColumn(columnPosition) لإزالة العمود url.
columnIndexOf_(colName)
دالة المساعدة هذه هي مجرد دالة أداة بسيطة تبحث في الصف الأول من ورقة البيانات عن اسم معيّن. تستخدم الأسطر الثلاثة الأولى طرقًا سبق أن رأيتها للحصول على قائمة بأسماء عناوين الأعمدة من الصف 1 في جدول البيانات. يتم تخزين هذه الأسماء في المتغيّر columnNames.
بعد ذلك، تراجع الدالة كل اسم بالترتيب. إذا عثر على عمود يطابق الاسم الذي يتم البحث عنه، يتوقف ويعرض فهرس العمود. إذا وصل إلى نهاية قائمة الأسماء بدون العثور على الاسم، سيعرض القيمة -1 للإشارة إلى أنّه لم يتم العثور على الاسم.
النتائج
يمكنك الاطّلاع على دالة التنسيق أثناء عملها باتّباع الخطوات التالية:
- إذا لم يسبق لك ذلك، احفظ مشروع النص البرمجي في "محرِّر برمجة التطبيقات".
- انقر على عنصر القائمة التنسيقات السريعة > تنسيق عنوان العمود.
يجب أن تبدو النتائج على النحو التالي:
لقد أتممت الآن مهمة تنسيق أخرى. بعد تنسيق عناوين الأعمدة والصفوف، يوضّح القسم التالي كيفية تنسيق البيانات.
6. تنسيق مجموعة البيانات
بعد أن أضفت العناوين، لننشئ دالة تنسّق بقية البيانات في ورقة البيانات. سنستخدم خيارات التنسيق التالية:
- ألوان خلفية الصفوف البديلة (المعروفة باسم التخطيط)
- تغيير تنسيقات التاريخ
- تطبيق الحدود
- ضبط الحجم التلقائي لكل الأعمدة والصفوف
ستنشئ الآن دالة formatDataset() وطريقة مساعدة إضافية لتطبيق هذه التنسيقات على بيانات جدولك.
التنفيذ
كما في السابق، أضِف دالة لأتمتة عملية تنسيق البيانات. يُرجى تنفيذ ما يلي:
- في محرِّر "برمجة تطبيقات Google"، أضِف الدالة
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());
}
- أضِف دالة المساعدة التالية في نهاية مشروع النص البرمجي، بعد الدالة
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)");
}
- احفظ مشروع النص البرمجي.
مراجعة الرموز البرمجية
لنراجع الرمز في كلّ من هاتين الدالتين بشكل منفصل:
formatDataset()
تتّبع هذه الدالة نمطًا مشابهًا لدوال التنسيق السابقة التي سبق لك تنفيذها. أولاً، يتم الحصول على متغيرات للاحتفاظ بمراجع إلى الورقة النشطة (sheet) ونطاق البيانات (fullDataRange).
ثانيًا، تستخدم الدالة طريقة Range.offset(rowOffset, columnOffset, numRows, numColumns) لإنشاء نطاق (noHeadersRange) يغطي جميع البيانات في ورقة العمل، باستثناء عناوين الأعمدة والصفوف. بعد ذلك، يتحقّق الرمز مما إذا كان هذا النطاق الجديد يتضمّن نطاقات حالية (باستخدام Range.getBandings()). هذا الإجراء ضروري لأنّ "برمجة تطبيقات Google" تعرض خطأ إذا حاولت تطبيق نطاقات جديدة في حال توفّر نطاقات حالية. إذا لم يكن هناك تدرّج، تضيف الدالة تدرّجًا رماديًا فاتحًا باستخدام 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) لتطبيق التنسيق.
النتائج
يمكنك الاطّلاع على دالة التنسيق أثناء عملها باتّباع الخطوات التالية:
- إذا لم يسبق لك ذلك، احفظ مشروع النص البرمجي في "محرِّر برمجة التطبيقات".
- انقر على عنصر القائمة التنسيقات السريعة > تنسيق مجموعة البيانات.
يجب أن تبدو النتائج على النحو التالي:
لقد أتممت مهمة تنسيق أخرى بشكل آلي. بعد أن أصبحت أوامر التنسيق هذه متاحة، لنضِف المزيد من البيانات لتطبيقها عليها.
7. جلب بيانات واجهة برمجة التطبيقات وتنسيقها
حتى الآن في هذا الدرس التطبيقي حول الترميز، رأيت كيف يمكنك استخدام "برمجة تطبيقات Google" كوسيلة بديلة لتنسيق جدول البيانات. بعد ذلك، ستكتب رمزًا برمجيًا يستخرج البيانات من واجهة برمجة تطبيقات عامة ويدرجها في جدول البيانات وينسّقها لتكون قابلة للقراءة.
في الدرس التطبيقي السابق، تعلّمت كيفية استرداد البيانات من واجهة برمجة تطبيقات. ستستخدم التقنيات نفسها هنا. في هذا التمرين، سنستخدم واجهة برمجة التطبيقات العامة Star Wars API (SWAPI) لملء جدول البيانات. على وجه التحديد، ستستخدم واجهة برمجة التطبيقات للحصول على معلومات حول الشخصيات الرئيسية التي تظهر في أفلام Star Wars الثلاثة الأصلية.
سيطلب الرمز البرمجي من واجهة برمجة التطبيقات الحصول على كمية كبيرة من بيانات JSON، وتحليل الاستجابة، ووضع البيانات في ورقة جديدة، ثم تنسيق الورقة.
التنفيذ
في هذا القسم، ستضيف بعض عناصر القائمة الإضافية. يطلب كل عنصر من عناصر القائمة تنفيذ نص برمجي لتغليف البيانات ينقل المتغيّرات الخاصة بالعنصر إلى الدالة الرئيسية (createResourceSheet_()). عليك تنفيذ هذه الدالة وثلاث دوال مساعدة إضافية. كما في السابق، تساعد الدوال المساعدة في عزل الأجزاء المقسّمة منطقيًا من المهمة وتساعد في الحفاظ على قابلية قراءة الرمز.
اتّخِذ الإجراءات التالية:
- في "محرِّر برمجة التطبيقات"، عدِّل الدالة
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();
}
- احفظ مشروع النص البرمجي.
- في محرّر النصوص البرمجية، اختَر
onOpenمن قائمة الدوال وانقر على تشغيل. يؤدي ذلك إلى تشغيلonOpen()لإعادة إنشاء قائمة جدول البيانات باستخدام الخيارات الجديدة التي أضفتها. - لإنشاء ملف "برمجة تطبيقات Google"، انقر على "إضافة ملف"
> نص برمجي بجانب الملفات. - أدخِل اسمًا للبرنامج النصي الجديد، مثل "API"، ثم اضغط على Enter. (تضيف "برمجة التطبيقات" تلقائيًا اللاحقة
.gsإلى اسم ملف البرنامج النصي). - استبدِل الرمز في ملف 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();
}
- أضِف دالتَي المساعدة التاليتَين إلى نهاية ملف مشروع النص البرمجي 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);
}
- احفظ مشروع النص البرمجي.
مراجعة الرموز البرمجية
لقد أضفت للتو الكثير من الرموز. لنستعرض كل دالة على حدة لفهم طريقة عملها:
onOpen()
هنا، أضفت بعض عناصر القائمة إلى قائمة Quick formats. لقد ضبطت خط فاصل ثم استخدمت الطريقة Menu.addSubMenu(menu) لإنشاء بنية قائمة متداخلة تتضمّن ثلاثة عناصر جديدة. تتم إضافة العناصر الجديدة باستخدام طريقة Menu.addItem(caption, functionName).
دوال التغليف
تنفّذ جميع عناصر القائمة المضافة إجراءً مشابهًا، وهو محاولة إنشاء ورقة تتضمّن بيانات تم استخراجها من SWAPI. الفرق الوحيد هو أنّ كلّ منهما يركّز على فيلم مختلف.
سيكون من الملائم كتابة دالة واحدة لإنشاء ورقة، وأن تقبل الدالة مَعلمة لتحديد الفيلم الذي سيتم استخدامه. ومع ذلك، لا تتيح لك الطريقة Menu.addItem(caption, functionName) تمرير المَعلمات إليها عند استدعائها من خلال القائمة. إذًا، كيف تتجنّب كتابة الرمز نفسه ثلاث مرات؟
الإجابة هي وظائف التغليف. هذه دوال بسيطة يمكنك طلبها، وهي بدورها تطلب على الفور دالة أخرى مع ضبط معلَمات محدّدة.
في هذا المثال، يستخدم الرمز البرمجي ثلاث دوال تغليف: createPeopleSheetIV() وcreatePeopleSheetV() وcreatePeopleSheetVI(). ترتبط عناصر القائمة بهذه الوظائف. عند النقر على أحد عناصر القائمة، يتم تنفيذ دالة التغليف واستدعاء دالة إنشاء الورقة الرئيسية createResourceSheet_(resourceType, idNumber, episodeNumber) على الفور، مع تمرير المَعلمات المناسبة لعنصر القائمة. في هذه الحالة، يعني ذلك أنّه يجب الطلب من دالة إنشاء جداول البيانات إنشاء جدول بيانات مليء ببيانات الشخصيات الرئيسية من أحد أفلام Star Wars.
createResourceSheet_(resourceType, idNumber, episodeNumber)
هذه هي وظيفة إنشاء ورقة البيانات الرئيسية لهذا التمرين. وبمساعدة بعض الدوال المساعدة، تحصل على بيانات واجهة برمجة التطبيقات، وتحلّلها، وتنشئ ورقة، وتكتب بيانات واجهة برمجة التطبيقات في الورقة، ثم تنسّق الورقة باستخدام الدوال التي أنشأتها في الأقسام السابقة. لنراجع التفاصيل:
أولاً، تستخدم الدالة fetchApiResourceObject_(url) لتقديم طلب إلى واجهة برمجة التطبيقات من أجل استرداد معلومات أساسية عن الفيلم. يتضمّن ردّ واجهة برمجة التطبيقات مجموعة من عناوين URL التي يمكن أن يستخدمها الرمز البرمجي للحصول على مزيد من التفاصيل حول أشخاص معيّنين (يُعرفون هنا باسم الموارد) من الأفلام. يجمع الرمز كل ذلك في مصفوفة resourceUrls.
بعد ذلك، يستخدم الرمز fetchApiResourceObject_(url) بشكل متكرّر لطلب بيانات من واجهة برمجة التطبيقات لكل عنوان URL خاص بمورد في resourceUrls. يتم تخزين النتائج في الصفيفة resourceDataList. كل عنصر من هذه المصفوفة هو كائن يصف شخصية مختلفة من الفيلم.
تحتوي عناصر بيانات الموارد على عدة مفاتيح شائعة ترتبط بمعلومات حول هذا الحرف. على سبيل المثال، يرتبط المفتاح name باسم الشخصية في الفيلم. نفترض أنّ مفاتيح كل عنصر بيانات مصدر متطابقة، لأنّها مصمّمة لاستخدام بُنى عناصر مشتركة. نحتاج إلى قائمة المفاتيح لاحقًا، لذا يخزّن الرمز قائمة المفاتيح في resourceObjectKeys باستخدام طريقة Object.keys() في JavaScript.
بعد ذلك، تستدعي دالة الإنشاء الدالة المساعدة createNewSheet_(name) لإنشاء ورقة سيتم وضع البيانات الجديدة فيها. يؤدي استدعاء دالة المساعد هذه أيضًا إلى تفعيل ورقة البيانات الجديدة.
بعد إنشاء ورقة البيانات، يتم استدعاء الدالة المساعدة fillSheetWithData_(resourceSheet, objectKeys, resourceDataList) لإضافة جميع بيانات واجهة برمجة التطبيقات إلى ورقة البيانات.
وأخيرًا، يتم استدعاء جميع دوال التنسيق التي أنشأتها سابقًا لتطبيق قواعد التنسيق نفسها على البيانات الجديدة. بما أنّ ورقة البيانات الجديدة هي النشطة، يمكن للرمز إعادة استخدام هذه الدوال بدون تعديل.
fetchApiResourceObject_(url)
تشبه دالة المساعدة هذه دالة المساعدة fetchBookData_(ISBN) المستخدَمة في الدرس العملي السابق التعامل مع البيانات. يأخذ هذا الرمز عنوان URL المحدّد ويستخدم طريقة UrlFetchApp.fetch(url, params) للحصول على استجابة. يتم بعد ذلك تحليل الردّ إلى عنصر JSON باستخدام الطريقتَين HTTPResponse.getContextText() وJSON.parse(json) في JavaScript. بعد ذلك، يتم عرض عنصر JSON الناتج.
createNewSheet_(name)
هذه الدالة المساعدة بسيطة إلى حدّ ما. يتحقّق أولاً مما إذا كانت ورقة بالاسم المحدّد متوفّرة في جدول البيانات. إذا كان الأمر كذلك، ستفعّل الدالة الورقة وتعرضها.
إذا لم تكن ورقة البيانات متوفّرة، تنشئ الدالة ورقة بيانات باستخدام Spreadsheet.insertSheet(sheetName) وتفعّلها وتعرض ورقة البيانات الجديدة.
fillSheetWithData_(resourceSheet, objectKeys, resourceDataList)
تتولّى دالة المساعدة هذه مهمة ملء ورقة البيانات الجديدة ببيانات واجهة برمجة التطبيقات. تتلقّى هذه الدالة ورقة البيانات الجديدة وقائمة مفاتيح العناصر وقائمة عناصر موارد واجهة برمجة التطبيقات كمَعلمات. يمثّل كل مفتاح عنصر عمودًا في الورقة الجديدة، ويمثّل كل عنصر مورد صفًا.
أولاً، تحسب الدالة عدد الصفوف والأعمدة اللازمة لعرض بيانات واجهة برمجة التطبيقات الجديدة. هذا هو حجم قائمة الموارد والمفاتيح، على التوالي. تحدّد الدالة بعد ذلك نطاق إخراج (resourceRange) سيتم وضع البيانات فيه، مع إضافة صف إضافي لاحتواء عناوين الأعمدة. يحتوي المتغيّر resourceValues على مصفوفة قيم ثنائية الأبعاد مستخرَجة من resourceRange.
بعد ذلك، تتكرّر الدالة على كل مفتاح كائن في القائمة objectKeys. يتم ضبط المفتاح كعنوان للعمود، ثم يتم تكرار العملية على كل عنصر من عناصر الموارد. بالنسبة إلى كل زوج (صف، عمود)، يتم نسخ معلومات واجهة برمجة التطبيقات المقابلة إلى العنصر resourceValues[row][column].
بعد ملء resourceValues، تتم إزالة البيانات من ورقة الوجهة باستخدام Sheet.clear() في حال كانت تحتوي على بيانات من نقرات سابقة على عناصر القائمة. وأخيرًا، يتم إدخال القيم الجديدة في جدول البيانات.
النتائج
يمكنك الاطّلاع على نتائج عملك من خلال اتّباع الخطوات التالية:
- إذا لم يسبق لك ذلك، احفظ مشروع النص البرمجي في "محرِّر برمجة التطبيقات".
- انقر على عنصر القائمة التنسيقات السريعة > إنشاء ورقة شخصية > الحلقة الرابعة.
يجب أن تبدو النتائج على النحو التالي:
لقد كتبت الآن رمزًا برمجيًا لاستيراد البيانات إلى "جداول بيانات Google" وتنسيقها تلقائيًا.
8. الخاتمة
تهانينا على إكمال هذا الدرس العملي. لقد اطّلعت على بعض خيارات التنسيق في "جداول بيانات Google" التي يمكنك تضمينها في مشاريعك في "برمجة تطبيقات Google"، وأنشأت تطبيقًا رائعًا يستورد مجموعة كبيرة من بيانات واجهة برمجة التطبيقات وينسّقها.
هل كان هذا الدرس التطبيقي العملي مفيدًا؟
ما تعلّمته
- كيفية تطبيق عمليات تنسيق مختلفة في "جداول بيانات Google" باستخدام "برمجة التطبيقات"
- كيفية إنشاء قوائم فرعية باستخدام الدالة
onOpen() - كيفية تنسيق قائمة تم استرجاعها من عناصر JSON في ورقة بيانات جديدة باستخدام "برمجة تطبيقات Google"
الخطوات التالية
يوضّح لك الدرس التطبيقي التالي في قائمة التشغيل هذه كيفية استخدام "برمجة تطبيقات Google" لعرض البيانات في رسم بياني وتصدير الرسوم البيانية إلى عروض "العروض التقديمية من Google".
يمكنك العثور على الدرس التطبيقي التالي على إنشاء رسوم بيانية وعرض البيانات في "العروض التقديمية من Google".