العمل مع جداول بيانات جوجل Google Sheets باستخدام بايثون

يُعَد تطبيق جداول بيانات جوجل Google Sheets تطبيقًا مجانيًا ومستندًا إلى الويب ومتاحًا لأيّ شخص لديه حساب جوجل Google أو عنوان جيميل Gmail، وأصبح منافسًا مفيدًا وغنيًا بالميزات لبرنامج إكسل Excel. تحتوي جداول بيانات جوجل على واجهة برمجة تطبيقات API خاصة بها، ولكن يمكن أن تكون هذه الواجهة مربكةً بعض الشيء في عملية التعلم والاستخدام. سنغطّي في هذا المقال وحدة EZSheets الخارجية والموثقة على موقعها الرسمي، والتي لا تُعَد كاملة الميزات مثل واجهة برمجة تطبيقات جداول البيانات الرسمية من جوجل، ولكنها تسهّل تنفيذ مهام جداول البيانات الشائعة.

تثبيت وإعداد وحدة EZSheets

يمكنك تثبيت وحدة EZSheets من خلال فتح نافذة طرفية جديدة وتشغيل الأمر pip install --user ezsheets، وستثبِّت وحدة EZSheets أيضًا كجزء من هذا التثبيت الوحدات google-api-python-client و google-auth-httplib2 و google-auth-oauthlib، حيث تسمح هذه الوحدات لبرنامجك بتسجيل الدخول إلى خوادم جوجل وإنشاء طلبات واجهة برمجة التطبيقات API. تعالج وحدة EZSheets عملية التفاعل مع هذه الوحدات، لذلك لا داعي للقلق بشأن كيفية عملها.

الحصول على الاعتماديات Credentials والملفات المفتاحية Token Files

يجب تفعيل جداول بيانات جوجل وواجهات برمجة تطبيقات جوجل درايف Google Drive على حسابك على جوجل قبل أن تتمكّن من استخدام وحدة EZSheets. انتقل إلى صفحتي الويب التاليتين وانقر على زر التفعيل Enable API الموجودة في أعلى كل منهما:

يجب أيضًا أن تحصل على ثلاثة ملفات، والتي يجب حفظها في المجلد نفسه لسكربت بايثون Python الذي امتداده ‎.py ويستخدم وحدة EZSheets، وهذه الملفات هي:

ملف الاعتماديات واسمه credentials-sheets.json.
مفتاح Token جداول بيانات جوجل واسمه token-sheets.pickle.
مفتاح Token جوجل درايف واسمه token-drive.pickle.

يولّد ملف الاعتماديات ملفات المفاتيح، وأسهل طريقة للحصول على ملف الاعتماديات هي الانتقال إلى صفحة Google Sheets Python Quickstart والنقر على زر التفعيل الملون باللون الأزرق Enable the Google Sheets API كما هو موضح في الشكل التالي، ولكن يجب أن تسجّل الدخول إلى حسابك في جوجل لعرض هذه الصفحة:

الحصول على ملف credentials.json

سيؤدي النقر على هذا الزر إلى ظهور نافذة تحتوي على رابط تنزيل ضبط العميل Download Client Configuration الذي يتيح لك تنزيل ملف credentials.json. أعِد تسمية هذا الملف إلى الاسم credentials-sheets.json وضعه في المجلد نفسه لسكربتات بايثون الخاصة بك.

شغّل الأمر import ezsheets لاستيراد وحدة EZSheets بعد الحصول على الملف credentials-sheets.json، حيث ستفتح نافذة متصفح جديدة لتتمكّن من تسجيل الدخول إلى حسابك على جوجل عند استيراد وحدة EZSheets في المرة الأولى. انقر بعد ذلك على زر السماح Allow كما هو موضح في الشكل التالي:

السماح لصفحة Python Quickstart بالوصول إلى حسابك على جوجل

سبب ظهور الرسالة السابقة هو أنك نزّلتَ ملف الاعتماديات من صفحة Google Sheets Python Quickstart، وستفتح هذه النافذة مرتين: الأولى للوصول إلى جداول بيانات جوجل والثانية للوصول إلى جوجل درايف، حيث تستخدم وحدةُ EZSheets الوصولَ إلى جوجل درايف لرفع جداول البيانات وتنزيلها وحذفها.

ستطالبك نافذة المتصفح بإغلاقه بعد تسجيل الدخول، وسيظهر الملفان token-sheets.pickle و token-drive.pickle في المجلد نفسه الذي يوجد فيه الملف credentials-sheets.json. ستجري هذه العملية فقط في المرة الأولى التي تشغِّل فيها الأمر import ezsheets.

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

ملاحظة: لا تشارك ملفات الاعتماديات أو المفاتيح مع أيّ شخص، وتعامل معها مثل كلمات المرور.

إبطال ملف الاعتماديات

إذا شاركتَ ملفات الاعتماديات أو المفاتيح مع شخصٍ ما عن طريق الخطأ، فلن يتمكّن هذا الشخص من تغيير كلمة مرور حسابك على جوجل، ولكن سيكون لديه حق الوصول إلى جداول بياناتك. يمكنك إبطال هذه الملفات بالانتقال إلى صفحة طرفية المطور على منصة سحابة جوجل Google Cloud Platform، ولكن يجب تسجيل الدخول إلى حسابك على جوجل لعرض هذه الصفحة. انقر على رابط الاعتماديات Credentials في الشريط الجانبي، ثم انقر على أيقونة سلة المهملات بجانب ملف الاعتماديات الذي شاركته عن طريق الخطأ، كما هو موضح في الشكل التالي:

