برنامج Google Apps Script التعليمي لإتقان وحدات الماكرو

يعرف المدراء التنفيذيون الفعالون أن الوقت هو العامل المحدد ... ربما لا شيء آخر يميز المديرين التنفيذيين الفعالين بقدر اهتمامهم بالوقت.

بيتر دراكر

الوقت هو أثمن مواردنا. نريد أن ننفقه على الأنشطة الأكثر تأثيرًا والأكثر تراكمًا للقيمة التي يمكننا القيام بها ، ليس فقط لأن تلك الأنشطة عادةً ما تحمل أعلى قيمة نقدية ، ولكن أيضًا لتحدي أنفسنا باستمرار وتحقيق أقصى قدر من الرضا الوظيفي لدينا.

هناك العديد من الطرق لتحسين كفاءتك وإنتاجيتك من أجل الاستفادة بشكل أفضل من وقتك. في مقال سابق حول جداول بيانات Google ، أوضحت كيف أن قوة التعاون عبر الإنترنت هي أحد مفاتيح زيادة الإنتاجية.

في مقال آخر ، أوضحت كيف يمكن أن تكون لغة برمجة Python أداة تحليل وأتمتة مهام قوية للمهنيين الماليين.

استلهامًا من هذا ، أريد الآن تقديم برنامج تعليمي لـ Google Apps Script. يتيح لك Google Apps Script كتابة نصوص وبرامج في JavaScript لأتمتة المنتجات في G Suite من Google والاتصال بها وتوسيعها ، بما في ذلك جداول البيانات والمستندات والعروض التقديمية و Gmail و Drive والعديد من المنتجات الأخرى. يتطلب تعلمها استثمارًا في الوقت ، كما هو الحال في كتابة النصوص ، لكن زيادة الإنتاجية والفرص الإضافية التي تفتحها تجعل الأمر يستحق ذلك.

كخطوة أولى ، لنبدأ بالنظر إلى مفهوم مألوف: وحدات الماكرو.

تسجيل واستخدام وحدات الماكرو في جداول بيانات جوجل

إذا كنت قد قضيت وقتًا طويلاً في العمل مع Excel ، فأنت ملزم بالتعامل مع واجهة ماكرو VBA (Visual Basic for Applications) في Excel في وقت ما. إما من خلال تسجيلها أو كتابتها بنفسك أو التخلص من تلك التي أنشأها الآخرون.

تعد وحدات الماكرو طريقة رائعة لأتمتة عمليات سير العمل المتكررة والمملة. قد لا تكون VBA لغة كرست الكثير من الوقت لتتعلمها ، ولكن جمالها هو أنك لم تكن بحاجة إليها حقًا لتصبح منتجًا وتنشئ وحدات الماكرو الخاصة بك. يمكنك ببساطة تسجيل سير العمل الذي تريد أتمتة ، ثم الانتقال إلى الكود وإجراء التغييرات الصغيرة اللازمة لجعل الماكرو أكثر عمومية.

في بعض النواحي ، يعد VBA درسًا رائعًا ومنسيًا حول كيفية تعريف الأشخاص غير التقنيين بالبرمجة . الطريقة التي يمكنك من خلالها تسجيل الإجراءات ثم ملء الشفرة للمراجعة لاحقًا هي بالفعل طريقة أكثر واقعية للتعلم من قراءة الكتب المدرسية ومشاهدة البرامج التعليمية بشكل سلبي.

تتوفر وظيفة التسجيل نفسها لـ VBA في جداول بيانات Google. فيما يلي مثال بسيط على كيفية استخدامه:

لنبدأ ببعض البيانات النموذجية ، باستخدام استعلام IMPORTHTML لاستيراد جدول. في هذا المثال ، قمت بتنزيل قائمة من ويكيبيديا لأكبر 15 صندوق تحوط في العالم. وغني عن البيان ، ولكن هذا مثال اعتباطي ؛ القصد هو أن تركز أكثر على التطبيق ، على الموضوع.

يتم بدء عملية تسجيل الماكرو عبر مسار القائمة التالي: الأدوات> وحدات الماكرو> تسجيل الماكرو.

ثم نتصفح الإجراءات (تنسيق الكمبيوتر الشخصي) التي نريد تسجيلها:

1. حدد الصف الأول
2. اضغط على Shift + Ctrl + Down Arrow لتحديد كل شيء
3. Ctrl + C للنسخ
4. Shift + F11 لإنشاء ورقة جديدة
5. امنح الورقة اسمًا جديدًا
6. اضغط على Shift + Control + V للصق القيم

بمجرد الانتهاء من ذلك ، اضغط على زر حفظ في نافذة الماكرو في الأسفل ، واعطها اسمًا واختصارًا اختياريًا للوحة المفاتيح.

بالنسبة للإجراءات الأبسط التي يمكن تكرارها بالضبط من خلال هذه الخطوات نفسها ، ستنتهي العملية هنا ويمكنك البدء في استخدام الماكرو الخاص بك على الفور. ومع ذلك ، في هذه الحالة ، نحتاج إلى إجراء بعض التغييرات قبل أن تصبح الشفرة قابلة للاستخدام. على سبيل المثال ، يجب أن يكون للورقة التي ننسخها اسمًا مختلفًا في كل مرة. دعونا نرى كيف نفعل ذلك.

كتابة برمجة تطبيقات Google يدويًا

الآن سنرى عظام Google Apps Script لأول مرة ؛ منصة البرمجة التي تعمل على خوادم Google. يعمل هذا على تمكين وحدات الماكرو الخاصة بنا ويسمح لك بإنشاء مهام سير عمل معقدة للغاية ، وحتى وظائف إضافية ، للتطبيقات نفسها. يمكن استخدامه لأتمتة ليس فقط عمل جدول البيانات ولكن في الواقع تقريبًا أي شيء مترابط داخل G Suite من Google.

لغة برمجة Apps Script هي JavaScript ، وهي إحدى لغات البرمجة الأكثر شيوعًا ، مما يعني أن هناك ثروة من الموارد المتاحة لأي شخص يرغب في التعلم على نطاق واسع. ولكن ، تمامًا كما هو الحال مع VBA ، لا تحتاج حقًا إلى: يمكنك استخدام وظيفة التسجيل نفسها والقيام ببساطة بالخطوات التي تريد أن تكون قادرًا على تكرارها تلقائيًا. قد يبدو الإخراج من التسجيل خامًا وعلى الأرجح لن يتطابق تمامًا مع ما تريد إنجازه ، ولكنه سيوفر نقطة انطلاق قوية بدرجة كافية. لنفعل ذلك الآن مع البرنامج النصي الذي سجلناه للتو.

عند التسجيل ، من المنطقي أن تكون حريصًا على عدم تسجيل أي خطوات إضافية عن طريق الصدفة لا ترغب في التقاطها في التسجيل النهائي ، ولكن يصعب أحيانًا تجنبها: شيء بسيط مثل اختيار خلية مختلفة قبل الضغط على زر الإيقاف سيتم التقاط زر التسجيل وتكرار ذلك لاحقًا في كل مرة تقوم فيها بتشغيل البرنامج النصي. ستكون الخطوة الأولى عند تحرير البرنامج النصي الخاص بنا هي تنظيفه وإزالة أي خطوات من هذا القبيل. دعنا نتعمق بالذهاب إلى Tools> Script Editor في قائمة الملف.

إذا كنت تعرف JavaScript ، فستتعرف على ذلك على الفور ، وقد تفاجأ أيضًا برؤية الكلمة الرئيسية "var" بدلاً من "let" أو "const" كما ترى في JavaScript الحديث. يعكس هذا حقيقة أن إصدار JavaScript في Apps Script قديم جدًا ولا يدعم العديد من ميزات اللغة الأكثر حداثة. في النهاية ، سأقدم حلاً بديلاً ولكن لأولئك الذين يرغبون في استخدام أحدث ميزات اللغة.

عند تشغيل البرنامج النصي في المرة الأولى ، سيطلب إذنًا ، وهذا أمر منطقي ، نظرًا لأن البرامج النصية يمكنها تعديل (وربما حذف) جميع بياناتك. من المرجح أن تتعرف على عملية التفويض من منتجات Google الأخرى.

الآن يمكننا البدء في تعديل الكود. التغييرات التي نحتاج إلى إجرائها طفيفة ، ولكن إذا قمت بذلك للمرة الأولى ، فقد لا يزال يتطلب بعض البحث السريع من خلال وثائق Sheets Apps Script و / أو بحث سريع عن مفهوم JavaScript ، مثل العمل مع التواريخ. هنا تأتي حقيقة أن JavaScript هي لغة منتشرة في متناول اليد: عادةً ما يمكن العثور على حل لأي مشكلة أو وظيفة تتبادر إلى الذهن بسرعة إذا قمت بصياغة مصطلح البحث الخاص بك بطريقة مباشرة.