صفحة الاعتماديات في طرفية المطور على منصة سحابة جوجل

يمكن إنشاء ملف اعتماديات جديد من هذه الصفحة من خلال النقر على زر إنشاء الاعتماديات "Create Credentials" وتحديد خيار معرّف عميل OAuth أو "OAuth Client ID" كما هو موضح في الشكل السابق، ثم حدّد الخيار "أخرى Other" بالنسبة لنوع التطبيق وسمِّ الملف بأيّ اسم تريده. سيُدرَج بعد ذلك ملف الاعتماديات الجديد في الصفحة، ويمكنك النقر على أيقونة التنزيل لتنزيله. سيكون للملف الذي ستنزِّله اسم ملف طويل ومعقد، لذا يجب إعادة تسميته إلى اسم الملف الافتراضي الذي تحاول الوحدة EZSheets تحميله وهو credentials-sheets.json. يمكنك أيضًا إنشاء ملف اعتماديات جديد من خلال النقر على زر تفعيل واجهة برمجة تطبيقات جداول بيانات جوجل "Enable the Google Sheets API" المذكور في القسم السابق.

كائنات جدول البيانات Spreadsheet

يمكن أن يحتوي جدول البيانات Spreadsheet في جداول بيانات جوجل على أوراق Sheets متعددة والتي تُسمَّى أيضًا أوراق عمل Worksheets، وتحتوي كل ورقة على أعمدة Columns وصفوف Rows من القيم. يوضح الشكل التالي جدول بيانات بعنوان بيانات التعليم "Education Data"، والذي يحتوي على ثلاث أوراق بعنوان الطلاب "Students" والصفوف "Classes" والموارد "Resources"، ويُسمَّى العمود الأول من كل ورقة A، ويسمى الصف الأول 1:

جدول بيانات بعنوان "Education Data" مكوَّن من ثلاث أوراق

سيتمثّل معظم عملك في تعديل كائنات الورقة Sheet، ولكن يمكنك أيضًا تعديل كائنات جدول البيانات Spreadsheet، كما سنوضّح في القسم التالي.

إنشاء جداول البيانات وتحميلها وسردها

يمكنك إنشاء كائن Spreadsheet جديد من جدول بيانات موجود مسبقًا أو جدول بيانات فارغ أو جدول بيانات مرفوع على جداول بيانات جوجل، حيث يمكن إنشاء كائن Spreadsheet من جدول بياناتٍ موجود مسبقًا على جداول بيانات جوجل، ولكن أن تعرف السلسلة النصية لمعرّف جدول البيانات. يمكن العثور على المعرّف الفريد لجداول بيانات جوجل في عنوان URL، بعد الجزء spreadsheets/d/‎ وقبل الجزء ‎/edit، فمثلًا يوجد جدول البيانات الموضّح في الشكل السابق على عنوان URL الذي هو

https://docs.google.com/spreadsheets/d/1J-Jx6Ne2K_vqI9J2SO-TAXOFbxx_9tUjwnkPC22LjeU/edit#gid=151537240/‎

وبالتالي يكون معرّفه 1J-Jx6Ne2K_vqI9J2SO-TAXOFbxx_9tUjwnkPC22LjeU.

ملاحظة: معرّفات جداول البيانات المُستخدَمة في هذا المقال خاصة بجداول بيانات حساب جوجل الخاص بالكاتب، إذ لن تعمل إذا أدخلتها في صدفتك التفاعلية Interactive Shell، لذا انتقل إلى جداول بيانات جوجل لإنشاء جداول بيانات ضمن حسابك ثم احصل على المعرّفات من شريط العناوين.

مرّر معرّف جدول بياناتك بوصفه سلسلةً نصية إلى الدالة ezsheets.Spreadsheet()‎ للحصول على كائن Spreadsheet لجدول البيانات الخاص بهذا المعرّف:

>>> import ezsheets
>>> ss = ezsheets.Spreadsheet('1J-Jx6Ne2K_vqI9J2SO-TAXOFbxx_9tUjwnkPC22LjeU')
>>> ss
Spreadsheet(spreadsheetId='1J-Jx6Ne2K_vqI9J2SO-TAXOFbxx_9tUjwnkPC22LjeU')
>>> ss.title
'Education Data'

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

يمكنك إنشاء جدول بيانات جديد وفارغ من خلال استدعاء الدالة ezsheets.createSpreadsheet()‎ وتمرير سلسلةٍ نصية إليها، حيث تمثل هذه السلسلة النصية عنوان جدول البيانات الجديد. لندخِل مثلًا ما يلي في الصدفة التفاعلية:

>>> import ezsheets
>>> ss = ezsheets.createSpreadsheet('Title of My New Spreadsheet')
>>> ss.title
'Title of My New Spreadsheet'

يمكنك رفع جدول بيانات إكسل Excel أو أوبن أوفيس OpenOffice أو CSV أو TSV موجود مسبقًا إلى جداول بيانات جوجل من خلال تمرير اسم ملف جدول البيانات إلى الدالة ezsheets.upload()‎. إذًا لندخِل ما يلي في الصدفة التفاعلية مع وضع اسم ملف جدول بياناتك مكان الملف my_spreadsheet.xlsx:

>>> import ezsheets
>>> ss = ezsheets.upload('my_spreadsheet.xlsx')
>>> ss.title
'my_spreadsheet'