التغييرات التي تم إجراؤها في هذا الإصدار من البرنامج النصي من الإصدار الأصلي المسجل هي أنه بدلاً من الاسم الثابت للورقة الجديدة التي نقوم بإنشائها ، نقوم الآن بتسميتها بتاريخ اليوم بدلاً من ذلك. بالإضافة إلى ذلك ، نقوم أيضًا بتغيير مسار النسخ في النهاية للإشارة إلى هذه الورقة الجديدة. توضح الصفوف الأربعة الأخيرة أيضًا كيفية إجراء بعض عمليات التنسيق ، مثل تغيير قيمة الخلية وتغيير حجم الأعمدة وإخفاء خطوط الشبكة.

 function createSnapshot() { var spreadsheet = SpreadsheetApp.getActive(); var date = new Date().toISOString().slice(0,10); var destination = spreadsheet.insertSheet(date); spreadsheet.getRange('HTML!A1:F1').activate(); spreadsheet.getSelection() .getNextDataRange(SpreadsheetApp.Direction.DOWN) .activate(); spreadsheet.getActiveRange() .copyTo(SpreadsheetApp.setActiveSheet(destination) .getRange(1,1), SpreadsheetApp.CopyPasteType.PASTE_VALUES,false); var sheet = spreadsheet.setActiveSheet(destination) sheet.getRange("D1").setValue("AUM $bn") sheet.setHiddenGridlines(true); sheet.getRange("A1:D1").setFontWeight("bold"); sheet.autoResizeColumns(1, 4); };

سيظهر تشغيل البرنامج النصي الآن أن الورقة الجديدة تمت تسميتها بالفعل بتاريخ اليوم وتحتوي على المعلومات المنسوخة كقيم (وليس صيغ) من الورقة الرئيسية.

يمكن الآن إضافة تصورات المخططات باستخدام نفس عملية التسجيل. لقد استخدمت هذا لإنشاء ثلاثة مخططات بسيطة.

سيبدو تنظيف الكود لكل منها على النحو التالي:

 function createColumnChart() { var spreadsheet = SpreadsheetApp.getActive(); spreadsheet.getRange('C1:D16').activate(); var sheet = spreadsheet.getActiveSheet(); chart = sheet.newChart() .asColumnChart() .addRange(spreadsheet.getRange('B1:D16')) .setMergeStrategy(Charts.ChartMergeStrategy.MERGE_COLUMNS) .setTransposeRowsAndColumns(false) .setNumHeaders(-1) .setHiddenDimensionStrategy(Charts.ChartHiddenDimensionStrategy.IGNORE_BOTH) .setOption('useFirstColumnAsDomain', true) .setOption('curveType', 'none') .setOption('domainAxis.direction', 1) .setOption('isStacked', 'absolute') .setOption('series.0.color', '#0b5394') .setOption('series.0.labelInLegend', 'AUM $bn') .setPosition(19, 6, 15, 5) .build(); sheet.insertChart(chart); };

مرة أخرى ، لا تقلق إذا بدت بعض الخيارات مربكة: كل هذا تم إنشاؤه تلقائيًا ، ما عليك سوى فهم ما يكفي لإزالة الخطوات غير الضرورية وربما إجراء تعديلات صغيرة لاحقًا.

أمثلة على برمجة تطبيقات Google المتقدمة: توصيل جداول البيانات بـ Google Drive والعروض التقديمية

بدأ كل شيء الآن في التبلور ، ولكن ماذا لو كان الناتج الفعلي الذي نريده ليس جدول بيانات بل عرضًا تقديميًا؟ إذا كان الأمر كذلك ، فقد يكون معظم العمل من هنا لا يزال يدويًا ، ولم نوفر الكثير من الوقت إذا احتجنا إلى القيام بذلك على أساس متكرر.

دعنا الآن نستكشف ما قد يبدو عليه إنشاء عرض تقديمي تلقائيًا باستخدام بيانات المثال من جدول البيانات الخاص بنا.

أصبح هذا التمرين الآن أكثر تقدمًا لسببين:

1. سنحتاج إلى التعرف على كيفية العمل مع العروض التقديمية من Google (و Google Drive) بالإضافة إلى جداول البيانات.
2. في العروض التقديمية ، أو عند العمل بين تطبيقات G Suite بشكل عام ، لا تتوفر وظيفة "تسجيل الماكرو". هذا يعني أنك بحاجة إلى معرفة معلومات كافية عن Apps Script (وأن تكون مرتاحًا في التنقل في الوثائق الخاصة بكل منتج من منتجات G Suite) لكتابة نصوص برمجية من البداية.