يمكنك سرد جداول البيانات الموجودة على حسابك على جوجل من خلال استدعاء الدالة listSpreadsheets()‎ التي تعيد قاموسًا Dictionary مفاتيحه هي معرّفات جداول البيانات وقيمه هي عناوين جداول البيانات. إذًا لندخِل ما يلي في الصدفة التفاعلية بعد رفع جدول البيانات:

>>> ezsheets.listSpreadsheets()
{'1J-Jx6Ne2K_vqI9J2SO-TAXOFbxx_9tUjwnkPC22LjeU': 'Education Data'}

يمكنك بعد الحصول على كائن Spreadsheet استخدام سماته وتوابعه للتعامل مع جدول البيانات المُستضاف على جداول بيانات جوجل عبر الإنترنت.

سمات Attributes كائن جدول البيانات Spreadsheet

توجد البيانات الفعلية في الأوراق الخاصة بجدول البيانات، ولكن يحتوي كائن Spreadsheet على السمات title و spreadsheetId و url و sheetTitles و sheets للتعامل مع جدول البيانات. لندخِل ما يلي في الصدفة التفاعلية:

>>> import ezsheets
>>> ss = ezsheets.Spreadsheet('1J-Jx6Ne2K_vqI9J2SO-TAXOFbxx_9tUjwnkPC22LjeU')
>>> ss.title         # عنوان جدول البيانات
'Education Data'
>>> ss.title = 'Class Data' # تغيير العنوان
>>> ss.spreadsheetId # ‫المعرّف الفريد (وهو سمة للقراءة فقط)
'1J-Jx6Ne2K_vqI9J2SO-TAXOFbxx_9tUjwnkPC22LjeU'
>>> ss.url           # ‫عنوان URL الأصلي (وهو سمة للقراءة فقط)
'https://docs.google.com/spreadsheets/d/1J-Jx6Ne2K_vqI9J2SO-
TAXOFbxx_9tUjwnkPC22LjeU/'
>>> ss.sheetTitles     # ‫عناوين جميع كائنات الورقة Sheet
('Students', 'Classes', 'Resources')
>>> ss.sheets          # كائنات الورقة‫ Sheet في جدول البيانات بالترتيب
(<Sheet sheetId=0, title='Students', rowCount=1000, columnCount=26>, <Sheet
sheetId=1669384683, title='Classes', rowCount=1000, columnCount=26>, <Sheet
sheetId=151537240, title='Resources', rowCount=1000, columnCount=26>)
>>> ss[0]              # كائن الورقة الأول في جدول البيانات 
<Sheet sheetId=0, title='Students', rowCount=1000, columnCount=26>
>>> ss['Students']     # يمكن أيضًا الوصول إلى الأوراق باستخدام العنوان
<Sheet sheetId=0, title='Students', rowCount=1000, columnCount=26>
>>> del ss[0]          # حذف كائن الورقة الأول في جدول البيانات
>>> ss.sheetTitles     # ‫أصبح كائن الورقة "Students" محذوفًا
('Classes', 'Resources')

إذا عدّل شخصٌ ما جدول البيانات من موقع جداول بيانات جوجل، فيمكن للسكربت الخاص بك تحديث كائن Spreadsheet ليطابق البيانات الموجودة على الإنترنت من خلال استدعاء التابع refresh()‎:

>>> ss.refresh()

لن يحدّث هذا التابع سمات كائن Spreadsheet فحسب، بل سيحدّث البيانات الموجودة في كائنات Sheet التي يحتوي عليها كائن Spreadsheet، وستنعكس التغييرات التي تجريها على كائن Spreadsheet في جدول البيانات الموجود على الإنترنت ضمن الزمن الحقيقي.

تنزيل ورفع جداول البيانات

يمكنك تنزيل جدول بيانات جوجل بعددٍ من التنسيقات مثل: إكسل وأوبن أوفيس OpenOffice و CSV و TSV و PDF، ويمكنك أيضًا تنزيله كملف مضغوط ZIP يحتوي على ملفات HTML لبيانات جدول البيانات، حيث تحتوي الوحدة EZSheets على دوالٍ لكل خيار من هذه الخيارات كما سنوضح فيما يلي:

>>> import ezsheets
>>> ss = ezsheets.Spreadsheet('1J-Jx6Ne2K_vqI9J2SO-TAXOFbxx_9tUjwnkPC22LjeU')
>>> ss.title
'Class Data'
>>> ss.downloadAsExcel() # تنزيل جدول البيانات كملف إكسل
'Class_Data.xlsx'
>>> ss.downloadAsODS() # تنزيل جدول البيانات كملف أوبن أوفيس
'Class_Data.ods'
>>> ss.downloadAsCSV() # تنزيل الورقة الأولى فقط كملف‫ CSV
'Class_Data.csv'
>>> ss.downloadAsTSV() # تنزيل الورقة الأولى فقط كملف‫ TSV
'Class_Data.tsv'
>>> ss.downloadAsPDF() # ‫تنزيل جدول البيانات كملف PDF
'Class_Data.pdf'
>>> ss.downloadAsHTML() # تنزيل جدول البيانات كملف مضغوط‫ ZIP مؤلَّفٍ من ملفات HTML
'Class_Data.zip'

لاحظ أن الملفات التي لها تنسيق CSV و TSV يمكن أن تحتوي على ورقة واحدة فقط، لذلك إذا نزّلت جدول بيانات من جداول بيانات جوجل بهذا التنسيق، فستحصل على الورقة الأولى فقط، ولكن يمكنك تنزيل أوراق أخرى من خلال تغيير السمة index الخاصة بكائن Sheet إلى القيمة 0.

تعيد جميع دوال التنزيل سلسلة نصية لاسم الملف الذي جرى تنزيله، ويمكنك أيضًا تحديد اسم ملفك لجدول البيانات من خلال تمرير اسم الملف الجديد إلى دالة التنزيل كما يلي، ويجب أن تعيد الدالة اسم الملف المُحدَّث:

>>> ss.downloadAsExcel('a_different_filename.xlsx')
'a_different_filename.xlsx'

حذف جداول البيانات

يمكننا حذف جدول بيانات من خلال استدعاء التابع delete()‎:

>>> import ezsheets
>>> ss = ezsheets.createSpreadsheet('Delete me') # إنشاء جدول البيانات
>>> ezsheets.listSpreadsheets() # التأكد من إنشاء جدول بيانات
{'1aCw2NNJSZblDbhygVv77kPsL3djmgV5zJZllSOZ_mRk': 'Delete me'}
>>> ss.delete() # حذف جدول البيانات
>>> ezsheets.listSpreadsheets()
{}

سينقل التابع delete()‎ جدول بياناتك إلى مجلد سلة المهملات على جوجل درايف، حيث يمكنك عرض محتويات مجلد سلة المهملات، ولكن يمكن حذف جدول البيانات نهائيًا من خلال تمرير القيمة True لوسيط الكلمة المفتاحية Keyword Argument الذي هو permanent كما يلي:

>>> ss.delete(permanent=True)

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

كائنات الورقة Sheet

يحتوي كائن Spreadsheet على كائن Sheet واحد أو أكثر، حيث تمثّل كائنات Sheet صفوف وأعمدة البيانات الموجودة في الورقة، ويمكنك الوصول إلى هذه الأوراق باستخدام عامل الأقواس المربعة وعدد صحيح يمثل الفهرس. تحتوي السمة sheets على مجموعة Tuple من كائنات Sheet بالترتيب الذي تظهر به في جدول البيانات. يمكنك الوصول إلى كائنات Sheet في جدول البيانات من خلال إدخال ما يلي في الصدفة التفاعلية:

>>> import ezsheets
>>> ss = ezsheets.Spreadsheet('1J-Jx6Ne2K_vqI9J2SO-TAXOFbxx_9tUjwnkPC22LjeU')
>>> ss.sheets    # ‫كائنات الورقة Sheet في جدول البيانات بالترتيب
(<Sheet sheetId=1669384683, title='Classes', rowCount=1000, columnCount=26>,
<Sheet sheetId=151537240, title='Resources', rowCount=1000, columnCount=26>)
>>> ss.sheets[0] # الحصول على كائن الورقة الأول في جدول البيانات
<Sheet sheetId=1669384683, title='Classes', rowCount=1000, columnCount=26>
>>> ss[0]        # الحصول أيضًا على كائن الورقة الأول في جدول البيانات
<Sheet sheetId=1669384683, title='Classes', rowCount=1000, columnCount=26>

يمكنك أيضًا الحصول على كائن Sheet باستخدام عامل الأقواس المربعة وسلسلة نصية تمثّل اسم الورقة، وتحتوي السمة sheetTitles الخاصة بكائن Spreadsheet على مجموعةٍ تمثّل جميع عناوين الأوراق. إذًا لندخِل مثلًا ما يلي في الصدفة التفاعلية:

>>> ss.sheetTitles # ‫عناوين جميع كائنات الورقة Sheet في جدول البيانات
('Classes', 'Resources')
>>> ss['Classes'] # يمكن أيضًا الوصول إلى الأوراق باستخدام العنوان
<Sheet sheetId=1669384683, title='Classes', rowCount=1000, columnCount=26>

يمكنك بعد الحصول على كائن Sheet قراءة البيانات منه وكتابة البيانات فيه باستخدام توابع كائن Sheet كما سنوضّح في القسم التالي.

قراءة وكتابة البيانات

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

>>> import ezsheets
>>> ss = ezsheets.createSpreadsheet('My Spreadsheet')
>>> sheet = ss[0] # الحصول على الورقة الأولى في جدول البيانات
>>> sheet.title
'Sheet1'
>>> sheet = ss[0]
>>> sheet['A1'] = 'Name' # ‫ضبط القيمة في الخلية A1
>>> sheet['B1'] = 'Age'
>>> sheet['C1'] = 'Favorite Movie'
>>> sheet['A1'] # قراءة القيمة في الخلية‫ A1
'Name'
>>> sheet['A2'] # تعيد الخلايا الفارغة سلسلة نصية فارغة
''
>>> sheet[2, 1] # العمود 2 والصف 1 هو عنوان الخلية‫ B1 نفسه
'Age'
>>> sheet['A2'] = 'Alice'
>>> sheet['B2'] = 30
>>> sheet['C2'] = 'RoboCop'

يجب أن ينتج عن هذه التعليمات جدول بيانات جوجل يشبه الشكل التالي:

جدول البيانات الذي أنشأناه باستخدام تعليمات المثال السابق

يمكن لعدة مستخدمين تحديث الورقة في الوقت ذاته، لذا يمكنك تحديث البيانات المحلية في كائن Sheet من خلال استدعاء التابع refresh()‎ الخاص بهذا الكائن:

>>> sheet.refresh()

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

عنونة الأعمدة والصفوف