يهدف هذا المثال التالي إلى توفير بعض اللبنات الأساسية لتبدأ بها وتتعرف عليها.

للبدء ، دعنا ننشئ نموذجًا نريد لاحقًا ملؤه بالمحتوى باستخدام البرنامج النصي الخاص بنا. فيما يلي شريحتان بسيطتان للعرض التقديمي قمت بتجميعهما معًا:

بعد ذلك ، ستحتاج إلى الحصول على معرف هذا القالب لأنه سيتعين عليك الرجوع إليه في البرنامج النصي الخاص بك. لا شعوريًا ، سترى هذا المعرف عدة مرات لأنه ، في الواقع ، التسلسل العشوائي للأحرف والأرقام التي تراها في عنوان URL لمتصفحك:

https://docs.google.com/presentation/p/ this_is_your_presentation_ID /edit#slide=id.p.

الآن علينا إضافة الأسطر التالية إلى نصنا الأصلي. سيطلب هذا مرة أخرى الحصول على إذن ، هذه المرة للوصول إلى Google Drive الخاص بك.

 function createPresentation() { var template; var template = DriveApp.getFileById(templateId); var copy = template.makeCopy("Weekly report " + date).getId(); var presentation = SlidesApp.openById(copy); }

لن ترى أي ملاحظات مرئية فورية إذا قمت بتشغيل مقتطف الشفرة هذا ، ولكن إذا نظرت في مجلد Google Drive الخاص بك حيث قمت بتخزين القالب ، فستجد أنه تم بالفعل إنشاء نسخة منه ، ولديها اليوم التاريخ في اسم الملف. ونحن على بداية جيدة!

دعنا الآن نستخدم المزيد من الكتل الإنشائية لبدء ملئه بالمحتوى ، برمجيًا بدلاً من اليد. أضف الصفوف التالية إلى نفس الوظيفة:

 presentation.getSlides()[0] .getPageElements()[0] .asShape() .getText() .setText("Weekly Report " + date);

أصبحت الأمور الآن أكثر إثارة للاهتمام ، حيث قمنا بتغيير الصفحة الأولى لتشمل تاريخ اليوم. في "العروض التقديمية" ، كما هو الحال في "جداول البيانات" ، أنت تعمل مع كائنات (ممثلة بالفئات) لكل منها خصائص وطرق (مثل الوظائف المرفقة). يتم تنظيمها في تسلسل هرمي ، حيث يكون SpreadsheetsApp أو DriveApp أو SlidesApp هو كائن المستوى الأعلى. في مقتطف الشفرة أعلاه ، نحتاج إلى التنقل عبر هذا التسلسل الهرمي خطوة بخطوة للوصول إلى العنصر الذي نريد تحريره ، في هذه الحالة: النص في مربع نص. من الناحية العملية ، هذا يعني الوصول من خلال كائنات العرض التقديمي والشرائح والصفحة إيليمنت والشكل ، حتى نصل أخيرًا إلى كائن TextRange الذي نريد تحريره.

قد يكون تتبع نوع الكائن الذي تتعامل معه أمرًا محيرًا وقد يصعب حل الأخطاء التي تنتج عن محاولة تطبيق عملية على الكائن الخطأ. لسوء الحظ ، لا توفر وظيفة المساعدة ورسائل الخطأ في Script Editor نفسه دائمًا الكثير من الإرشادات هنا ، والشيء الفضي هو أن هذا الاهتمام سيؤدي على الأقل إلى تحسين ممارسات مراقبة الجودة لديك.

بعد إنشاء العرض التقديمي وتحديث العنوان ، حان الوقت الآن لإدراج أحد مخططاتنا الجديدة فيه. مع مراعاة التسلسل الهرمي للكائنات ، يجب أن يكون الرمز التالي منطقيًا الآن:

 var spreadsheet = SpreadsheetApp.getActive(); var sheet = spreadsheet.getSheetByName(date); var chart = sheet.getCharts()[0]; var position = {left: 25, top: 75}; var size = {width: 480, height: 300}; presentation.getSlides()[1] .insertSheetsChart(chart, position.left, position.top, size.width, size.height);

إذا قمت بتشغيل البرنامج النصي الكامل ، يجب أن يبدو العرض التقديمي الناتج كما يلي:

نأمل أن يوضح هذا المثال المبادئ ويوفر مصدر إلهام لبدء تجربتك الخاصة. إذا فكرت في الأمر ، فأنا متأكد من أنه يمكنك العثور على بعض الأمثلة على الأقل للعمل اليدوي المنجز في شركتك اليوم والذي يجب أن تتم أتمتة بهذه الطريقة. تعمل على توفير الوقت للتفكير والتحليل وتطبيق الأحكام ، بدلاً من خلط البيانات ميكانيكيًا من تنسيق و / أو مكان إلى آخر. تحسين تجربة التطوير كما ذكرنا سابقًا ، إصدار JavaScript المدعوم في Google Apps Script قديم ووظائف محرر النص البرمجي عبر الإنترنت محدودة للغاية. إذا كنت تقوم فقط بتسجيل ماكرو أو كتابة بضع عشرات من السطور ، فلن تلاحظ ذلك حقًا. ومع ذلك ، إذا كان لديك خطط طموحة لأتمتة جميع جوانب تقاريرك الأسبوعية أو الشهرية ، أو ترغب في إنشاء مكونات إضافية ، فسيسعدك معرفة أن هناك أداة سطر أوامر تتيح لك التطوير باستخدام بيئة التطوير المفضلة لديك .

إذا كنت في مثل هذه المستويات من الكفاءة ، فربما ترغب أيضًا في الاستفادة من أحدث الميزات التي توفرها JavaScript ، وربما أكثر من ذلك ، لأنه باستخدام أداة سطر الأوامر يمكنك أيضًا تطويرها في TypeScript.

استخدام Python لبرمجة جداول بيانات Google

إذا وجدت أن العمل مع Apps Script ليس كوب الشاي الخاص بك ، فهناك خيارات أخرى ، حسب حالة الاستخدام. إذا كنت ترغب في إجراء المزيد من عمليات الطحن المتقدمة للأرقام ، أو الاتصال بواجهات برمجة التطبيقات أو قواعد البيانات ، أو ببساطة تفضل لغة برمجة Python على JavaScript ، فإن Colaboratory من Google هو منتج لا يقدر بثمن. يمنحك دفتر Jupyter يعمل على خوادم Google والذي يسمح لك بكتابة نصوص Python التي تتكامل بسلاسة مع ملفات Google Drive الخاصة بك ، ومن خلال مكتبة "gspread" ، يسهل العمل مع بيانات جدول البيانات.

لقد أوجزت العديد من فوائد Python في مقال حول كيفية استخدامها للوظائف المالية ، والذي يعمل أيضًا كمقدمة لطيفة للعمل مع دفاتر Python و Jupyter في سياق الأعمال والسياق المالي. إحدى الفوائد المهمة جدًا بالنسبة لي هي أنه على عكس Apps Script ، فإن دفتر Python في Colaboratory تفاعلي ، لذلك ترى النتائج (أو رسالة الخطأ) بعد تنفيذ كل سطر أو كتلة صغيرة من التعليمات البرمجية.

الأتمتة تسبب الإدمان

أظهر هذا البرنامج التعليمي لـ Google Apps Script لمحة عما يمكن تحقيقه من خلال لغة الترميز الخاصة بـ Google. الاحتمالات لا حصر لها تقريبا. ومع ذلك ، إذا لم تكن لديك خلفية تقنية ، فقد تبدو أمثلة الشفرات شاقة وقد تفكر في نفسك أن مكاسب الإنتاجية المكتسبة من تعلم Google Apps Script قد لا تكون كافية لتفوق الاستثمار الكبير من حيث الوقت المطلوب لتتعلمه.

هذا ، بالطبع ، يعتمد على العديد من العوامل ، بما في ذلك نوع الدور الذي لديك ، أو الذي تتوقع أن تلعبه ، في المستقبل. ولكن حتى إذا كنت لا تتوقع أن تفعل شيئًا مشابهًا للأمثلة الموضحة هنا ، فإن فهم ما هو ممكن وتقريبًا مقدار العمل الذي يتطلبه التنفيذ يمكن أن يثير الأفكار والأفكار حول كيفية تحسين الإنتاجية في شركتك ، من أجل عملائك ، أو أنت شخصيًا.

أنا شخصياً أستطيع أن أشهد على الرضا بالجلوس وضرب الزر الذي يكمل ساعة من العمل اليدوي الممل في أقل من دقيقة. بعد القيام بذلك للمرة الخمسين ، ستكون ممتنًا للساعتين اللتين قضتهما في تجميع كل ذلك معًا في المقام الأول ، مما أدى في النهاية إلى توفير وقتك لمزيد من المساعي ذات القيمة المضافة. بعد فترة ، تصبح فوائد قابلية التوسع هذه إدمانًا.