تعمل عنونة الخلايا في جداول بيانات جوجل كما هو الحال في إكسل، ولكن الفرق الوحيد بينهما هو احتواء جداول بيانات جوجل على أعمدة وصفوف تستند إلى القيمة 1، أي أن العمود أو الصف الأول موجود في الفهرس 1 وليس في الفهرس 0 على عكس فهارس القائمة المستندة إلى القيمة 0 في لغة بايثون. يمكنك تحويل العنوان الذي تنسيقه سلسلة نصية 'A2' إلى عنوانٍ تنسيقه مجموعة (column, row) (والعكس صحيح) باستخدام الدالة convertAddress()‎. تحوّل الدالتان getColumnLetterOf()‎ و getColumnNumberOf()‎ أيضًا عنوان العمود من الحروف إلى الأعداد وبالعكس. لندخِل مثلًا ما يلي في الصدفة التفاعلية:

>>> import ezsheets
>>> ezsheets.convertAddress('A2') # تحويل العناوين‫...
(1, 2)
>>> ezsheets.convertAddress(1, 2) # ‏… وتحويلها بالعكس مرة أخرى
'A2'
>>> ezsheets.getColumnLetterOf(2)
'B'
>>> ezsheets.getColumnNumberOf('B')
2
>>> ezsheets.getColumnLetterOf(999)
'ALK'
>>> ezsheets.getColumnNumberOf('ZZZ')
18278

تُعَد العناوين التي لها تنسيق السلسلة النصية 'A2' ملائمةً لكتابة العناوين في شيفرتك المصدرية، وتكون العناوين التي لها تنسيق المجموعة (column, row) ملائمةً إذا أردتَ التكرار على مجالٍ من العناوين واحتجتَ صيغةً رقمية للعمود، لذا تُعَد الدوال convertAddress()‎ و getColumnLetterOf()‎ و getColumnNumberOf()‎ مفيدةً عندما تريد التحويل بين هذين التنسيقين.

قراءة وكتابة الأعمدة والصفوف بأكملها

قد تستغرق كتابة البيانات ضمن خلية واحدة في كل مرة وقتًا طويلًا كما ذكرنا سابقًا، ولكن تحتوي وحدة EZSheets على توابع خاصة بكائن Sheet لقراءة وكتابة الأعمدة والصفوف بأكملها في الوقت ذاته، حيث يقرأ التابعان getColumn()‎ و getRow()‎ من الأعمدة والصفوف ويكتب التابعان updateColumn()‎ و updateRow()‎ في الأعمدة والصفوف. تنشِئ هذه التوابع طلبات إلى خوادم جداول بيانات جوجل لتحديث جدول البيانات، لذا يجب أن تكون متصلًا بالإنترنت. سنرفع في مثالنا جدول بيانات أسعار المنتجات produceSales.xlsx من المقال السابق إلى جداول بيانات جوجل، حيث تبدو الصفوف الثمانية الأولى كما في الشكل التالي:

06_produceSalesSpreadsheet (1).png

يمكنك رفع جدول البيانات produceSales.xlsx من خلال إدخال ما يلي في الصدفة التفاعلية:

>>> import ezsheets
>>> ss = ezsheets.upload('produceSales.xlsx')
>>> sheet = ss[0]
>>> sheet.getRow(1) # الصف الأول هو الصف 1 وليس الصف 0
['PRODUCE', 'COST PER KiloGram', 'KiloGrams SOLD', 'TOTAL', '', '']
>>> sheet.getRow(2)
['Potatoes', '0.86', '21.6', '18.58', '', '']
>>> columnOne = sheet.getColumn(1)
>>> sheet.getColumn(1)
['PRODUCE', 'Potatoes', 'Okra', 'Fava beans', 'Watermelon', 'Garlic',
--snip--
>>> sheet.getColumn('A') # ‫النتيجة نفسها للتعليمة getColumn(1)‎
['PRODUCE', 'Potatoes', 'Okra', 'Fava beans', 'Watermelon', 'Garlic',
--snip--
>>> sheet.getRow(3)
['Okra', '2.26', '38.6', '87.24', '', '']
>>> sheet.updateRow(3, ['Pumpkin', '11.50', '20', '230'])
>>> sheet.getRow(3)
['Pumpkin', '11.50', '20', '230', '', '']
>>> columnOne = sheet.getColumn(1)
>>> for i, value in enumerate(columnOne):
...     # اجعل قائمة بايثون تحتوي على سلاسل نصية بأحرف كبيرة‫:
...     columnOne[i] = value.upper()
...
>>> sheet.updateColumn(1, columnOne) # تحديث العمود بأكمله في طلب واحد

تسترد الدالتان getRow()‎ و getColumn()‎ البيانات من جميع الخلايا الموجودة في صف أو عمود محدد بوصفها قائمةً من القيم، وتصبح الخلايا الفارغة قيمًا لسلاسل نصية فارغة في القائمة. يمكنك تمرير رقم أو حرف العمود إلى الدالة getColumn()‎ لإخبارها باسترداد بيانات عمودٍ معين، حيث وضّحنا في المثال السابق أن التعلمتين getColumn(1)‎ و getColumn('A')‎ تعيدان القائمة نفسها.

تكتب الدالتان updateRow()‎ و updateColumn()‎ فوق البيانات الموجودة في الصف أو العمود على التوالي باستخدام قائمة القيم المُمرّرة إليهما، فمثلًا احتوى الصف الثالث في المثال السابق على معلومات حول البامية Okra في البداية، لكن أدّى استدعاء الدالة updateRow()‎ إلى وضع بيانات حول اليقطين Pumpkin مكانها، ثم استدعينا الدالة sheet.getRow(3)‎ مرةً أخرى لعرض القيم الجديدة في الصف الثالث.

لنحدّث بعد ذلك جدول بيانات "produceSales"، حيث يُعَد تحديث خلية واحدة في كل مرة أمرًا بطيئًا إذا كان لديك العديد من الخلايا التي تريد تحديثها، بينما يُعَد الحصول على عمود أو صف كقائمة وتحديث القائمة ثم تحديث العمود أو الصف بأكمله باستخدام القائمة أسرع بكثير، حيث يمكن إجراء جميع التغييرات في طلبٍ واحد.

يمكن الحصول على كافة الصفوف دفعةً واحدة من خلال استدعاء التابع getRows()‎ لإعادة قائمةٍ بجميع القوائم، حيث تمثل كل قائمة من القوائم الداخلية الموجودة ضمن القائمة الخارجية صفًا واحدًا من الورقة. يمكنك تعديل هذه القيم الموجودة في هيكل البيانات لتغيير اسم المنتج Produce Name وعدد الكيلوجرامات المباعة Kilograms Sold والتكلفة الإجمالية Total لبعض الصفوف، ثم تمرّرها إلى التابع updateRows()‎ من خلال إدخال ما يلي في الصدفة التفاعلية:

>>> rows = sheet.getRows() # الحصول على جميع الصفوف في جدول البيانات
>>> rows[0] # فحص القيم الموجودة في الصف الأول
['PRODUCE', 'COST PER KiloGrams', 'KiloGrams SOLD', 'TOTAL', '', '']
>>> rows[1]
['POTATOES', '0.86', '21.6', '18.58', '', '']
>>> rows[1][0] = 'PUMPKIN' # تغيير اسم المنتج
>>> rows[1]
['PUMPKIN', '0.86', '21.6', '18.58', '', '']
>>> rows[10]
['OKRA', '2.26', '40', '90.4', '', '']
>>> rows[10][2] = '400' # تغيير عدد الكيلوجرامات المباعة
>>> rows[10][3] = '904' # تغيير التكلفة الإجمالية
>>> rows[10]
['OKRA', '2.26', '400', '904', '', '']
>>> sheet.updateRows(rows) # تحديث جدول البيانات عبر الإنترنت بالتغييرات التي أجريناها

يمكنك تحديث الورقة بأكملها في طلب واحد من خلال تمرير قائمةٍ من القوائم المُعادة من الدالة getRows()‎ والمُعدَّلة بالتغييرات التي أجريناها على الصفين 1 و 10 إلى الدالة updateRows()‎.

لاحظ أن الصفوف الموجودة في ورقة جداول بيانات جوجل تحتوي على سلاسل نصية فارغة في نهايتها، لأن الورقة التي رفعناها تحتوي على 6 أعمدة، ولدينا 4 أعمدة فقط من البيانات. يمكنك قراءة عدد الصفوف والأعمدة في الورقة باستخدام السمتين rowCount و columnCount، ثم يمكنك تغيير حجم الورقة من خلال ضبط هاتين القيمتين.

>>> sheet.rowCount        # عدد الصفوف في الورقة
23758
>>> sheet.columnCount     # عدد الأعمدة في الورقة
6
>>> sheet.columnCount = 4 # تغيير عدد الأعمدة إلى 4
>>> sheet.columnCount     # يصبح الآن عدد الأعمدة في الورقة 4
4

يجب أن تحذف التعليمات السابقة العمودين الخامس والسادس من جدول بيانات "produceSales" كما هو موضّح في الشكل التالي:

الورقة قبل (على اليسار) وبعد (على اليمين) تغيير عدد الأعمدة إلى 4.

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

إنشاء وحذف الأوراق

تبدأ جميع جداول بيانات جوجل بورقة واحدة اسمها "Sheet1"، ولكن يمكنك إضافة أوراق إضافية إلى نهاية قائمة الأوراق باستخدام التابع createSheet()‎ الذي تمرّر إليه سلسلة نصية لاستخدامها كعنوان للورقة الجديدة، ويمكن للوسيط الثاني الاختياري الخاص بهذا التابع تحديد فهرس العدد الصحيح للورقة الجديدة. يمكنك إنشاء جدول بيانات ثم إضافة أوراق جديدة إليه من خلال إدخال ما يلي في الصدفة التفاعلية:

>>> import ezsheets
>>> ss = ezsheets.createSpreadsheet('Multiple Sheets')
>>> ss.sheetTitles
('Sheet1',)
>>> ss.createSheet('Spam') # إنشاء ورقة جديدة في نهاية قائمة الأوراق
<Sheet sheetId=2032744541, title='Spam', rowCount=1000, columnCount=26>
>>> ss.createSheet('Eggs') # إنشاء ورقة جديدة أخرى
<Sheet sheetId=417452987, title='Eggs', rowCount=1000, columnCount=26>
>>> ss.sheetTitles
('Sheet1', 'Spam', 'Eggs')
>>> ss.createSheet('Meat', 0) # إنشاء ورقة عند الفهرس 0 في قائمة الأوراق
<Sheet sheetId=814694991, title='Meat', rowCount=1000, columnCount=26>
>>> ss.sheetTitles
('Meat', 'Sheet1', 'Spam', 'Eggs')

تضيف التعليمات السابقة ثلاث أوراق جديدة إلى جدول البيانات هي: "Meat" و"Spam" و"Eggs" بالإضافة إلى الورقة الافتراضية "Sheet1". تُرتَّب الأوراق الموجودة في جدول البيانات، وتضاف الأوراق الجديدة إلى نهاية القائمة إن لم تمرِّر وسيطًا ثانيًا إلى الدالة createSheet()‎، حيث يحدّد هذا الوسيط فهرس الورقة. أنشأنا في المثال السابق الورقة التي عنوانها "Meat" في الفهرس 0، مما يجعل الورقة "Meat" هي الورقة الأولى في جدول البيانات وإزاحة الأوراق الثلاث الأخرى بمقدار موضعٍ واحد، ويشبه ذلك سلوك تابع القائمة insert()‎. يمكنك رؤية الأوراق الجديدة على التبويبات الموجودة أسفل الشاشة كما هو موضَّح في الشكل التالي:

جدول بيانات الأوراق المتعددة "Multiple Sheets" بعد إضافة أوراق "Spam" و"Eggs" و"Meat"

يحذف التابع delete()‎ الخاص بالكائن Sheet ورقةً من جدول البيانات، ولكن إذا أدرتَ الاحتفاظ بالورقة مع حذف البيانات الموجودة فيها، فاستدعِ التابع clear()‎ لمسح جميع الخلايا وجعل هذه الورقة ورقةً فارغة. إذا لندخِل ما يلي في الصدفة التفاعلية:

>>> ss.sheetTitles
('Meat', 'Sheet1', 'Spam', 'Eggs')
>>> ss[0].delete()      # ‫حذف الورقة الموجودة في الفهرس 0 أي الورقة "Meat"
>>> ss.sheetTitles
('Sheet1', 'Spam', 'Eggs')
>>> ss['Spam'].delete() # ‫حذف الورقة "Spam"
>>> ss.sheetTitles
('Sheet1', 'Eggs')
>>> sheet = ss['Eggs']  # إسناد الورقة‫ "Eggs" إلى متغير
>>> sheet.delete()      # ‫حذف الورقة "Eggs"
>>> ss.sheetTitles
('Sheet1',)
>>> ss[0].clear()       # مسح جميع الخلايا الموجودة في الورقة‫ "Sheet1"
>>> ss.sheetTitles      # الورقة‫ "Sheet1" فارغة ولكنها لا تزال موجودة
('Sheet1',)

يكون حذف الأوراق حذفًا نهائيًا، إذ لا توجد طريقة لاستعادة البيانات، ولكن يمكنك إنشاء نسخة احتياطية من الأوراق من خلال نسخها إلى جدول بيانات آخر باستخدام التابع copyTo()‎ كما سنوضّح في القسم التالي.

نسخ الأوراق

يحتوي كل كائن Spreadsheet على قائمةٍ مرتبة من كائنات Sheet الموجودة ضمنه، حيث يمكنك استخدام هذه القائمة لإعادة ترتيب الأوراق (كما وضّحنا في القسم السابق) أو نسخها إلى جداول بيانات أخرى، إذ يمكن نسخ كائن Sheet إلى كائن Spreadsheet آخر من خلال استدعاء التابع copyTo()‎ الذي نمرّر إليه كائن Spreadsheet الهدف كوسيط. لندخِل ما يلي في الصدفة التفاعلية لإنشاء جدولي بيانات ونسخ بيانات جدول البيانات الأول إلى الورقة الأخرى:

>>> import ezsheets
>>> ss1 = ezsheets.createSpreadsheet('First Spreadsheet')
>>> ss2 = ezsheets.createSpreadsheet('Second Spreadsheet')
>>> ss1[0]
<Sheet sheetId=0, title='Sheet1', rowCount=1000, columnCount=26>
>>> ss1[0].updateRow(1, ['Some', 'data', 'in', 'the', 'first', 'row'])
>>> ss1[0].copyTo(ss2) # نسخ الورقة‫ Sheet1 الخاصة بجدول البيانات ss1 إلى جدول البيانات ss2
>>> ss2.sheetTitles    # ‫سيحتوي جدول البيانات ss2 على نسخة من الورقة Sheet1 الخاصة بجدول البيانات ss1 Sheet1
('Sheet1', 'Copy of Sheet1')

لاحظ تسمية الورقة المنسوخة بالاسم Copy of Sheet1، لأن جدول البيانات الهدف (ss2 في المثال السابق) يحتوي مسبقًا على ورقة بالاسم Sheet1. تظهر الأوراق المنسوخة في نهاية قائمة أوراق جدول البيانات الهدف، ولكن يمكنك تغيير السمة index لإعادة ترتيبها في جدول البيانات الجديد.

التعامل مع الحصص Quotas في جداول بيانات جوجل

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

يُقيَّد مستخدمو جداول بيانات جوجل بإنشاء 250 جدول بيانات جديد يوميًا، ويمكن لحسابات جوجل المجانية إجراء 100 طلب قراءة و100 طلب كتابة في كل 100 ثانية وفقًا لإرشادات مطوري جوجل، إذ ستؤدي محاولة تجاوز هذه الحصة إلى رفع الاستثناء googleapiclient.errors.HttpError أو "Quota exceeded for quota group" الذي يمثّل تجاوز الحصة المتاحة، حيث تلتقط الوحدة EZSheets تلقائيًا هذا الاستثناء وتعيد محاولة الطلب. إذا حدث ذلك، فستستغرق استدعاءات الدوال لقراءة البيانات أو كتابتها عدة ثوانٍ أو حتى دقيقة أو دقيقتين قبل أن تعيد شيئًا ما، وإذا استمر الطلب في الفشل، وهو أمرٌ ممكن إذا أجرى سكربتٌ آخر يمتلك الاعتماديات نفسها طلباتٍ أيضًا، فستعيد الوحدة EZSheets رفعَ هذا الاستثناء.

يؤدي ذلك إلى أنه قد تستغرق استدعاءات توابع الوحدة EZSheets عدة ثوانٍ قبل أن تعيد شيئًا ما. إذا أردتَ عرضَ حجم استخدامك لواجهة برمجة التطبيقات أو زيادةَ حصتك، فانتقل إلى صفحة IAM & Admin Quotas للتعرف على كيفية الدفع مقابل زيادة حجم الاستخدام. إذا أردتَ التعامل مع استثناءات HttpError بنفسك، فيمكنك ضبط ezsheets.IGNORE_QUOTA على القيمة True، وسترفع توابع الوحدة EZSheets هذه الاستثناءات عندما تواجهها.

مشاريع للتدريب

حاول كتابة البرامج التي تؤدي المهام التي سنوضّحها فيما يلي لكسب خبرة عملية أكبر.

برنامج لتنزيل بيانات نماذج جوجل Google Forms

تتيح لك نماذج جوجل إنشاء نماذج بسيطة عبر الإنترنت تسهّل جمع المعلومات من الأشخاص، حيث تُخزَّن المعلومات التي يدخلها هؤلاء الأشخاص في النموذج ضمن جداول بيانات جوجل. جرّب كتابة برنامجٍ يمكنه تنزيل معلومات النموذج التي أرسلها المستخدمون تلقائيًا، لذا انتقل إلى نماذج جوجل وأنشئ نموذجًا جديدًا، حيث سيكون هذا النموذج فارغًا، ثم أضِف الحقول إلى النموذج الذي يطلب من المستخدم اسمه وعنوان بريده الإلكتروني، ثم انقر على زر "إرسال Send" في الجزء العلوي الأيمن للحصول على رابط لنموذجك الجديد مثل الرابط https://goo.gl/forms/QZsq5sC2Qe4fYO592/‎، وحاول إدخال بعض الأمثلة على الردود في هذا النموذج.

انقر على الزر الأخضر "Create Spreadsheet" في تبويب "الردود Responses" في نموذجك لإنشاء جدول بيانات جوجل الذي سيحتوي على الردود التي يرسلها المستخدمون. يُفترَض أن تشاهد إجاباتك في الصفوف الأولى من جدول البيانات. اكتب بعد ذلك سكربت بايثون مع استخدام الوحدة EZSheets لجمع قائمة بعناوين البريد الإلكتروني في جدول البيانات.

برنامج لتحويل جداول البيانات إلى تنسيقات أخرى

يمكنك استخدام جداول بيانات جوجل لتحويل ملف جدول بيانات إلى تنسيقات أخرى، لذا جرّب كتابة سكربت يمرر ملفًا مُرسَلًا إلى الدالة upload()‎.نزّل جدول البيانات بعد رفعه على جداول بيانات جوجل باستخدام الدوال downloadAsExcel()‎ و downloadAsODS()‎ وغيرها من الدوال المماثلة لإنشاء نسخة من جدول البيانات بتنسيقات أخرى.

برنامج للعثور على الأخطاء في جدول البيانات

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

>>> import ezsheets
>>> ss = ezsheets.Spreadsheet('1jDZEdvSIh4TmZxccyy0ZXrH-ELlrwq8_YYiZrEOB4jg')

أعمدة الورقة الأولى في جدول البيانات هي عدد حبات الفاصولياء في الجرة "Beans per Jar" وعدد الجِرار "Jars" وعدد حبات الفاصولياء الكلي "Total Beans"، حيث ينتج العمود "Total Beans" من ضرب الأعداد الموجودة في العمودين "Beans per Jar" و "Jars"، ولكن يوجد خطأ في أحد الصفوف البالغ عددها 15000 صفًا في هذه الورقة. يُعَد ذلك عددًا كبيرًا جدًا من الصفوف التي لا يمكن التحقق منها يدويًا، ولكن يمكنك كتابة سكربت يتحقق من العمود "Total Beans".

يمكنك الوصول إلى الخلايا الفردية في صف باستخدام ss[0].getRow(rowNum)‎، حيث ss هو كائن Spreadsheet و rowNum هو رقم الصف، وتذكّر أن أرقام الصفوف في جداول بيانات جوجل تبدأ من العدد 1 وليس من 0. ستكون قيم الخلايا سلاسلًا نصية، لذا يجب تحويلها إلى أعداد صحيحة حتى يتمكّن برنامجك من العمل معها. يُقيَّم التعبير int(ss[0].getRow(2)[0]) * int(ss[0].getRow(2)[1]) == int(ss[0].getRow(2)[2])‎ على القيمة True إذا احتوى الصف على القيمة الإجمالية الصحيحة، لذا ضع هذا الشيفرة البرمجية في حلقة لتحديد الصف الموجود في الورقة الذي يحتوي على القيمة الإجمالية غير الصحيحة.

الخلاصة

يُعَد تطبيق جداول بيانات جوجل تطبيقًا شائعًا لجداول البيانات عبر الإنترنت التي تعمل في متصفحك. يمكنك تنزيل جداول البيانات وإنشاؤها وقراءتها وتعديلها باستخدام الوحدة الخارجية EZSheets التي تمثّل جداول البيانات بوصفها كائنات Spreadsheet التي تحتوي على قائمة مرتبة من كائنات Sheet، وتحتوي كل ورقة على أعمدة وصفوف من البيانات التي يمكنك قراءتها وتحديثها بطرق متعددة.

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

ملاحظة: يمكنك الحصول على التوثيق الكامل لميزات الوحدة EZSheet من موقعها الرسمي.

ترجمة -وبتصرُّف- للمقال Working with Google Sheets لصاحبه Al Sweigart.